Dapper.NET 浅析
4.92/5 (55投票s)
Dapper.NET 是一个开源、轻量级的 ORM，易于使用，并且与任何实现了 .NET 提供程序的数据库兼容。
引言
多年来，我看到了许多 .NET 对象关系映射器 (ORM)，但最终总是对结果感到有些失望。我记得看到 LINQ-to-SQL 的预览时，它在生成代码以管理关系数据库与业务对象模型之间数据移动的速度和简便性给我留下了深刻的印象。而且这一切都不需要手动编写 SQL！稍后，又出现了 Entity Framework，当时至少它看起来像 LINQ-to-SQL，但更好！
然而，当我在实际开发项目中不得不使用其中一些技术时，它们的局限性就显现出来了。从繁琐的 XML 定义文件到不理想的性能，我花了大量时间实现“修复”和“变通方法”来尝试将框架“硬塞”到项目架构中，并使其达到我想要的性能。所以现在（除非是一个需要快速开发的非常简单的单层应用程序），我更愿意回到使用老式的 ADO.NET 类，因为它们提供了我经常在某些 ORM 中失去的数据访问层的灵活性和控制权。
直到一位同事告诉我 Dapper.NET...
Dapper.NET 是一个开源、轻量级的 ORM，由 Stack Overflow 背后的开发人员编写。它易于使用，并且与任何实现了 .NET 提供程序的数据库兼容（即：提供了 IDbConnection 接口的实现）。有关更多信息，请查看该项目的 网站。
本文旨在简要介绍 Dapper.NET 并附带一些示例，希望能展示我为何喜欢这个产品。
获取 Dapper
在撰写本文时，Dapper 没有提供预编译的二进制文件，因此您需要从 网站下载源代码并自行编译。这没什么大不了的，因为项目很小，而且没有其他依赖项。当您打开解决方案时，您感兴趣的项目就是那个只称为“Dapper”（如果您使用 .NET 3.5，则为“Dapper NET35”）的项目。
使用 Dapper
Dapper 实现为一系列扩展方法，可以调用在实现了 IDbConnection 接口的任何对象上。在接下来的示例中，我将使用 SQL Server，特别是 AdventureWorks 示例数据库。
Query() 方法
正如其名称所示，Query() 扩展方法及其重载用于从数据库中提取信息并使用它们来填充我们的业务对象模型。
在此示例中，我们将从数据库填充 SubCategory 对象的集合。这是我们的 POCO SubCategory 类
// C#
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
' Visual Basic
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
这是从数据库填充 SubCategory 对象集合的代码
// C#
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
' Visual Basic
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
是的，就是这么简单！请注意，在此示例中我使用了嵌入式 SQL，但我也同样可以使用存储过程。我在 SQL 中使用了别名，以确保结果集的列与 SubCategory 类的属性匹配。Dapper 会处理其余的。
现在来看看如何从数据库中获取单个 SubCategory
// C#
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
' Visual Basic
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
在这里，我们将一个参数对象传递给 Query() 方法。参数对象可以是任何属性与查询中使用的 SQL 参数匹配的对象。由于 Query() 方法始终返回一个对象集合，因此我们只需调用 LINQ 的 SingleOrDefault() 方法，因为我们知道查询应该只返回 1 或 0 行。
Dapper 还能够使用真正的延迟加载来填充嵌套对象。考虑 Product 类，它有一个 SubCategory 属性，返回一个 SubCategory 对象
// C#
public class Product
{
public int ProductID { get; set; }
public string Name { get; set; }
public string ProductNumber { get; set; }
// NOTE: Some properties omitted for brevity.
public DateTime ModifiedDate { get; set; }
public SubCategory SubCategory { get; set; }
}
VB.NET
' Visual Basic
Public Class Product
Public Property ProductID As Integer
Public Property Name As String
Public Property ProductNumber As String
' NOTE: Some properties omitted for brevity.
Public Property ModifiedDate As DateTime
Public Property SubCategory As SubCategory
End Class
这是填充我们的业务对象的代码
// C#
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
' Visual Basic
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
在这里，Query() 接受涉及的业务对象的类型参数以及要返回的对象类型。与前面的示例一样，传递给方法的第一个参数是 SQL 查询。第二个参数是一个映射函数，它描述了两个对象应如何嵌套（即，将 Product 对象的 SubCategory 属性设置为 SubCategory 对象）。
如果从 Query() 方法中省略了类型参数，则会返回一个动态对象集合，其属性与结果集中的列匹配。看看这个示例，它用于获取单个产品的缩略图
// C#
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
' Visual Basic
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
Execute() 方法
就像 Query() 方法用于从数据库获取数据一样，Execute() 方法用于我们*不*检索数据的情况（例如：INSERT、UPDATE 和 DELETE 数据）。然而，它的用法与 Query() 方法非常相似，只是它总是返回一个整数（受影响的行数），而不是一个对象集合。
在此示例中，我们将一个新的 SubCategory 插入数据库
// C#
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
' Visual Basic
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
与 Query() 方法一样，Execute() 方法接受一个参数对象。由于 SQL 参数的名称与 SubCategory 对象本身的属性匹配，我直接使用该对象作为参数对象。
我还创建了一个方便的方法，用于将数据库生成的标识值分配给我们的 POCO 对象
// C#
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
' Visual Basic
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
为了完整起见，这里是更新 SubCategory 的代码
// C#
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
' Visual Basic
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
以及删除 SubCategory 的代码
// C#
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
' Visual Basic
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
事务支持
Dapper 还支持事务操作。例如，以下代码从数据库中删除一个产品及其所有相关图像
// C#
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
' Visual Basic
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
存储过程支持
正如我前面提到的，Dapper 还支持存储过程。下面的示例使用存储过程来获取给定员工的经理列表
// C#
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
' Visual Basic
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
摘要
总而言之，Dapper.NET 非常易于使用，并提供了高度的灵活性，可以根据需要访问数据并将其映射到任何业务对象。它还具有不需要繁琐的 XML（或类似）定义文件即可进行设置的优点。
有关 Dapper 的更多信息，请查看 官方项目主页。
我的代码示例来自一个非常简单的 MVC 应用程序，可在此 处找到。