Introduction
Over the years, I've seen several object-relational-mappers (ORMs) come along for .NET and always ultimately end up being somewhat disappointed with the end result. I remember seeing a preview of LINQ-to-SQL being absolutely blown away by the speed and ease with which you could generate code which would manage the movement of data between your relational database and your business object model. And all without having to hand-roll any SQL! A little later, it was Entity Framework which, at the time at least, seemed to be like LINQ-to-SQL but even better!
However, the time eventually came when I had to use some of these technologies in real-world development projects and it was then that some of their limitations became apparent. From cumbersome XML definition files to sub-optimal performance, I have spent so much time implementing 'fixes' and 'workarounds' to try and shoe-horn a framework into a project's architecture and get it to perform the way I want, that now (unless it is a very simple 1-tier application that needs to be developed rapidly), I prefer to go back to using the stalwart ADO.NET classes of yesteryear, as they offer the flexibility and control over my data access layer that I often find is taken away from me when using some of these ORMs.
That was until a colleague told me about Dapper.NET...
Dapper.NET is an Open-Source, lightweight ORM written by the developers behind Stack Overflow. It is simple to use, and is compatible with any database which implements a provider for .NET (i.e.: provides an implementation of the IDbConnection interface). For more information, check out the project's website.
The aim of this article is to give a brief introduction to Dapper.NET along with some examples, and hopefully demonstrate why I like this product.
Getting Dapper
At the time of writing, there are no pre-compiled binaries available for Dapper, so you have to download the source code from the website and compile it yourself. This is no big deal as the project is quite small and has no other dependencies. When you open the solution, the project you are interested in is the one simply called "Dapper" (or "Dapper NET35" if you are using .NET 3.5).
Using Dapper
Dapper is implemented as a series of Extension Methods which can be called on any object which implements the IDbConnection interface. In the following examples, I am going to use SQL-Server, specifically the AdventureWorks sample database.
The Query() Method
The Query() Extension Method and its overloads are used, as the name suggests, for extracting information from the database and using it to populate our business object model.
In this example, we are going to populate a collection of SubCategory objects from the database. Here is our POCO SubCategory class:
public class Category
{
public int Id { get; set; }
public string Name { get; set; }
public DateTime ModifiedOn { get; set; }
}
public class SubCategory : Category
{
public int CategoryId { get; set; }
}
VB.NET
Public Class Category
Public Property Id As Integer
Public Property Name As String
Public Property ModifiedOn As DateTime
End Class
Public Class SubCategory
Inherits Category
Public Property CategoryId As Integer
End Class
And here is the code to populate a collection of SubCategory objects from the database:
public IEnumerable<SubCategory> SelectSubCategories()
{
using (IDbConnection connection = OpenConnection())
{
const string query = "SELECT ProductSubcategoryId AS Id, " +
"ProductCategoryID AS CategoryId, [Name], ModifiedDate AS ModifiedOn " +
"FROM Production.ProductSubcategory";
return connection.Query<SubCategory>(query);
}
}
VB.NET
Public Function SelectSubCategories() As IEnumerable(Of SubCategory)
Using connection As IDbConnection = OpenConnection()
Const query As String = "SELECT ProductSubcategoryId AS Id, " & _
"ProductCategoryID AS CategoryId, [Name], ModifiedDate AS ModifiedOn " + _
"FROM Production.ProductSubcategory"
Return connection.Query(Of SubCategory)(query)
End Using
End Function
Yes, it really is as simple as that! Note that I have used embedded SQL in this example, but I could have just as easily used a Stored Procedure. I have used aliases in the SQL to ensure the columns of the result-set match the properties of the SubCategory class. Dapper does the rest.
Now for getting a single SubCategory out of the database:
public SubCategory SelectSubCategory(int subCategoryId)
{
using (IDbConnection connection = OpenConnection())
{
const string query = "SELECT ProductSubcategoryId AS Id, " +
"ProductCategoryID AS CategoryId, [Name], ModifiedDate AS ModifiedOn " +
"FROM Production.ProductSubcategory " +
"WHERE ProductSubcategoryId = @SubCategoryId";
return connection.Query<SubCategory>(query,
new { SubCategoryId = subCategoryId }).SingleOrDefault();
}
}
VB.NET
Public Function SelectSubCategory(ByVal subCategoryId As Integer) As SubCategory
Using connection As IDbConnection = OpenConnection()
Const query As String = "SELECT ProductSubcategoryId AS Id, " & _
"ProductCategoryID AS CategoryId, [Name], ModifiedDate AS ModifiedOn " + _
"FROM Production.ProductSubcategory " + _
"WHERE ProductSubcategoryId = @SubCategoryId"
Return connection.Query(Of SubCategory)(query, _
New With {.SubCategoryId = subCategoryId}).SingleOrDefault()
End Using
End Function
Here, we pass in a parameter object to the Query() method. The parameter object can be any object whose properties match the SQL parameters used in the query. As the Query() method always returns a collection of objects, we simply call the LINQ SingleOrDefault() method as we know the query should only return 1 or 0 rows.
Dapper also has the ability to populate nested objects using true eager-loading. Consider the Product class which has a property SubCategory, which returns a SubCategory object:
public class Product
{
public int ProductID { get; set; }
public string Name { get; set; }
public string ProductNumber { get; set; }
public DateTime ModifiedDate { get; set; }
public SubCategory SubCategory { get; set; }
}
VB.NET
Public Class Product
Public Property ProductID As Integer
Public Property Name As String
Public Property ProductNumber As String
Public Property ModifiedDate As DateTime
Public Property SubCategory As SubCategory
End Class
Here is the code to populate our business objects:
public IEnumerable<Product> SelectProductsWithSubCategories()
{
using (IDbConnection connection = OpenConnection())
{
const string query = "SELECT p.ProductID, p.Name, p.ProductNumber, " +
"p.MakeFlag, p.FinishedGoodsFlag, p.Color, p.SafetyStockLevel, " +
"p.ReorderPoint, p.StandardCost, p.ListPrice, p.Size, p.SizeUnitMeasureCode, " +
"p.WeightUnitMeasureCode, p.Weight, p.DaysToManufacture, p.ProductLine, " +
"p.Class, p.Style, p.ProductSubcategoryID, p.ProductModelID, " +
"p.SellStartDate, p.SellEndDate, p.DiscontinuedDate, p.ModifiedDate, " +
"s.ProductSubcategoryId AS Id, s.ProductCategoryID AS CategoryId, " +
"s.[Name], s.ModifiedDate AS ModifiedOn " +
"FROM Production.Product p " +
"LEFT OUTER JOIN Production.ProductSubcategory " +
"s ON s.ProductSubcategoryId = p.ProductSubcategoryID";
return connection.Query<Product, SubCategory, Product>(query,
(product, subCategory) => { product.SubCategory = subCategory; return product; });
}
}
VB.NET
Public Function SelectProductsWithSubCategories() As IEnumerable(Of Product)
Using connection As IDbConnection = OpenConnection()
Const query As String = "SELECT p.ProductID, p.Name, p.ProductNumber, " & _
"p.MakeFlag, p.FinishedGoodsFlag, p.Color, " & _
"p.SafetyStockLevel, p.ReorderPoint, " & _
"p.StandardCost, p.ListPrice, p.Size, p.SizeUnitMeasureCode, " & _
"p.WeightUnitMeasureCode, p.Weight, p.DaysToManufacture, " & _
"p.ProductLine, p.Class, p.Style, p.ProductSubcategoryID, " & _
"p.ProductModelID, p.SellStartDate, p.SellEndDate, " & _
"p.DiscontinuedDate, p.ModifiedDate, " + _
"s.ProductSubcategoryId AS Id, s.ProductCategoryID " & _
"AS CategoryId, s.[Name], s.ModifiedDate AS ModifiedOn " + _
"FROM Production.Product p " + _
"LEFT OUTER JOIN Production.ProductSubcategory " & _
"s ON s.ProductSubcategoryId = p.ProductSubcategoryID"
Return connection.Query(Of Product,
SubCategory, Product)(query, Function(product, subCategory)
product.SubCategory = subCategory
Return product
End Function)
End Using
End Function
Here, the Query() takes type-parameters of the business objects involved as well as the type of object to return. As in previous examples, the first parameter passed into the method is the SQL query. The second is a mapping function which describes how the two objects should be nested (i.e., setting the SubCategory property of the Product object to the SubCategory object).
If the type-parameter is omitted from the Query() method, a collection of dynamic objects is returned, whose properties match the columns in the result-set. Take a look at this example, which is used to get the thumbnail photo for a single product:
public byte[] SelectThumbnail(int productId)
{
using (IDbConnection connection = OpenConnection())
{
const string query = "SELECT pp.ThumbNailPhoto " +
"FROM Production.ProductPhoto pp " +
"INNER JOIN Production.ProductProductPhoto " +
"ppp ON ppp.ProductPhotoID = pp.ProductPhotoID " +
"WHERE ppp.ProductID = @ProductId";
dynamic result = connection.Query(query,
new { ProductId = productId }).SingleOrDefault();
return result != null ? result.ThumbNailPhoto : null;
}
}
VB.NET
Public Function SelectThumbnail(ByVal productId As Integer) As Byte()
Using connection As IDbConnection = OpenConnection()
Const query As String = "SELECT pp.ThumbNailPhoto " + _
"FROM Production.ProductPhoto pp " + _
"INNER JOIN Production.ProductProductPhoto ppp " & _
"ON ppp.ProductPhotoID = pp.ProductPhotoID " + _
"WHERE ppp.ProductID = @ProductId"
Dim result As Object = connection.Query(query, _
New With {.ProductId = productId}).SingleOrDefault()
Return If(Not result Is Nothing, result.ThumbNailPhoto, Nothing)
End Using
End Function
The Execute() Method
Just as the Query() method is used to get data out of the database, the Execute() method is used in situations where we are not retrieving data (e.g.: INSERT, UPDATE, and DELETE data). Its use, however, is very similar to the Query() method, except that it always returns an integer (the number of rows affected) instead of a collection of objects.
In this example, we are going to insert a new SubCategory into the database:
public int InsertSubCategory(SubCategory subCategory)
{
using (IDbConnection connection = OpenConnection())
{
const string query =
"INSERT INTO Production.ProductSubcategory(ProductCategoryID, [Name]) " +
"VALUES (@CategoryId, @Name)";
int rowsAffectd = connection.Execute(query, subCategory);
SetIdentity<int>(connection, id => subCategory.Id = id);
return rowsAffectd;
}
}
VB.NET
Public Function InsertSubCategory(ByVal subCategory As SubCategory) As Integer
Using connection As IDbConnection = OpenConnection()
Const query As String = _
"INSERT INTO Production.ProductSubcategory(ProductCategoryID, [Name]) " + _
"VALUES (@CategoryId, @Name)"
Dim rowsAffected As Integer = connection.Execute(query, subCategory)
SetIdentity(Of Integer)(connection, Sub(id) subCategory.Id = id)
Return rowsAffected
End Using
End Function
As with the Query() method, the Execute() method takes a parameter object. As the names of the SQL parameters match the properties of the SubCategory object itself, I simply use that as the parameter object.
I have also created a convenient method for assigning the identity value, generated by the database, to our POCO object:
protected static void SetIdentity<T>(IDbConnection connection, Action<T> setId)
{
dynamic identity = connection.Query("SELECT @@IDENTITY AS Id").Single();
T newId = (T)identity.Id;
setId(newId);
}
VB.NET
Protected Shared Sub SetIdentity(Of T)(ByVal connection As _
IDbConnection, ByVal setId As Action(Of T))
Dim identity As Object = _
connection.Query("SELECT @@IDENTITY AS Id").Single()
Dim newId As T = CType(identity.Id, T)
setId(newId)
End Sub
For the sake of completeness, here is the code for updating a SubCategory:
public int UpdateSubCategory(SubCategory subCategory)
{
using (IDbConnection connection = OpenConnection())
{
const string query = "UPDATE Production.ProductSubcategory " +
"SET ProductCategoryID = @CategoryId, " +
"[Name] = @Name, " +
"ModifiedDate = @ModifiedOn " +
"WHERE ProductSubcategoryID = @Id";
return connection.Execute(query, subCategory);
}
}
VB.NET
Public Function UpdateSubCategory(ByVal subCategory As SubCategory) As Integer
Using connection As IDbConnection = OpenConnection()
Const query As String = "UPDATE Production.ProductSubcategory " + _
"SET ProductCategoryID = @CategoryId, " + _
"[Name] = @Name, " + _
"ModifiedDate = @ModifiedOn " + _
"WHERE ProductSubcategoryID = @Id"
Return connection.Execute(query, subCategory)
End Using
End Function
And deleting a SubCategory:
public int DeleteSubCategory(SubCategory subCategory)
{
using (IDbConnection connection = OpenConnection())
{
const string query = "DELETE FROM Production.ProductSubcategory " +
"WHERE ProductSubcategoryID = @Id";
return connection.Execute(query, subCategory);
}
}
VB.NET
Public Function DeleteSubCategory(ByVal subCategory As SubCategory) As Integer
Using connection As IDbConnection = OpenConnection()
Const query As String = "DELETE FROM Production.ProductSubcategory " + _
"WHERE ProductSubcategoryID = @Id"
Return connection.Execute(query, subCategory)
End Using
End Function
Transaction Support
Dapper also supports transactional operations. For example, the following code deletes a product and any related images from the database:
public int DeleteProduct(Product product)
{
using (IDbConnection connection = OpenConnection())
{
const string deleteImageQuery = "DELETE FROM Production.ProductProductPhoto " +
"WHERE ProductID = @ProductID";
const string deleteProductQuery = "DELETE FROM Production.Product " +
"WHERE ProductID = @ProductID";
IDbTransaction transaction = connection.BeginTransaction();
int rowsAffected = connection.Execute(deleteImageQuery,
new { ProductID = product.ProductID }, transaction);
rowsAffected += connection.Execute(deleteProductQuery,
new { ProductID = product.ProductID }, transaction);
transaction.Commit();
return rowsAffected;
}
}
VB.NET
Public Function DeleteProduct(ByVal product As Product) As Integer
Using connection As IDbConnection = OpenConnection()
Const deleteImageQuery As String = "DELETE FROM Production.ProductProductPhoto " + _
"WHERE ProductID = @ProductID"
Const deleteProductQuery As String = "DELETE FROM Production.Product " + _
"WHERE ProductID = @ProductID"
Dim transaction As IDbTransaction = connection.BeginTransaction()
Dim rowsAffected As Integer = connection.Execute(deleteImageQuery, _
New With {.ProductID = product.ProductID}, transaction)
rowsAffected += connection.Execute(deleteProductQuery, _
New With {.ProductID = product.ProductID}, transaction)
transaction.Commit()
Return rowsAffected
End Using
End Function
Stored Procedure Support
As I mentioned earlier, Dapper also supports Stored Procedures. The example below uses a Stored Procedure to get a list of managers for a given employee:
public IEnumerable<Manager> SelectManagers(int employeeId)
{
using (IDbConnection connection = OpenConnection())
{
const string storedProcedure = "dbo.uspGetEmployeeManagers";
return connection.Query<Manager>(storedProcedure,
new { EmployeeID = employeeId }, commandType: CommandType.StoredProcedure);
}
}
VB.NET
Public Function SelectManagers(ByVal employeeId As Integer) As IEnumerable(Of Manager)
Using connection As IDbConnection = OpenConnection()
Const storedProcedure As String = "dbo.uspGetEmployeeManagers"
Return connection.Query(Of Manager)(storedProcedure, _
New With {.EmployeeID = employeeId}, commandType:=CommandType.StoredProcedure)
End Using
End Function
Summary
To summarize, Dapper.NET is extremely easy to use and offers a high degree of flexibility with regard to how data is accessed and mapped to any business object. It also has the advantage of not requiring a cumbersome XML (or similar) definition file to set it up.
For further information on Dapper, take a look at the official project homepage.
My code examples are taken from a very simple MVC application which is available here.
