對 OracleCommand.Parameters 做排序

2011-07-12

7567
0
C#小程式+語法隨手記
2011-07-13

最近在操作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讓他排到最後面去

回首頁