使用 Dapper 輕鬆地將資料庫操作帶入 .NET 開發世界

Dapper 是一個開源的輕量級 ORM (Object-Relational Mapping) 框架，可以直接將查詢結果關連到動態物件。Dapper 不僅支援 SQL Server，還可以在各種主流的關聯式資料庫中使用，包括 MySQL、PostgreSQL、SQLite 等。我們就來看看它強大的功能吧。

環境準備

安裝SQL Server並匯入資料

這篇文章會示範利用Dapper連線到SQL Server，首先要到SQL_Server的網站安裝SQL Server，當然如果有其他的資料庫也可以安裝別的。安裝方式就不多說介紹。

接者到這邊下載著名的北風資料庫範例，本文會下載2022的版本，等等我們需要把還原檔匯入到資料庫。

下載完之後，我們要利用SQL Server Management Studio (SSMS)，將備份還原到 SQL Server 的執行個體，如果沒有安裝的話可以從這邊下載安裝。

將bak檔還原之後，資料庫就會有北風資料庫的測試資料了，等等會拿Dapper來操作db.Customers、db.Territories、db.Orders這些Table。

安裝 Dapper 套件

透過Nuget安裝Dapper套件。

也需要安裝SqlClient，用來連線SQL Server的套件。

建立對應資料表的類別

等等拿Customers、Territories還有Orders資料表來使用，先分別建立它們對應的類別，請注意欄位的資料類型。

C#
public class Customers
{
    public string CustomerID { get; set; } = string.Empty;

    public string CompanyName { get; set; } = string.Empty;

    public string ContactName { get; set; } = string.Empty;

    public string ContactTitle { get; set; } = string.Empty;

    public string City { get; set; } = string.Empty;

    public string Region { get; set; } = string.Empty;

    public string PostalCode { get; set; } = string.Empty;

    public string Country { get; set; } = string.Empty;

    public string Phone { get; set; } = string.Empty;

    public string Fax { get; set; } = string.Empty;
}
C#
public class Territories
{
    public string TerritoryID { get; set; } = string.Empty;

    public string TerritoryDescription { get; set; } = string.Empty;

    public int RegionID { get; set; }
}
C#
public class Orders
{
    public int OrderID { get; set; }

    public string CustomerID { get; set; } = string.Empty;

    public int EmployeeID { get; set; }

    public DateTime OrderDate { get; set; }

    public DateTime RequiredDate { get; set; }

    public DateTime ShippedDate { get; set; }

    public int ShipVia { get; set; }

    public decimal Freight { get; set; }

    public string ShipName { get; set; } = string.Empty;

    public string ShipAddress { get; set; } = string.Empty;

    public string ShipCity { get; set; } = string.Empty;

    public string ShipRegion { get; set; } = string.Empty;

    public string ShipPostalCode { get; set; } = string.Empty;

    public string ShipCountry { get; set; } = string.Empty;
}

建立連線字串

建立SQL Connecting連線主體，我是使用本機的SQL Server，目標的資料庫是master，connectstring設定成這樣就好。如果要連其他資料庫的話詳細的連線字串可以參考這篇來調整。

C#
SqlConnection conn = new SqlConnection("Server=.;Initial Catalog=master;Integrated Security=true;");

普通Query語法

Query：將SQL 查詢結果轉換成IEnumerable型態物件。

C#
SqlConnection conn = new SqlConnection("Server=.;Initial Catalog=master;Integrated Security=true;");
var sql = "SELECT * FROM Customers";
var results = conn.Query(sql);
C#
SqlConnection conn = new SqlConnection("Server=.;Initial Catalog=master;Integrated Security=true;");
var sql = "SELECT * FROM Customers";
var results = conn.Query<Customers>(sql);

回傳的結果會是IEnumerable的列舉型態。

查詢使用Query<T>的方法，<T>傳入我們要的物件，如此一來可以將搜尋結果轉換成物件；如果直接用Query，回傳會是dynamic的型別。

QueryFirst：回傳第一筆資料，如果資料沒有符合會Exception

C#
SqlConnection conn = new SqlConnection("Server=.;Initial Catalog=master;Integrated Security=true;");
var sql = "SELECT * FROM Customers";
var results = conn.QueryFirst<Customers>(sql);

回傳的結果會是第一筆資料的物件，可以直接存取物件的屬性。

如果沒有符合條件則會Exception。

QueryFirstOrDefault：回傳第一筆資料，如果沒有符合回傳null

C#
SqlConnection conn = new SqlConnection("Server=.;Initial Catalog=master;Integrated Security=true;");
var sql = "SELECT * FROM Customers";
var results = conn.QueryFirstOrDefault<Customers>(sql);

回傳的結果會是第一筆資料的物件，同QueryFirst可以存取物件的屬性，如果沒有符合則回傳null。

這張圖片的 alt 屬性值為空，它的檔案名稱為 image-14.png

QuerySingle：回傳結果唯一的資料，如果沒有結果或有多個結果則Exception。

回傳的結果是條件唯一的資料，同QueryFirst，如果沒有符合或有多筆資料會Exception。

QuerySingleOrDefault：回傳結果唯一的資料，如果沒有結果則為null，有多個結果則Exception。

回傳的結果是條件唯一的資料，同QueryFirst，如果沒有符合則會錯誤。

QueryAsync、QueryFirstAsync、QueryFirstOrDefaultAsync、QuerySingleAsync、QuerySingleOrDefaultAsync：對應功能的非同步方法

Query總結

以下比較不同Query的差異

當沒有資料時當有一筆資料時當有多筆資料時
Query長度為0的IenumerableIenumerableIenumerable
QueryFirstException第一筆物件回傳第一筆物件
QueryFirstOrDefaultnull第一筆物件回傳第一筆物件
QuerySingleException第一筆物件Exception
QuerySingleOrDefaultnull第一筆物件Exception
不同Query的差異

QueryMultiple：將多個SQL指令對應到類別。

C#
SqlConnection conn = new SqlConnection("Server=.;Initial Catalog=master;Integrated Security=true;");
var sql = "SELECT * FROM Customers;SELECT * FROM Territories;";
var results = conn.QueryMultiple(sql);

var territories = results.Read<Territories>();
////System.Collections.Generic.List`1[Dapper_Example.Territories]
var customers = results.Read<Customers>();
///System.Collections.Generic.List`1[Dapper_Example.Customers]

dapper的指令第一個是丟SQL語法，第二個參數則是丟查詢參數，Dapper就可以將輸入的參數轉成SQL參數。

使用Parameters 參數查詢

Anonymous匿名參數

C#
SqlConnection conn = new SqlConnection("Server=.;Initial Catalog=master;Integrated Security=true;");
var sql = "SELECT * FROM Customers where CustomerID =  @CustomerID;";
var results = conn.QueryFirst(sql, new { CustomerID = "ALFKI" });

Dynamic 動態參數

C#
SqlConnection conn = new SqlConnection("Server=.;Initial Catalog=master;Integrated Security=true;");
var sql = "SELECT * FROM Customers where CustomerID =  @CustomerID;";
var results = conn.QueryFirst(sql, new Customers { CustomerID = "ALFKI" });
C#
SqlConnection conn = new SqlConnection("Server=.;Initial Catalog=master;Integrated Security=true;");
var sql = "SELECT * FROM Customers where CustomerID =  @CustomerID;";
// 建立動態參數
var parameters = new DynamicParameters();
parameters.Add("@CustomerID", "ALFKI");
var results = conn.QueryFirst(sql, parameters);

在做in條件搜尋時，只需要傳入list的參數即可。

C#
SqlConnection conn = new SqlConnection("Server=.;Initial Catalog=master;Integrated Security=true;");
var sql = "SELECT * FROM Customers where CustomerID in  @CustomerID;";
var results = conn.Query(sql, new { CustomerID = new[] { "ALFKI", "ANATR" } });

在dapper也提供不同的資料表join起來，分別印射不同的類別中

首先我們要產生一段SQL語法，要把Orders和Customers兩張表結合起來，並使用CustomerID的欄位名稱當作join的條件

SQL
select * from Orders left join Customers
on Orders.CustomerID = Customers.CustomerID
C#
SqlConnection conn = new SqlConnection("Server=.;Initial Catalog=master;Integrated Security=true;");
var sql = "select * from Orders left join Customers on Orders.CustomerID = Customers.CustomerID";
var products = conn.Query<Orders, Customers, Tuple<Orders, Customers>>(sql,
(orders, customers) =>
{
    orders.CustomerID = customers.CustomerID;
    return Tuple.Create(orders, customers);
},
splitOn: "CustomerID");

Query的語法裡面可以指定要印射的類別，並可以指定一個以上。這個範例泛型的前兩個參數我拿Orders和Customers的類別進去，也就是兩個join的table，第三個則是回傳的類型我是使用Tuple。

第一個參數是丟sql語法，第二的參數要告訴Dapper mapping的值，類似sql的on語法，第三的參數則join的表用哪個欄位區分。

如此一來兩個Model就可以mapping到瞜，當然還可以mapping更多Model，處理就會更複雜些了。

更多的說明可以參考這篇

insert

C#
SqlConnection conn = new SqlConnection("Server=.;Initial Catalog=master;Integrated Security=true;");
var sql = "INSERT INTO Customers VALUES (@CustomerID,@CompanyName,@ContactName,@ContactTitle,@Address,@City,@Region,@PostalCode,@Country,@Phone,@Fax)";
///可以輸入多筆資料
///result會回傳受影響的資料
var result = conn.Execute(sql, new[]{
    new { CustomerID = "HELLO", CompanyName = "Ooorito", ContactName="Ooorito", ContactTitle = "Owner" , Address="Taipei", City = "Taipei" ,Region = "TW" ,PostalCode = "20001" , Country="Taiwan" , Phone = "091234567" , Fax = "091234567"},
    new { CustomerID = "WORLD", CompanyName = "Ooorito", ContactName="Ooorito2", ContactTitle = "Owner" , Address="Taipei", City = "Taipei" ,Region = "TW" ,PostalCode = "20001" , Country="Taiwan" , Phone = "091234567" , Fax = "091234567"},
});

Update

C#
SqlConnection conn = new SqlConnection("Server=.;Initial Catalog=master;Integrated Security=true;");
var sql = "update Customers set Phone = @Phone where  CustomerID = @CustomerID";
var result = conn.Execute(sql, new { Phone = "123", CustomerID = "HELLO" });

Delete

C#
SqlConnection conn = new SqlConnection("Server=.;Initial Catalog=master;Integrated Security=true;");
var sql = "delete from Customers where CustomerID = @CustomerID";
var result = conn.Execute(sql, new { CustomerID = "HELLO" });
標籤
分享這篇文章

其他文章

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *