Enterprise Library 2.0 - Data Access Application Block 補充：參數陣列快取與重複使用

針對頻繁呼叫同一預存程序的情境，將參數陣列封裝為方法並快取，可提升執行效率。以下提供實作範例：

取得預存程序參數陣列

/// <summary>
/// 取得預存程序參數陣列，方便重複呼叫並可快取
/// </summary>
public DbParameter[] GetDBCommandParameters(Database db, string spName)
{
    DbCommand dbcomm = db.GetStoredProcCommand(spName);
    db.DiscoverParameters(dbcomm);
    dbcomm.Parameters.RemoveAt(0); // 移除 @return_value

    DbParameter[] result = new DbParameter[dbcomm.Parameters.Count];
    dbcomm.Parameters.CopyTo(result, 0);

    foreach (DbParameter dp in result)
        dp.Value = DBNull.Value;

    return result;
}

參數陣列賦值

/// <summary>
/// 對參數陣列賦值
/// </summary>
public void AssignParameters(DbParameter[] commandParameters, object[] parameterValues)
{
    if (commandParameters == null || parameterValues == null) return;
    if (commandParameters.Length != parameterValues.Length)
        throw new ArgumentException("參數陣列長度不匹配！");

    for (int i = 0, j = commandParameters.Length; i < j; i++)
        commandParameters[i].Value = parameterValues[i];
}

呼叫範例

[TestMethod]
public void SelectPersonBySex()
{
    string sex = "男";
    Database db = DatabaseFactory.CreateDatabase();
    string spName = "SelectPersonBySex";

    DbParameter[] parameters = this.GetDBCommandParameters(db, spName);
    object[] strValues = new object[] { sex };
    this.AssignParameters(parameters, strValues);

    DataSet ds = db.ExecuteDataSet(spName, parameters);
    Assert.AreEqual(ds.Tables[0].Rows.Count, 1);
}

搭配快取機制（如 CacheManager），可進一步減少 DiscoverParameters 的重複開銷。