下載工具程式
下載Pdf檔
一、前言
由於經常在程式設計的討論區，看到網友們提問：「如何新增一筆資料」、「如何查詢」、「如何修改資料」等等，一些有關資料庫操作的話題。而看到一些初學 者對於程式的寫法都是由開啟連線、指定連線字串........關閉連線，一口氣地將所有跟資料庫操作有關的指令從頭寫到尾。等到在別處也有與資料庫有關 的操作也是再一次一口氣從頭寫到尾。這樣的寫法不只是造成往後程式維護的不容易，且浪費許多時間在重複相同的工作上。
因此，在接下來的 文章中會介紹一個由阿源哥哥開發並經常在使用的類別庫。該類別庫適用於MS SQL、Access、MySQL，可於程式一開始開發時就決定使用的資料庫的種類，亦可以於程式開發完後只要更改一下設定，不需重新編譯程式即可切換資 料庫。該類別庫不僅可以用在Windows Form上，也可用於Web Form上，且不限於使用VB.NET 或 C# 為程式開發語言。
二、準備工作
請將下載回來的檔案解壓縮後，您將會有兩個檔案「KeigenTools.exe」「Keigen.dll」，請將這兩個檔案放在相同的目錄中(目錄名 稱任意)。讀者在程式開發中主要會使用到「Keigen.dll」這個類別庫，但是由於筆者習慣將連線字串(ConnectionString)加密，因 此您可以利用「KeigenTools.exe」來產生專案所要用到的加密後的連線字串。接下來先說明「KeigenTools.exe」的使用方法。
程式執行後會出現如圖的視窗，會有兩個按鈕，讓使用者選擇所要作的工作。一個是可輸入任意的文字字串，並對其加/解密。另一個是輸入資料庫的一些基本訊 息，後會產生一組明碼及加密後的連線字串。產生完後的字串可按TextBox旁的「複製」鍵，將產生的字串複製到剪貼簿中，方便能貼到等一下會說明的程式 設定檔的連線字串中。
三、連線字串
由於一般來講，在一個專案中會使用相同的資料庫，且在程式開發測試階段與正式上線時，會使用不同的資料庫，因此會有切換連線字串的需求，如果將資料庫的 連線字串統一寫在一個檔案中管理是比較可行及有效率的。而在Windows Form會寫在app.config中，在Web Form會寫在web.config中。請讀者依您所開發的案子將下述的片斷碼放在設定檔的區段中。
上述的DataProvider，如果在程式開發的初始階段就決定使用何種資料庫(MS SQL、Access、MySQL)的話，這一段可省略不必寫。如果希望在程式開發完後只要更改一下設定，不需重新編譯程式即可切換資料庫的話，這一段必 須要依使用資料庫種類來更改設定：
ConnectionString的Value值就將依前述方法所產生的加密後的字串貼上即可。
四、在專案中參考
接下來在專案中主要是要使用「Keigen.dll」，請將該類別庫加入參考。
欲使用MySQL的讀者，下載安裝 Connector/Net 5.1，並將MySql.Data加入參考。
五、使用說明
因為資料庫的操作不外乎是「新增」「修改」「刪除」「查詢」等操作，而其中「新增」「修改」「刪除」皆為非查詢操作，都是準備好SQL指令(或預存程 序)，及參數，下達到資料庫即可，而本文旨在說明「Keigen.dll」組件的使用方法，並非SQL指令的教學，故在此部分會以一個在資料庫中插入一筆 資料的簡單例子，並以文字敘述方式、參數式、預存程序等三種方式說明。雖然會說明一下純文字敘述的SQL指令，但是由於SQL Injection等安全性考量，筆者建議以參數式或是預存程序來對資料庫操作。「查詢」會以DataReader、DataSet、DataTable 等三種方式讀出資料。
接下來會以MSSQL、 Access、MySQL、通用型，等四種資料庫依序作說明，而各種資料庫的使用方式其實只是「大同小異」只要瞭解其中一種用法，其它三種很快就能明白如 何使用。或許有讀者會問，既然已經有了「通用型」的可適用於前三種資料庫，那就只用「通用型」就好了？因為各種資料庫系統所能用的欄位型別、SQL指令並 不完全相同，欲使用通用型，那在程式設設當中就必須選用這三種資料庫系統共通的型別及指令這樣才能在不更改程式碼的原則下，隨時切換資料庫。若是一個專案 在開發的初期已經非常明確地知道要使用何種資料庫，且將來沒有可能換成別的資料庫的話，筆者建議在一開始就使用合適的作法，這樣可以把該資料庫系統的特長 髮揮到最大。除非您所開發的系統將來是要強調可通吃各主流的資料庫系統，那就可使用「通用型」但是相對的必須因為選用共通的型別及指令，而必須有所犧牲。
接下來的說明例子會以一個名為Test的資料表，有四個欄位uid、Name、Salary、Birthday，資料型態分別為：數字(自動增量)、文字、數字、日期。讀者若有練習的需求請自行建立。
五：一、使用MSSQL
在程式的最前面，引入適當的NameSpace
Imports Keigen.DataBase.MSSQL
Imports System.Data.SqlClient
非查詢操作
參數式的使用方法：
Dim sql As String
sql = "INSERT INTO Test (Name, Salary, Birthday) VALUES(@Name, @Salary, @Birthday)"
Dim params As SqlParameter() = {New SqlParameter("@Name", "王大頭"), _
New SqlParameter("@Salary", 30000), _
New SqlParameter("@Birthday", "1963/10/21")}
Db.ExecuteNonQuery(CommandType.Text, sql, params)
文字敘述的使用方法：
Dim sql As String
sql = "INSERT INTO Test (Name, Salary, Birthday) VALUES('林小喬', 28000, '1995/8/8')"
Db.ExecuteNonQuery(CommandType.Text, sql)
使用預存程序：
Dim params As SqlParameter() = {New SqlParameter("@Name", "莊自強"), _
New SqlParameter("@Salary", 33000), _
New SqlParameter("@Birthday", "1986/11/12")}
Db.ExecuteNonQuery(CommandType.StoredProcedure, "addUser", params)
預存程序：
CREATE PROCEDURE addUser
@Name nvarchar(20),
@Salary int,
@Birthday datetime
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Test
(Name, Salary, Birthday)
VALUES(@Name, @Salary, @Birthday)
END
GO
查詢操作
使用SqlDataReader
Dim sql As String = "SELECT * FROM Test WHERE Name = @Name"
Dim params As SqlParameter() = {New SqlParameter("@Name", "莊自強")}
Using dr As SqlDataReader = Db.ExecuteReader(CommandType.Text, sql, params)
If dr.Read Then
MessageBox.Show(dr.Item("Salary"))
End If
End Using
使用DataTable
Dim sql As String = "SELECT * FROM Test WHERE Salary > @Salary"
Dim params As SqlParameter() = {New SqlParameter("@Salary", 20000)}
Dim dt As DataTable = Db.ReadDataTable(CommandType.Text, sql, params)
Me.DataGridView1.DataSource = dt
使用DataSet
Dim sql As String = "SELECT * FROM Test WHERE Salary > @Salary"
Dim params As SqlParameter() = {New SqlParameter("@Salary", 20000)}
Dim ds As DataSet = Db.ReadDataSet(CommandType.Text, sql, params)
Me.DataGridView1.DataSource = ds.Tables(0)
由以上幾個查詢的例子，讀者應該也是能看出除了準備SQL指令及參數的部分外，真正對資料庫操作的也是簡化到只有一行，只要使用適當的物件來接值並接續資料讀進來所要做的處理及可。
五：二、使用Access
在程式的最前面，引入適當的NameSpace
Imports Keigen.DataBase.Access
Imports System.Data.OleDb
非查詢操作
參數式的使用方法：
Dim sql As String = "INSERT INTO Test (Name, Salary, Birthday) VALUES(?, ?, ?)"
Dim params As OleDbParameter() = {New OleDbParameter("?", "王大頭"), _
New OleDbParameter("?", 30000), _
New OleDbParameter("?", "1963/10/21")}
db.ExecuteNonQuery(CommandType.Text, sql, params)
文字敘述的使用方法：
Dim sql As String
sql = "INSERT INTO Test (Name, Salary, Birthday) VALUES('林小喬', 28000, '1995/8/8')"
db.ExecuteNonQuery(CommandType.Text, sql)
查詢操作
使用OleDbDataReader
Dim sql As String = "SELECT * FROM Test WHERE Name = ?"
Dim params As OleDbParameter() = {New OleDbParameter("?", "王大頭")}
Using dr As OleDbDataReader = db.ExecuteReader(CommandType.Text, sql, params)
If dr.Read Then
MessageBox.Show(dr.Item("Salary"))
End If
End Using
使用DataTable
Dim sql As String = "SELECT * FROM Test WHERE Salary > ?"
Dim params As OleDbParameter() = {New OleDbParameter("?", 20000)}
Dim dt As DataTable = db.ReadDataTable(CommandType.Text, sql, params)
Me.DataGridView1.DataSource = dt
使用DataSet
Dim sql As String = "SELECT * FROM Test WHERE Salary > ?"
Dim params As OleDbParameter() = {New OleDbParameter("?", 20000)}
Dim ds As DataSet = db.ReadDataSet(CommandType.Text, sql, params)
Me.DataGridView1.DataSource = ds.Tables(0)
五：三、使用MySql
在程式的最前面，引入適當的NameSpace
Imports Keigen.DataBase.MySQL
Imports MySql.Data.MySqlClient
非查詢操作
參數式的使用方法：
Dim sql As String
sql = "INSERT INTO Test (Name, Salary, Birthday) VALUES(?Name, ?Salary, ?Birthday)"
Dim params As MySqlParameter() = {New MySqlParameter("?Name", "王大頭"), _
New MySqlParameter("?Salary", 30000), _
New MySqlParameter("?Birthday", "1963/10/21")}
Db.ExecuteNonQuery(CommandType.Text, sql, params)
文字敘述的使用方法：
Dim sql As String
sql = "INSERT INTO Test (Name, Salary, Birthday) VALUES('林小喬', 28000, '1995/8/8')"
Db.ExecuteNonQuery(CommandType.Text, sql)
使用預存程序：
Dim params As MySqlParameter() = {New MySqlParameter("Name", "莊自強"), _
New MySqlParameter("Salary", 33000), _
New MySqlParameter("Birthday", "1986/11/12")}
Db.ExecuteNonQuery(CommandType.StoredProcedure, "addUser", params)
預存程序：
CREATE PROCEDURE addUser (Name varchar(20), Salary int,Birthday datetime)
BEGIN
INSERT INTO Test
(Name, Salary, Birthday)
VALUES(Name, Salary, Birthday);
END
查詢操作
使用MySqlDataReader
Dim sql As String = "SELECT * FROM Test WHERE Name = ?Name"
Dim params As MySqlParameter() = {New MySqlParameter("?Name", "王大頭")}
Using dr As MySqlDataReader = Db.ExecuteReader(CommandType.Text, sql, params)
If dr.Read Then
MessageBox.Show(dr.Item("Salary"))
End If
End Using
使用DataTable
Dim sql As String = "SELECT * FROM Test WHERE Salary > ?Salary"
Dim params As MySqlParameter() = {New MySqlParameter("?Salary", 20000)}
Dim dt As DataTable = Db.ReadDataTable(CommandType.Text, sql, params)
Me.DataGridView1.DataSource = dt
使用DataSet
Dim sql As String = "SELECT * FROM Test WHERE Salary > ?Salary"
Dim params As MySqlParameter() = {New MySqlParameter("?Salary", 20000)}
Dim ds As DataSet = Db.ReadDataSet(CommandType.Text, sql, params)
Me.DataGridView1.DataSource = ds.Tables(0)
五：四、通用型
為了讓程式可以寫一次就可通用於MSSQL、Access、MySQL，就必須找出這三個資料庫系統的共通點，看了前面對於這三種資料庫系統的操作介紹，我們可以發現SQL指令如果是使用文字敘述的方式，對於三個系統是完全一樣的，而參數的表示方式就不一樣了：
@Name ß MSSQL
? ßAccess
?Name ß MySQL
而記憶中，Access好像是可以使用預存程序，但是筆者一時找不到作法，使用參數的作法又是防止SQL Injection的唯一好方法，因此我們會再增加一個Helper來解決三個系統參數表示的不同。接下來的說明就只針對使用參數的方式來說明。
在程式的最前面，引入適當的NameSpace
Imports Keigen.DataBase.General
Imports System.Data.Common
非查詢操作
參數式的使用方法：
Dim sql As String = "INSERT INTO Test (Name, Salary, Birthday) VALUES(" & _
Db.GetCommandParamName("Name") & "," & _
Db.GetCommandParamName("Salary") & "," & _
Db.GetCommandParamName("Birthday") & ")"
Dim params As DbParameter() = {Db.Parameter("Name", "李大同"), _
Db.Parameter("Salary", 31000), _
Db.Parameter("Birthday", "1967/10/12")}
Db.ExecuteNonQuery(CommandType.Text, sql, params)
查詢操作
使用DbDataReader
Dim sql As String = "SELECT * FROM Test WHERE Name = " & Db.GetCommandParamName("Name")
Dim params As DbParameter() = {Db.Parameter("Name", "李大同")}
Using dr As DbDataReader = Db.ExecuteReader(CommandType.Text, sql, params)
If dr.Read Then
MessageBox.Show(dr.Item("Salary"))
End If
End Using
使用DataTable
Dim sql As String = "SELECT * FROM Test WHERE Salary > " & Db.GetCommandParamName("Salary")
Dim params As DbParameter() = {Db.Parameter("Salary", 20000)}
Dim dt As DataTable = Db.ReadDataTable(CommandType.Text, sql, params)
Me.DataGridView1.DataSource = dt
使用DataSet
Dim sql As String = "SELECT * FROM Test WHERE Salary > " & Db.GetCommandParamName("Salary")
Dim params As DbParameter() = {Db.Parameter("Salary", 20000)}
Dim ds As DataSet = Db.ReadDataSet(CommandType.Text, sql, params)
Me.DataGridView1.DataSource = ds.Tables(0)
總結
經由前面說明的幾個資料庫操作實例，讀者是否發現，除了準備SQL指令及參數的部分外，真正對資料庫操作的都是簡化到只有一行，而在程式設計的實務上除 了資料庫操作外，也有許多的地方都類似這樣，有些部分是會隨著程式開發場合有所變動，而有些部分是不變的。因此如果能分析出「變動的部分」與「不變的部 分」將它們分離開來，將「不變的部分」整理成類別庫來使用，這樣可大大降低程式開發的時間，也可降低錯誤發生的機率及提高程式品質。
本文除了介紹【資料庫的瑞士刀】的使用方法外，另外也想讓程式設計的新鮮人多了一個新思維，原來程式設計也可這樣「偷懶」的。至於如何分析程式「不變的部分」將它們分離開來，將留待下次有機會時再解釋及說明。