簡介

2022-04-21 09:41 更新

什么是Dapper

Dapper是一個簡單的.NET對象映射器,在速度方面具有"King of Micro ORM"的頭銜,幾乎與使用原始的ADO.NET數(shù)據(jù)讀取器一樣快。ORM是一個對象關系映射器,它負責數(shù)據(jù)庫和編程語言之間的映射。

Dapper通過擴展IDbConnection提供一些有用的擴展方法去查詢您的數(shù)據(jù)庫。

Dapper是如何工作的

它可以分為三個步驟:

  • 創(chuàng)建一個IDbConnection接口對象;
  • 編寫一個查詢SQL來執(zhí)行CRUD操作;
  • 將查詢SQL作為Execute方法的參數(shù)傳遞。

安裝

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

PM> Install-Package Dapper

要求

Dapper可以與任何數(shù)據(jù)庫提供者一起工作,因為沒有數(shù)據(jù)庫特定的實現(xiàn)。

方法

Dapper會用以下幾個方法擴展您的IDbConnection接口:

string sqlInvoices = "SELECT * FROM Invoice;";
string sqlInvoice = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID;";
string sp = "EXEC Invoice_Insert";

using (var connection = My.ConnectionFactory())
{
        // 執(zhí)行普通SQL
    var invoices = connection.Query<Invoice>(sqlInvoices).ToList();
    // 執(zhí)行帶參數(shù)的SQL
    var invoice = connection.QueryFirstOrDefault(sqlInvoice, new {InvoiceID = 1});
    // 執(zhí)行存儲過程 
    var affectedRows = connection.Execute(sp, new { Param1 = "Single_Insert_1" }, commandType: CommandType.StoredProcedure);
}

參數(shù)

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

// 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,
    new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
    commandType: CommandType.StoredProcedure);

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

結果

查詢方法返回的結果可以映射到以下幾種類型:

string sql = "SELECT * FROM Invoice;";

using (var connection = My.ConnectionFactory())
{
    connection.Open();

    var anonymousList = connection.Query(sql).ToList();
    var invoices = connection.Query<Invoice>(sql).ToList();
}

工具

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


以上內容是否對您有幫助:
在線筆記
App下載
App下載

掃描二維碼

下載編程獅App

公眾號
微信公眾號

編程獅公眾號