短小精悍的
什么是小巧玲珑？
Dapper是.NET的简单对象映射器，在速度方面拥有微型ORM之王的标题，几乎与使用原始ADO.NET数据读取器一样快。 ORM是一个对象关系映射器，负责数据库和编程语言之间的映射。
Dapper通过提供有用的扩展方法来查询数据库来扩展IDbConnection。
Dapper如何工作：
- 创建一个IDbConnection对象。
- 编写查询以执行CRUD操作。
- 将查询作为Execute方法中的参数传递。
安装
Dapper通过NuGet安装： https //www.nuget.org/packages/Dapper
PM> Install-Package Dapper
需求
Dapper可以与任何数据库提供程序一起使用，因为没有特定于DB的实现。
方法
Dapper将使用多种方法扩展您的IDbConnection接口：
- 执行
- 询问
- QueryFirst
- QueryFirstOrDefault
- QuerySingle
- QuerySingleOrDefault
- QueryMultiple
string sqlOrderDetails = "SELECT TOP 5 * FROM OrderDetails;";
string sqlOrderDetail = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";
string sqlCustomerInsert = "INSERT INTO Customers (CustomerName) Values (@CustomerName);";
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
var orderDetails = connection.Query<OrderDetail>(sqlOrderDetails).ToList();
var orderDetail = connection.QueryFirstOrDefault<OrderDetail>(sqlOrderDetail, new {OrderDetailID = 1});
var affectedRows = connection.Execute(sqlCustomerInsert, new {CustomerName = "Mark"});
Console.WriteLine(orderDetails.Count);
Console.WriteLine(affectedRows);
FiddleHelper.WriteTable(orderDetails);
FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail });
}
参数
执行和查询方法可以使用多种不同方式的参数：
- 匿名
- 动态
- 名单
- 串
// Anonymous
var affectedRows = connection.Execute(sql,
new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
commandType: CommandType.StoredProcedure);
// Dynamic
DynamicParameters parameter = new DynamicParameters();
parameter.Add("@Kind", InvoiceKind.WebInvoice, DbType.Int32, ParameterDirection.Input);
parameter.Add("@Code", "Many_Insert_0", DbType.String, ParameterDirection.Input);
parameter.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
connection.Execute(sql,
parameter,
commandType: CommandType.StoredProcedure);
int rowCount = parameter.Get<int>("@RowCount");
// List
connection.Query<Invoice>(sql, new {Kind = new[] {InvoiceKind.StoreInvoice, InvoiceKind.WebInvoice}}).ToList();
// String
connection.Query<Invoice>(sql, new {Code = new DbString {Value = "Invoice_1", IsFixedLength =
结果
query方法返回的结果可以映射到多种类型：
- 匿名
- 强类型
- 多重映射
- 多结果
- 多类型
string sqlOrderDetails = "SELECT TOP 10 * FROM OrderDetails;";
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
var anonymousList = connection.Query(sqlOrderDetails).ToList();
var orderDetails = connection.Query<OrderDetail>(sqlOrderDetails).ToList();
Console.WriteLine(anonymousList.Count);
Console.WriteLine(orderDetails.Count);
FiddleHelper.WriteTable(orderDetails);
FiddleHelper.WriteTable(connection.Query(sqlOrderDetails).FirstOrDefault());
}
常用类型
- 异步
- 缓存
- 事务
- 存储过程
// Async
connection.QueryAsync<Invoice>(sql)
// Buffered
connection.Query<Invoice>(sql, buffered: false)
// Transaction
using (var transaction = connection.BeginTransaction())
{
var affectedRows = connection.Execute(sql,
new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
commandType: CommandType.StoredProcedure,
transaction: transaction);
transaction.Commit();
}
// Stored Procedure
var affectedRows = connection.Execute(sql,
new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
commandType: CommandType.StoredProcedure);
