再來看一次取得員工資料的範例程式碼。
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 取得物件的所有參數名稱與參數值的程式碼片段如下，
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 參數清單：
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 記錄要執行的指令碼與參數資訊：
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;
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 參數值寫回指令資訊物件同名參數：
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 判斷的重覆程式碼。
public void CloseConnection(IDbConnection conn)
{
if (conn == null)
return;
conn.Close();
}
其他 DataAccessCommand 提供的執行指令方法還有這些
/// <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
沒有留言:
張貼留言