Dapper: An Introduction

In my last post I argued the case for using a micro ORM. In this post I will introduce Dapper, show you how to set it up and how to do basic CRUD operations.

All these examples will be compared against the same operation in Entity Framework so we can compare and contrast the code produced.

For these examples I will be using the relevant Nuget as much as possible. The object used will be the examples from the Entity Framework samples package found here.

So without further ado lets get started.

Getting Started

Installation of Dapper is simple using the Nuget package simple using the following commands:

Install-Package Dapper

All that Dapper needs to perform queries is on option connection, unlike Entity Framework which requires an instance of the DbContext. To get an open connection I’ve created a simple class that returns a connection:

Simply calling this class will return an open database connection. Good practice would be to get the connection string from the config file rather that hard coding the value. Note that I’m creating an SqlConnection; if you want to use Sql Compact Edition then you need to use a SqlCeConnection.

If you want to profile your SQL using the excellent mini profiler (which I recommend you do) then you need to wrap that connection in a ProfiledDbConnection using the following code:

Lets See The Code

Now that we have seen how to get a database connection lets look at the more important aspect of getting results out of the database.

Lists

To start with lets just get a list of Posts from the database:

How to return a list filtered by some value.

As you can see you need to have more familiarity with SQL if you want to be using Dapper. If you’re familiar with SQL then there’s nothing scary in the code and the Dapper methods are just as simple to use as the Entity Framework.

Single

Next lets look at returning just a single value from the database. As with returning the lists there is not a huge amount of difference between the code; Entity Framework is slightly less code and easier to read at a glance.

Insert

Looking at the basics of returning data from the database using Dapper, it is not that difficult compared to Entity Framework. The differences between the two start to appear when we do inserts.

Note that the actual insert to the database doesn’t occur when you add the post to the post collection, but when you call db.SaveChanges(). When this is called the Id property is then set with the identity column in the database.

As you can see the inserts for Dapper get slightly more complex as we also return the new identity created. In the Entity Framework this is all bundled into the SaveChanges method and kept hidden from the programmer.

Note that if you’re using SQL Compact Edition you cannot combine two or more SQL statements in the same batch, so the second statement to get the id would need to be wrapped in a separate statement. Also when using SQL Compact Edition, @@IDENTITY will need to be used as scope_identity() is not supported. The following code is an example of how to insert if you’re using SQL Compact Edition:

Update

Updating using Dapper is simpler than inserting but it still requires you to know more SQL than if you’re using the Entity Framework.

As with inserting, when updating using Entity Framework you must call SaveChanges() before the changes are committed to the database. Any changes made to the object between returning it from the database and committing it are tracked and will be passed to the database.

Delete

As with all queries in Dapper the SQL needs to be entered to delete items as well. Here is a sample of Entity Framework:

Again as with inserting and updating the SaveChanges() method needs to be called in order to commit the changes to the database. Compare this to Dapper:

With Dapper, if you already know the property values of the object then you don’t need to return the object from the database first, you can simply call the SQL delete statement with the criteria.

Conclusion

If you want to be using Dapper in everyday life then you do need to have a good knowledge of SQL and in writing SQL statements. I’ve just covered the basics here and as you can see it doesn’t take much for Dapper queries to become more complex and longer than the equivalent Entity Framework queries.

However Dapper is still incredibly powerful, and in the next part of this series we will be looking at more complex examples such as paging and additional libraries available to Dapper that makes life easier.