短小精悍的

什么是小巧玲瓏？

Dapper是.NET的簡(jiǎn)單對(duì)象映射器，在速度方面擁有微型ORM之王的標(biāo)題，幾乎與使用原始ADO.NET數(shù)據(jù)讀取器一樣快。 ORM是一個(gè)對(duì)象關(guān)系映射器，負(fù)責(zé)數(shù)據(jù)庫和編程語言之間的映射。

Dapper通過提供有用的擴(kuò)展方法來查詢數(shù)據(jù)庫來擴(kuò)展IDbConnection。

Dapper如何工作：

• 創(chuàng)建一個(gè)IDbConnection對(duì)象。
• 編寫查詢以執(zhí)行CRUD操作。
• 將查詢作為Execute方法中的參數(shù)傳遞。

安裝

Dapper通過NuGet安裝： https //www.nuget.org/packages/Dapper

PM> Install-Package Dapper

需求

Dapper可以與任何數(shù)據(jù)庫提供程序一起使用，因?yàn)闆]有特定于DB的實(shí)現(xiàn)。

方法

Dapper將使用多種方法擴(kuò)展您的IDbConnection接口：

• 執(zhí)行
• 詢問
• 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 });
}

參數(shù)

執(zhí)行和查詢方法可以使用多種不同方式的參數(shù)：

• 匿名
• 動(dòng)態(tài)
• 名單
• 串

// 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 = 

結(jié)果

query方法返回的結(jié)果可以映射到多種類型：

• 匿名
• 強(qiáng)類型
• 多重映射
• 多結(jié)果
• 多類型

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());
}

常用類型

• 異步
• 緩存
• 事務(wù)
• 存儲(chǔ)過程

// 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);