第十七章 ADO.NET 與資料庫

1. 第十七章 ADO.NET與資料庫

2. 17-1 資料庫基本認識 將資料存入硬碟的方式有二種，其一是檔案，其二是資料庫。此二種模式最大的差別，在於資料庫與一般使用者之間多了一個資料庫管理系統(Database Management System，DBMS)。 於檔案模式中，程式設計者必須了解資料的型態、長度，親自撰寫程式才能取得硬碟資料；於資料庫模式中，程式設計者的工作就輕鬆了，因為程式設計者與硬碟之間多了一個資料庫管理系統，就如同您身旁有一位秘書或出門有司機一樣，凡事只要動口而不必動手，當然工作會輕鬆愉快。所以，使用者可使用不同的應用程式，或甚至不寫任何程式，即可透過資料庫管理系統取得資料。所以本書不介紹使用檔案模式存取硬碟資料，直接介紹資料庫，就如同現代人開車不用學手排車，其道理是相同的。

3. 編 號 姓 名 性 別 職 務 年 齡 A01 張建原 男 教師 38 A02 洪國勝 男 教師 37 B01 孫得昌 男 主任 42 B02 王麗萍 女 組長 32 資料表(Table) 假設有員工基本資料如下，若將以下資料以資料庫模式存入硬碟，則稱此檔案為資料表。

4. 欄位(Field) 上圖中的直向資料，例如編號、姓名、年齡及職務等稱為欄位，本例共有五個欄位。

5. 記錄(Record) 上圖中的橫向資料，例如A01、張建原、男、教師稱為一筆記錄，本例共有四筆記錄。

6. 資料項(Data Item) 上圖中的“A01”、“張建原”或“主任”等單一資料稱為資料項，本例共有二十個資料項，資料項是資料庫的最小單位。

7. 資料庫(Database) 眾多相關資料表的集合稱為資料庫。

8. 資料集(DataSet or RecordSet) 我們可從一個或一個以上的資料表中，使用SQL敘述萃取某些欄位，而形成一個資料集合，此一集合即稱為資料集。

9. 索引(Index) 讀者可以想像一下，一本國語字典至少都有兩種索引，一是注音索引表，另一是部首索引表。各位可以注意每個索引表都只有兩個欄位，一是國字本身，一是頁數，而較大的字典甚至還有電信明碼或四角號碼等索引。電腦的索引原理也是相同的，增加索引的目的是用來增加查詢的速度。對資料庫而言，我們只要指定哪些欄位要建立成索引之後，資料庫就會自動幫我們建立索引區，然後根據索引搜尋資料，並且針對這些欄位加以排序。但要注意一點的是，索引在資料庫中是獨立的資料區域，因此會佔據額外的硬碟空間，所以，只在可加速搜尋的欄位加裝索引即可，以免浪費硬碟空間。

10. 17-2 ADO.NET ADO.NET (ActiveX Data Objects for the .NET Framework)是微軟所提出新一代的資料存取服務，並將這些服務提供給 .NET 程式設計人員使用。透過ADO.NET 所提供的元件，您就可以存取關聯式資料庫、XML 和應用程式資料。ADO.NET 支援數種開發需求，包括建立前端 (Front End) 資料庫用戶端，以及應用程式、工具、語言或 Internet 瀏覽器使用的中介層商務物件 (Middle Tier Business Object)。 ADO.NET包含了以下六個命名空間(Namespace)：

11. System.Data ADO.NET架構的核心命名空間，主要是包含了資料庫基本物件。例如，資料表(DataTable)、欄位(DataColumn)、列(DataRow)及資料集(DataSet)等。

12. System.Data.Common 包含.NET資料提供者(Data Provider)共用的類別。

13. System.Data.OleDb 其中的類別可讓您連接至OLE DB的資料來源、對來源執行SQL命令及讀取結果。

14. System.Data.SqlClient 組成SQL Server的.NET資料提供者的類別，允許您連接到SQL Server、執行SQL命令並讀取結果。System.Data.SqlClient命名空間與System.Data.OleDb命名空間類似，但它是針對存取SQL Server及更新版本而進行最佳化。

15. System.Data.SqlTypes 提供SQL Server內原生資料(Native Data)型別的類別。這些類別會提供較安全、較快速的資料型別，來替代其他的資料型別。使用這個命名空間中的類別，可避免萬一發生遺失精確度的情況時所造成的型別轉換錯誤。

16. System.Xml 針對處理XML文件，所提供標準架構的類別。 用戶端應用程式透過ADO.NET元件，存取後端資料庫的架構如下圖所示：

17. 用戶端應用程式 資料提供者(Data Provider) 資料來源 視窗程式 Connection SQL Server 讀取 (Select) 網頁程式 Commend Commend 寫入 其他類型 (Update 資料庫 DataAdapter Insert Delete) OLE DB資 DataReader DataSet 料來源

18. .NET 資料提供者 (Data Provider) .NET 資料提供者的用途是連接資料庫、執行命令和擷取結果。這些結果可能會經過直接處理、放入 ADO.NET DataSet 物件、與來自多種來源的資料合併，或者在各層間遠端傳遞。

19. 物 件 說 明 Connection 建立連至特定資料來源的連接。 Command 對資料來源執行命令。例如，Select、Insert、Update、Delete等SQL指令，以及Stored Procedure。 DataReader 從資料來源讀取順向唯讀的資料流。 DataAdapter 使用資料來源填入 DataSet 並將更新解析回資料來源。 DataSet 1. ADO.NET支援中斷連線、分散式資料處理不可或缺的要 項。 2.是常駐記憶體的資料表示，可提供一致的關聯式程式撰 寫模型，而不受資料來源限制。 下列表格列出構成 .NET 資料提供者的五個核心物件。

20. .NET Framework 包含二種資料提供者： • 1.SQL Server .NET 資料提供者：使用自己的通訊協定與 SQL Server 通訊，用來直接存取SQL Server，會得到最佳效能，其命名空間為System.Data.SqlClient。 • 2.OLE DB .NET 資料提供者：使用原生OLE DB來啟用資料存取，可存取各種OLE DB資料來源。例如，Access、OLAP Services、Directory Services或其他異質資料庫等，其命名空間為System.Data.OleDb。 以上兩種資料提供者的差異，比較如下圖。

21. SQL Server.NET 資料提供者 OLE DB.NET 資料提供者 OLE DB 服務元件 SQL Sever 7.0 OLE DB 提供者 或更新版本 OLE DB 資料來源

22. 17-3 使用對話框連結資料庫 在上一節中我們認識了.NET資料提供者的核心物件，現在就讓我們來學習如何在VB.NET的設計階段，能夠自動產生這些物件，並且透過這些物件，只要寫極少的程式，就可以連結到資料庫。

23. <範例>17-3a Access資料庫“C:\VBBook\Northwind.mdb”『客戶』資料表的內容如下，請利用ADO.NET提供的物件，顯示出來。

24. 17-4 使用程式連結資料庫 在17-3節中，我們雖然學到了透過滑鼠的拖拉，就可以自動產生ADO.NET的物件，但這種做法，卻不是最好的方式，道理很簡單，因為沒有彈性可言。例如，我們無法從兩個以上的資料表中擷取資料。再者，我們無法決定何時才要真正的連上資料庫。最後，也是最重要的一點，我們無法傳送SQL敘述至後端的資料庫。對SQL不熟的讀者，請看第18章，在第18章中將介紹如何透過SQL敘述操作資料庫。

25. 在Database程式設計中，如何由前端傳送SQL敘述至後端的資料庫，是一項非常重要的主題。要改進上述的這些缺點，只有靠程式敘述，先產生ADO.NET提供的物件之後，再進一步地控制這些物件的屬性及方法，來完成我們的工作。以下是VB.NET程式存取資料庫的步驟，分別是選擇資料提供者、建立Connection物件、建立Command物件、建立DataAdapter物件、建立DataSet物件及建立與連結DataGrid物件，分別說明如下：在Database程式設計中，如何由前端傳送SQL敘述至後端的資料庫，是一項非常重要的主題。要改進上述的這些缺點，只有靠程式敘述，先產生ADO.NET提供的物件之後，再進一步地控制這些物件的屬性及方法，來完成我們的工作。以下是VB.NET程式存取資料庫的步驟，分別是選擇資料提供者、建立Connection物件、建立Command物件、建立DataAdapter物件、建立DataSet物件及建立與連結DataGrid物件，分別說明如下：

26. 選擇資料提供者﹝Data Provider﹞ 我們必須要先確定欲連結資料庫的種類，以選擇適當的.NET資料提供者。例如，SQL Server 7.0以上的版本，要在程式最開始的地方，加上以下敘述： Imports System.Data.SqlClient 若要連結OLE DB資料來源，像是Access資料庫，則要加上以下敘述： Imports System.Data.OleDb

27. 建立Connection物件 連結資料庫的第一步是建立Connection物件，並設定ConnectionString屬性。首先，建立物件的語法如下： Dim 物件名稱 As New OleDbConnection( ) 其次，設定ConnectionString屬性的語法如下： 物件名稱.ConnectionString = "連結字串" 以上物件的建立與屬性的設定，亦可合併如下： Dim 物件名稱 As New OleDbConnection("連結字串")

28. 例如，假設欲連結Access檔案，此檔案的路徑及檔名為“C:\VBBook \Northwind.mdb”，則所需敘述如下： Dim cn As New OleDbConnection( ) cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\VBBook\Northwind.mdb" 在"連結字串"中，我們看到了「Provider」以及「Data Source」兩個屬性，「Provider」是用來設定與資料庫連線時所使用的驅動程式，而「Data Source」則是用來告訴系統我們要連結的Access檔案在什麼地方。 最後，別忘了呼叫Open方法，建立和資料庫的連結。其敘述如下： cn.Open( )

29. 另外，若是我們要連結至SQL Server的話，其敘述如下： Imports System.Data.SqlClient Dim cn As New SqlConnection() cn.ConnectionString = "Server=ComputerName;" & _ "Database=DatabaseName;" & _ "UID=UserID; PWD=Password" 在"連結字串"中，我們看到了「Server」、「Database」、「UID」以及「PWD」四個屬性，「Server」是用來指定SQL Server資料庫是在那一台電腦上，「Database」是用來指定資料庫的名稱，「UID」是用來指定登入資料庫的帳號，而「PWD」則是用來指定登入資料庫的密碼。

30. 建立Command物件 與資料庫建立連結後，我們必須藉由建立『Command』物件，傳送SQL敘述給後端資料庫，並與Connection物件連結，其語法如下： Dim 物件名稱 As New OleDbCommand( ) 物件名稱.CommandText = “SQL 敘述” 物件名稱.Connection = cn cn 是Connection物件，Connection屬性用來連結至Connection物件，以上三個敘述亦可合併如下：

31. Dim物件名稱As New OleDbCommand("SQL 敘述", cn) 'cn 是Connection物件 例如，我們要藉由以上敘述的cn物件，找出『客戶』資料表中的所有紀錄，其敘述如下： Dim cm As New OleDbCommand() cm.CommandText = "Select * From 客戶" cm.Connection = cn

32. 建立DataAdapter物件 當我們設定SQL敘述後，必須藉由建立DataAdapter物件與Command物件連結，並執行SQL敘述擷取資料至用戶端，其語法如下： Dim 物件名稱 As New OleDbDataAdapter() 物件名稱 = New OleDbDataAdapter(cm) 'cm 是Command物件 以上二個敘述亦可合併如下： Dim 物件名稱 As New OleDbDataAdapter(cm)

33. 建立DataSet物件 當DataAdapter物件擷取資料至用戶端後，利用Fill方法，將資料填入DataSet物件。例如，我們要從上例的cm物件，將『客戶』資料表中的所有紀錄填入DataSet物件，其敘述如下： Dim da As New OleDbDataAdapter() Dim ds As New DataSet() da = New OleDbDataAdapter(cm) da.Fill(ds, "客戶")

34. 建立與連結DataGrid物件 欲將取得的資料，藉由表單輸出，必須先於表單配置DataGrid物件。其次，再設定DataSource屬性，如以下敘述： DataGrid1.DataSource = ds.Tables("客戶") '將結果顯示至DataGrid物件

35. <範例>17-4a 使用本節所介紹的程式敘述，產生ADO.NET物件，重做範例17-3a，並將結果顯示出來。

36. <範例>17-4b 示範不使用『Command』物件，重做範例17-4a，並請自行比較兩範例的程式碼。

37. 17-5 資料繫結﹝DataBinding﹞類別 資料繫結可以讓控制項與資料來源產生連結，顯示資料來源的資料。我們在此介紹兩種資料繫結。

38. 單一欄位值的資料繫結 有一些控制項(Control)，像是TextBox、CheckBox、RadioButton等，可以與資料來源結合，用來顯示某一欄位的值，其語法如下： Control.DataBindings.Add("Property", DataSource,"DataMember") • Property：是控制項的某個屬性。 • DataSource：可以是DataSet、DataTable或DataView。 • DataMember：如果「DataSource」是DataSet，DataMember就是「資料表.欄位名稱」；如果「DataSource」是DataTable，DataMember就是「欄位名稱」。例如，以下敘述將『客戶』資料表的『客戶編號』欄位的值，由TextBox控制項顯示出來。 txt客戶編號.DataBindings.Add("Text", ds, "客戶.客戶編號")

39. 多個欄位值的資料繫結 最常見的控制項為DataGrid，可以顯示多個欄位的多筆記錄。例如，以下敘述將『客戶』資料表的內容，由DataGrid元件顯示出來。 DataGrid1.DataSource = ds 'ds 為 DataSet DataGrid1.DataMember = "客戶"

40. <範例>17-5a 將Access資料庫“C:\VBBook\Northwind.mdb”『客戶』資料表的內容，利用本節介紹的兩種資料繫結，顯示出來。

41. 17-6 資料的編輯

42. 屬 性 說 明 Count 取得 BindingManagerBase 管理的資料列數 Position 以零起始的索引，指向目前的記錄指標位置 記錄指標的移動 當DataSource與控制項產生資料繫結之後，我們可以利用BindingManagerBase類別來處理記錄指標。而這個類別主要利用兩個重要的屬性，來處理記錄指標。請看下表：

43. 我們若要建立BindingManagerBase物件，必須使用BindingContext類別建立或傳回被收納資料繫結控制項所使用之資料來源的BindingManagerBase。最常見的情形是使用Form類別的BindingContext來傳回表單上資料繫結控制項的BindingManagerBase物件。例如，以下敘述可得到BindingManagerBase物件bm，以管理表單的資料繫結與處理記錄指標：我們若要建立BindingManagerBase物件，必須使用BindingContext類別建立或傳回被收納資料繫結控制項所使用之資料來源的BindingManagerBase。最常見的情形是使用Form類別的BindingContext來傳回表單上資料繫結控制項的BindingManagerBase物件。例如，以下敘述可得到BindingManagerBase物件bm，以管理表單的資料繫結與處理記錄指標： Dim bm As BindingManagerBase bm = Me.BindingContext(ds, "客戶") 'Me 為表單本身 由以上敘述取得bm之後，就可以透過此物件移動記錄指標了。例如： bm.Position = bm.Position + 1 '將記錄向後移動一筆  bm.Position = bm.Position – 1 '將記錄向前移動一筆

44. <範例>17-6a 使用資料繫結，重做範例17-5a，使得使用者可使用上一筆、下一筆、首筆及尾筆等按鈕，而移動記錄。

45. 記錄的查詢 在設計資料庫系統時，前端維護畫面一定要具備四項功能：新增、修改、刪除及查詢。其中查詢功能是利用SQL的Select敘述，Where條件則是運用一些技巧得到。例如，維護畫面設計如下圖所示，而查詢條件是如果使用者在“客戶編號”文字欄(TextBox)內有輸入值，我們就找出“客戶編號”為該值的資料；而如果使用者沒有輸入任何值，我們就找出所有的資料。

46. 以上畫面的SQL敘述如下： Dim strQuery As String strQuery = "Select * From 客戶" If txt客戶編號.Text <> "" Then strQuery = strQuery + " Where 客戶編號 = " + Chr(39) + _ txt客戶編號.Text + Chr(39) 'Chr(39)為單引號 End If 然後將所得到之SQL敘述傳給後端資料庫，並將自資料庫擷取之記錄，傳入DataSet物件(ds)。以上步驟的敘述如下：

47. cn.Open() cm.CommandText = strQuery cm.Connection = cn da = New OleDbDataAdapter(cm) da.Fill(ds, "客戶") 最後利用資料繫結，將所擷取之記錄顯示出來。其敘述如下： '顯示單一欄位值的資料繫結 txt客戶編號.DataBindings.Add("Text", ds, "客戶.客戶編號") txt公司名稱.DataBindings.Add("Text", ds, "客戶.公司名稱") txt連絡人.DataBindings.Add("Text", ds, "客戶.連絡人") '顯示多個欄位值的資料繫結 DataGrid1.DataSource = ds DataGrid1.DataMember = "客戶"

48. <範例>17-6b 重做範例17-6a，並加入查詢之功能。 程式說明 • 1.在每次新的查詢前，要將記錄指標移至第一筆，否則會產生錯誤。 • 2.在每次新的查詢前，要將DataSet及單一欄位值的資料繫結TextBox物件，使用Clear方法將本身內容清空。 • 3.在做查詢動作時，要考慮到控制各個按鍵的作用，讓介面更易於操作。

49. 如果要新增一筆記錄至『客戶』資料表，我們可利用BindingContext物件的AddNew方法，其敘述如下：如果要新增一筆記錄至『客戶』資料表，我們可利用BindingContext物件的AddNew方法，其敘述如下： Me.BindingContext(ds, "客戶").AddNew( ) 'Me 為表單本身 '利用以下敘述將值填入各個欄位 ds.Tables("客戶").Rows(bm.Position)("客戶編號") = txt客戶號.Text ds.Tables("客戶").Rows(bm.Position)("公司名稱") = txt公司稱.Text ds.Tables("客戶").Rows(bm.Position)("連絡人") = txt連絡人.Text

50. 但是要注意，這時並未將資料寫入資料庫，必須再使用以下敘述，將資料真正的寫入資料庫。但是要注意，這時並未將資料寫入資料庫，必須再使用以下敘述，將資料真正的寫入資料庫。 Dim cb As New OleDbCommandBuilder(da) 'da 為 DataAdapter 物件 cb.DataAdapter.Update(ds.Tables("客戶")) 'ds 為 DataSet 物件 那如果我們做了一半卻想取消這筆新增的記錄，又要如何處理呢？利用以下敘述即可。 Me.BindingContext(ds, “客戶").CancelCurrentEdit( ) Me.ds.RejectChanges()