在小舖的討論中,看到Keven大推薦使用Dapper,心想這是什麼?於是花點時間尋找一下,發現這真是個好物,可以大幅精簡開發時的程式碼
而且,黑大在2014年的時候就已經寫文記錄下來。
小喵特別練習一下順便記錄下來,提供未來應用時候的參考
緣起
在藍色小舖的一篇討論中,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幫忙處理多筆的新增,您只需要準備好
- 新增的SQL語法
- 新增的多筆物件集合
剩下的,就交給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,也許您可以試著用線上的工具進行轉換,這裡提供幾個參考
Microsoft MVP Visual Studio and Development Technologies (2005~2019/6) | topcat Blog:http://www.dotblogs.com.tw/topcat |