非同步的 SQL 資料庫封裝

引言

我一直在尋找一種簡單有效的庫，它能在簡化資料庫相關的程式設計的同時提供一種非同步的方法來預防死鎖。

我找到的大部分庫要麼太繁瑣，要麼靈活性不足，所以我決定自己寫個。

使用這個庫，你可以輕鬆地連線到任何 SQL-Server 資料庫，執行任何儲存過程或 T-SQL 查詢，並非同步地接收查詢結果。這個庫採用 C# 開發，沒有其他外部依賴。

背景

你可能需要一些事件驅動程式設計的背景知識，但這不是必需的。

使用

這個庫由兩個類組成：

• BLL (Business Logic Layer) 提供訪問MS-SQL資料庫、執行命令和查詢並將結果返回給呼叫者的方法和屬性。你不能直接呼叫這個類的物件，它只供其他類繼承.
• DAL (Data Access Layer) 你需要自己編寫執行SQL儲存過程和查詢的函式，並且對於不同的表你可能需要不同的DAL類。

首先，你需要像這樣建立 DAL 類：

namespace SQLWrapper
{
  public class DAL : BLL
  {
    public DAL(string server, string db, string user, string pass)
    {
      base.Start(server, db, user, pass);
    }

    ~DAL()
    {
      base.Stop(eStopType.ForceStopAll);
    }

    ///////////////////////////////////////////////////////////
    // TODO: Here you can add your code here...
  }
}

由於BLL類維護著處理非同步查詢的執行緒，你需要提供必要的資料來拼接連線字串。千萬別忘了呼叫`Stop`函式，否則解構函式會強制呼叫它。

NOTE:如果需要連線其他非MS-SQL資料庫，你可以通過修改BLL類中的`CreateConnectionString`函式來生成合適的連線字串。

為了呼叫儲存過程，你應該在DAL中編寫這種函式:

public int MyStoreProcedure(int param1, string param2)
{
    // 根據儲存過程的返回型別建立使用者資料
    StoredProcedureCallbackResult userData = new StoredProcedureCallbackResult(eRequestType.Scalar);

    // 在此定義傳入儲存過程的引數，如果沒有引數可以省略 userData.Parameters = new System.Data.SqlClient.SqlParameter[] {         
 new System.Data.SqlClient.SqlParameter("@param1", param1),
        new System.Data.SqlClient.SqlParameter("@param2", param2),
    };

    // Execute procedure...
    if (!ExecuteStoredProcedure("usp_MyStoreProcedure", userData))
        throw new Exception("Execution failed");

    // 等待執行完成...
    // 等待時長為 <userdata.tswaitforresult> 
    // 執行未完成返回 <timeout>
    if (WaitSqlCompletes(userData) != eWaitForSQLResult.Success)
        throw new Exception("Execution failed");

    // Get the result...
    return userData.ScalarValue;
}

正如你所看到的，儲存過程的返回值型別可以是`Scalar`,`Reader`和`NonQuery`。對於`Scalar`，`userData`的`ScalarValue`引數有意義(即返回結果)；對於`NonQuery`，`userData`的`AffectedRows`引數就是受影響的行數；對於`Reader`型別，`ReturnValue`就是函式的返回值，另外你可以通過`userData`的`resultDataReader`引數訪問recordset。

再看看這個示例:

public bool MySQLQuery(int param1, string param2)
{
    // Create user data according to return type of store procedure in SQL(這個註釋沒有更新,說明《註釋是魔鬼》有點道理)
    ReaderQueryCallbackResult userData = new ReaderQueryCallbackResult();

    string sqlCommand = string.Format("SELECT TOP(1) * FROM tbl1 
      WHERE code = {0} AND name LIKE '%{1}%'", param1, param2);

    // Execute procedure...
    if (!ExecuteSQLStatement(sqlCommand, userData))
        return false;

    // Wait until it finishes...
    // Note, it will wait (userData.tsWaitForResult) 
    // for the command to be completed otherwise returns <timeout>
    if (WaitSqlCompletes(userData) != eWaitForSQLResult.Success)
        return false;

    // Get the result...
    if(userData.resultDataReader.HasRows && userData.resultDataReader.Read())
    {
        // Do whatever you want....
        int field1 = GetIntValueOfDBField(userData.resultDataReader["Field1"], -1);
        string field2 = GetStringValueOfDBField(userData.resultDataReader["Field2"], null);
        Nullable<datetime> field3 = GetDateValueOfDBField(userData.resultDataReader["Field3"], null);
        float field4 = GetFloatValueOfDBField(userData.resultDataReader["Field4"], 0);
        long field5 = GetLongValueOfDBField(userData.resultDataReader["Field5"], -1);
    }
    userData.resultDataReader.Dispose();

    return true;
}

在這個例子中，我們呼叫 `ExecuteSQLStatement` 直接執行了一個SQL查詢，但思想跟 `ExecuteStoredProcedure` 是一樣的。

我們使用 `resultDataReader` 的 `.Read()` 方法來迭代處理返回的結果集。另外提供了一些helper方法來避免疊代中由於NULL欄位、GetIntValueOfDBField 等引起的異常。

如果你要執行 SQL 命令而不是儲存過程，需要傳入 ExecuteSQLStatement 的 userData 有三類：

• ReaderQueryCallbackResult userData;
  適用於有返回recordset的語句，可以通過userData.resultDataReader獲得對返回的recordset的訪問。
• NonQueryCallbackResult userData
  適用於像UPDATE這種沒有返回內容的語句，可以使用userData.AffectedRows檢查執行的結果。
• ScalarQueryCallbackResult userData
  用於查詢語句只返回一個標量值的情況，例如`SELECT code FROM tbl WHEN ID=10`，通過userData.ScalarValue 取得返回的結果。

對於儲存過程，只有一種需要傳入 ExecuteStoredProcedure 的資料型別。但在宣告變數時你需要指明儲存過程的返回值型別：

• StoredProcedureCallbackResult userData(eRequestType)
  除了宣告不同外，其他操作與上面相同。

非同步地使用程式碼

假使你不希望呼叫執行緒被查詢阻塞，你需要週期性地呼叫 `WaitSqlCompletes` 來檢查查詢是否完成，執行是否失敗。

/// <summary>
/// 你需要週期性地呼叫WaitSqlCompletes(userData, 10) 
/// 來檢視結果是否可用!
/// </summary>
public StoredProcedureCallbackResult MyStoreProcedureASYNC(int param1, string param2)
{
    // Create user data according to return type of store procedure in SQL
    StoredProcedureCallbackResult userData = new StoredProcedureCallbackResult(eRequestType.Reader);

    // If your store procedure accepts some parameters, define them here, 
    // or you can omit it incase there is no parameter definition
    userData.Parameters = new System.Data.SqlClient.SqlParameter[] { 
        new System.Data.SqlClient.SqlParameter("@param1", param1),
        new System.Data.SqlClient.SqlParameter("@param2", param2),
    };

    // Execute procedure...
    if (!ExecuteStoredProcedure("usp_MyStoreProcedure", userData))
        throw new Exception("Execution failed");

    return userData;
}

在呼叫執行緒中你需要這樣做:

...
DAL.StoredProcedureCallbackResult userData = myDal.MyStoreProcedureASYNC(10,"hello");
...
// each time we wait 10 milliseconds to see the result...
switch(myDal.WaitSqlCompletes(userData, 10))
{
case eWaitForSQLResult.Waiting:
  goto WAIT_MORE;
case eWaitForSQLResult.Success:
  goto GET_THE_RESULT;
default:
  goto EXECUTION_FAILED;
}
...

資料庫狀態

在 BLL 中只有一個非同步地提供資料庫狀態的事件。如果資料庫連線被斷開了(通常是由於網路問題)，OnDatabaseStatusChanged 事件就會被掛起。

另外，如果連線恢復了，這個事件會被再次掛起來通知你新的資料庫狀態。

有趣的地方

在我開發程式碼的時候，我明白了連線字串中的連線時限（connection timeout）和SQL命令物件的執行時限（execution timeout）同樣重要。

首先，你必須意識到最大容許時限是在連線字串中定義的，並可以給出一些執行指令比連線字串中的超時時間更長的時間。

其次，每一個命令都有著它們自己的執行時限，在這裡的程式碼中預設為30秒。你可以很容易地修改它，使它適用於所有型別的命令，就像這樣：

userData.tsWaitForResult = TimeSpan.FromSeconds(15);