思勤無邪

2. Ado.Net

    2.1 應用Ado.net的一些思考原則
    1. 根據數據使用的方式來設計數據訪問層
    2. 緩存數據，避免不必要的操作
    3. 使用服務帳戶進行連接
    4. 必要時申請，盡早釋放
    5. 關閉可關閉的資源
    6. 減少往返
    7. 僅返回需要的數據
    8. 選擇適當的事務類型
    9. 使用存儲過程

    2.2 Connection
    數據庫連接是一種共享資源，并且打開和關閉的開銷較大。Ado.net默認啟用了連接池機制，關閉連接不會真的關閉物理連接，而只是把連接放回到連接池中。因為池中共享的連接資源始終是有限的，如果在使用連接后不盡快關閉連接，那么就有可能導致申請連接的線程被阻塞住，影響整個系統的性能表現。
    2.2.1 在方法中打開和關閉連接
    這個原則有幾層含義：
    1. 主要目的是為了做到必要時申請和盡早釋放
    2. 不要在類的構造函數中打開連接、在析構函數中釋放連接。因為這將依賴于垃圾回收，而垃圾回收只受內存影響，回收時機不定
    3. 不要在方法之間傳遞連接，這往往導致連接保持打開的時間過長

    這里強調一下在方法之間傳遞連接的危害：曾經在壓力測試中遇到過一個測試案例，當增大用戶數的時候，這個案例要比別的案例早很久就用掉連接池中的所有連接。經分析，就是因為A方法把一個打開的連接傳遞到了B方法，而B方法又調用了一個自行打開和關閉連接的C方法。在A方法的整個運行期間，它至少需要占用兩條連接才能夠成功工作，并且其中的一條連接占用時間還特別長，所以造成連接池資源緊張，影響了整個系統的可伸縮性！

    2.2.2 顯式關閉連接
    Connection對象本身在垃圾回收時可以被關閉，而依賴垃圾回收是很不好的策略。推薦使用using語句顯式關閉連接，如下例： 

using (SqlConnection conn = new SqlConnection(connString))
{
    conn.Open();
    
}    // Dispose is automatically called on the conn variable here

    2.2.3 確保連接池啟用
    Ado.net是為每個不同的連接串建立連接池，因此應該確保連接串不會出現與具體用戶相關的信息。另外，要注意連接串是大小寫敏感的。
    2.2.4 不要緩存連接
    例如，把連接緩存到Session或Application中。在啟用連接池的情況下，這種做法沒有任何意義。
    2.3 Command
    2.3.1 使用ExecuteScalar和ExecuteNonQuery
    如果想返回像Count(*)、Sum(Price)或Avg(Quantity)那樣的單值，可以使用ExecuteScalar方法。ExecuteScalar返回第一行第一列的值，將結果集作為標量值返回。因為單獨一步就能完成，所以ExecuteScalar不僅簡化了代碼，還提高了性能。
    使用不返回行的SQL語句時，例如修改數據（INSERT、UPDATE或DELETE）或僅返回輸出參數或返回值，請使用ExecuteNonQuery。這避免了用于創建空DataReader的任何不必要處理。
    2.3.2 使用Prepare
    當需要重復執行同一SQL語句多次，可考慮使用Prepare方法提升效率。需要注意的是，如果只是執行一次或兩次，則完全沒有必要。例如：

cmd.CommandText = "insert into Table1 ( Col1, Col2 ) values ( @val1, @val2 )";

cmd.Parameters.Add( "@val1", SqlDbType.Int, 4, "Col1" );
cms.Parameters.Add( "@val2", SqlDbType.NChar, 50, "Col2");

cmd.Parameters[0].Value = 1;
cmd.Parameters[1].Value = "XXX";
cmd.Prepare();
cmd.ExecuteNonQuery();

cmd.Parameters[0].Value = 2;
cmd.Parameters[1].Value = "YYY";
cmd.ExecuteNonQuery();

cmd.Parameters[0].Value = 3;
cmd.Parameters[1].Value = "ZZZ";
cmd.ExecuteNonQuery();

    2.3.3 使用綁定變量 ★
    SQL語句需要先被編譯成執行計劃，然后再執行。如果使用綁定變量的方式，那么這個執行計劃就可以被后續執行的SQL語句所復用。而如果直接把參數合并到了SQL語句中，由于參數值千變萬化，執行計劃就難以被復用了。例如上面Prepare一節給出的示例，如果把參數值直接寫到insert語句中，那么上面的四次調用將需要編譯四次執行計劃。
    為避免這種情況造成性能損失，要求一律使用綁定變量方式。
    2.4 DataReader
    DataReader最適合于訪問只讀的單向數據集。與DataSet不同，數據集并不全部在內存中，而是隨不斷發出的read請求，一旦發現數據緩沖區中的數據均被讀取，則從數據源傳輸一個數據緩沖區大小的數據塊過來。另外，DataReader保持連接，DataSet則與連接斷開。
    2.4.1 顯式關閉DataReader
    與連接類似，也需要顯式關閉DataReader。另外，如果與DataReader關聯的Connection僅為DataReader服務的話，可考慮使用Command對象的ExecuteReader(CommandBehavior.CloseConnection)方式。這可以保證當DataReader關閉時，同時自動關閉Connection。
    2.4.2 用索引號訪問代替名稱索引號訪問屬性
    從Row中訪問某列屬性，使用索引號的方式比使用名稱方式有細微提高。如果會被頻繁調用，例如在循環中，那么可考慮此類優化。示例如下：

cmd.CommandText = "select Col1, Col2 from Table1" ;
SqlDataReader dr = cmd.ExecuteReader();

int col1 = dr.GetOrdinal("Col1");
int col2 = dr.GetOrdinal("Col2");

while (dr.Read())
{
    Console.WriteLine( dr[col1] + "_" + dr[col2]);
}

    2.4.3 使用類型化方法訪問屬性
    從Row中訪問某列屬性，用GetString、GetInt32這種顯式指明類型的方法，其效率較通用的GetValue方法有細微提高，因為不需要做類型轉換。
    2.4.4 使用多數據集
    部分場景可以考慮一次返回多數據集來降低網絡交互次數，提升效率。示例如下：

cmd.CommandText = "StoredProcedureName";    // The stored procedure returns multiple result sets.
SqlDataReader dr = cmd.ExecuteReader();

while (dr.read())
// read first result set

dr.NextResult();

while (dr.read())
// 

    2.5 DataSet
    2.5.1 利用索引加快查找行的效率
    如果需要反復查找行，建議增加索引。有兩種方式：
    1. 設置DataTable的PrimaryKey
    適用于按PrimaryKey查找行的情況。注意此時應調用DataTable.Rows.Find方法，一般慣用的Select方法不能利用索引。
    2. 使用DataView
    適用于按Non-PrimaryKey查找行的情況。可為DataTable創建一個DataView，并通過SortOrder參數指示建立索引。此后使用Find或FindRows查找行。