[ASP.NET] 輕量級ORM - Dapper 使用

一直以來都知道Dapper很好用,但卻一直沒有認真整理他的用法,難得有時間就稍微整理一下該如何去使用。

這篇文章比較長找自己需要的就好。

 

【開發環境】
開發工具:Visual Studio Professional 2015
.Net Framework:4.6.1
套件:Dapper

 

【Dapper是什麼?】
Dapper是.Net平台的一種ORM套件,提供物件關聯對應(ORM)的功能,並且為開源軟體,其撰寫方式與ADO.NET寫法相似。

 

【Dapper特色】

  • Speedy and high performance(快速高效能)
  • Fewer lines of code(減少撰寫的Code)
  • Object mapper(對應物件)
  • Choice of static/dynamic object binding(靜態/動態物件綁定)
  • Easy handling of SQL query(簡易的SQL查詢)
  • Multiple query support(支援多筆查詢)
  • Support and easy handling of stored procedures(支援預存程序)
  • Operating directly on IDbConnection interface(IDbConnection運行)
  • Bulk data insert functionality(大量資料新增)

  

【Dapper效能】

  

【Dapper大小】

很明顯的Dapper小很多,以上兩個版本皆為2018/01/11於Nuget下載之最新版本。

  • Entity Framework 版本 v6.2.0  Size : 5,103KB
  • Dapper 版本 v1.50.4  Size : 167KB

  

【Dapper安裝】
安裝方式其實很容易,就是透過Nuget搜尋Dapper,或使用套件管理主控台去安裝。

Install-Package Dapper -Version 1.50.4

 

【Dapper使用】

Dapper - Query
光是查詢Dapper就提供了很多種方法可以使用,很多方法都跟Lambda用法一樣。

//Query Model
List<MyModel> results = null;
using (SqlConnection conn = new SqlConnection(strConnection))
{
	string strSql ="Select * from Users" ;
	results = conn.Query<MyModel>(strSql).ToList();
}

 查詢回來可以立刻Bind至Model裡享受強型別的好處。

//Query Anonymous
using (SqlConnection conn = new SqlConnection(strConnection))
{
	string strSql ="Select * from Users" ;
	var results = conn.Query(strSql).ToList();
}

▲不宣告強型別也能順利接回來。

//QueryFirst
MyModel results = null;
using (SqlConnection conn = new SqlConnection(strConnection))
{
	string strSql ="Select * from Users" ;
	results = conn.QueryFirst<MyModel>(strSql);
}

 如同Lambda的First(),會將符合條件的第一筆回傳回來,如果沒有符合會拋出錯誤。

//QueryFirstOrDefault
MyModel results = null;
using (SqlConnection conn = new SqlConnection(strConnection))
{
	string strSql ="Select * from Users" ;
	results = conn.QueryFirstOrDefault<MyModel>(strSql);
}

如同Lambda的FirstOrDefault(),會將符合條件的第一筆回傳回來,如果沒有符合回傳null。

//QuerySingle
MyModel results = null;
using (SqlConnection conn = new SqlConnection(strConnection))
{
	string strSql ="Select * from Users" ;
	results = conn.QuerySingle<MyModel>(strSql);
}

如同Lambda的Single(),查詢唯一符合條件的資料,如果沒有符合或符合條件為多筆時會拋出錯誤。

//QuerySingleOrDefault
MyModel results = null;
using (SqlConnection conn = new SqlConnection(strConnection))
{
	string strSql ="Select * from Users" ;
	results = conn. QuerySingleOrDefault<MyModel>(strSql);
}

如同Lambda的SingleOrDefault(),查詢唯一符合條件的資料,如果沒有符合回傳null,但如果符合條件為多筆時會拋出錯誤。

//QueryMultiple
using (SqlConnection conn = new SqlConnection(strConnection))
{
	string strSql ="Select * from Users; Select * from Account;" ;
	using( var results = conn. QueryMultiple(strSql))
	{
		//第一段SQL
		var users = results.Read().ToList();
		//第二段SQL 強型別
		var accounts = results.Read<MyModel>().ToList();
	}
}

同時查詢兩段SQL,查詢回來一樣可以選擇是否要用強型別去接值。

Dapper - Parameter​
我們寫ADO.NET時常常會用Parameter來防止SQL injection,Dapper這方面也有支援。

//Anonymous
//單一參數
using (SqlConnection conn = new SqlConnection(strConnection))
{
	var results = conn.Execute("MyStoredProcedure" 
				,new {Param1 =1,Param2=" ImParam" }
				, commandType: CommandType.StoredProcedure);
}
//多組參數
using (SqlConnection conn = new SqlConnection(strConnection))
{
	var results = conn.Execute("MyStoredProcedure" 
				,new []{new {Param1 =1,Param2=" ImParam" }
				, new {Param1 =2,Param2=" N2" }}
				, commandType: CommandType.StoredProcedure);
}

同時查詢兩段SQL,查詢回來一樣可以選擇是否要用強型別去接值。

//Dynamic
using (SqlConnection conn = new SqlConnection(strConnection))
{
	//設定參數
	DynamicParameters parameters = new DynamicParameters();
	parameters.Add("@Param1", "abc",DbType.String, ParameterDirection.Input);
	parameters.Add("@Return1", dbType: DbType.Int32,direction: ParameterDirection.ReturnValue);

	conn.Execute("MyStoredProcedure", parameters, commandType: CommandType.StoredProcedure);
	int result = parameters.Get<int> ("@Return1");
}

DynamicParameters也可以接回Return值。

//List
List<MyModel> results = null;
using (SqlConnection conn = new SqlConnection(strConnection))
{
	string strSql ="SELECT * FROM Users WHERE UserId IN @ids" ;
	
	results = conn.Query<MyModel>(strSql,new { ids= new[]{"001", "002", "004", "008"}}).ToList();
}

SQL內常用的IN也能塞陣列給他。

//String
List<MyModel> results = null;
using (SqlConnection conn = new SqlConnection(strConnection))
{
	string strSql ="SELECT * FROM Users WHERE UserId = @id" ;
	
	results = conn.Query<MyModel>(strSql,new { id= new DbString {Value="002", IsFixedLength = false, Length = 3, IsAnsi = true}}).ToList();
}

關於String我想有必要說一下,Dapper如果使用暱名型別預設string為NVARCHAR,或許大家會跟我一樣想說...有什麼不一樣?感謝史丹利大大的這篇,總算讓我知道差別在哪裡了,指定型別效能比較好喔!

Dapper - Execute
執行Insert、Update、Delete、Stored Procedure時使用。

//Stored Procedure
using (SqlConnection conn = new SqlConnection(strConnection))
{
	//準備參數
	DynamicParameters parameters = new DynamicParameters();
	parameters.Add("@Param1", "abc",DbType.String, ParameterDirection.Input);
	parameters.Add("@OutPut1", dbType: DbType.Int32,direction: ParameterDirection.Output);
	parameters.Add("@Return1", dbType: DbType.Int32,direction: ParameterDirection.ReturnValue);
	conn.Execute("MyStoredProcedure", parameters, commandType: CommandType.StoredProcedure);
	//接回Output值
	int outputResult = parameters.Get<int> ("@OutPut1");
	//接回Return值
	int returnResult = parameters.Get<int> ("@Return1");
}

Stored Procedure會用到的input、output、return都可以用。

//INSERT statement
using (SqlConnection conn = new SqlConnection(strConnection))
{
	string strSql ="INSERT INTO Users(col1,col2) VALUES (@c1,@c2);" ;
	//新增多筆參數
	dynamic datas = new []{ new { c1 = "A", c2 = "A2" }
				, new { c1 = "B", c2 = "B2" }
				, new { c1 = "C", c2 = "C2" }};
	conn.Execute( strSql, datas);
}

一次新增多筆也是可以的。

//UPDATE statement
using (SqlConnection conn = new SqlConnection(strConnection))
{
	string strSql = " UPDATE Users SET col1=@c1 WHERE col2=@c2" ;
	//修改多筆參數
	dynamic datas = new []{ new { c1 = "A", c2 = "A2" }
				, new { c1 = "B", c2 = "B2" }
				, new { c1 = "C", c2 = "C2" }};
	conn.Execute( strSql, datas);
}

修改多筆資料也OK喔!

//DELETE statement
using (SqlConnection conn = new SqlConnection(strConnection))
{
	string strSql = " DELETE Users WHERE col2=@c2" ;
	//刪除多筆參數
	dynamic datas = new []{ new {c2 = "A2" }
				, new {c2 = "B2" }
				, new {c2 = "C2" }};
	conn.Execute( strSql, datas);
}

刪除多筆當然也可以用IN去寫。

Dapper - Async​
非同步方法Dapper也是有提供,寫法只有一點點不一樣而已。

  • ExecuteAsync
  • QueryAsync
  • QueryFirstAsync
  • QueryFirstOrDefaultAsync
  • QuerySingleAsync
  • QuerySingleOrDefaultAsync
  • QueryMultipleAsync
//QueryAsync
List<MyModel> results = null;
using (SqlConnection conn = new SqlConnection(strConnection))
{
	string strSql ="Select * from Users" ;
	//非同步
	results = conn. QueryAsync<MyModel>(strSql).Result.ToList();
}

▲寫法沒多大改變,除了方法名稱不一樣以外,最大的不同是要.Result

Dapper - Transaction​
交易是如此的重要,Dapper當然也不會忘記。

//Transaction
using (SqlConnection conn = new SqlConnection(strConnection))
{
	string strSql = " UPDATE Users SET col1=@c1 WHERE col2=@c2" ;
	dynamic datas = new []{ new { c1 = "A", c2 = "A2" }
				, new { c1 = "B", c2 = "B2" }
				, new { c1 = "C", c2 = "C2" }};
	//交易
	using(var tran = conn.BeginTransaction())
	{
		conn.Execute( strSql, datas);
		tran.Commit();
	}
}

單一資料庫時建議使用(效能較好)。

//TransactionScope
//加入參考
using System.Transactions;

//交易
using(var tranScope = new TransactionScope())
{
	using (SqlConnection conn = new SqlConnection(strConnection))
	{
		string strSql = " UPDATE Users SET col1=@c1 WHERE col2=@c2" ;
		dynamic datas = new []{ new { c1 = "A", c2 = "A2" }
					, new { c1 = "B", c2 = "B2" }
					, new { c1 = "C", c2 = "C2" }};
		conn.Execute( strSql, datas);
	}
	tranScope.Complete();
} 

用於異質資料庫交易。

 【後記】
第一次整理這麼長的一篇,如有寫錯的地方還請各位先進多多指教。

 【參考資料】
Dapper ORM
Dapper Tutorial
Dapper – GitHub
[.NET] Dapper ORM 查詢參數問題
SqlTransaction與TransactionScope的效能差異​