8. 程式完全控制資料庫連線之 1 前面從資料庫連線到資料欄位顯示與資料異動, 通通可以用參數設定或部分的程式撰寫來完成. 現在我們介紹從資料庫連線開始就以程式完成. 這個部份的工作只有一個, 寫使用者登錄帳號密碼時的檢查程式. 我們以下先使用 Access 完成這個工作 (1). 建新的 table: UserTable(UserID, Pwd) 包含 UserID 跟 Pwd 兩個文字型態的欄位 (2). 畫面設計 : (3). 程式 Sub UserLogin() Dim SqlConn As OleDbConnection Dim SqlCmd As OleDbCommand Dim Reader As OleDbDataReader 這是連 access 資料庫的固定寫法, 請注意寫的內容與順序 SqlConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\pluto\course\ 講義 \db\samples\dbc.mdb") SqlConn.Open() SqlCmd = New OleDbCommand("select * from UserTable where UserID = '" & TextBox1.Text & "'", SqlConn) 從這裡開始是抓出一個資料的寫法 Reader.Read() If Reader.HasRows() = True Then MessageBox.Show(Reader.Item("Pwd")) 這樣會把 pwd 秀出來, 如何改寫成密碼檢查? End If SqlConn.Close() End Sub 1
(4). SQL Server 呢? 前面是 Access 的版本, 如何完成 SQL Server 的版本? 同樣的, 請在 SQL Server 上建立 table: UserTable(UserID, Pwd), 同樣的畫面設計, 但是程式碼不同, 請自己比較 Sub UserLogin() Dim SqlConn As SqlConnection Dim SqlCmd As SqlCommand Dim Reader As SqlDataReader 注意 SQL Server 要設定帳號密碼 sa/sa123, 不可以直接使用本機帳號 SqlConn = New SqlConnection("user id=sa;password=sa123;initial catalog=mytest;data source=(local);connect Timeout=10") SqlConn.Open() SqlCmd = New SqlCommand("select * from UserTable where UserID = '" & TextBox1.Text & "'", SqlConn) If Reader.HasRows() = True Then MessageBox.Show(Reader.Item("Pwd")) 這樣會把 pwd 秀出來, 如何改寫成密碼檢查? End If SqlConn.Close() End Sub (4). 有錯? 注意你有沒有 imports Access: Imports System Imports System.Data Imports System.Data.OleDb SQL Server: Imports System Imports System.Data Imports System.Data.SqlClient 2
9. 程式完全控制資料庫連線之 2 現在進行更複雜的工作, 或者說用程式設計的方式完成先前的工作 如畫面所示, 除了帳號密碼之外, 重做一次 GetItem 的工作, 但是這次我們全部都直接以程式撰寫完成 Q: 回想一下, 需要完成此工作需要哪些資訊 (1). 抓出總筆數 SqlConn.Open() sql = "select count(*) from supplier" 注意 SQL 指令的使用, 這樣有什麼好處?? SqlCmd = New OleDbCommand(sql, SqlConn) 'cn = SqlCmd.ExecuteScalar.ToString() 也可以只用這行取得筆數 Reader.Read() 每次讀取資料前記得呼叫 Read() cn = Reader.Item(0) (2). 知道總筆數後, 用迴圈把 sid 塞入 ComboBox1 注意 : 這裏之前漏掉什麼? SQL 指令呢? ComboBox1.Items.Clear() why to do that? For i = 0 To cn 1 why cn-1, not cn? Reader.Read() 每次讀取資料前記得呼叫 Read() ComboBox1.Items.Add(Reader.Item(0)) sid 這個欄位是第 0 個欄位 也可以寫成 ComboBox1.Items.Add(Reader.Item( sid )) Next (3). 每次 ComboBox1 改變時, 把 ComboBox1.text 抓出來, 然後到資料庫內將這筆資料抓出來 sql = "select * from supplier where sid = '" & ComboBox1.Text & "'" 注意 sql 指令的變化, 然後其餘的抓取資料都跟前面相似 3
注意 : 在資料庫存取這部份有許多小技巧, 請同學發展自己的最佳模式. 以下整理一些常見的花招 a. 資料庫連線基本動作. 請先搞清楚你是連哪一種資料庫, 前面我們列出 Access 跟 SQL Server 的連線範例, 這裏再重複一次 Access 的動作 Dim SqlConn As OleDbConnection Dim SqlCmd As OleDbCommand Dim Reader As OleDbDataReader Dim sql as String SqlConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\pluto\course\ 講義 \db\samples\dbc.mdb") SqlConn.Open() sql = select * from supplier 重點是你要會寫 sql, 複雜的 sql 指令一定要會 SqlCmd = New OleDbCommand(sql, SqlConn) 到此為止是基本的作法, 以下是真正抓出資料的作法 Reader.Read() If Reader.HasRows() Then TextBox3.Text = Reader.Item("sid") 抓資料時可以直接寫欄位名稱 TextBox4.Text = Reader.Item(1) 抓資料時也可以寫欄位編號, 由 0 開始 TextBox5.Text = Reader.Item(2) TextBox6.Text = Reader.Item(3) End If b. sql 這個字串的內容扮演重要關鍵, 如果是複雜的 sql 指令時, 很容易弄錯. 想想看有沒有好辦法可以比較不會出錯 Ans: (i) 資料庫內先跑跑你的 sql 指令, (ii) 在 debug 模式看 sql 指令有沒有問題 c. 總筆數的計算 : 直接用 count(*) 比較快. 同樣的算總合, 最大最小等也可以使用 sql 指令直接擺平 d. 有沒有人覺得每次使用前都得連資料庫, 真是太麻煩了.. 怎麼偷懶呢? Ans: 放在 form load 這個函數, 在 form 剛啟動時就連資料庫 ; 或者是設計成使用者在某個動作之後 ( 例如按下連線按鈕等 ) 在連資料庫. 以後需要查詢資料時只要改變 sql 跟後續的抓資料動作就可以. 這一個做法有個很大的危險 4
10. 資料異動 新增修改刪除除了 select 之外, 別忘記 insert/update/delete 這三個要角, 現在我們設計一個具有這三個功能的程式 (1). 設計畫面 (2). Insert. 主要的工作有兩部份 : 一是把 sql 字串內寫成 insert 的指令, 二是讓 SqlCmd 能夠執行這個非 select 的 SQL 命令 程式流程如下 檢查欲輸入的資料是否已經存在 If 資料已經存在 顯示錯誤訊息 Else 輸入資料 檢查資料輸入是否正確 End if 程式流程代表著你的邏輯思考, 先把流程想好再把每一個步驟轉換成程式碼. 程式碼轉換只是程式撰寫的熟練度, 真正重要的是邏輯思考能力 (3). Insert 功能的程式碼如下. 如果你的思考流程不同, 即使是相同 insert 功能也會有不同的寫法. 當然思考流程的結果絕對是受到 user 要求所影響 5
Dim SqlCmd As OleDbCommand OleDbConnection 宣告放在 global 變數 Dim Reader As OleDbDataReader Dim sql As String 宣告一個變數放 sql 命令是我喜歡的習慣 Dim x, cn As Integer sql = "select count(*) from supplier where sid = '" & TextBox3.Text & "'" 檢查現在要新增的資料是不是已經存在, 避免重複輸入造成錯誤 why 重複輸入會有錯誤? 什麼叫重複輸入? SqlCmd = New OleDbCommand(sql, SqlConn) SqlConn 是 global 變數 Reader.Read() cn = Reader.Item(0) 取出筆數, 如果 cn 是 0 或是 1 分別代表? If cn = 0 Then 'not found, insert the data ( 這個註解可以提醒自己邏輯流程 ) sql = "insert into supplier values ('" sql = sql & TextBox3.Text & "', '" sql = sql & TextBox4.Text & "', '" sql = sql & TextBox5.Text & "', '" sql = sql & TextBox6.Text & "')" sql 命令的決定是基本的字串處理, 這是一個簡單的例子 還有其他的做法嗎? 想想其他的可能作法以及優缺點, 找出你喜歡的模式 SqlCmd = New OleDbCommand(sql, SqlConn) 這裡跟前面的 select 命令一樣 x = SqlCmd.ExecuteNonQuery() 這裡跟 select 的命令不一樣 If x <> 1 Then ExecuteNonQuery 傳回資料異動筆數 MessageBox.Show("insert error") Else MessageBox.Show("insert a new data") End If End If (4). update 跟 delete 功能, 請同學自行完成注意操作上的差異 update: 每個欄位都可以修改, 所以應該是把每個欄位 ( 不管使用者是不是真的有改到 ) 都放入 update 命令中, 但是主鍵是在 where 而不在異動的欄位之內 e.g. update supplier set sname = abc, status = 30, city = London where sid = s6 delete: 欲決定單一筆資料只需要主鍵, 所以一般只需要 e.g. delete from supplier where sid = s1 這樣的指令就可以, 而不必每個欄位都在 where 條件內出現 不論是 update 或 delete, 其實就是修改 sql 字串的內容, 但是邊想 SQL 命令邊用 VB.net 程式碼寫 sql 字串很容易出錯, 可以先在 Access (or SQL Server) 上測試你想好的 SQL 命令是否正確, 然後在.net 上修改 sql 字串. 6
11. 資料異動 新增修改刪除 搭配畫面的設計現在把問題弄得更複雜, 設計一個正常操作流程的程式畫面規劃與功能思考. 新增修改刪除三個功能的操作特性都不一樣, 例如新增顯然是要把一筆資料中每個欄位都輸入後才可以存入資料庫, 但是刪除只需要指定主鍵內容確認刪除目標就可以, 而不需要全部的欄位都考慮. 所以我們在畫面設計時必須把所有的功能一起考慮 介面設計. 畫面跟前面的程式很像, 不過把 TextBox 的 ReadOnly 屬性設為 true, 讓使用者不能修改資料. 當使用者希望新增資料時, 必須先按下 insert, 然後畫面轉變成讓使用者輸入資料, 同時關閉另外兩個功能按鈕. 我們使用同樣的概念設計 update 跟 delete 以下是三個功能的畫面設計與功能描述 按下 Insert 畫面的轉變 1. TextBox 的 ReadOnly 設為 true 2. TextBox 的內容清空 3. Update 跟 Delete 的 Enabled 設為 false 4. Insert 的文字改為 Save 按下 Save 畫面的轉變 1. TextBox 的 ReadOnly 設為 false 2. Update 跟 Delete 的 Enabled 設為 true 3. Save 的文字改為 Insert 4. 新增資料進資料庫 7
按下 Update 畫面的轉變 1. TextBox 的 ReadOnly 設為 true 2. Insert 跟 Delete 的 Enabled 設為 false 3. Update 的文字改為 Save 按下 Save 畫面的轉變 1. TextBox 的 ReadOnly 設為 false 2. Update 跟 Delete 的 Enabled 設為 true 3. Save 的文字改為 Insert 4. 修改資料進資料庫 按下 Delete 畫面的轉變 1. 將畫面上的這筆資料刪除 2. 將 TextBox 的文字清除 3. 重做 GetItems (why?) 請同學認真做這個作業, 這是比較接近實際開發的功能, 同時包含著視窗畫面處理之程式邏輯流程, 是一個很好的練習工作 8
12. 複合的資料查詢 想想 TIP 上查詢個人或班級的課程資訊, 允許使用者輸入的查詢條件是多樣的. 這種多樣的查詢條件是實際開發系統時常常碰到, 現在就來做做一個練習 (1). 如下圖, 這是查詢某零件之供應商功能, 使用者可以輸入兩個條件 : pid: 哪些供應商可以提供這種零件 city: 零件之庫存城市範例 : 輸入 pid 是 p1, city 是 London, 代表我們要找可以提供 p1 且零件庫存在 London 的供應商資訊 ( 符合條件的有 s1, s2 兩個 ) (2). 思考 : 要怎麼做 a. 簡化問題. 整個問題其實很簡單, 就是輸入條件, 然後顯示資料 b. 輸入條件部分 : 因為使用者可以輸入 pid 或 city 或兩個都輸入, 所以必須是彈性的條件. 想想怎麼做才能把這兩個彈性條件寫入 sql c. 顯示資料的部份 : d. 畫面的處理 : 包含 city 下拉式選單內的資料如何出現, 上一筆下一筆怎麼弄 (3). 輸入條件部分, 也就是前面範例中的 sql 字串. 請先在 Access 內寫好預期的 sql, 然後移植到.Net 上把變動的部份改過這部份請同學可以自行練習, 簡單來說就是不管你輸入的條件為何, 字串 sql 都必須是正確可以執行且可以反映你的查詢條件 (4). 顯示資料的部份. 前面都是使用 OleDbDataReader, 現在介紹另一種做法 9
使用 DataAdapter 與 DataSet, 請仔細注意二者的搭配使用 Dim Adapter As OleDbDataAdapter Dim DS As DataSet SqlConn 跟前面的範例一樣, 自己想辦法解決 sql: 這個字串決定你的查詢條件, 這是這次練習的重點之一 Adapter = New OleDbDataAdapter(sql, SqlConn) DS = New DataSet() Adapter.Fill(DS, "Parts") 給這個 DataSet 一個名字 這三行是使用 DataAdapter 跟 DataSet 的固定模式 cn = DS.Tables("Parts").Rows.Count ' 這個 DataSet 內的資料筆數 LabelCN.Text = cn what is it? i = 1 只顯示第一筆 如何使用 DataSet 顯示資料在前面已經介紹過, 請自行複習 TextSid.Text = DS.Tables("Parts").Rows(i - 1).Item("sid") TextSname.Text = DS.Tables("Parts").Rows(i - 1).Item("sname") TextStatus.Text = DS.Tables("Parts").Rows(i - 1).Item("status") TextCity.Text = DS.Tables("Parts").Rows(i - 1).Item("city") LabelNum.Text = i what is it? 注意這個程式不是複製過去就可以直接執行的 這個練習使用 DataAdapter 跟 DataSet, 先前使用的是 DataReader, 二者的差異? a. 記憶體與資料庫連線 DataReader 是每一次呼叫 read 時都到資料庫抓出一筆資料, 這樣比較節省記憶體, 但是每次必須一直保持資料庫連線 DataAdapter 跟 DataSet 是第一次使用時在資料庫抓出全部資料放入記憶體中, 記憶體負擔比較大, 但是必須注意記憶體資料與資料庫資料之一致性注意 : 二者優缺點剛好顛倒 b. 上一筆與下一筆因為 DataReader 只能每次抓一筆, 所以基本上不能抓前一筆或直接指定第幾筆資料 ; DataAdapter 跟 DataSet 就沒這個限制 c. 資料修改與異動 ( 在 insert/update/delete 上 ) DataReader 如前面的練習, 可以在 sql 下指令後, 使用 ExecuteNonQuery 這個方法執行, 直接異動資料庫內的資料 DataAdapter 跟 DataSet 就像是在記憶體中複製一個 ( 符合你的 sql 條件 ) 的資料表格, 所以異動資料就是在這個複製的資料表格上工作, 最後再把異動處改回資料庫內. 這樣或許會有資料不一致的問題出現 10
建議 a. 如果是 insert/update/delete, 請使用 DataReader 最直接, 前面的範例程式可以直接使用 b. 如果是 select, 請注意你工作需要的特性. 如果是很單純的從頭到尾把資料逐筆抓出來, 使用 DataReader 如果資料抓出來後還需要上一筆下一筆或直接指定第幾筆之類的工作, 請使用 DataAdapter 跟 DataSet c. 凡事都可能有例外, 所以最重要的是你培養出自己的喜好與感覺, 找到適合自己的做法 (5). 畫面的處理 a. 城市 的下拉式選單, 使用 DataReader 抓出相異的程式資料 sql = "select distinct city from parts order by city" SqlCmd = New OleDbCommand(sql, SqlConn) ComboCity.Items.Clear() While Reader.Read() 這樣寫就可以從第一筆抓到最後一筆, 但事先不知道筆數 ComboCity.Items.Add(Reader.Item(0)) End While b. 上一筆下一筆的寫法哪裡會紀錄目前顯示到第幾筆?? 想想看 c. 畫面控制在製作畫面前必須先考慮好使用者操作畫面時的感覺, 整個流程想一次, 這樣你就能做好畫面設計 11