Dapper dot net basics

Dapper is a .NET-based micro-ORM (Object Relational Mapping) system created by the Stack Overflow guys, that makes it super-easy to query a database and map results to an object (amongst other things). You can check out the site with examples and more information here.

Benefits of using Dapper compared with a full-blown ORM solutions such as NHibernate include:

  • no need to worry about entity mappings – Dapper works all this stuff out for you
  • you don’t need to flush the connection every time you execute a query
  • it’s twice as fast as NHibernate for select queries (with a few limitations, explained in more detail on their website)

It’s crazily easy to install into your project, and ridiculously simple to use – I was able to get a quick test project up and running in less than 30 minutes, using Dapper to execute a SQL query and map the output to an object. I thought I would share what I did with you!

1. Set up the basics:
– install the dapper package through NuGet (install-package dapper)
– add the reference to the top of your class: using Dapper;

2. Now set up your SQL connection in your usual way

// set up database connection…
connection = new CreateConnection()// set up your sql query and create an instance of the object you want to populate with the results
var sql = “blah blah blah”;
User user = new User();

3. add your Dapper code:

// execute the query and populate the object with the results
using (connection)
{
connection.Open();
var user = connection.Query< User>(sql);  return user.FirstOrDefault();
}

Now compare step 3 with the code I previously used below, which does exactly the same thing but using much more code to achieve it:

using (connection)
{
var sqlCmd = new SqlCommand (sql, connection);
connection.Open();  SqlDataReader reader = sqlCmd.ExecuteReader();if (reader.HasRows)
{
while (reader.Read())
{
user.Id = reader.GetInt32(0);
user.Username = reader.GetString(1);
user.Email = reader.GetString(2);
user.Password = reader.GetString(3);
user.Firstname = reader.GetString(4);
user.Lastname = reader.GetString(5);
}
}
}

I’m sure there are drawbacks to using Dapper (for example it won’t track changes to the User object above, and allow you to save those changes back to the DB as NHibernate does), but I still think it’s worth checking out further to see for yourself.

Tagged with: , ,
2 comments on “Dapper dot net basics