對 OracleCommand.Parameters 做排序
最近在操作Oracle下OracleCommand的時候,不論 Select 或 Update,為了避免SQL Injection
用Parameters是很基本的一件事。
但在SQLCommand中加入Parameter可以不用按照Command下的順序,但在OracleCommand加入時卻要照順序
如:
SELECT * FROM TABLE WHERE A=:A AND B=:B
如果這樣下的話
OracleCommand.Parameters.Add("B","2");
OracleCommand.Parameters.Add("A","1");
雖然不會報錯,但返回的結果卻會不正確,一定要照著Command的順序下才行
為了這個問題,被偷偷搞了好幾次,於是痛下決心想要好好解決一下
NOTE:由於專案在使用時,有包裝過Connection及Command,所以用法會類似
public static class DBHelper
{
public static void CreateCommand(OracleCommand cmd,Dictionary<string,object> whereParameters)
{
foreach (var item in whereParameters)
{
cmd.Parameters.Add(item.Key,item.Value);
}
}
}
這樣子用,因此我的目標是可以將whereParameters按照我Command.Text的參數順序排好
大概想了兩種解決辦法,一種是用RegularExpression,另外一種是IComparer
測試資料:
//模擬SQL
string sql = @"SELECT * FROM TABLE WHERE one=:one AND
two=:two AND
three=:three AND
four =:four AND
five =:five";
//故意將參數順序弄錯
Dictionary<string, string> dic = new Dictionary<string, string>();
dic.Add("one", "1");
dic.Add("three", "3");
dic.Add("four", "4");
dic.Add("five", "5");
dic.Add("two", "2");
解法一 RegularExpression :
//這個Dictionary模擬是OracleCommand.Parameters
Dictionary<string, object> parameters = new Dictionary<string, object>();
//使用RegularExpression,抓出 ":"到邊界中間的字元
Regex r = new Regex(@":(?<parameter>\w+?)\b");
//比對
foreach (Match item in r.Matches(sql))
{
//取出比對成功的parameter群組的值
var key = item.Groups["parameter"].Value;
//如果這個key存在於dic裡面
if (dic.ContainsKey(key))
{
//加入到Parameters裡面
parameters.Add(key, dic[key]);
}
}
解法二 IComparer
實作IComparer
public class SortParameters : IComparer<string>
{
string _sql;
//傳入所下的SQL
public SortParameters(string sql)
{
_sql = sql;
}
public int Compare(string x, string y)
{
//找出在sql中":key "的index
int i = _sql.IndexOf(String.Concat(":", x ," "));
int j = _sql.IndexOf(String.Concat(":", y ," "));
//如果找不到就丟到最後面
i = (i == -1 ? int.MaxValue : i);
j = (j == -1 ? int.MaxValue : j);
return (i >= j) ? ((i > j) ? 1 : 0) : -1;
}
}
用法
//這個Dictionary模擬是OracleCommand.Parameters
Dictionary<string, object> parameters = new Dictionary<string, object>();
//使用自訂的SortParameters做排序
var orderParameters = dic.OrderBy(p => p.Key, new SortParameters(sql));
foreach (var item in orderParameters)
{
parameters.Add(item.Key, item.Value);
}
結果,兩個方法都可行
效能比一比
執行10000的毫秒數
雖然後者的效能差了些,但我想我應該會選擇後者來使用。
P.S
剛寫完馬上就被糾正,第二種解法如果SQL是 "WHERE one=:one AND one2=:one2"
結果就會不正確....||| 還要再想想怎麼解決。
解法目前是抓":key "(有一個空白)的IndexOf,但最後一個where條件的後面可能沒有空白,因此
IndexOf是-1時,就給他int.MaxValue讓他排到最後面去