一、ADO.NET和資料庫程式基礎
1.1 安身立命之基本：SQL
SQL語句時操作關係型資料庫的基礎，在開發資料訪問層、除錯系統等工作中十分常用，掌握SQL對於每一個程式設計師（無論是.NET、Java還是C++等）都非常重要。這裡挑選了一個常見的面試題目，來熱熱身。
常見場景：通過SQL實現單錶行列轉換
行列轉換時資料庫系統中經常遇到的一個需求，在資料庫設計時，為了適合資料的累積儲存，往往採用直接記錄的方式，而在展示資料時，則希望整理所有記錄並且轉置顯示。下圖是一個行列轉換的示意圖：
①好了，廢話不多說，先建立一張表DeptMaterialDetails：
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE [dbo].[DeptMaterialDetails]( [Id] [int] IDENTITY(1,1) NOT NULL, [DeptName] [varchar](50) NULL, [MaterialName] [varchar](100) NULL, [Number] [int] NULL, CONSTRAINT [PK_DeptMaterialDetails] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] |
②填充一些測試資料進該表：
③分析需求，可以發現希望做的是找出具有相同部門的記錄，並根據其材料的值累加。經過一番折騰，可以寫出如下SQL語句：
|
1 2 3 4 5 6 |
select DeptName as '部門', SUM(case MaterialName when '材料1' then Number else 0 end) as '材料1消耗', SUM(case MaterialName when '材料2' then Number else 0 end) as '材料2消耗', SUM(case MaterialName when '材料3' then Number else 0 end) as '材料3消耗' from DeptMaterialDetails group by DeptName |
執行效果如下圖所示，是不是已經完成要求了：
But，根據上述SQL語句，得到的結果永遠只有3種材料的消耗量，如果新增了材料4，那麼是不是需要改SQL語句？這時候是不是又想起了在實際開發中時常提到的可擴充套件性？
④我們可以根據需要動態拼裝一個SQL語句，即動態地根據實際材料數目來得到最後的查詢語句：
|
1 2 3 4 5 6 7 8 9 10 11 |
--申明一個字串用於動態拼接 declare <a href='http://www.jobbole.com/members/sql'>@sql</a> varchar(8000) --拼接SQL語句 set <a href='http://www.jobbole.com/members/sql'>@sql</a> = 'select DeptName as "部門"' --動態地獲得材料名，為每個材料構建一個列 select <a href='http://www.jobbole.com/members/sql'>@sql</a> = <a href='http://www.jobbole.com/members/sql'>@sql</a> + ',SUM(case MaterialName when '''+temp.Item+''' then Number else 0 end) as ['+temp.Item+'消耗]' from (select distinct MaterialName as Item from DeptMaterialDetails) as temp --最終拼上資料來源和分組依據 select <a href='http://www.jobbole.com/members/sql'>@sql</a> = <a href='http://www.jobbole.com/members/sql'>@sql</a> + ' from DeptMaterialDetails group by DeptName' --執行SQL語句 exec(@sql) |
執行結果和第一種方式相同，但是需要注意的是：
動態SQL命令的執行效率往往不高，因為動態拼接的原因，導致資料庫（查詢優化器）可能無法對這樣的命令進行優化。此外，這樣的SQL命令還受限於字串的長度（需要事先確定其長度限制），而動態SQL命令的長度往往是根據實際表的內容而改變，因此這類動態SQL命令無法保證100%正常執行。
1.2 ADO.NET支援哪幾種資料來源？
ADO.NET支援的資料來源很多，從類別上來劃分的話可以大致分為四類。ADO.NET也正是通過如下所示這四個名稱空間來實現對這些資料來源的支援的：
① System.Data.SqlClient
這也許是.NET程式設計師最常用的了，因為MSSQL你懂的！當然，這不是連線MSSQL的唯一方案，通過OLEDB或者ODBC都可以訪問，但是SqlClient下的元件直接針對MSSQL，因此ADO.NET其實是為其專門做了一些優化工作，因此使用MSSQL應該首選 System.Data.SqlClient 名稱空間。
② System.Data.OracleClient
顧名思義，這個名稱空間針對Oracle資料庫產品，並且還得搭配Oracle資料庫的客戶端元件（Oracle.DataAccess.dll）一起使用。
③ System.Data.OleDb
該名稱空間下的元件主要針對OLEDB（Microsoft提供的通向不同資料來源的低階API）的標準介面，它還可以連線其他非SQL資料型別的資料來源。OLEDB是一種標準的介面，實現了不同資料來源統一介面的功能。
④ System.Data.Odbc
該名稱空間下的元件針對ODBC標準介面。
關於ODBC：開放資料庫互連（Open Database Connectivity，ODBC）是微軟公司開放服務結構（WOSA，Windows Open Services Architecture）中有關資料庫的一個組成部分，它建立了一組規範，並提供了一組對資料庫訪問的標準API（應用程式程式設計介面）。這些API利用SQL來完成其大部分任務。ODBC本身也提供了對SQL語言的支援，使用者可以直接將SQL語句送給ODBC。
總體來說，ADO.NET為我們遮蔽了所有的資料庫訪問層次，提供了統一的API給我們，使我們無需考慮底層的資料來源是具體的DataBase還是另一種標準介面。下圖直觀地展示了ADO.NET與可能的資料來源的連線：
二、ADO.NET和資料庫的連線
2.1 簡述資料庫連線池的機制
資料庫連線一般都被認為是一個效能成本相對較大的動作，所以針對資料庫連線以及讀寫的優化往往是系統優化的關鍵點。資料庫連線池就是一個非常重要的優化機制。
（1）資料庫連線池的基本概念
資料庫連線池，顧名思義就是一個儲存資料庫連線的緩衝池，由於連線和斷開一個資料庫的開銷很大（想想經典的TCP三次握手和四次揮手），反覆連線和斷開資料庫對於系統的效能影響將會非常嚴重。而在.NET程式中，有時候是無法預測下一次資料庫訪問的需求何時到來，所以通常的做法就是在使用完一個連線後就立即關閉它，這就需要ADO.NET的內部機制來維護這個訪問池。
下圖展示了資料庫連線池的機制，在該機制中，當一個使用者新申請了一個資料庫連線時，當資料庫池內連線匹配的情況下，使用者會從連線池中直接獲得一個被保持的連線。在使用者使用完呼叫Close關閉連線時，連線池會將該連線返回到活動連線池中，而不是真正關閉連線。連線回到了活動連結池中後，即可在下一個Open呼叫中重複使用。
預設情況下，資料庫連線時處於啟用狀態的。我們也可以通過資料庫連線字串設定關閉資料庫連線池，如下面的程式碼所示：
|
1 2 3 4 5 |
using (SqlConnection connection = new SqlConnection("Server=127.0.0.1;Initial Catalog=TestDB;Integrated Security=SSPI;Pooling=false")) { connection.Open(); // 執行你想要執行的資料庫操作 } |
其中引數Pooling=false就代表了關閉連線池。當然，我們還可以設定連線池中的最大和最小連線數，引數分別對應Max Pool Size和Min Pool Size。
（2）資料庫連線的複用
由於資料來源和連線引數選擇的不同，每個資料庫的連線並不是完全通用的。因此，ADO.NET選擇通過連線字串來區分。一旦使用者使用某個連線字串來申請資料庫連線，ADO.NET將判斷連線池中是否存在擁有相同連線字串的連線，如果有則直接分配，沒有則新建連線。
我們可以看看下面一段程式碼，三個不同的連線中，第三個複用第一個連線，第二個則無法複用第一個連線：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
using (SqlConnection connection = new SqlConnection("Server=127.0.0.1;Initial Catalog=TestDB;Integrated Security=SSPI")) { // 假設這是啟動後第一個資料庫連線請求，一個新連線將被建立 connection.Open(); } using (SqlConnection connection = new SqlConnection("Server=127.0.0.1;Initial Catalog=TestDB1;Integrated Security=SSPI")) { // 由於和上一個連線的字串不同，因此無法複用第一個連線 connection.Open(); } using (SqlConnection connection = new SqlConnection("Server=127.0.0.1;Initial Catalog=TestDB;Integrated Security=SSPI")) { // 連線字串和第一個連線相同，儲存在連線池中的第一個連線被複用 connection.Open(); } |
（3）不同資料來源的連線池機制
事實上，ADO.NET元件本身並不直接包含連線池，而針對不同類別機制的資料來源指定不同的連線池方案。對於SqlClient、OracleClient名稱空間下的元件，使用的連線池是由託管程式碼直接編寫的，可以理解為連線池直接在.NET框架中執行。而對於OLEDB和ODBC的資料來源來說，連線池的實現完全依靠OLEDB和ODBC提供商實現，ADO.NET只與其約定相應規範。
2.2 如何提高連線池內連線的重用率
由於只有相同連線字串才能共享連線，因此經常導致連線池失效的問題，所以需要提高連線池內連線的重用率。
（1）連線池重用率低下的原因
由於資料庫連線池僅按照資料庫連線字串來判斷連線是否可重用，所以連線字串內的任何改動都會導致連線失效。就係統內部而言，資料庫連線字串中最常被修改的兩個屬性就是資料庫名和使用者名稱/密碼。
因此，對於多資料庫的系統來說，只有同一資料庫的連線才會被共用，如下圖所示：
而對多使用者的系統而言，只有同一使用者的申請才能共用資料庫連線，如下圖所示：
（2）如何提高資料庫連線池重用率
這裡提供一種能夠有效提高資料庫連線池重用率的方式，但是也會帶來一點小安全隱患，在進行設計時需要權衡利弊關係，並根據實際情況來指定措施。
① 建立跳板資料庫
在資料庫內建立一個所有許可權使用者都能訪問的跳板資料庫，在進行資料庫連線時先連線到該資料庫，然後再使用 use databasename 這樣的SQL語句來選擇需要訪問的資料庫，這樣就能夠避免因為訪問的資料庫不一致而導致連線字串不一致的情況。
下面的示例程式碼演示了這一做法：
|
1 2 3 4 5 6 7 8 |
// 假設這裡使用Entry資料作為跳板資料庫，然後再使用databaseName指定的資料庫 using (SqlConnection connection = new SqlConnection("Server=192.168.80.100;Uid=public;Pwd=public;Database=Entry")) { connection.Open(); SqlCommand command = connection.CreateCommand(); command.CommandText = string.Format("USE {0}", databaseName); command.ExecuteNonQuery(); } |
② 不使用資料庫使用者系統來管理系統許可權
這樣做的結果就是永遠使用管理員的賬號來連線資料庫，而在做具體工作時再根據使用者的實際許可權，使用程式碼來限定操作。帶來的好處就是：資料庫看連線字串不會因為實際使用者的不同而不同。當然，永遠使用管理員賬號來連線也會相應帶來安全隱患！
下圖展示了採用了這種方案後資料庫連線池的使用情況：
三、使用ADO.NET讀寫資料庫
3.1 ADO.NET支援訪問資料庫的方式有哪些？
對於關係型資料庫，ADO.NET支援兩種訪問模式，一種是連線式的訪問模式，而另外一種則是離線式的訪問模式。
（1）連線式的訪問
連線式的訪問是指讀取資料時保持和資料庫的連線，並且在使用時獨佔整個連線，逐步讀取資料。這種模式比較適合從資料量龐大的資料庫中查詢資料，並且不能確定讀取數量的情況。使用XXXCommand和XXXDataReader物件來讀取資料就是一個典型的連線式資料訪問，這種模式的缺點就是：資料庫連線被長時間地保持在開啟的狀態。
下面的一段示例程式碼展示了這一讀取模式的典型使用，首先是資料訪問層的靜態方法，該方法返回一個指定SQL命令返回的SqlDataReader獨享，該物件唄關閉時會自動關閉依賴的資料庫連線。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
/// <summary> /// 資料訪問層型別 /// </summary> public class DataHelper { private static readonly String conn_String = "Server=localhost;Integrated Security=true;database=TestDB"; /// <summary> /// 使用給定的sql來訪問資料庫 /// 返回SqlDataReader物件，提供連線式訪問 /// </summary> /// <param name="sql">SQL命令</param> /// <returns>SqlDataReader物件</returns> public static SqlDataReader GetReader(String sql) { SqlConnection con = new SqlConnection(conn_String); try { // 開啟連線，執行查詢 // 並且返回SqlDataReader con.Open(); using (SqlCommand cmd = con.CreateCommand()) { cmd.CommandText = sql; SqlDataReader dr = cmd.ExecuteReader (CommandBehavior.CloseConnection); return dr; } } // 連線資料庫隨時可能發生異常 catch (Exception ex) { if (con.State != ConnectionState.Closed) { con.Close(); } return null; } } } |
其次是呼叫該方法的入口，使用者將會得到一個連線著資料庫的SqlDataReader物件，該物件本身並不包含任何資料，使用者可以通過該物件讀取資料庫中的資料。但由於是連線方式，讀取只能是順序地逐條讀取。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
/// <summary> /// 使用資料庫訪問層 /// 連線式讀取資料 /// </summary> class Program { // SQL命令 private static readonly String sql = "select * from dbo.DeptMaterialDetails"; static void Main(string[] args) { // 使用連線式方法讀取資料來源 using (SqlDataReader reader = DataHelper.GetReader(sql)) { // 得到列數 int colcount = reader.FieldCount; // 列印列名 for (int i = 0; i < colcount; i++) { Console.Write("{0} ", reader.GetName(i)); } Console.WriteLine(); // 順序讀取每一行，並列印 while (reader.Read()) { for (int i = 0; i < colcount; i++) { Console.Write("{0}\t", reader[i].ToString()); } Console.WriteLine(); } reader.Close(); } Console.ReadKey(); } } |
下圖是這個示例的執行結果，從資料庫中讀取了指定表的內容：
（2）離線式的訪問
離線式的訪問並不是指不連線資料庫，而是指一般在讀取實際資料時連線就已經斷開了。離線式訪問方式在連線至資料庫後，會根據SQL命令批量讀入所有記錄，這樣就能直接斷開資料庫連線以供其他執行緒使用，讀入的記錄將暫時存放在記憶體之中。離線式訪問的優點就在於不會長期佔用資料庫連線資源，而這樣做的代價就是將消耗記憶體來儲存資料，在大資料量查詢的情況下該方式並不適用。例如，使用XXXDataAdapter和DataSet物件就是最常用的離線式訪問方式。
下面的例項程式碼對上面的連線式做了一些修改，藉助SqlDataAdapter和DataSet來實現離線式訪問：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
/// <summary> /// 資料訪問層型別 /// </summary> public class DataHelper { private static readonly String conn_String = "Server=localhost;Integrated Security=true;database=TestDB"; /// <summary> /// 使用給定的sql來訪問資料庫 /// 返回DataSet物件 /// </summary> /// <param name="sql">SQL命令</param> /// <returns>DataSet物件</returns> public static DataSet GetDataSet(String sql) { SqlConnection con = new SqlConnection(conn_String); DataSet ds = new DataSet(); try { // 開啟連線，執行查詢 // 並且返回DataSet con.Open(); using (SqlDataAdapter sd = new SqlDataAdapter(sql, con)) { // 這裡資料將被批量讀入 sd.Fill(ds); } return ds; } // 連線資料庫隨時可能發生異常 catch (Exception ex) { if (con.State != ConnectionState.Closed) { con.Close(); } return ds; } } } /// <summary> /// 使用資料庫訪問層 /// 離線式讀取資料 /// </summary> class Program { //SQL命令 private static readonly String sql = "select * from dbo.DeptMaterialDetails"; static void Main(string[] args) { DataSet ds = DataHelper.GetDataSet(sql); // 列印結果，這裡假設只對DataSet中的第一個表感興趣 DataTable dt = ds.Tables[0]; // 列印列名 foreach (DataColumn column in dt.Columns) { Console.Write("{0} ", column.ColumnName); } Console.WriteLine(); // 列印表內容 foreach (DataRow row in dt.Rows) { for (int i = 0; i < dt.Columns.Count; i++) { Console.Write("{0} ", row[i].ToString()); } Console.WriteLine(); } Console.ReadKey(); } } |
由於資料訪問類的處理相當趕緊，呼叫者輕鬆就能獲得包含資料來源的DataSet物件，這時任何操作都已經和資料來源沒有聯絡了。
3.2 簡述SqlDataAdapter的基本工作機制
ADO.NET提供的XXXDataAdapter型別都使用了非常一致的機制，並且向使用者提供了統一的介面。一個SqlDataAdapter物件，在資料庫操作中充當了中間適配的角色，它組織起資料快取對資料庫的所有操作，進行統一執行。一個SqlDataAdapter物件內實際包含四個負責具體操作的SqlCommand物件，它們分別負責查詢、更新、插入和刪除操作。下圖展示了SqlDataAdapter的工作機制：
如上圖所示，實際上進行資料操作的是包含在SqlDataAdapter內的四個SqlCommand物件，而當SqlDataAdapter的Update方法被呼叫時，它會根據DataSet獨享的更新情況而呼叫插入、刪除和更新等命令。
3.3 如何實現批量更新的功能？
（1）批量更新的概念
使用XXXDataAdapter更新資料，由於每一行都需要都需要一個從程式集到資料庫的往返，在大批量更新的情況下，效率是非常低的。可以考慮使用一次傳送多條更新命令的處理方式，這就需要用到UpdateBatchSize屬性。在.NET 2.0之後，SqlClient和OracleClient都支援這個屬性，這裡以SQL Server資料來源為例，介紹一下UpdateBatchSize的基本使用。
UpdateBatchSize的值一共有三種：
① =0，DbDataAdapter將使用伺服器能處理的最大批處理大小；
② =1，禁用批量更新；
③ >1，使用UpdateBatchSize操作批處理一次性傳送的量；
當批量更新被允許時，SqlDataAdapter的Update方法將每次傳送多條更新命令到資料庫，從而提高效能。
But，使用批量更新並不意味著SQL的合併或優化。事實上，批量的意義在於把多個發往資料庫伺服器的SQL語句放在一個請求中傳送。例如，將UpdateBatchSize設定為20時，原本每個更新行傳送一次更新命令將變為每20個更新行傳送一次更新命令，而每個命令中包含了20個更新一行的命令。下圖展示了這一區別：
（2）批量更新的使用
下面的示例程式碼展示瞭如何使用UpdateBatchSize屬性來設定批量更新，這裡更改了DataHelper的Update方法，在內部設定了UpdateBatchSize屬性。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
public class DataHelper { private static readonly string conn_string = "Server=localhost;Integrated Security=true;database=TestDB"; //選擇、更新、刪除和插入的SQL命令 static readonly string SQL_SELECT = "SELECT * FROM DeptMaterialDetails"; static readonly string SQL_UPDATE = "UPDATE DeptMaterialDetails SET Department=@Department,Item=@Item,Number=@Number where Id=@Id"; static readonly string SQL_DELETE = "DELETE FROM DeptMaterialDetails where Id=@Id"; static readonly string SQL_INSERT = "Insert INTO DeptMaterialDetails (Department,Item,Number) VALUES (@Department,@Item,@Number)"; /// <summary> /// 得到SqlDataAdapter，私有方法 /// </summary> /// <param name="con"></param> /// <returns></returns> private static SqlDataAdapter GetDataAdapter(SqlConnection con) { SqlDataAdapter sda = new SqlDataAdapter(); sda.SelectCommand = new SqlCommand(SQL_SELECT, con); sda.UpdateCommand = new SqlCommand(SQL_UPDATE, con); sda.DeleteCommand = new SqlCommand(SQL_DELETE, con); sda.InsertCommand = new SqlCommand(SQL_INSERT, con); sda.UpdateCommand.Parameters.AddRange(GetUpdatePars()); sda.InsertCommand.Parameters.AddRange(GetInsertPars()); sda.DeleteCommand.Parameters.AddRange(GetDeletePars()); return sda; } // 三個SqlCommand的引數 private static SqlParameter[] GetInsertPars() { SqlParameter[] pars = new SqlParameter[3]; pars[0] = new SqlParameter("@Department", SqlDbType.VarChar, 50, "Department"); pars[1] = new SqlParameter("@Item", SqlDbType.VarChar, 50, "Item"); pars[2] = new SqlParameter("@Number", SqlDbType.Int, 4, "Number"); return pars; } private static SqlParameter[] GetUpdatePars() { SqlParameter[] pars = new SqlParameter[4]; pars[0] = new SqlParameter("@Id", SqlDbType.VarChar, 50, "Id"); pars[1] = new SqlParameter("@Department", SqlDbType.VarChar, 50, "Department"); pars[2] = new SqlParameter("@Item", SqlDbType.VarChar, 50, "Item"); pars[3] = new SqlParameter("@Number", SqlDbType.Int, 4, "Number"); return pars; } private static SqlParameter[] GetDeletePars() { SqlParameter[] pars = new SqlParameter[1]; pars[0] = new SqlParameter("@Id", SqlDbType.VarChar, 50, "Id"); return pars; } /// <summary> /// 更新資料庫，使用批量更新 /// </summary> /// <param name="ds">資料集</param> public static void Update(DataSet ds) { using (SqlConnection connection = new SqlConnection(conn_string)) { connection.Open(); using (SqlDataAdapter adapater = GetDataAdapter(connection)) { // 設定批量更新 adapater.UpdateBatchSize = 0; adapater.Update(ds); } } } } |
PS：近年來比較流行的輕量級ORM例如Dapper一類的這裡就不作介紹了，後續我會實踐一下寫一個初探系列的文章。另外，資料庫中的事務及其隔離級別一類的介紹也會在後續詳細閱讀《MSSQL技術內幕》後寫一個讀書筆記，到時分享給各位園友。
參考資料
（1）朱毅，《進入IT企業必讀的200個.NET面試題》
（2）張子陽，《.NET之美：.NET關鍵技術深入解析》
（3）王濤，《你必須知道的.NET》
（4）百度百科，ODBC