Dapper官方教程翻译1:Dapper是什么?
什么是Dapper?
Dapper是.NET的一个简单的对象映射器,在速度上拥有微ORM之王的头衔,并且几乎与使用原始ADO.NET数据读取器的速度一样快。ORM是一个对象关系映射器,它负责数据库和编程语言之间的映射。Dapper通过提供有用的扩展方法来查询数据库,从而扩展IDbConnection。
如何使用Dapper?
使用Dapper只需要三步:
1.创建IDbConnecton对象
2.写一个CURD语句
3.将语句作为参数传递给Execute方法。
Dapper安装:
使用NuGet命令:Install-Package Dapper
数据库要求:
只要是提供了数据库Provider的数据库,就可以用Dapper。
Dapper扩展方法:
-
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 SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
-
{
-
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 });
-
}
Dapper参数:
-
// 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 = false, Length = 9, IsAnsi = true}}).ToList();
Dapper执行结果
-
string sqlOrderDetails = "SELECT TOP 10 * FROM OrderDetails;";
-
-
using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
-
{
-
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());
-
}
Dapper其他辅助工具
-
// 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);