搜尋此網誌

2018/04/13

SampleCMS 資料層元件說明：指令執行者 DataAccessCommand 運作方式說明

在前一篇提到＂將指令資訊物件 (cmdInfo) 丟給指令執行者 (cmd) 去執行取回填入資料的 DataSet，執行過程中指令執行者 (cmd) 會自動抓取指令資訊物件 (cmdInfo) 的成員變數，依照成員變數的名稱，將其值傳送給 SP 的同名參數＂，現在我們一起來看看指令執行者是如何依照指令資訊物件的描述去資料庫執行 Stored Procedure（以下簡稱 SP）。

再來看一次取得員工資料的範例程式碼。
?
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
public DataSet GetEmployeeData(string empAccount)
{
    IDataAccessCommand cmd = DataAccessCommandFactory.GetDataAccessCommand(DBs.MainDB);
    spEmployee_GetData cmdInfo = new spEmployee_GetData()
    {
        EmpAccount = empAccount
    };
 
    DataSet ds = cmd.ExecuteDataset(cmdInfo);
    dbErrMsg = cmd.GetErrMsg();
 
    return ds;
}
 
public class spEmployee_GetData : IDataAccessCommandInfo
{
    // DataAccessCommand 會使用欄位變數當做 SqlParameter 的產生來源(使用名稱、值)；屬性不包含在其中。
    // 輸出參數請加上屬性 [OutputPara]
    public string EmpAccount;
 
    public CommandType GetCommandType()
    {
        return CommandType.StoredProcedure;
    }
 
    public string GetCommandText()
    {
        return "dbo.spEmployee_GetData";
    }
}

Line 3 的 cmd 取得的物件是 new DataAccessCommand(DBs.MainDB);
Line 9 的 cmd.ExecuteDataset(cmdInfo); 請看下方 DataAccessCommand.ExecuteDataset 活動圖。

DataAccessCommand.ExecuteDataset 活動圖
以下說明活動圖中有標號的活動項目，

1.從指令資訊物件取得指令碼與參數資料並且暫存：
取得指令類型、指令碼以及使用 Reflection 取得物件的所有參數名稱與參數值的程式碼片段如下，
?
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
CommandType cmdType = cmdInfo.GetCommandType();
string cmdText = cmdInfo.GetCommandText();
//動態取得物件的公用欄位
List<SqlParaInfo> paraInfos = GenerateSqlParaInfos(cmdInfo);
 
protected List<SqlParaInfo> GenerateSqlParaInfos(IDataAccessCommandInfo cmdInfo)
{
    //動態取得物件的公用欄位
    FieldInfo[] fields = cmdInfo.GetType().GetFields();
    List<SqlParaInfo> paraInfos = new List<SqlParaInfo>();
 
    if (fields.Length > 0)
        hasParameters = true;
 
    foreach (FieldInfo field in fields)
    {
        object[] outputAttrs = field.GetCustomAttributes(typeof(OutputParaAttribute), false);
 
        object fieldValue = null;
        fieldValue = field.GetValue(cmdInfo);
        bool isOutputPara = false;
 
        if (outputAttrs.Length > 0)
        {
            isOutputPara = true;
            hasOutputParameters = true;
        }
 
        paraInfos.Add(new SqlParaInfo()
        {
            Name = field.Name,
            Value = fieldValue,
            IsOutput = isOutputPara,
            ParaType = field.FieldType,
            ParaFieldInfo = field
        });
    }
 
    return paraInfos;
}

Line 1, 2: 這裡使用 IDataAccessCommandInfo 定義的 GetCommandType(), GetCommandText() 取得指令類型與指令碼。

Line 4: 從 GenerateSqlParaInfos(cmdInfo) 取回暫存的自訂類別參數清單。

Line 9: 利用 Reflection，從 cmdInfo.GetType().GetFields() 取回指令資訊物件裡的所有公用欄位資訊 FieldInfo。

Line 15~37: 從每一個 FieldInfo 抓出欄位名稱、欄位值以及欄位型別等資料，暫存至 SqlParaInfo 類別的參數清單。

Line 17, 23: 查詢這個欄位是否有寫上自訂屬性 [Output]，有的話代表它是輸出型的參數。

Line 20, 31, 34: field.GetValue(cmdInfo) 取回欄位的值。field.Name 取回欄位名稱。field.FieldType 取回欄位型別。

2.從 IDataAccessSource 取得連線物件並開啟連線：
使用 IDataAccessSource.CreateConnectionInstanceWithOpen() 取回一個 SqlConnection 物件，同時已使用 SqlConnection.Open() 連線至資料庫。

3.從暫存的參數資料清單轉出一份 SqlParameter 參數清單：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SqlParameter[] commandParameters = GenerateCommandParameters(paraInfos);
 
protected SqlParameter[] GenerateCommandParameters(List<SqlParaInfo> paraInfos)
{
    List<SqlParameter> sqlParas = new List<SqlParameter>();
 
    foreach (SqlParaInfo paraInfo in paraInfos)
    {
        SqlParameter sqlPara = new SqlParameter("@" + paraInfo.Name, paraInfo.Value);
 
        if (paraInfo.IsOutput)
            sqlPara.Direction = ParameterDirection.Output;
 
        paraInfo.SqlPara = sqlPara;
 
        sqlParas.Add(sqlPara);
    }
 
    return sqlParas.ToArray();
}

Line 1: 將暫存的參數資料清單 paraInfos 轉出一份 SqlParameter 參數清單 commandParameters。

Line 9: 將參數名稱冠上 @ 前置符號做為 SqlParameter 要送去資料庫的參數名稱，例如 EmpAccount 就會以 @EmpAccount 傳送給資料庫。參數值從 paraInfo.Value 取得。

Line 11, 12: 指定是否為輸出型的參數。

Line 14: 把產生的 SqlParameter 物件也記錄在暫存參數清單中，之後要用來取回指令執行後的輸出型參數值。

4.用 log4net 記錄要執行的指令碼與參數資訊：
?
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
LogSql(cmdText, commandParameters);
 
protected void LogSql(string commandText, params SqlParameter[] commandParameters)
{
    if (Logger.IsDebugEnabled)
    {
        try
        {
            string separator = ", ";
            StringBuilder sbParams = new StringBuilder(500);
            foreach (SqlParameter tempParam in commandParameters)
            {
                string tempValue = null;
                if (tempParam.Value == null)
                {
                    tempValue = "(null)";
                }
                else if (Convert.IsDBNull(tempParam.Value))
                {
                    tempValue = "(DBNull)";
                }
                else
                {
                    tempValue = tempParam.Value.ToString();
                }
 
                switch (tempParam.Direction)
                {
                    case ParameterDirection.Output:
                    case ParameterDirection.InputOutput:
                        sbParams.Append("output ");
                        break;
                }
 
                sbParams.AppendFormat("{0}={1}", tempParam.ParameterName, tempValue);
                sbParams.Append(separator);
            }
 
            int separatorLen = separator.Length;
            if (sbParams.Length >= separatorLen)
            {
                sbParams.Remove(sbParams.Length - separatorLen, separatorLen);
            }
 
            Logger.DebugFormat("sql: {0}; params: {1};", commandText, sbParams.ToString());
        }
        catch (Exception ex)
        {
            Logger.Error("params SqlParameter", ex);
        }
    }
}

Line 1: 記錄要執行的指令碼與參數資訊。

Line 5: 當 log4net 的 <level> 設定值為 DEBUG 時才會記錄。

儲存的 sql log 內容如下，
sql: dbo.spEmployee_UpdateLoginInfo; params: @EmpAccount=admin, @ThisLoginIP=127.0.0.1;
sql: dbo.spEmployee_GetData; params: @EmpAccount=admin;

5.使用 SqlHelper.ExecuteDataset 將指令送到資料庫執行並取回 DataSet 資料：
?
1
2
3
4
5
6
7
8
9
if (hasParameters)
{
    ds = SqlHelper.ExecuteDataset(conn, cmdType, cmdText,
        commandParameters);
}
else
{
    ds = SqlHelper.ExecuteDataset(conn, cmdType, cmdText);
}

SqlHelper 是微軟在 2004 年左右，放在 Microsoft.ApplicationBlocks.Data 裡面的一個好用的工具類別，它把 SqlCommand, SqlDataAdapter, SqlDataReader 常用的功能包裝成一系列的方法。若用來執行 SP 的話，還可以寫成 SqlHelper.ExecuteDataset(conn, "spName", pa1, pa2, pa3)，它會自動幫忙把 pa1, pa2, pa3 的值依順序放置 SP 的對應參數裡，對於長期使用 SP 的我來說，實在是太方便了，也因此用到現在。

這裡使用的是 SqlHelper.ExecuteDataset()。

* Data Access Application Block for .NET v2 下載位置
* 這個版本是 2.0，印象中更舊的版本 (v1.x) 還沒支援可傳入 SqlTransaction 的方法，若有需要交易功能的人請多加注意。

6.將 SqlParameter output 參數值寫回指令資訊物件同名參數：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
if (hasOutputParameters)
{
    UpdateOutputParameterValuesOfSqlParaInfosFromSqlParameter(paraInfos, cmdInfo);
}
 
protected void UpdateOutputParameterValuesOfSqlParaInfosFromSqlParameter(List<SqlParaInfo> paraInfos, IDataAccessCommandInfo cmdInfo)
{
    foreach (SqlParaInfo paraInfo in paraInfos)
    {
        if (paraInfo.IsOutput)
        {
            FieldInfo outputParaFieldInfo = paraInfo.ParaFieldInfo;
            outputParaFieldInfo.SetValue(cmdInfo, paraInfo.SqlPara.Value);
        }
    }
}

Line 1, 3: 如果這次的參數中有輸出型參數的話才要把 SqlParameter 輸出型參數值寫回指令資訊物件同名參數。

Line 12, 13: paraInfo.SqlPara 為執行前暫存的 SqlParameter，paraInfo.ParaFieldInfo 為執行前暫存的 FieldInfo，也就是指令資訊物件的成員變數 Reflection 欄位資訊，這一行程式將 SqlParameter 的值寫回指令資訊物件的同名成員變數。

7.使用 IDataAccessSource.CloseConnection 關閉連線：
統一使用 IDataAccessSource.CloseConnection(conn); 關閉連線，減少 null 判斷的重覆程式碼。
?
1
2
3
4
5
6
7
public void CloseConnection(IDbConnection conn)
{
    if (conn == null)
        return;
 
    conn.Close();
}

其他 DataAccessCommand 提供的執行指令方法還有這些
?
1
2
3
4
5
6
7
8
9
10
11
12
13
/// <summary>
/// 執行指令
/// </summary>
bool ExecuteNonQuery(IDataAccessCommandInfo cmdInfo);
/// <summary>
/// 執行指令並取回第一個欄位值
/// </summary>
/// <param name="errCode">做為錯誤碼的值</param>
T ExecuteScalar<T>(IDataAccessCommandInfo cmdInfo, T errCode);
/// <summary>
/// 執行指令並取回 DataReader
/// </summary>
IDataReader ExecuteReader(IDataAccessCommandInfo cmdInfo, out SqlConnection connOut);

它們的運作方式和本篇介紹的 ExecuteDataset() 大同小異，最大的差別是在使用 SqlHelper 呼叫不同的對應方法。

若需要 DataAccessCommand 原始碼請看 DataAccessCommand.cs


Posted by at
Labels:

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)