Transcript VB.NET程式設計範例教本

第14章 ADO.NET存取資料庫





14-1
14-2
14-3
14-4
14-5
資料庫的基礎
使用ADO.NET存取資料庫
DataGrid控制項顯示資料庫
SQL的資料庫查詢
SQL的資料庫操作
1
14-1 資料庫的基礎


14-1-1 什麼是資料庫
14-1-2 使用Access建立資料庫
2
14-1-1 什麼是資料庫

資料庫正確的說只是「資料庫系統」
（Database System）的一部分，資料庫系統
是由「資料庫」（Database）和「資料庫管理
系 統 」 （ Database Mangement System，
DBMS）所組成，如下圖所示：
3
14-1-2 使用Access建立資料庫
欄位名稱
資料型別
長度
BookID
文字
6
BookTitle
文字
30
BookPrice
貨幣
BookPubDate
日期/時間
4
14-2 ADO.NET的基礎


14-2-1 什麼是ADO.NET
14-2-2 使用DataReader物件讀取資料庫
5
14-2-1 什麼是ADO.NET-說明


ADO.NET是微軟新一代的技術，它是
「ADO」（ActiveX Data Object）元件
的後繼者。
ADO.NET的目的是提供一致的物件模型，
存取和編輯資料來源的資料，提供一致的
資料處理方式，資料來源並不限於資料庫，
幾乎任何資料來源(如 EXCEL、文字檔、
Access等)都可以，以VB.NET應用程式
來說，使用ADO.NET主要的目的是存取資
料庫。
6
ADO.NET
DataReader
1
1
Command
1
2
表單控制項
(Data Binding)
2
Connection
DataAdapter
2
2
DataSet
DataTable
Data Source
7
ADO.NET 第一種應用

使用 Connection 物件建立資料來源的資
料連結，然後使用 Command 物件執行指
令取得資料來源的資料，(以資料庫來說此
Command 即 SQL 指令)，在取得資料後，
使用 DataReader 物件讀取或填入
DataSet 物件，最後使用 Data Binging
在表單控制項 (如 TextBox 或 DataGrid )
顯示資料表的紀錄。如需 Insert / Delete
/ Update 資料庫也是蔡取類似方法。
8
14-2-1 什麼是ADO.NETConnection物件

Connection物件可以建立與存取資料來源
的連結，以資料庫來說Connection物件還
負責初始資料庫，Connection物件一共分
為兩種：
• OLE DB：使用OleDbConnection物件
• SQL Server：使用SqlConnection物件
9
14-2-1 什麼是ADO.NETCommand物件


Command物件可以對資料來源執行指令，
以資料庫的資料來源而言，就是執行SQL
指令，在建立Connection物件建立資料庫
連結後，就可以使用Command物件執行
SQL指令。
Command物件也分為兩種：
• OLE DB：使用OleDbCommand物件
• SQL Server：使用SqlCommand物件
10
14-2-1 什麼是ADO.NETDataReader物件


ADO.NET的DataReader物件可以從資料
來源使用Command物件執行SQL指令，
取得如同循序檔案一般唯讀且單向的資料
流。因為DataReader只在記憶體儲存一筆
記錄，所以能夠提升應用程式的執行效能，
並且降低系統的負荷。
DataReader物件分為兩種：
• OLE DB：使用OleDbDataReader物件
• SQL Server：使用SqlDataReader物件
11
14-2-1 什麼是ADO.NETDataSet和DataTable物件


DataSet物件是由DataTable物件所組成，
DataSet物件可以代表一個儲存在記憶體的資料
庫，每一個DataTable儲存一個資料表的記錄資
料，每一個DataRow物件就是一筆記錄。
在建立好Connection物件的資料庫連結後，我們
使用DataAdapter物件的Fill方法將資料表的資
料填入DataSet物件，也就是資料表的
DataTable物件。DataAdapter物件分為兩種：
• OLE DB：使用OleDbDataAdapter物件
• SQL Server：使用SqlDataAdapter物件
12
14-2-2 使用DataReader物件讀
取資料庫-說明


VB.NET應用程式使用ADO.NET存取資料庫最簡
單且快速的方式是使用DataReader物件。
以連結Access資料庫為例，在程式碼使用
ADO.NET時，需要使用Imports關鍵字匯入OLE
DB所需的名稱空間Namespace，如下所示：
Imports System.Data.OleDb

使用SQL Server資料庫，其匯入的名稱空間，
如下所示：
Imports System.Data.SqlClient
13
14-2-2 使用DataReader物件讀取資料庫步驟1

步驟１：建立和開啟資料庫連結
• 首先我們需要建立Connection物件來連結資料庫，
如下所示：
Dim objCon As OleDbConnection
objCon = New OleDbConnection(strDbCon)
• StrDbCon字串變數指定資料來源的字串，如下
所示：
strDbCon = "Provider=Microsoft.Jet.OLEDB.4.0;“ & _
“Data Source=..\..\Books.mdb"
• 開啟資料庫連結
objCon.open()
14
14-2-2 使用DataReader物件讀
取資料庫-步驟2


步驟２：建立Command物件
在開啟資料庫連結後，就可以建立
Command物件，並且指定欲執行的SQL指
令字串，如下所示：
Dim objCmd As OleDbCommand objCmd = _
New OleDbCommand("SELECT * FROM Book", objCon)
15
14-2-2 使用DataReader物件讀
取資料庫-步驟3


步驟３：取得DataReader物件
在建立好Command物件後，接著就可以
使用ExecuteReader方法執行SQL指令取
得DataReader物件，如下所示：
Dim objDataReader As OleDbDataReader
objDataReader = objCmd.ExecuteReader()

上述程式碼取得DataReader物件
objDataReader。
16
14-2-2 使用DataReader物件讀
取資料庫-步驟3 Execute方法
Execute 方法
說明
ExecuteNonQuery
執行 SQL 指令但是不會傳回任何資料，通常是使用在第
14-5 節資料庫操作指令 INSERT、DELETE 和 UPDATE
ExecuteScalar
執行 SQL 指令從資料表只取得一個欄位資料，如果是資
料表，就是第 1 筆記錄的第 1 個欄位值
ExecuteReader
執行 SQL 指令傳回 DataReader 物件
17
14-2-2 使用DataReader物件讀
取資料庫-步驟4


步驟４：取出查詢結果的資料表記錄資料
DataReader物件如同循序檔案一般，當執行
SQL指令取得DataReader物件後，就如同開啟
一個讀取的DataReader檔案，程式碼需要使用
While迴圈讀取資料表的每一筆記錄，如下所示：
While objDataReader.Read()
out &= objDataReader.Item("BookID") & " "
out &= objDataReader.Item("BookTitle") & " "
out &= objDataReader.Item("BookPrice") & _
vbNewLine
End While
18
DataReader 屬性



取得指定的欄位值：
objDataReader.Item(“BookTitle”)
objDataReader.Item(3)：由 0 開始
FieldCount: 取得欄位數
IsClosed: 檢查是否關閉，True 表示關閉
19
14-2-2 使用DataReader物件讀
取資料庫-步驟5


步驟５：關閉DataReader和資料庫連結
最後需要關閉DataReader物件和資料庫連
結的Connection物件，如下所示：
objDataReader.Close()
objCon.Close()

上述程式碼使用Close方法關閉
DataReader和Connection物件。
20
14-3 DataSet物件與DataGrid控
制項


14-3-1 將資料表填入DataSet物件
14-3-2 DataGrid控制項顯示資料表
21
14-3-1 將資料表填入DataSet物
件-說明


DataSet物件可以建立儲存在記憶體中的
資料庫，每一個資料表是一個DataTable
物件。
不同於DataReader物件如同循序檔案，我
們需要將資料表的記錄資料先填入
DataSet物件，然後再來顯示DataSet物
件的記錄資料。
22
14-3-1 將資料表填入DataSet物
件-步驟1


步驟１：建立Connection物件
如同上一節的DataReader物件，我們還是
需要使用Connection物件建立資料連結，
如下所示：
objCon = New OleDbConnection(strDbCon)
objCon.Open()

上述程式碼建立objCon的資料連結物件，
然後開啟資料庫連結。
23
14-3-1 將資料表填入DataSet物
件-步驟2



步驟２：建立DataAdapter物件
在DataSet物件是使用DataAdapter物件取得記
錄資料，這個物件屬於DataSet和Connection物
件資料來源間的橋樑，以便將資料表填入
DataSet物件。
DataAdapter物件也擁有兩個版本，SQL
Server的是SqlDataAdapter，以本節為例是使
用OLE DB的OleDbDataAdapter，如下所示：
Dim objDataAdapter As OleDbDataAdapter
objDataAdapter = New OleDbDataAdapter( _
"SELECT * FROM Book", objCon)
24
14-3-1 將資料表填入DataSet物
件-步驟3


步驟３：建立DataSet物件填入記錄資料
現在我們就可以建立DataSet物件，執行
DataAdapter的SQL查詢指令，將記錄資
料填入DataSet物件，如下所示：
Dim objDataSet As DataSet = New DataSet()
objDataAdapter.Fill(objDataSet, "Books")
DataTable 物
件名稱
25
14-3-1 將資料表填入DataSet物件-步驟4


步驟４：取出查詢結果的資料表記錄資料
在使用DataAdapter物件將資料表記錄填入
DataSet後，請使用For Each迴圈取出每一個
DataRow物件，也就是一筆記錄，如下所示：
Dim objRow As DataRow
For Each objRow in objDataSet.Tables("Books").Rows
out &= objRow("BookID") & vbNewLine
out &= objRow("BookTitle") & vbNewLine
…………
Next

如果需要指定第幾筆紀錄
objRow= objDataset.Tables(“Books”).Rows(0)

objDataset.Tables(“Books”).Rows.Count 可以得到
共幾筆紀錄
26
14-3-1 將資料表填入DataSet物
件-步驟5


步驟５：關閉資料庫連結
最後記得關閉資料庫連結，以此例建立的
Connection物件為objCon，如下所示：
objCon.Close()
27
14-3-2 DataGrid控制項顯示資料
表-說明

DataGrid控制項可以將資料來源的資料以
表格方式顯示，例如：將資料表的記錄資
料如同Excel試算表一般的顯示每筆記錄和
欄位資料。
28
14-3-2 DataGrid控制項顯示資料
表-建立
29
14-3-2 DataGrid控制項顯示資料
表-屬性
屬性
說明
Name
控制項名稱
CaptionText
控制項的標題文字
AllowSorting
設定是否可按一下資料欄的標頭，就以此欄位進行排序，預
設值 True 可以，False 為不可以
DataSource
設定控制項的資料來源，可以是 DataSet 和 DataTable 等物件
ReadOnly
控制項是否為唯讀，預設值 False 為不是，True 為唯讀
30
14-3-2 DataGrid控制項顯示資料
表-指定資料來源

當表單新增DataGrid控制項後，只需將資
料表的記錄資料填入DataSet物件，就可
以指定DataGrid控制項的DataSource屬
性，如下所示：
dtgOutput.DataSource = _
objDataSet.Tables("Books")

上述程式碼將DataGrid控制項的資料來源
指定成DataSet物件名為Books的
DataTable物件。
31
14-4 SQL的資料庫查詢


14-4-1 SQL語言的基礎
14-4-2 SQL的查詢指令
32
14-4-1 SQL語言的基礎-說明

「SQL」(Structured Query Language)為
「ANSI」(American National Standards
Institute) 標準的資料庫語言，SQL指令語
法可以用來存取和更新資料庫的記錄，目前
Access、SQL Server、Informix、Oracle
和Sybase等資料庫系統都支援ANSI的SQL
語言。
33
14-4-1 SQL語言的基礎-指令

SQL語言的指令可以分為4大部分，如下表所示：
• 「資料定義語言DDL」(Data Definition
Language)：屬於資料表建立和欄位定義的SQL指令。
• 「資料操作語言DML」(Data Manipulation
Language)：屬於資料表記錄插入、刪除和更新指令。
• 「資料查詢語言DQL」(Data Query Language)：
屬於資料表查詢指令，只有一個SELECT指令。
• 「資料控制語言DCL」(Data Control Language)：
屬於資料庫安全設定和權限管理指令。
34
14-4-2 SQL的查詢指令-語法

在SQL語言的資料查詢指令只有一個
SELECT指令，完整的指令語法如下所示：
SELECT 欄位1, 欄位2 FROM 資料表 WHERE conditions

上述SELECT指令的欄位1~2為記錄的欄位，
conditions為查詢條件，這個指令使用口
語來說是「從資料表取回符合WHERE子句
條件的記錄，顯示欄位1和2」。
35
14-4-2 SQL的查詢指令-“*”記
錄欄位

使用"*"符號代表所有資料表的欄位，表示
取回資料表記錄的所有欄位，如下所示：
SELECT * FROM Book

上述指令沒有WHERE子句，所以是將資料
表內所有的記錄和欄位取回。
36
14-4-2 SQL的查詢指令-單一查
詢條件的WHERE子句

SQL查詢指令的單一條件，WHERE子句的基本
規則和範例，如下所示：
• 文字欄位要加上單引號，如下所示：
SELECT * FROM Book WHERE BookID='F8111A'
• 數字欄位並不需要額外的字元括起，如下所示：
SELECT * FROM Book WHERE BookPrice>550
• 文字的欄位可以使用【LIKE】包含運算子配合"%"萬
用字元，此時查詢的條件子句只需包含的子字串就符
合條件，如下所示：
SELECT * FROM Book WHERE BookID LIKE '%1%'
• 數字或日期欄位可以使用<>、>、<、>=和<=不等
於、大於、小於、大於等於和小於等於等運算子建立
多樣化的查詢條件。
37
14-4-2 SQL的查詢指令-多重查
詢條件的WHERE子句

WHERE子句的查詢條件可以使用AND和OR邏輯
運算子連接，其基本語法，如下所示：
• AND且運算子：連接的前後條件都必須成立，整個條
件才能成立，例如：書號包含"3"且書名有"研究"的子
字串，如下所示：
SELECT * FROM Book WHERE BookID LIKE '%3%'
AND BookTitle LIKE '%研究%'
• OR或運算子：連接的前後條件只需任何一個成立即可，
例如：書號包含"3"或書名有"研究"的子字串，如下所
示：
SELECT * FROM Book WHERE BookID LIKE '%3%'
OR BookTitle LIKE '%研究%'
38
14-4-2 SQL的查詢指令-排序輸出

SQL的查詢結果可以指定欄位進行由小到大，或
由大到小排序，只需在SELECT指令的最後加上
ORDER BY子句即可，如下所示：
SELECT * FROM Book WHERE BookPrice>=500
ORDER BY BookPrice

上述查詢結果使用BookPrice欄位排序，預設是
由小到大的ASC，如果想倒過來由大到小，只需
加上DESC，如下所示：
SELECT * FROM Book WHERE BookPrice>=500
ORDER BY BookPrice DESC
39
14-4-2 SQL的查詢指令-SQL的
聚合函數
函數
說明
Count(Column)
計算記錄筆數
Avg(Column)
計算欄位平均值
Max(Column)
取得記錄欄位的最大值
Min(Column)
取得記錄欄位的最小值
Sum(Column)
取得記錄欄位的總計
StDev(Column)
統計樣本的標準差
StDevP(Column)
統計母體的標準差
Var(Column)
統計樣本的變異數
VarP(Column)
統計母體的變異數
40
14-5 SQL的資料庫操作


14-5-1 資料庫操作的SQL指令
14-5-2 圖書資料管理程式
41
14-5-1 資料庫操作的SQL指令INSERT插入記錄1

SQL插入記錄操作指令是新增一筆記錄到資料表，
INSERT指令的語法格式，如下所示：
INSERT INTO table (column1,column2,…..)
VALUES ('value1', 'value2 ', …)

INSERT指令的注意事項，如下所示：
• 不論是欄位或值的清單，都需要使用逗號分隔。
• INSERT指令VALUES的值，數值不用單引號包圍，
字元需要單引號包圍，日期/時間需要"#"符號括起。
• INSERT指令的欄位名稱清單，並不需要和資料表定
義的欄位數目或順序相同，只需選擇需要資料的欄位，
括號內的欄位名稱順序需和VALUES值的順序相同。
42
14-5-1 資料庫操作的SQL指令INSERT插入記錄2

在資料表Book插入一筆圖書記錄的SQL指
令，如下所示：
INSERT INTO Book
(BookID,BookTitle,BookAuthor,BookPrice,BookP
ubDate) VALUES ('F8915','ASP網頁製作徹底研究','陳會安
',580,#06/06/2000#)

上述SQL指令中欄位值為字串時使用單引號，
數字值沒有，日期/時間加上"#"符號。
43
14-5-1 資料庫操作的SQL指令DELETE刪除記錄

SQL刪除記錄指令是將資料表內符合條件的記錄
通通刪除掉，DELETE指令的語法格式，如下所
示：
DELETE FROM table WHERE conditions

DELETE指令的注意事項，如下所示：
• WHERE子句是DELETE指令的必要元素，如果沒有
WHERE子句，資料表內的所有記錄都會刪除。

在資料表Book刪除一筆圖書記錄的SQL指令，如
下所示：
DELETE FROM Book WHERE BookID='F8915'
44
14-5-1 資料庫操作的SQL指令UPDATE更新記錄1

SQL更新記錄指令是將資料表內符合條件的記錄，
更新指定欄位的內容，UPDAT E指令的語法格式，
如下所示：
UPDATE table SET column1 = 'value1' WHERE
conditions

如果更新的欄位不只一個，請使用逗號分隔，其
語法格式，如下所示：
UPDATE table SET column1 = 'value1' , column2 =
'value2' WHERE conditions

UPDATE指令的注意事項，如下所示：
• WHERE子句是必要元素，如果沒有WHERE子句，資
料表內所有記錄欄位都會更新。
• 更新欄位值是數值不用單引號包圍，字元需要使用單
45
引號包圍，日期時間為「#」符號。
14-5-1 資料庫操作的SQL指令UPDATE更新記錄2

在資料表Book更改記錄圖書資料的SQL指
令，如下所示：
UPDATE Book SET BookPrice=650,
BookPubDate=#6/15/2002# WHERE
BookID='F8534A'

上述SQL指令的WHERE條件為圖書書號
BookID欄位，然後使用SET子句更新欄位
資料。
46
14-5-2 圖書資料管理程式

在VB.NET應用程式的程式碼只需在開啟資料庫
連結和建立Command物件後，就可以使用
Command物件的ExecuteNonQuery方法執行
SQL指令，如下所示：
Dim intRowsAffected As Integer
intRowsAffected = objCmd.ExecuteNonQuery()

上述程式碼的變數intRowsAffected傳回資料表
影響的記錄數，因為是使用ExecuteNonQuery
方法執行SQL指令，所以並不會傳回
DataReader記錄資料。
47
14-5-2 圖書資料管理程式


不只 DataGrid 可以用 Data Binding 方式來為
其 DataSource 指定資料來源，只要控制項有
DataSource 欄位都可以用 Data Binding 方式
決定其展現內容。如 ListBox、ComboBox 等
均可以此方法動態決定其內容選項。
ListBox 決定 DataSource 之後，需要指定下列
屬性：
• DisplayMember: 選項顯示的欄位
• ValueMember: 選擇的值的欄位，以
ListBox1.SelectedValue 來得到此選項之值
48
補充：直接由 OleDbDataAdapter
控制項來指定 資料庫及DataSet
步驟一：拖曳 OleDdDataAdapter
49
步驟二：按下一步
50
步驟三：如果為新的資料庫，
按 “新增連接” 鈕
51
步驟四：於 “提供者” TabPage
選擇適當的連線資料，Access
應選擇 Microsoft Jet 4.0 OLE
DB Provider，按下一步
52
步驟五：按 “…” 鈕
53
步驟六：選擇 資料庫後
按 “開啟” 鈕，然後於資料連結內容 (步驟五) 視窗測試連線成功後，按 “確定”
54
步驟七：剛剛選定之
資料庫資訊會顯示於
“資料佩接器組態精靈”視窗，
按 “下一步”
55
步驟八：按 “下一步”
56
步驟九：輸入 SQL
語法
57
步驟十：按 “完成”
58
自動產生 OldDbConnection1
物件 及 OleDbDataAdapter1
物件，程式中可以直接使用
59