為何ADO.NET不支援IN 參數化,Dapper支援 ?

原理

1. 判斷參數的屬性是否為IEnumerable類別子類別
2. 假如是,以該參數名稱為主 + Parameter正則格式找尋SQL內的參數字串 (正則格式 : ([?@:]參數名)(?!\w)(\s+(?i)unknown(?-i))?)
3. 將找到的字串以() + 多個屬性名稱+流水號方式替換
4. 依照流水號順序依序CreateParameter > SetValue

關鍵程式部分

以下用sys.objects來查SQL Server的表格跟視圖當追蹤例子 :

var result = cn.Query(@"select * from sys.objects where type_desc In @type_descs", new { type_descs = new[] { "USER_TABLE", "VIEW" } });

Dapper會將SQL字串改成以下方式執行

select * from sys.objects where type_desc In (@type_descs1,@type_descs2)
-- @type_descs1 = nvarchar(4000) - 'USER_TABLE'
-- @type_descs2 = nvarchar(4000) - 'VIEW'

查看Emit IL可以發現跟之前的參數化IL很不一樣,非常的簡短

IL_0000: ldarg.1    
IL_0001: castclass  <>f__AnonymousType0`1[System.String[]]
IL_0006: stloc.0    
IL_0007: ldarg.0    
IL_0008: callvirt   System.Data.IDataParameterCollection get_Parameters()/System.Data.IDbCommand
IL_000d: ldarg.0    
IL_000e: ldstr      "type_descs"
IL_0013: ldloc.0    
IL_0014: callvirt   System.String[] get_type_descs()/<>f__AnonymousType0`1[System.String[]]
IL_0019: call       Void PackListParameters(System.Data.IDbCommand, System.String, System.Object)/Dapper.SqlMapper
IL_001e: pop        
IL_001f: ret        

轉成C#代碼來看,會很驚訝地發現:「這段根本不需要使用Emit IL簡直多此一舉」

    public static void TestMeThod(IDbCommand P_0, object P_1)
    {
        var anon = (<>f__AnonymousType0<string[]>)P_1;
        IDataParameterCollection parameter = P_0.Parameters;
        SqlMapper.PackListParameters(P_0, "type_descs", anon.type_descs);
    }

沒錯,是多此一舉,甚至 IDataParameterCollection parameter = P_0.Parameters;這段代碼根本不會用到。

Dapper這邊做法是有原因的,因為要能跟非集合參數配合使用,像是前面例子加上找出訂單Orders名稱的資料邏輯

var result = cn.Query(@"select * from sys.objects where type_desc In @type_descs and name like @name"
    , new { type_descs = new[] { "USER_TABLE", "VIEW" }, @name = "order%" });

對應生成的IL轉換C#代碼就會是以下代碼,達到能搭配使用目的 :

    public static void TestMeThod(IDbCommand P_0, object P_1)
    {
        <>f__AnonymousType0<string[], string> val = P_1;
        IDataParameterCollection parameters = P_0.Parameters;
        SqlMapper.PackListParameters(P_0, "type_descs", val.get_type_descs());
        IDbDataParameter dbDataParameter = P_0.CreateParameter();
        dbDataParameter.ParameterName = "name";
        dbDataParameter.DbType = DbType.String;
        dbDataParameter.Direction = ParameterDirection.Input;
        object obj = val.get_name();
        int num;
        if (obj == null)
        {
            obj = DBNull.Value;
            num = 0;
        }
        else
        {
            num = ((((string)obj).Length > 4000) ? (-1) : 4000);
        }
        dbDataParameter.Value = obj;
        if (num != 0)
        {
            dbDataParameter.Size = num;
        }
        parameters.Add(dbDataParameter);
    }

另外為何Dapper這邊Emit IL會直接呼叫工具方法PackListParameters,是因為IN的參數化數量是不固定,所以不能由固定結果反推程式碼方式動態生成方法。

該方法裡面包含的主要邏輯:

1. 判斷集合參數的類型是哪一種 (假如是字串預設使用4000大小)
2. 正則判斷SQL參數以流水號參數字串取代
3. DbCommand的Paramter的創建

SQL參數字串的取代邏輯也寫在這邊,如圖片