第 14 章 ADO.NET的資料庫存取

1. 第14章 ADO.NET的資料庫存取 • 14-1 資料庫與ADO.NET的基礎 • 14-2 新增資料來源 • 14-3 DataReader與DataAdapter物件 • 14-4 SQL資料庫語言 • 14-5 應用實例：iPods產品管理程式

2. 14-1 資料庫與ADO.NET的基礎 • 14-1-1 什麼是資料庫 • 14-1-2 ADO.NET的基礎 • 14-1-3 ADO.NET物件模型

3. 14-1-1 什麼是資料庫-說明 • 一般來說，我們所泛稱的「資料庫」（Database），正確的說只是「資料庫系統」（Database System）的一部分，資料庫本身如同是一個電子檔案櫃，它是一種資料儲存單位，內容是經過組織的資料集合。 • 在資料庫儲存的資料是企業能夠正常運作的關鍵。想想看！銀行如果沒有帳戶和交易記錄的資料庫，客戶存款和提款要如何運作。航空公司需要依賴訂票系統的資料庫，才能讓旅行社訂機票，旅客才知道班機是否已經客滿。

4. 14-1-1 什麼是資料庫-資料庫系統(說明) • 資料庫系統（Database Systems）是由資料庫（Database）和「資料庫管理系統」（Database Management Systems）組成，例如：SQL Server Express和Access是資料庫管理系統，iPods.mdf和iPods.mdb檔案是資料庫。 • 目前主流的資料庫管理系統是關聯式資料庫管理系統（Relational Database Management Systems），例如：Access、MySQL、SQL Server和Oracle等。Visual Basic資料庫應用程式的架構。

5. 14-1-1 什麼是資料庫-資料庫系統(圖例) • 應用程式存取資料是下達SQL（Structured Query Language）語言給資料庫管理系統後，資料庫管理系統依指令來查詢、新增、刪除和更新資料庫的資料。

6. 14-1-1 什麼是資料庫-資料庫的組成：資料表、記錄與欄位(說明) • 資料庫主要的目的是儲存資料，資料是儲存在資料庫的「資料表」（Tables），每一個資料表使用「欄位」（Fields）分類成很多群組，每一個群組是一筆「記錄」（Records），例如：通訊錄資料表的記錄，如下表所示：

7. 14-1-1 什麼是資料庫-資料庫的組成：資料表、記錄與欄位(索引) • 資料庫可以擁有多個資料表，為了加速資料存取，資料表可以使用「索引」（Index）技術將資料系統化整理，以便在大量資料能夠快速找到所需的資料或進行排序。 • 例如：在通訊錄資料表使用編號欄位建立主索引鍵，或稱為「主鍵」（Primary Key），如此就可以透過編號來加速資料表記錄的搜尋和排序功能。

8. 14-1-2 ADO.NET的基礎-說明 • ADO.NET目前版本是2.0版，它是微軟的資料存取技術，可以使用一致的物件模型存取資料來源的資料，也就是一致的資料處理方式，至於資料來源並不限資料庫，幾乎任何資料來源都可以。 • 以Visual Basic應用程式來說，使用ADO.NET主要的目的是建立資料庫應用程式。在ADO.NET元件的眾多類別是以名稱空間或稱命名空間（Namespace）的類別架構來組織，屬於一種階層架構（進一步說明請參閱第17章）。

9. 14-1-2 ADO.NET的基礎-名稱空間 • ADO.NET的主要名稱空間的說明，如下表所示：

10. 14-1-3 ADO.NET物件模型-圖例 • ADO.NET主要類別物件有：Connection、Command、DataReader和DataSet，其物件模型如下圖所示：

11. 14-1-3 ADO.NET物件模型-說明1 • Connection物件：建立與資料來源間的連結。 • OLE DB是使用OleDbConnection物件，SQL Server是使用SqlConnection物件。 • Command物件：對資料來源執行指令，以資料庫來說，就是執行SQL指令。 • OLE DB是使用OleDbCommand物件，SQL Server是使用SqlCommand物件。 • DataReader物件：可以從資料來源使用Command物件執行指令，取得唯讀（Read-Only）和只能向前（Forward-Only）的串流資料，每次只能從資料來源讀取一列資料（即一筆）儲存到記憶體，所以執行效率非常高。 • OLE DB是使用OleDbDataReader物件，SQL Server是使用SqlDataReader物件。

12. 14-1-3 ADO.NET物件模型-說明2 • DataSet和DataTable物件：DataSet物件是由DataTable物件組成的集合物件，DataSet物件代表儲存在記憶體的資料庫，每一個DataTable儲存一個資料表的記錄資料，並且可以設定資料表間的關聯性（Relationship）。 • DataAdapter物件：它是DataSet和Connection物件資料連結間的橋樑，可以將資料表填入DataSet物件。 • OLE DB是使用OleDbDataAdapter物件，SQL Server是使用SqlDataAdapter物件。

13. 14-2 新增資料來源 • 14-2-1 ADO.NET與資料繫結 • 14-2-2 新增資料來源的DataSet物件 • 14-2-3 DataGridView控制項的資料繫結

14. 14-2-1 ADO.NET與資料繫結-說明 • 資料繫結（Databinding）可以將外部資料整合到Windows Form控制項。它是一種高擴充性、可重複使用和容易維護的技術。 • .NET Framework的資料繫結技術可以將控制項屬性連接到任何可用「資料」（Data），在此的資料可以是單純資料、物件屬性，控制項名稱的集合物件等，.NET Framework可以將這些資料視為類別的屬性來存取。 • ADO.NET在資料繫結扮演資料提供者的角色，也就是將ADO.NET取得的資料整合至Windows Form控制項，換句話說，我們可以輕鬆在控制項顯示資料庫的記錄資料。

15. 14-2-1 ADO.NET與資料繫結-物件說明 • 當Visual Basic專案使用資料來源建立資料繫結後，就會自動在元件匣新增相關物件來建立Visual Basic資料庫應用程式。其說明如下： • DataConnector物件：資料來源和控制項間的連結，它連結的是單一資料表的資料，即DataTable物件，簡單的說，透過它可以設定連接至DataSet物件的哪一個資料表。 • TableAdapter物件：其功能類似DataAdapter物件，可以支援資料來源的資料表來執行多次查詢。換句話說，只需單一TableAdapter物件，就可以更新DataSet物件中，多個資料表的記錄資料。 • DataNavigator物件：瀏覽資料來源DataSet物件的工具列控制項，提供按鈕可以移至前一筆、下一筆、新增、編輯和刪除記錄資料等。

16. 14-2-2 新增資料來源的DataSet物件-說明 • VBE提供資料來源組態精靈，可以在專案新增資料庫、Web服務或物件的資料來源（Data Source），也就是建立DataSet物件。 • 在Visual Basic控制項可以使用資料繫結技術來顯示資料庫的記錄資料，我們並不用自行撰寫程式碼，只需使用資料來源組態精靈新增資料來源，就可以自動建立資料繫結所需的DataSet物件。

17. 14-2-2 新增資料來源的DataSet物件-資料來源組態精靈

18. 14-2-2 新增資料來源的DataSet物件-資料來源視窗

19. 14-2-3 DataGridView控制項的資料繫結-說明 • 在「資料來源」視窗展開資料表的DataTable物件後，只需拖拉資料表至表單，就可以自動建立DataGridView控制項，或單筆編輯所需的控制項。

20. 14-2-3 DataGridView控制項的資料繫結-瀏覽編輯 • 在「資料來源」視窗的資料表選【DataGridView】後，將資料表拖拉至表單，就可以建立DataGridView控制項，以瀏覽方式來顯示記錄資料，並且自動建立資料繫結所需的相關物件，如下圖所示：

21. 14-2-3 DataGridView控制項的資料繫結-單筆編輯 • 在「資料來源」視窗的iPod資料表後選【詳細資料】，就可以自動建立單筆記錄編輯功能所需的控制項，和資料繫結的相關物件，如右圖所示：

22. 14-3 DataReader與DataAdapter物件 • 14-3-1 使用DataReader物件讀取記錄資料 • 14-3-2 Using/End Using程式區塊 • 14-3-3 將資料表填入DataSet物件

23. 14-3 DataReader與DataAdapter物件 • ADO.NET的DataReader物件，可以如同循序檔案來讀取資料庫的記錄資料。DataAdapter物件可以將資料表填入DataSet物件。 • 在Visual Basic程式碼使用ADO.NET物件時，我們可以在專案屬性頁勾選（第15-3節說明），或在模組以Imports關鍵字來匯入OLE DB所需的名稱空間，如下所示： Imports System.Data.OleDb • SQL Server Express資料庫匯入的名稱空間，如下所示： Imports System.Data.SqlClient

24. 14-3-1 使用DataReader物件讀取記錄資料-步驟一 步驟一：建立和開啟資料庫連結 • 在Visual Basic程式首先需要建立Connection物件來連接資料庫，如下所示： Dim objCon As OleDbConnection objCon = New OleDbConnection(strDbCon) • 上述程式碼使用New關鍵字建立名為objCon的OleDbConnection物件，StrDbCon字串變數可以指定資料來源的連接字串。 • 在建立好Connection物件後，就可以使用Open()方法開啟資料庫連結，如下所示； objCon.Open()

25. 14-3-1 使用DataReader物件讀取記錄資料-步驟二 步驟二：建立Command物件 • 在開啟資料庫連結後，就可以建立Command物件，並且指定欲執行的SQL指令字串，如下所示： Dim objCmd As OleDbCommand strSQL = "SELECT * FROM iPod" objCmd = New OleDbCommand(strSQL, _ objCon)

26. 14-3-1 使用DataReader物件讀取記錄資料-步驟三 步驟三：取得DataReader物件 • 在建立好Command物件後，就可以使用ExecuteReader()方法執行SQL指令來取得DataReader物件，如下所示： Dim objDataReader As OleDbDataReader objDataReader = objCmd.ExecuteReader() • 上述程式碼取得DataReader物件objDataReader。

27. 14-3-1 使用DataReader物件讀取記錄資料-步驟四 步驟四：取出查詢結果的資料表記錄資料 While objDataReader.Read() out &= objDataReader.Item("ModelNo") & " " out &= objDataReader.Item("Name") & vbTab out &= objDataReader.Item("Storage") & vbTab out &= objDataReader.Item(3) & vbTab out &= objDataReader.Item(4) & vbTab out &= objDataReader.Item(5) & vbNewLine End While

28. 14-3-1 使用DataReader物件讀取記錄資料-步驟五 步驟五：關閉DataReader和資料庫連結 • 最後就可以關閉DataReader和Connection物件，如下所示： objDataReader.Close() objCon.Close() • 上述程式碼使用Close()方法關閉DataReader和Connection物件。

29. 14-3-2 Using/End Using程式區塊-說明 • Visual Basic程式常常需要建立一些資源，並且在使用後馬上釋放資源佔用的記憶體空間，例如：ADO.NET的Connection和Command物件。 • 為了避免忘了釋放資源，Visual Basic 2005新增Using/End Using程式區塊來進行資源管理。

30. 14-3-2 Using/End Using程式區塊-範例 • 在Using/End Using程式區塊的開始可以建立資源，程式區塊中使用資源，當離開程式區塊，就會自動釋放此資源，如下所示： Using objCon As New _ OleDbConnection(strDbCon) objCon.Open() ……… End Using • 上述程式碼使用Using/End Using程式區塊建立Connection物件，如此就不需要在最後使用Close()方法關閉資料庫連結和釋放資源。

31. 14-3-3 將資料表填入DataSet物件-步驟一 • Step 1 建立Connection物件：如同上一節DataReader物件，我們仍然需要使用Connection物件建立和開啟資料連結，只是改為Using/End Using程式區塊，如下所示： Using objCon As New OleDbConnection(strDbCon) objCon.Open() ……… End Using

32. 14-3-3 將資料表填入DataSet物件-步驟二 • Step 2 建立DataAdapter物件：它是DataSet和Connection物件資料連結間的橋樑，可以將資料表填入DataSet物件，其建構子的第1個參數是SQL指令字串，第2個參數是Connection物件，如下所示： Dim objDataAdapter As OleDbDataAdapter objAdapter = New _ OleDbDataAdapter(strSQL, objCon)

33. 14-3-3 將資料表填入DataSet物件-步驟三 • Step 3 填入DataSet物件：執行DataAdapter物件的SQL查詢指令後，使用Fill()方法將記錄填入DataSet物件，第1個參數是DataSet物件，第2個參數是DataTable物件名稱，如下所示： Dim objDataSet As DataSet = New DataSet() objAdapter.Fill(objDataSet, "iPod")

34. 14-3-3 將資料表填入DataSet物件-步驟四 • Step 4 建立資料繫結：將控制項的DataSource屬性，指定成DataSet物件名為iPod的DataTable物件就可以建立資料繫結，如下所示： dgvOutput.DataSource = _ objDataSet.Tables("iPod")

35. 14-3-3 將資料表填入DataSet物件-圖例

36. 14-4 SQL資料庫語言 • 14-4-1 SQL語言的基礎 • 14-4-2 SQL查詢指令 • 14-4-3 資料庫操作的SQL指令

37. 14-4-1 SQL語言的基礎-說明 • 「SQL」（Structured Query Language）為「ANSI」（American National Standards Institute）標準的資料庫語言，它可以存取和更新資料庫的記錄資料。目前Access、SQL Server、Informix、Oracle和Sybase等關聯式資料庫系統都支援ANSI的SQL語言。 • 1974年一種稱為SEQUEL的語言，這是Chamberlin和Boyce的作品，它建立SQL語言的原型，IBM稍加修改後作為其資料庫DBMS的資料庫語言，稱為System R。1980年SQL名稱正式誕生，從此SQL語言逐漸壯大成為一種標準的關聯式資料庫語言。

38. 14-4-1 SQL語言的基礎-指令 • SQL語言的指令主要分為三大部分，如下表所示： • 資料定義語言（Data Definition Language，DDL）：建立資料表、索引和視界（Views）等，並且定義資料表的欄位。 • 資料操作語言（Data Manipulation Language，DML）：屬於資料表記錄查詢、插入、刪除和更新指令。 • 資料控制語言（Data Control Language，DCL）：屬於資料庫安全設定和權限管理的相關指令。

39. 14-4-2 SQL查詢指令-語法 • 在SQL語言的資料查詢指令只有一個SELECT指令，完整指令語法如下所示： SELECT 欄位1, 欄位2 FROM 資料表 WHERE conditions • 上述SELECT指令的欄位1~2為記錄的欄位，conditions為查詢條件，使用口語來說就是「從資料表取回符合WHERE子句條件的記錄，顯示欄位1和2」。

40. 14-4-2 SQL查詢指令- "*"記錄欄位 • SELECT指令可以使用"*"符號代表所有資料表的欄位，表示取回資料表記錄的所有欄位，如下所示： SELECT * FROM iPod • 上述指令沒有WHERE子句，所以是將資料表內所有的記錄和欄位取回。

41. 14-4-2 SQL查詢指令-單一查詢條件的WHERE子句1 • 文字欄位需加單引號或雙引號括起，例如：型號ModleNo為MA146LL，此時的SQL指令字串，如下所示： SELECT * FROM iPod WHERE ModelNo="MA146LL" • 數字欄位不需要單引號括起，例如：價格為8000元，此時的SQL指令，如下所示： SELECT * FROM iPod WHERE Price=8000

42. 14-4-2 SQL查詢指令-單一查詢條件的WHERE子句2 • 文字和備註欄位可以使用【LIKE】包含運算子，只需包含此字串即符合條件，再配合"%"或"_"萬用字元，可以代表任何字串或單一字元，只需包含的子字串就符合條件。例如：查詢白色的iPod產品資料。SQL指令如下所示： SELECT * FROM iPod WHERE Name LIKE "%白%" • 數字或日期/時間欄位可以使用<>、>、<、>=和<=不等於、大於、小於、大於等於和小於等於等運算子建立多樣化的查詢條件。

43. 14-4-2 SQL查詢指令-多重查詢條件的WHERE子句 • WHERE子句的查詢條件如果不只一個，可以使用AND和OR邏輯運算子來連接，其基本語法如下所示： • AND且運算子：連接前後條件都必須成立，整個條件才能成立，例如：名稱包含"白"且價格小於等於8000元。SQL指令如下所示： SELECT * FROM iPod WHERE Price<=8000 AND Name LIKE '%白%' • OR或運算子：連接的前後條件只需任何一個成立即可，例如：名稱包含"白"或價格小於等於8000元。SQL指令如下所示： SELECT * FROM iPod WHERE Price<=8000 OR Name LIKE '%白%'

44. 14-4-2 SQL查詢指令-排序輸出 • SQL查詢結果可以指定欄位進行由小到大，或由大到小排序，只需在SELECT指令的最後加上ORDER BY子句即可，如下所示： SELECT * FROM iPod WHERE Price>=8000 ORDER BY Price ASC • 上述查詢結果使用Price欄位排序，預設是由小到大的ASC。如果想倒過來由大到小，只需加上DESC，如下所示： SELECT * FROM iPod WHERE Price>=8000 ORDER BY Price DESC

45. 14-4-2 SQL查詢指令-SQL聚合函數 • SQL聚合函數是資料表欄位的筆數、平均、範圍和統計函數，可以提供進一步資料分析的結果，如下表所示：

46. 14-4-3 資料庫操作的SQL指令-說明 • SQL資料庫操作指令有： • INSERT插入 • DELETE刪除 • UPDATE更新記錄。 • 在建立好SQL操作指令後，Visual Basic程式碼可以使用Command物件的ExecuteNonQuery()方法來執行SQL指令。

47. 14-4-3 資料庫操作的SQL指令-INSERT插入記錄(語法) • SQL插入記錄INSERT指令可以新增一筆記錄。其基本語法如下所示： INSERT INTO table (column1,column2,…..) VALUES ('value1', 'value2 ', …) • 上述SQL指令的table是準備插入記錄的資料表名稱，column1~n為資料表內的欄位名稱（不需全部欄位），value1~n是對應的欄位值。

48. 14-4-3 資料庫操作的SQL指令-INSERT插入記錄(注意事項) • INSERT指令的注意事項，如下所示： • 不論是欄位或值清單，都需要使用逗號分隔。 • INSERT指令VALUES的值，數字不用引號包圍，字元與日期/時間需要使用引號包圍，Access的日期/時間是使用"#"符號。 • INSERT指令的欄位清單不需和資料表定義的欄位數目或順序相同，只需選擇需要新增的欄位即可，但是括號內的欄位名稱順序需要和VALUES值的順序相同。

49. 14-4-3 資料庫操作的SQL指令-INSERT插入記錄(範例) • 例如：在資料表iPod插入一筆記錄的SQL指令，如下所示： INSERT INTO iPod (ModelNo, Name,Storage, Price, StockDate) VALUES ('M9586LL','白色蘋果iPod Photo‘ ,'60GB',15000,#2006/2/20#) • 上述SQL指令的欄位值是字串有使用單引號括起，數字沒有，日期/時間加上"#"符號。

50. 14-4-3 資料庫操作的SQL指令-UPDATE更新記錄(語法) • SQL更新記錄UPDATE指令是將資料表內符合條件的記錄，更新其欄位內容。其基本語法如下所示： UPDATE table SET column1 = 'value1' WHERE conditions • 上述指令的table是資料表，SET子句column1是資料表的欄位名稱，不用全部只需指定要更新的欄位，value1是更新欄位值。如果更新欄位不只一個，請使用逗號分隔，如下所示： UPDATE table SET column1 = 'value1' , column2 = 'value2' WHERE conditions