對 OracleCommand.Parameters 做排序

對 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);
}

結果,兩個方法都可行

image

效能比一比

執行10000的毫秒數

image

 

雖然後者的效能差了些,但我想我應該會選擇後者來使用。

 

P.S

剛寫完馬上就被糾正,第二種解法如果SQL是 "WHERE one=:one AND one2=:one2"

結果就會不正確....||| 還要再想想怎麼解決。

解法目前是抓":key "(有一個空白)的IndexOf,但最後一個where條件的後面可能沒有空白,因此

IndexOf是-1時,就給他int.MaxValue讓他排到最後面去