搜尋此網誌

2018/04/17

SampleCMS 資料層元件說明：客製化指令資訊類別 (DataAccessCommandInfo)

前一篇提到如何透過四個步驟來建立資料層中用來存取指定 Stored Procedure（以下簡稱 SP）的指令資訊類別 (DataAccessCommandInfo) 。

接下來要繼續說明如何客製化指令資訊類別，例如：我想直接在資料層寫 sql script、我想在資料層開啟交易執行多個 SP。


DataAccessCommand.ExecuteDataset 活動圖（改）
上圖是 指令執行者 DataAccessCommand 運作方式說明 文章裡示意方法 ExecuteDataset() 被呼叫時其內部的活動流程。今天要說明的就是上圖綠框中「指令資訊的 ExecuteDataset 方法」的實作方式。

針對指令執行者的下列四個資料存取方法，
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/// <summary>
/// 執行指令並取回 DataSet
/// </summary>
DataSet ExecuteDataset(IDataAccessCommandInfo cmdInfo);
/// <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);

系統提供對應的四個介面給指令資訊使用，
?
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
/// <summary>
/// 自訂取回 DataSet 的執行功能
/// </summary>
public interface ICustomExecuteDataset
{
    /// <summary>
    /// 執行指令並取回 DataSet
    /// </summary>
    DataSet ExecuteDataset(IDataAccessCommandInnerTools innerTools);
}
 
/// <summary>
/// 自訂執行功能
/// </summary>
public interface ICustomExecuteNonQuery
{
    /// <summary>
    /// 執行指令
    /// </summary>
    bool ExecuteNonQuery(IDataAccessCommandInnerTools innerTools);
}
 
/// <summary>
/// 自訂取回第一個欄位值的執行功能
/// </summary>
public interface ICustomExecuteScalar
{
    /// <summary>
    /// 執行指令並取回第一個欄位值
    /// </summary>
    /// <param name="errCode">做為錯誤碼的值</param>
    T ExecuteScalar<T>(IDataAccessCommandInnerTools innerTools, T errCode);
}
 
/// <summary>
/// 自訂取回 DataReader 的執行功能
/// </summary>
public interface ICustomExecuteReader
{
    /// <summary>
    /// 執行指令並取回 DataReader
    /// </summary>
    IDataReader ExecuteReader(IDataAccessCommandInnerTools innerTools, out SqlConnection connOut);
}

如何使用這些介面，以下拿帳號與權限系統裡的一個功能「儲存員工身分後端作業授權清單」為例做說明。

功能「儲存員工身分後端作業授權清單」的目標是把要設定給一個員工身分的多筆授權資料一次儲存到資料庫中，輸入的參數有三個 string RoleName, List<RoleOpDescParamsDA> roleOps, string PostAccount，其中一個為清單物件。

而資料庫中對應的 SP 資訊如下，這個 SP 只提供一次儲存一筆授權資料：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- =============================================
-- Author:      <lozen_lin>
-- Create date: <2017/11/15>
-- Description: <儲存員工身分後端作業授權>
-- =============================================
create procedure dbo.spEmployeeRoleOperationsDesc_SaveData
@RoleName nvarchar(20)
,@OpId int
,@CanRead bit
,@CanEdit bit
,@CanReadSubItemOfSelf bit
,@CanEditSubItemOfSelf bit
,@CanAddSubItemOfSelf bit
,@CanDelSubItemOfSelf bit
,@CanReadSubItemOfCrew bit
,@CanEditSubItemOfCrew bit
,@CanDelSubItemOfCrew bit
,@CanReadSubItemOfOthers bit
,@CanEditSubItemOfOthers bit
,@CanDelSubItemOfOthers bit
,@PostAccount varchar(20)
as

因此需要客製化指令資訊類別，將本功能輸入的清單參數以迴圈的方式逐筆去呼叫 SP 將授權資料存入資料庫，另外，我希望能確保清單中所有的授權資料儲存後不要有落掉部分資料的可能，因此想使用交易機制讓這些授權資料要嘛就全部都有存入資料庫，要嘛萬一儲存過程中有任意一筆失敗了就回復到未執行儲存前。

先從呼叫者端的程式碼看起，
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/// <summary>
/// 儲存員工身分後端作業授權清單
/// </summary>
public bool SaveListOfEmployeeRolePrivileges(RolePrivilegeParams param)
{
    IDataAccessCommand cmd = DataAccessCommandFactory.GetDataAccessCommand(DBs.MainDB);
    SaveListOfEmployeeRoleOperationsDesc cmdInfo = new SaveListOfEmployeeRoleOperationsDesc()
    {
        RoleName = param.RoleName,
        roleOps = param.GetRoleOpsOfDA(),
        PostAccount = param.PostAccount
    };
    bool result = cmd.ExecuteNonQuery(cmdInfo);
    dbErrMsg = cmd.GetErrMsg();
 
    return result;
}

Line 7 的 SaveListOfEmployeeRoleOperationsDesc 為客製化的指令資訊類別，
對於呼叫者端來說，用法與其他指令資訊類別相同。

接著來看客製化指令資訊類別 class SaveListOfEmployeeRoleOperationsDesc 的元件關聯圖以及程式碼，
class SaveListOfEmployeeRoleOperationsDesc 元件關聯圖

?
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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
/// <summary>
/// 儲存員工身分後端作業授權清單
/// </summary>
public class SaveListOfEmployeeRoleOperationsDesc : IDataAccessCommandInfo, ICustomExecuteNonQuery
{
    public string RoleName;
    public List<RoleOpDescParamsDA> roleOps;
    public string PostAccount;
 
    public SaveListOfEmployeeRoleOperationsDesc()
    {
        roleOps = new List<RoleOpDescParamsDA>();
    }
 
    public CommandType GetCommandType()
    {
        return CommandType.StoredProcedure;
    }
 
    public string GetCommandText()
    {
        return "SaveListOfEmployeeRoleOperationsDesc";
    }
 
    public bool ExecuteNonQuery(IDataAccessCommandInnerTools innerTools)
    {
        if (roleOps.Count == 0)
        {
            innerTools.SetErrMsg("roleOps is empty");
            return false;
        }
 
        ILog Logger = innerTools.GetLogger();
        IDataAccessSource db = innerTools.GetDataAccessSource();
        SqlConnection conn = null;
        SqlTransaction tran = null;
 
        try
        {
            //建立連線資訊並開啟連線
            conn = db.CreateConnectionInstanceWithOpen();
            tran = conn.BeginTransaction();
 
            foreach (RoleOpDescParamsDA roleOp in roleOps)
            {
                innerTools.SetLogSql("spEmployeeRoleOperationsDesc_SaveData",
                    RoleName,
                    roleOp.OpId,
                    roleOp.CanRead,
                    roleOp.CanEdit,
                    roleOp.CanReadSubItemOfSelf,
                    roleOp.CanEditSubItemOfSelf,
                    roleOp.CanAddSubItemOfSelf,
                    roleOp.CanDelSubItemOfSelf,
                    roleOp.CanReadSubItemOfCrew,
                    roleOp.CanEditSubItemOfCrew,
                    roleOp.CanDelSubItemOfCrew,
                    roleOp.CanReadSubItemOfOthers,
                    roleOp.CanEditSubItemOfOthers,
                    roleOp.CanDelSubItemOfOthers,
                    PostAccount
                    );
 
                SqlHelper.ExecuteNonQuery(tran, "dbo.spEmployeeRoleOperationsDesc_SaveData",
                    RoleName,
                    roleOp.OpId,
                    roleOp.CanRead,
                    roleOp.CanEdit,
                    roleOp.CanReadSubItemOfSelf,
                    roleOp.CanEditSubItemOfSelf,
                    roleOp.CanAddSubItemOfSelf,
                    roleOp.CanDelSubItemOfSelf,
                    roleOp.CanReadSubItemOfCrew,
                    roleOp.CanEditSubItemOfCrew,
                    roleOp.CanDelSubItemOfCrew,
                    roleOp.CanReadSubItemOfOthers,
                    roleOp.CanEditSubItemOfOthers,
                    roleOp.CanDelSubItemOfOthers,
                    PostAccount
                    );
            }
 
            tran.Commit();
        }
        catch (Exception ex)
        {
            Logger.Error("", ex);
 
            //回傳錯誤訊息
            innerTools.SetErrMsg(ex.Message);
 
            if (tran != null)
                tran.Rollback();
 
            return false;
        }
        finally
        {
            //關閉連線資訊
            db.CloseConnection(conn);
        }
 
        return true;
    }
}

以下參照程式碼行號做說明，

Line 4: 除了實作介面 IDataAccessCommandInfo 之外，增加實作介面 ICustomExecuteNonQuery。

Line 6~8: 定義這個指令資訊的參數。

Line 25~104 實作介面 ICustomExecuteNonQuery 的方法 ExecuteNonQuery(IDataAccessCommandInnerTools innerTools)。

Line 25: 傳入的物件 innerTools 提供存取資料庫時所需的元件以及做為資料層可使用的方法。從元件關聯圖右側的 IDataAccessCommandInnerTools 可看到其提供的所有方法。

Line 33, 87: 從 innerTools 取得 log4net 元件，當發生例外錯誤時使用 log4net 記錄錯誤訊息。

Line 34, 41, 100: 從 innerTools 取得資料存取來源元件，透過資料存取來源元件建立 SqlConnection 元件並且連線至資料庫，最後透過資料存取來源元件關閉連線。

Line 42: 開啟交易功能，取得 SqlTransaction 元件。

Line 44~81: 逐筆將授權資料透過 SP dbo.spEmployeeRoleOperationsDesc_SaveData 寫入資料庫。

Line 46~62: 使用 innerTools.SetLogSql(string, params object[]) 記錄呼叫的 SP 名稱與參數資訊。

Line 64~80: 使用 SqlHelper.ExecuteNonQuery(SqlTransaction, string, params object[]) 在交易模式下執行 SP。記得將 Line 42 取得的 SqlTransaction 元件傳入第一個參數。

Line 83: 能執行到這一行代表儲存授權資料的過程中都很順利，呼叫 SqlTransaction.Commit() 認可這次的交易過程，完成交易。

Line 90: 透過 innerTools.SetErrMsg(string); 將呼叫者需要知道的錯誤訊息回傳給指令執行者元件記錄，呼叫者能透過指令執行者元件提供的 GetErrMsg() 取得這個錯誤訊息（在呼叫者端的程式碼 Line 14）。

Line 92, 93: 由於儲存授權資料的過程中有異常，復原這次的交易。


當指令執行者的 ExecuteNonQuery(IDataAccessCommandInfo cmdInfo) 用到上述的客製化指令資訊物件時，就會跳去執行指令資訊類別寫的 ExecuteNonQuery(IDataAccessCommandInnerTools innerTools) ，詳細的程式碼如下，
?
1
2
3
4
5
6
7
8
9
10
11
12
13
/// <summary>
/// 執行指令
/// </summary>
public bool ExecuteNonQuery(IDataAccessCommandInfo cmdInfo)
{
    //若有自訂執行功能就轉用自訂的
    if (cmdInfo is ICustomExecuteNonQuery)
    {
        return ((ICustomExecuteNonQuery)cmdInfo).ExecuteNonQuery(this);
    }
 
    // ...略...
}

ExecuteDataset(), ExecuteReader(), ExecuteScalar<T>() 也是一樣的規則，當發現指令資訊物件有實作對應的客製化介面時，就會跳去執行對應的指令資訊類別方法。

除了上述客製化存取資料用的介面，還有一種是「在執行sql指令前異動參數內容」
?
1
2
3
4
5
6
7
8
9
10
/// <summary>
/// 在執行sql指令前異動參數內容
/// </summary>
public interface IModifyCommandParametersBeforeExecute
{
    /// <summary>
    /// 在執行sql指令前異動參數內容
    /// </summary>
    void ModifyCommandParametersBeforeExecute(SqlParameter[] commandParameters);
}

用法請參考 DemoCommandInfos.cs 裡面的範例碼，
?
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
/// <summary>
/// 在執行sql指令前異動參數內容; custom modify CommandParameters before execute.
/// </summary>
public class spDemo_ModifyCommandParametersBeforeExecute : IDataAccessCommandInfo, IModifyCommandParametersBeforeExecute
{
    // DataAccessCommand 會使用欄位變數當做 SqlParameter 的產生來源(使用名稱、值)；屬性不包含在其中。
    // 輸出參數請加上屬性 [OutputPara]
    // DataAccessCommand generates SqlParameter information(name, value) from these fields automatically. Property is not included.
    // Output parameter needs attribute [OutputPara]
    public int pa1;
    public string pa2;
    public int? paNullable;
 
    public CommandType GetCommandType()
    {
        return CommandType.StoredProcedure;
    }
 
    public string GetCommandText()
    {
        return "dbo.spDemo_ModifyCommandParametersBeforeExecute";
    }
 
    public void ModifyCommandParametersBeforeExecute(SqlParameter[] commandParameters)
    {
        foreach (SqlParameter pa in commandParameters)
        {
            switch (pa.ParameterName)
            {
                case "@paNullable":
                    if (!paNullable.HasValue)
                    {
                        pa.Value = -1;
                    }
                    break;
            }
        }
    }
}

class spDemo_ModifyCommandParametersBeforeExecute 的目的是，參數 paNullable 提供給呼叫者的是可以給 null 的型別 int?，但是 SP 的 @paNullable 對於相同參數意義的值不是設計成給 null 而是需要給 -1，因此需要在傳值給 SP 之前將 @paNullable 的 null 改寫為 -1。若有其他類似的需求需要在傳送資料到資料庫之前以自訂的規則轉換參數值，就可以考慮使用這個介面。

p.s. 上述所有客製化用的介面在 DemoCommandInfos.cs 內容裡都有寫一些基本的範例程式碼做為參考。




Posted by at
Labels:

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)