Visual C# 2010 程式設計經典

1. Visual C# 2010 程式設計經典 第16章 ADO .NET資料庫 存取與交易處理

2. 16.1 如何引用ADO .NET命名空間 • .NET Framework 架構中是由所用資料庫(資料來源)來決定使用 SQL .NET Data Provider 或是 OLE DB .NET Data Provider。 • 兩者 .NET Data Provider 分屬不同的命名空間 需使用 Imports 敘述來引用對應 ADO .NET 的 命名空間。 才能在撰寫程式時，使用簡潔 ADO .NET 物件 來存取資料庫。

3. 一. System.Data • 是ADO .NET 命名空間的核心，大部分是由構成ADO .NET 架構的類別所組成。 • 這些類別是 Managed 應用程式用來存取資料的主要方法。 • 它定義 Tables、Rows、Columns、Constraints 和 DataSet 所代表的型別。 • DataSet 類別是位於 System.Data 命名空間中，撰寫程式時，若用到 DataSet 類別，需在程式開頭用 using 敘述來引用此命名空間，寫法： using System.Data; // 引用ADO .NET基礎物件

4. 二. System.Data.OleDb • 此命名空間允許連接到 OLE DB 這類型資料來源、接受 SQL 查詢和透過 Fill 方法，將資料填入 DataSet。 • 資料來源包括： Access、Excel、SQL Server 7.0 以上版本的資料庫…等。 • 在程式中用這類型資料庫，需在程式開頭用 using 敘述引用此命名空間，寫法： using System.Data.OleDb; // 引用OLE DB 資料來源的物件 • 在 OLE DB .NET Data Provider 下，所使用的 ADO .NET 物件名稱前面需加上 OleDb，如：OleDbConnection、OleDbCommand、OleDbDataReader… 等。

5. 三. System.Data.SqlClient • 此命名空間允許直接連接 SQL Server 7.0(含)以上版本的資料庫。 • 在程式中用此命名空間，需在程式開頭用 using 敘述含入 System.Data.SqlClient，寫法： using System.Data.SqlClient; • 在 SQL .NET Data Provider 下，所用的 ADO .NET物件名稱的前面必須加上Sql。如：SqlConnection、SqlCommand、SqlDataReader … 等

6. 下表即是ADO .NET在各所屬命名空間中各類別命名對照表：

7. 16.2 如何使用Connection物件16.2.1 如何使用Connection物件連接資料庫 • ADO .NET提供的Connection物件，主要可用來與資料來源之間建立連接。 • 下面介紹在ADO .NET架構下，如何使用Connection來開啟或關閉連接SQL Server與OLE DB資料來源。

8. Case1 引用System.Data.OleDb命名空間(適用Access 2003、Excel 2003…等以上版本) • 引用命名空間：using System.Data.OleDb ; • 建立連接字串：宣告名稱為cnStr的字串變數，用來存放資料庫的連線字串，並指定資料庫所在的真實路徑。string cnStr = "Provider=Microsoft.Jet.OLEDB.4.0; " + "Data Source=資料庫真實路徑" ; • 宣告OleDbConnection資料庫連接物件：OleDbConnection cn;

9. 建立OleDbConnection資料庫連接物件：建立OleDbConnection物件並指定資料庫的連接字串，OleDbConnection物件名稱為「cn」。cn = new OleDbConnection(cnStr); • 使用Open方法開啟與資料庫的連接：cn.Open(); • 完成資料庫存取後再使用Close方法關閉與資料庫的連接：cn.Close();

10. Case2 引用System.Data.SqlClient命名空間(連接SQL Server 7.0以上版本的資料庫伺服器) • SqlConnection及OleDbConnection物件的資料庫連線字串寫法不一樣。 • 下面寫法示範如何連接至SQL Server。 01 using System.Data.SqlClient; ………… 02 SqlConnection cn ; 03 string cnStr = "Server=localhost ;database=資料庫名稱 ;uid=sa;pwd=;" ; 04 cn = new SqlConnection(cnStr); 05 cn.Open() ; // 開啟與資料庫的連線 ………… 06 cn.Close() ; // 關閉與資料庫的連線

11. 以下是SqlConnection物件連接字串的參數設定： • server：可指定資料庫的伺服器名稱、IP位址、localhost(代表本機)。 • database：SQL Server資料庫的名稱。 • uid：資料庫連接帳號，sa表示使用SQL Server資料庫管理者帳號。 • pwd：資料庫連接密碼。 • 若uid與pwd都不加，可以改用「Integrated Security=True」，則表示使用目前登入系統的Windows帳號來連接 SQL Server。如果在ASP .NET網頁(Web Form)中，根據不同版本的IIS所使用的帳號會不一樣。譬如：IIS 5(Windows XP)是使用「ASPNET」帳號；IIS 6(Windows Server 2003)及IIS 7(Windows Server 2008)是使用「NETWORK SERVICE」帳號。 • 如果要連接到SQL Server伺服器名稱是「Server1」、使用者帳號是「sa」、密碼是「1234」、且開啟的資料庫為「Northwind」，其連接字串寫法如下： string cnStr = "Server=Server1;database=Northwind;uid=sa;pwd=1234;";

12. Case3 引用System.Data.SqlClient命名空間連接SQL Server Express資料庫檔案 • 下面寫法示範如何連接至SQL Server Express的「Northwind.mdf」資料庫檔案。 01 using System.Data.SqlClient; ………… 02 SqlConnection cn 03 String cnStr = "Data Source=.\\SQLExpress;" + "AttachDbFilename=|DataDirectory|Northwind.mdf;" + "Integrated Security=True;User Instance=True;"; 04 cn = new SqlConnection(cnStr); 05 cn.Open() ; // 開啟與資料庫的連線 ………… 06 cn.Close() ; // 關閉與資料庫的連線

13. 以下是SqlConnection物件連接字串的參數設定 • Data Source用來設定主機名稱。指定「.\\SQLExpress」表示要連接本機的SQLExpress實體。 • AttachDbFilename用來指定資料庫檔名稱。「|DataDirectory|」表示目前的資料庫預設資料夾路徑，指定「|DataDirectory|Northwind.mdf」表示要連接目前執行檔下的Northwind.mdf。 • Integrated Security用來指定是否使用Windows的帳號認證來連接資料庫。指定為True表示要使用Windows的帳號認證來連接資料庫。 • User Instance用來指定SQL Server Express是否由新的使用者實體來執行。

14. 16.2.2 Connection物件常用成員

15. 操作步驟請參閱16-8~16-12頁

16. 16.2.3 如何使用using與Connection物件連接資料庫 • 有時我們可能會忘了使用Close方法，或不想明確的關閉連接資料庫，但又想要釋放Connection的連接資源，此時就可以使用using{…}區塊來達成。 • 在 C# 最常使用using{…} 敘述區塊來處理資料庫連接的程式碼，在using中宣告並建立Connection物件，當離開using{…} 敘述區塊時，Connection物件即馬上被釋放掉。其寫法如下： using(SqlConnection cn = new SqlConnection()) { cn.ConnectionString = "Data Source=.\\SQLExpress;" + "AttachDbFilename=|DataDirectory|Northwind.mdf;" + "Integrated Security=True;User Instance=True;" ; // 處理資料庫的程式敘述 cn.Open(); } // 當離開using區塊時即馬上釋放Connection物件的資源

17. ConnectionDemo2.sln範例示範使用using{…} 敘述區塊。執行結果如下圖，當表單載入時即在using 敘述建立cn物件為SqlConnection類別，並使用對話方塊顯示目前資料庫的連接狀態，當離開using{…} 敘述區塊時，cn物件馬上被釋放掉。

18. 14 using (SqlConnection cn = new SqlConnection()) 15 { 16 cn.ConnectionString = "Data Source=.\\SQLExpress;" 17 + "AttachDbFilename=|DataDirectory|Northwind.mdf;" 18 + "Integrated Security=True;User Instance=True;"; 19 cn.Open(); 20 if (cn.State == ConnectionState.Open) 21 { 22 MessageBox.Show("資料庫已連接", "目前狀態"); 23 } 24 }

19. 16.2.4 如何使用應用程式組態檔存取 資料庫的連接字串 • 前面兩個範例都將連接字串寫死在程式中，此種做法有很大的問題，若應用程式內有多個表單都必須連接到相同的資料庫，將來應用程式要安裝到使用者環境時，即要針對使用者的環境逐一重設每一個表單的資料庫連接字串，然後再重新編譯應用程式，如此費時又費力。因此比較好的方式就是將資料庫連接字串設定在應用程式組態檔中，其好處是當連接字串有改變時，您可以透過文字檔直接修改連接字串就可以了，不需要再進入整合開發環境設定連接字串和重新編繹程式，應用程式組態檔的檔名必須設為app.config。

20. 操作步驟請參閱16-14~16-18頁

21. 16.3 如何使用DataReader物件16.3.1 DataReader物件簡介 • DataReader物件可以由資料庫中順向(Forward-only)逐筆讀取資料流中的資料列，它並不是一次將所有資料傳向用戶端的記憶體中，因此能提升應用程式的效能和降低系統的負荷量，因此執行速度快且不佔用記憶體太多的資源。 • DataReader物件讀取資料方式是先透過 Connection 物件和資料庫連接，再經由Command物件的ExecuteReader方法執行SQL Select查詢命令擷取出欲查詢的資料，再透過DataReader物件中所提供的屬性和方法，將擷取的資料以唯讀方式由記錄指標所指的資料列順向逐筆處理，將資料放入記憶體或直接顯示在表單上。 • 注意DataReader開啟時，必須和資料庫一直保持連接，此時Connection只能供DataReader使用，必須等到DataReader關閉後，才能允許執行Connection的任何命令。

22. 下圖即是DataReader物件讀取資料庫記錄的流程。下圖即是DataReader物件讀取資料庫記錄的流程。

23. 16.3.2 如何建立DataReader物件Case1 引用System.Data.SqlClient命名空間(適用SQL Server 7.0以上資料庫) • 引用命名空間：using System.Data.SqlClient ; • 建立可連接SQL Server資料庫的cn物件：SqlConnection cn = new SqlConnection("連接字串"); • 宣告dr、cmd分別屬於SqlDataReader、SqlCommand類別物件。寫法如下：SqlCommand cmd ; Sqlreader dr ; • 建立SqlCommand物件cmd，並設定該物件所要執行的SQL命令或預儲程序名稱。寫法如下：cmd = new SqlCommand("SQL命令或預儲程序名稱", cn);

24. 建立DataReader物件時必須先開啟與資料庫連接，接著再使用SqlCommand物件的ExecuteReader方法，執行所指定的SQL查詢命令以便建立dr物件，只要透過dr物件所提供的方法及屬性即可進行資料的瀏覽。寫法如下：cn.Open(); dr = cmd.ExecuteReader(); • 當資料庫讀取後再使用Close方法關閉與資料庫的連接，此時即會釋放DataReader物件資源。cn.Close();

25. Case2 引用System.Data.OleDb命名空間(適用SQL Server 6.5以上、Access、Excel…等版本的資料庫) 01 using System.Data.OleDb; ……. 02 OleDbConnection cn = new OleDbConnection("連接字串"); 03 OleDbCommand cmd ; 04 OleDbDataReader dr ; 05 cmd = new OleDbCommand("SQL命令或預儲程序名稱", cn); 06 cn.Open(); 07 dr = cmd.ExecuteReader(); ……. 08 cn.Close();

26. 16.3.3 DataReader物件常用成員 • 透過Command物件的ExecuteReader方法執行SQL的查詢命令即可建立DataReader物件，DataReader物件內所存放的是查詢結果的資料串流。 • 下表DataReader物件所提供的屬性與方法來逐一取得每筆記錄或相關欄位的資料。

29. 16.3.4 如何使用DataReader物件 讀取資料表記錄 • 當你想透過DataReader物件來讀取由資料庫中所擷取出的查詢結果。 • DataReader可以使用重複結構來檢查記錄指標是否已經指到EOF檔案結尾符號，若記錄指標尚未指到EOF表示資料未讀完，便可利用上表DataReader所提供的方法和屬性，順向逐一取得每個欄位的名稱和該欄位內所存放的資料。 • DataReader記錄指標指到EOF表示資料已經讀取完畢，便可結束讀取動作。

30. Case01 如何透過DataReader物件取得 資料列(記錄)的欄位名稱 • 利用FieldCount屬性取得欄位總數，再將此傳回值減1取得欄位註標的最大值，再利用for迴圈配合GetName方法分別取得各欄位的名稱，並將欄位名稱顯示在表單的textBox1文字方塊控制項上面。 for( i = 0 ; i< dr.FieldCount ; i++) { textBox1.Text += dr.GetName(i) + "\t"; }

31. Case02 如何透過DataReader物件來 顯示各資料列欄位內的資料 • 透過while{…} 敘述判斷記錄指標是否指到EOF檔案結尾符號？若記錄指標尚未指到EOF，表示資料列尚未讀完，此時透過for{…} 敘述將記錄指標所指到的資料列(記錄)各欄位內容，顯示在表單的textBox1文字方塊控制項上面。 while (dr.Read()) // dr.Read()為true表示尚未指到EOF { for( i = 0 ; i < dr.FieldCount ;i++) { textBox1.Text += dr[i].ToString() + "\t"; } textBox1.Text += Environment.NewLine; }

32. 利用while{…} 及Read方法判斷記錄指標是否指到EOF(檔案結尾符號)，若沒有指到EOF，則將目前DataReader指標所指向的記錄從資料庫讀出來並顯示在textBox1文字方塊控制項內。 while(dr.Read()) { textBox1.Text += dr["欄位名稱1"].ToString() + "\t"; textBox1.Text += dr["欄位名稱2"].ToString() + "\t"; textBox1.Text += dr["欄位名稱3"].ToString() + "\t"; ………… textBox1.Text += dr["欄位名稱N"].ToString() + "\t"; textBox1.Text += Environment.NewLine; }

33. 操作步驟請參閱16-23~16-26頁

34. 16.3.5 如何提升DataRader物件的 讀取效能 • 若執行效能來說使用索引方式會比使用欄位名稱還快，但透過上述的方法將資料讀取出來時，我們還要做轉型的動作才能再做其它的資料處理，資料轉型的動作太過煩雜。 • DataReader物件另外提供GetXXX方法來解決，如GetString、GetInt16…等等方法，在讀取資料時可以省略手動轉型的動作，以提升程式的執行效能。

35. GetXXX方法如下表：

36. 將前面範例讀取資料的部份，修改成如下使用GetXXX方法，完整範例請參閱DataReaderDemo4.sln。將前面範例讀取資料的部份，修改成如下使用GetXXX方法，完整範例請參閱DataReaderDemo4.sln。 while (dr.Read()) { textBox1.Text += dr.GetString(0) + "\t"; //讀取學號 textBox1.Text += dr.GetString(1) + "\t"; //讀取姓名 textBox1.Text += dr.GetInt32(2).ToString() + "\t"; //讀取國文 textBox1.Text += dr.GetInt32(3).ToString() + "\t"; //讀取英文 textBox1.Text += dr.GetInt32(4).ToString() + "\t"; //讀取數學 textBox1.Text += Environment.NewLine; }

37. SQL Server 7.0以上的資料庫可以使用GetSqlXXX方法，因GetSqlXXX方法底層是採用SQL Server的TDS格式交換資料，因此執行效能會比GetXXX方法更快。但使用Access、MySQL、Excel…等來當資料庫，則無法使用GetSqlXXX方法。將上例修改成如下使用GetSqlXXX方法，完整範例請參閱DataReaderDemo5.sln。 while (dr.Read()) { textBox1.Text += dr.GetSqlString(0).ToString() + "\t";//讀取學號 textBox1.Text += dr.GetSqlString(1).ToString() + "\t";//讀取姓名 textBox1.Text += dr.GetSqlInt32(2).ToString() + "\t";//讀取國文 textBox1.Text += dr.GetSqlInt32(3).ToString() + "\t";//讀取英文 textBox1.Text += dr.GetSqlInt32(4).ToString() + "\t";//讀取數學 textBox1.Text += Environment.NewLine; }

38. 16.4 如何使用DataSet物件16.4.1 DataSet物件簡介 • DataSet物件是一個記憶體中的資料庫，它採用離線存取資料庫的方式。 • DataSet中的資料更新完畢後，再重新和SQL Server資料庫進行連線，將資料全部一次更新到SQL Server資料庫中。 • DataSet執行效率佳，適用於多用戶端資料存取，但此種方式須耗費較多的記憶體空間。 • DataSet中可以包含一個以上的DataTable物件，DataTable物件相當於主記憶體中的一個資料表。 • DataAdapter物件是資料庫和DataSet之間溝通的橋樑。DataAdapter物件使用Command物件執行SQL命令，將由資料庫所擷取的資料送到DataSet，此時便可使用DataTable物件來存取資料表，將DataSet裡面的資料經過處理後再一次寫回資料庫。

40. 16.4.2 如何建立DataSet物件讀取資料表記錄 • 使用下列步驟可產生名稱為「成績單」與「股票行情表」的DataTable物件，且這兩個DataTable物件是儲存在DataSet物件ds內，因此您可以將DataSet物件想像成是儲存在記憶體內的資料庫。

41. Step1 建立ds屬於DataSet物件。DataSet ds = new DataSet() ; • Step2 建立daScore屬於SqlDataAdapter物件，並指定要查詢的 是「成績單」資料表，要連接的資料來源為「cn」物件。SqlDataAdapter daScore = new SqlDataAdapter ("SELECT * FROM 成績單", cn); • Step3 使用DataAdapter物件的Fill方法，將查詢資料的結果放到 DataSet物件中。此時DataSet物件中即會產生一個DataTable物件，該DataTable物件會以資料表的方式存放 查詢資料的結果，所以只要透過DataTable物件即可取得SQL命令所查詢的資料。daScore.Fill(ds, "成績單");

42. 上述完整程式碼如下： 01 using(SqlConnection cn = new SqlConnection()) 02 { 03 cn.ConnectionString = "Data Source=.\\SQLExpress;" 04 + "AttachDbFilename=|DataDirectory|ch16DB.mdf;" 05 + "Integrated Security=True;User Instance=True;" 06 DataSet ds = new DataSet(); 07 // 建立成績單的DataTable物件 08 SqlDataAdapter daScore = new SqlDataAdapter ("SELECT * FROM 成績單", cn); 09 daScore.Fill(ds, "成績單"); 10 // 建立股票行情表的DataTable物件 11 SqlDataAdapter daStock = new SqlDataAdapter ("SELECT * FROM 股票行情表", cn); 12 daStock.Fill(ds, "股票行情表"); 13 }

43. 上述程式在DataSet物件中產生了「成績單」及「股票行情表」的DataTable物件。您也可以透過DataSet物件所提供的Tables集合物件(由DataTable物件所構成)，來指定要取用哪一個DataTable物件。其寫法如下： // 宣告dtScore, dtStock物件為DataTable DataTable dtScore, dtStock; // dtScore物件設為ds物件內的成績單DataTable物件 dtScore=ds.Tables["成績單"]; // dtStock物件設為ds物件內的股票行情表DataTable物件 dtStock=ds.Tables["股票行情表"];

44. 上述程式也可改用索引來取得DataTable物件，Tables集合物件的註標起始值為0。上述程式也可改用索引來取得DataTable物件，Tables集合物件的註標起始值為0。 // 宣告dtScore, dtStock物件為DataTable DataTable dtScore, dtStock; // dtScore物件設為ds物件內的第1個DataTable物件 dtScore = ds.Tables[0] ; // dtScore物件設為ds物件內的第2個DataTable物件 dtStock = ds.Tables[1] ;

45. DataTable物件的TableName屬性可用來設定或取得DataTable的表格名稱， TableCollection集合物件的Count屬性可用來取得目前DataSet內共有多少個DataTable物件。其寫法如下： • 若DataTable物件指定給DataGridView控制項的DataSource屬性，則目前表單上DataGridView控制項內會顯示該DataTable物件中所有資料，寫法如下： int n = ds.Tables.Count ; string s = ds.Tables[i].TableName; dataGrid1View1.DataSource = ds.Tables["股票行情表"];

46. 操作步驟請參閱16-31~16-34頁

47. 16.4.3 如何建立DataTable物件讀取 資料表記錄 • DataSet物件模型可以了解，在DataSet下包含很多子類別，如DataTable、DataColumn、DataRow…等。 • DataSet是記憶體的資料庫可用來存放多個DataTable物件。 • DataTable可用來存放資料表的多筆記錄資料、每一筆記錄稱為DataRow、DataRow的集合稱為DataRowCollection。 • DataTable包含DataColumnCollection集合，集合中的項目稱為DataColumn，DataColumn用來表示每一個欄位的資訊與資料型別。

49. 若想要取得DataTable物件中的欄位數目、欄位名稱或某一欄某一列的資料內容，則可透過DataColumn或DataRow物件的屬性來取得，寫法如下：若想要取得DataTable物件中的欄位數目、欄位名稱或某一欄某一列的資料內容，則可透過DataColumn或DataRow物件的屬性來取得，寫法如下： • 取得DataTable物件dt的欄位總數。dt.Columns.Count • 取得DataTable物件dt的第j個的欄位名稱 (註標起始值為0)。dt.Columns[j].ColumnName • 取得DataTable物件dt的記錄資料總筆數。dt.Rows.Count • 取得DataTable物件dt的第i列某一個欄位的資料內容。dt.Rows[i]["欄位名稱"] • 取得DataTable物件dt的第i列第j欄的資料內容，註標起始值為0。dt.Rows[i][j]