topcat 姍舞之間的極度凝聚

緣起

在藍色小舖的一篇討論中，Keven大大提到了Dapper這個東西，小喵好奇這是什麼，因此搜尋了一下，發現這真是個好物，可以用很精簡的語法，將SQL語法透過ADO.NET撈取的資料，快速的對應到DTO的物件中，無論新增、修改、刪除、查詢，都非常好用。所以小喵特別練習一下，順便記錄下來，提供未來要應用時的參考。

範例資料表：北風的Shippers

小喵以北風資料庫，一個簡單的資料表：「Shippers」來當作這次的練習範例。依據這資料表的Schema，產生DTO的類別如下

Public Class ShipperInfo
    Public Property ShipperID As Integer = 0
    Public Property CompanyName As String = ""
    Public Property Phone As String = ""
End Class

加入參考

要使用Dapper，首先要將該元件加入參考。透過Nuget，可以很簡單的搜尋到，只需搜尋到後加入參考即可

Imports

加入參考後，使用的時候就是先 Imports Drapper 命名空間，小喵用ADO.NET資料存取，所以順便也Imports System.Data 與 System.Data.SqlClient

Imports System.Data
Imports System.Data.SqlClient
Imports Dapper

讀取放入物件集合

首先，先試試讀取資料，讀取後，塞入 List (Of ShipperInfo) 的物件集合中，顯示資料，就簡單使用 GridView
相同的動作，如果使用純 ADO.NET，勢必要用DataReader讀取後，迴圈一筆一筆、每筆各欄位逐一的來處理
沒想到，Dapper 竟然一行就搞定了

Protected Sub btnGetShipperByDapper_Click(sender As Object, e As EventArgs) Handles btnGetShipperByDapper.Click
	Try
		Dim SqlTxt As String = ""

		SqlTxt &= " SELECT * "
		SqlTxt &= " FROM Shippers (NOLOCK) "
		SqlTxt &= "  "

		Using Conn As New SqlConnection(ConnStr)
			'讀取並放入物件集合中（竟然Dapper一行就搞定）
			Dim oShippers As List(Of ShipperInfo) = Conn.Query(Of ShipperInfo)(SqlTxt)

			'將物件集合綁到GridView顯示
			Me.GridView1.DataSource = oShippers
			Me.GridView1.DataBind()
		End Using


	Catch ex As Exception
		Throw New Exception(ex.Message)
	End Try
End Sub

Dapper會依據讀到的資料表欄位名稱、與您的DTO物件屬性名稱對照將資料放入，因此，只要您的物件屬性「名稱」與資料庫的欄位一致，順序沒差，他就會自動的放到該放的欄位中。
這也意味著，當您的資料表中的欄位名稱改變，那麼您的DTO物件的屬性名稱也要跟著改變。

多筆新增

接著，嘗試讓Dapper幫忙處理多筆的新增，您只需要準備好

1. 新增的SQL語法
2. 新增的多筆物件集合

剩下的，就交給Dapper來處理，您不必一個一個欄位的去對照參數，當然，您的Insert語法中，Parameter的名稱、與資料表的欄位名稱、以及物件類別的Property名稱，要一致，才能正確的對照到

Protected Sub btnAddData_Click(sender As Object, e As EventArgs) Handles btnAddData.Click
	Try
		Using Conn As New SqlConnection(ConnStr)
			Dim SqlTxt As String = ""
			SqlTxt &= " INSERT INTO [dbo].[Shippers] "
			SqlTxt &= " 	(CompanyName, Phone) "
			SqlTxt &= " VALUES (@CompanyName, @Phone) "
			SqlTxt &= "  "

			Dim oShippers As New List(Of ShipperInfo)

			Dim tShipper As ShipperInfo
			'迴圈準備多筆新增的物件集合
			For y = 1 To 10
				tShipper = New ShipperInfo
				tShipper.CompanyName = "XX" & Format(y, 0)
				tShipper.Phone = "2223512" + y.ToString
				oShippers.Add(tShipper)
			Next

			'執行新增，一樣的一句搞定，只需傳入SQL語法與物件集合
			Conn.Execute(SqlTxt, oShippers)

			'新增完，將資料撈取出並用GridView顯示
			SqlTxt = ""
			SqlTxt &= " SELECT * "
			SqlTxt &= " FROM Shippers (NOLOCK) "
			SqlTxt &= "  "

			oShippers.Clear()
			oShippers = Conn.Query(Of ShipperInfo)(SqlTxt)
			Me.GridView1.DataSource = oShippers
			Me.GridView1.DataBind()

		End Using
	Catch ex As Exception
		Throw New Exception(ex.Message)
	End Try
End Sub

Drapper處理，就是那一句「 Conn.Execute(SqlTxt, oShippers) 」就搞定新增，真是神奇，可以節省好多程式碼。

多筆修改

多筆的修改，與新增差不多，一樣的把SQL語法準備好、把要更新的資料變成物件集合準備好，剩下語法一樣一句搞定

Protected Sub btnUpdData_Click(sender As Object, e As EventArgs) Handles btnUpdData.Click
	Try
		Using Conn As New SqlConnection(ConnStr)
			Dim SqlTxt As String = ""
			'組合維護的語法
			SqlTxt &= " UPDATE [dbo].[Shippers] "
			SqlTxt &= " SET "
			SqlTxt &= "     CompanyName = @CompanyName "
			SqlTxt &= "     , Phone = @Phone "
			SqlTxt &= " WHERE ShipperID = @ShipperID "
			SqlTxt &= "  "

			'將要維護的內容，組合放入物件集合中
			Dim tShipper As ShipperInfo
			Dim oShippers As New List(Of ShipperInfo)

			'70	XX6	22235126
			'71  XX7	22235127
			'72  XX8	22235128

			For y = 0 To 5
				tShipper = New ShipperInfo
				tShipper.ShipperID = 70 + y
				tShipper.CompanyName = "XX" & y & "X"
				tShipper.Phone = "2223512" & y & "X"

				oShippers.Add(tShipper)
			Next

			'執行修改，一樣一句搞定
			Conn.Execute(SqlTxt, oShippers)

			'修改完後，重新撈取放在GirdView中展現
			SqlTxt = ""
			SqlTxt &= " SELECT * "
			SqlTxt &= " FROM Shippers (NOLOCK) "
			SqlTxt &= "  "

			oShippers.Clear()
			oShippers = Conn.Query(Of ShipperInfo)(SqlTxt)
			Me.GridView1.DataSource = oShippers
			Me.GridView1.DataBind()


		End Using

	Catch ex As Exception
		Throw New Exception(ex.Message)
	End Try
End Sub

由於直接傳入SQL語法與物件集合，以往，可能會記錄資料修改的日期，像這樣的紀錄，就變成需要在傳入的物件就事先準備好，然後才透過 Conn.Execute(SqlTxt, oShippers) 這樣來一句搞定。這部分是與以往不太一樣的部分，要特別注意一下。

刪除資料

刪除資料，一樣的準備好刪除的 SQL語法 ，而刪除的Key，可以用具名的參數來處理，對應給值的時候，一樣的可具名。

Protected Sub btnDelData_Click(sender As Object, e As EventArgs) Handles btnDelData.Click
	Try
		Using Conn As New SqlConnection(ConnStr)

			Dim SqlTxt As String = ""
			'組合刪除的語法，其中，條件使用具名的參數
			SqlTxt &= " DELETE  [dbo].[Shippers] "
			SqlTxt &= " WHERE CompanyName LIKE @CompanyName "
			SqlTxt &= "  "

			'執行參數與內容刪除，透過 New With 的方式，提供具名的甚至可用Like這樣的方式
			Conn.Execute(SqlTxt, New With {.CompanyName = "XX%"})

			'重新取得所有資料，並用GridView顯示出來
			SqlTxt = ""
			SqlTxt &= " SELECT * "
			SqlTxt &= " FROM Shippers (NOLOCK) "
			SqlTxt &= "  "

			Dim oShippers As New List(Of ShipperInfo)


			oShippers = Conn.Query(Of ShipperInfo)(SqlTxt)
			Me.GridView1.DataSource = oShippers
			Me.GridView1.DataBind()
		End Using

	Catch ex As Exception
		Throw New Exception(ex.Message)
	End Try
End Sub

查詢的條件用Like 

2018/9/11補充：

當我們需要做LIKE條件查詢時，這部分的寫法如下：

Protected Sub btnGetShipperWhereLIKE_Click(sender As Object, e As EventArgs) Handles btnGetShipperWhereLIKE.Click
	Try
		Dim KeyWords As String = Me.txtKeyWords.Text
		Using Conn As New SqlConnection(ConnStr)
			Dim SqlTxt As String = ""
			SqlTxt &= " SELECT * "
			SqlTxt &= " FROM Shippers (NOLOCK) "
			SqlTxt &= " WHERE Phone LIKE @Phone "
			SqlTxt &= "  "

			'一句就完成查詢Where In，可具名的參數提供，並以陣列的方式傳入條件
			Dim oShippers As List(Of ShipperInfo) = Conn.Query(Of ShipperInfo)(SqlTxt, New With {.Phone = "%" & KeyWords & "%"})

			Me.GridView1.DataSource = oShippers
			Me.GridView1.DataBind()
		End Using

	Catch ex As Exception
		Throw New Exception(ex.Message)
	End Try
End Sub

 

查詢的條件用Where IN

另外，要透過Where IN的方式，一次顯示指定的某些筆資料，也是沒問題的

Protected Sub btnGetShipperWhereIN_Click(sender As Object, e As EventArgs) Handles btnGetShipperWhereIN.Click
	Try
		Using Conn As New SqlConnection(ConnStr)
			Dim SqlTxt As String = ""
			SqlTxt &= " SELECT * "
			SqlTxt &= " FROM Shippers (NOLOCK) "
			SqlTxt &= " WHERE ShipperID IN @ShipperIDs "
			SqlTxt &= "  "

			'一句就完成查詢Where In，可具名的參數提供，並以陣列的方式傳入條件
			Dim oShippers As List(Of ShipperInfo) = Conn.Query(Of ShipperInfo)(SqlTxt, New With {.ShipperIDs = New Integer() {1, 3}})

			Me.GridView1.DataSource = oShippers
			Me.GridView1.DataBind()
		End Using

	Catch ex As Exception
		Throw New Exception(ex.Message)
	End Try
End Sub

可具名的提供參數，並且以陣列的方式，提供WHERE IN的參數

 

動態參數

有時候，我們會依據使用者傳入的內容，來作為篩選的條件，而且可能篩選的欄位是變動的、非固定的，因此，需要有動態參數的方式，來幫忙，當使用者有傳遞5個條件，就要5個參數，10個條件，就要有10個參數

相關的做法如下：

'假設傳入了三個參數的條件內容
If Trim(Parameter1) <> "" Then
	SqlTxt &= " AND Parameter1 = @Parameter1 "
End If
If Trim(Parameter2) <> "" Then
	SqlTxt &= " AND Parameter2 = @Parameter2 "
End If
If Trim(Parameter3) <> "" Then
	SqlTxt &= " AND Parameter3 = @Parameter3 "
End If

'宣告動態參數
Dim dbArgs = New DynamicParameters()
If Trim(Parameter1) <> "" Then
	dbArgs.Add("Parameter1", Parameter1)
End If
If Trim(Parameter2) <> "" Then
	dbArgs.Add("Parameter2", Parameter2)
End If
If Trim(Parameter3) <> "" Then
	dbArgs.Add("Parameter3", Parameter3)
End If


oProducts = Conn.Query(Of ProductInfo)(SqlTxt, dbArgs)

 

使用時機：應該用於「資料存取層」

小喵這一篇的測試程式碼，都是為了測試Dapper的撰寫方式而寫。
在實際的應用上，這樣的元件，應該要應用在『資料存層』中，讓資料存取的相關部分，可以寫得更精簡些，與物件更密切些。
不建議在商業邏輯層、或者展現層中直接撰寫這樣的Code唷。

末記

Dapper果然是一個極輕量化的ORM利器，只需要一個大小為142KB（1.50.2版）dll
對於小喵這種原本用ADO.NET來撰寫程式的人，可以省略相當多的程式碼
後續使用上，如果還有其他比較特別的應用，會再回來補充此篇。
這篇就提供小喵未來撰寫的參考，也同時提供網友們參考。
^_^

 

 

---

以下是簽名：

• 歡迎轉貼本站的文章，不過請在貼文主旨上加上【轉貼】，並在文章中附上本篇的超連結與站名【topcat姍舞之間的極度凝聚】，感恩大家的配合。
• 小喵大部分的文章會以小喵熟悉的語言VB.NET撰寫，如果您需要C#的Code，也許您可以試著用線上的工具進行轉換，這裡提供幾個參考
  • http://converter.telerik.com/
  • http://www.carlosag.net/tools/codetranslator/
  • http://www.developerfusion.com/tools/convert/vb-to-csharp/

---

Microsoft MVP Visual Studio and Development Technologies (2005~2019/6)

topcat Blog:http://www.dotblogs.com.tw/topcat