/// <summary>
/// IDbCommand.Parameters檢核，找出Parameters數目不合問題
/// </summary>
/// <param name="cmd">IDbCommand物件</param>
/// <param name="prefix">CommandText中的參數名特徵，如:v_, @p_</param>
/// <param name="reorder">是否重新依參數出場順序排序</param>
/// <returns></returns>
public static string VerifyCommandParameters(
IDbCommand cmd, string prefix, bool reorder)
{ Dictionary<string, string> paramSymbolCheck =
new Dictionary<string, string>();
Dictionary<string, string> paramUsageCheck =
new Dictionary<string, string>();
Dictionary<string, IDataParameter> paramPool =
new Dictionary<string, IDataParameter>();
List<string> paramOrder = new List<string>();
string s = "";
for (int i = 0; i < prefix.Length; i++)
s += "[" + prefix.Substring(i, 1) + "]";
//用RegEx找出CommandText中的參數宣告
foreach (Match m in Regex.Matches(cmd.CommandText,
"(?ims)(?<n>" + s + "[a-zA-Z0-9_]+)"))
{ //OracleParameter要去掉前方的:才是ParameterName
string pn = m.Value.ToUpper().TrimStart(':'); paramSymbolCheck.Add(pn, m.Value);
paramOrder.Add(pn);
}
//找出所有參數物件
foreach (IDataParameter p in cmd.Parameters)
{ paramUsageCheck.Add(p.ParameterName.ToUpper(),
p.ParameterName);
paramPool.Add(p.ParameterName.ToUpper(), p);
}
StringBuilder sb = new StringBuilder();
//檢查是否每個Parameter都有被用到?
foreach (string n in paramUsageCheck.Keys)
{ if (!paramSymbolCheck.ContainsKey(n))
sb.AppendFormat("Parameter[{0}]-not used!\n", paramUsageCheck[n]);
}
//檢查是否每個參數宣告都有對應的Parameter?
foreach (string n in paramSymbolCheck.Keys)
{ if (!paramUsageCheck.ContainsKey(n))
sb.AppendFormat("Parameter[{0}]-missing!\n", paramSymbolCheck[n]);
}
//依出現順序重排參數
if (sb.Length == 0 && reorder)
{ cmd.Parameters.Clear();
foreach (string n in paramOrder)
cmd.Parameters.Add(paramPool[n]);
}
return sb.ToString();
}