Trace Dapper.NET Source Code - The Underlying Logic Of Parameterization
Tags: dotnet, csharp, dapper
Published: 2020-12-17 00:24:16

Github Link : [Trace\-Dapper.NET\-Source\-Code](https://github.com/shps951023/Trace-Dapper.NET-Source-Code)

----

## 20. The Underlying Logic Of Parameterization

One key function of Dapper: "Parameterization"

Main logic: GetCacheInfo checks whether there is a dynamic method in the cache >  If there is no cache, use the CreateParamInfoGenerator method Emit IL to create the AddParameter dynamic method >  Save it in the cache after creation

Next, we will focus on the underlying logic and "exquisite detail processing" in the CreateParamInfoGenerator method, using the result reverse code method, `ignoring the "unused fields"` and not generating the corresponding IL code to avoid resource waste. This is also the reason why the previous caching algorithm has to check different SQL strings.

The following are the key parts of the source code I picked:

```C#
internal static Action<IDbCommand, object> CreateParamInfoGenerator(Identity identity, bool checkForDuplicates, bool removeUnused, IList<LiteralToken> literals)
{
  //...
  if (filterParams)
  {
    props = FilterParameters(props, identity.sql);
  }

  var callOpCode = isStruct ? OpCodes.Call : OpCodes.Callvirt;
  foreach (var prop in props)
  {
    //Emit IL action
  }
  //...
}


private static IEnumerable<PropertyInfo> FilterParameters(IEnumerable<PropertyInfo> parameters, string sql)
{
  var list = new List<PropertyInfo>(16);
  foreach (var p in parameters)
  {
    if (Regex.IsMatch(sql, @"[?@:]" + p.Name + @"([^\p{L}\p{N}_]+|$)", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.CultureInvariant))
      list.Add(p);
  }
  return list;
}
```

Then check IL to verify, the query code is as follows

```C#
var result = connection.Query("select @Name name ", new { Name = "Wei", Age = 26}).First();
```

The IL code of the CreateParamInfoGenerator AddParameter dynamic method is as below:

```C#
IL_0000: ldarg.1    
IL_0001: castclass  <>f__AnonymousType1`2[System.String,System.Int32]
IL_0006: stloc.0    
IL_0007: ldarg.0    
IL_0008: callvirt   System.Data.IDataParameterCollection get_Parameters()/System.Data.IDbCommand
IL_000d: dup        
IL_000e: ldarg.0    
IL_000f: callvirt   System.Data.IDbDataParameter CreateParameter()/System.Data.IDbCommand
IL_0014: dup        
IL_0015: ldstr      "Name"
IL_001a: callvirt   Void set_ParameterName(System.String)/System.Data.IDataParameter
IL_001f: dup        
IL_0020: ldc.i4.s   16
IL_0022: callvirt   Void set_DbType(System.Data.DbType)/System.Data.IDataParameter
IL_0027: dup        
IL_0028: ldc.i4.1   
IL_0029: callvirt   Void set_Direction(System.Data.ParameterDirection)/System.Data.IDataParameter
IL_002e: dup        
IL_002f: ldloc.0    
IL_0030: callvirt   System.String get_Name()/<>f__AnonymousType1`2[System.String,System.Int32]
IL_0035: dup        
IL_0036: brtrue.s   IL_0042
IL_0038: pop        
IL_0039: ldsfld     System.DBNull Value/System.DBNull
IL_003e: ldc.i4.0   
IL_003f: stloc.1    
IL_0040: br.s       IL_005a
IL_0042: dup        
IL_0043: callvirt   Int32 get_Length()/System.String
IL_0048: ldc.i4     4000
IL_004d: cgt        
IL_004f: brtrue.s   IL_0058
IL_0051: ldc.i4     4000
IL_0056: br.s       IL_0059
IL_0058: ldc.i4.m1  
IL_0059: stloc.1    
IL_005a: callvirt   Void set_Value(System.Object)/System.Data.IDataParameter
IL_005f: ldloc.1    
IL_0060: brfalse.s  IL_0069
IL_0062: dup        
IL_0063: ldloc.1    
IL_0064: callvirt   Void set_Size(Int32)/System.Data.IDbDataParameter
IL_0069: callvirt   Int32 Add(System.Object)/System.Collections.IList
IL_006e: pop        
IL_006f: pop        
IL_0070: ret            
```

IL converted to C# code:

```C#
public class TestType
{
  public static void TestMeThod(IDataReader P_0, object P_1)
  {
    var anon = (<>f__AnonymousType1<string, int>)P_1;
    IDataParameterCollection parameters = ((IDbCommand)P_0).Parameters;
    IDbDataParameter dbDataParameter = ((IDbCommand)P_0).CreateParameter();
    dbDataParameter.ParameterName = "Name";
    dbDataParameter.DbType = DbType.String;
    dbDataParameter.Direction = ParameterDirection.Input;
    object obj = anon.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);
  }
}
```

It can be found that although the Age parameter is passed, the SQL string is not used, and Dapper will not generate the SetParameter action IL for this field. This detail processing really needs to give Dapper a thumbs up!

## 21. The underlying logic of IN multi-set parameterization

Why ADO.NET does not support IN parameterization, but Dapper does?

1. Check whether the attribute of the parameter is a subclass of IEnumerable 

2. If yes, use the parameter name + regular format to find the parameter string in SQL `(regular format: ([?@:]Parameter name)(?!\w)(\s+(?i)unknown(?- i))?)`

3. Replace the found string with `()` + multiple `attribute names + serial number`

4. CreateParameter> SetValue in order of serial number

Key Code part

![image](https://user-images.githubusercontent.com/12729184/101145551-ea3dbf00-3654-11eb-9590-2ba3418b004a.png)

The following uses sys.objects to check SQL Server tables and views as an example of tracking:

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

Dapper will change the SQL string to the following sql to execute

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

Looking at Emit IL, you can find that it is very different from the previous parameterized IL, which is very clean.

```C#
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   
```

Turning to C# code, you will be surprised to find: `This code does not need to use Emit IL at all. It is simply unnecessary.`

```C#
    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);
    }
```

That's right, it is unnecessary, even `IDataParameterCollection parameter = P_0.Parameters`; this code will not be used at all.

There is a reason for Dapper, because it can be used with `non-collective parameters`, such as the previous example and the data logic to find the name of the order.

```C#
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%" });
```

The corresponding generated IL conversion C# code will be the following code, which can be used together:

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

In addition, why does Emit IL directly call the tool method `PackListParameters` on Dapper? Because the number of IN parameters is not fixed, the method `cannot be dynamically generated from the fixed result`.

The main logic contained in this method:

1. Determine the type of the set parameter (if it is a string, the default size is 4000)
2. Regular role of SQL parameters are replaced with serial number parameter strings
3. Creation of DbCommand Paramter

![image](https://user-images.githubusercontent.com/12729184/101146110-97183c00-3655-11eb-90b8-0db8dec91d77.png)

The replacement logic of the SQL parameter string is also written here, such as the picture

![image](https://user-images.githubusercontent.com/12729184/101146159-a303fe00-3655-11eb-92c5-99968b387881.png)

## 22. DynamicParameter underlying logic and custom implementation

For example:  

```C#
using (var cn = Connection)
{
    var paramter = new { Name = "John", Age = 25 };
    var result = cn.Query("select @Name Name,@Age Age", paramter).First();
}
```

We already know that String type Dapper will automatically convert to database `Nvarchar` and a parameter with a `length of 4000`. The SQL actually executed by the database is as below:

```sql
exec sp_executesql N'select @Name Name,@Age Age',N'@Name nvarchar(4000),@Age int',@Name=N'John',@Age=25
```

This is an intimate design that is convenient for rapid development, but if you encounter a situation where the field is of `varchar` type, it may cause the `index to fail` due to the `implicit transformation`, resulting in low query efficiency.

At this time, the solution can use Dapper DynamicParamter to specify the database type and size to achieve the purpose of optimizing performance

```C#
using (var cn = Connection)
{
    var paramters = new DynamicParameters();
    paramters.Add("Name","John",DbType.AnsiString,size:4);
    paramters.Add("Age",25,DbType.Int32);
    var result = cn.Query("select @Name Name,@Age Age", paramters).First();
}
```

Then go to the source to see how to implement it. First, pay attention to the GetCacheInfo method. You can see that DynamicParameters create a dynamic method. The code is very simple, just call the AddParameters method.

```C#
Action<IDbCommand, object> reader;
if (exampleParameters is IDynamicParameters)
{
    reader = (cmd, obj) => ((IDynamicParameters)obj).AddParameters(cmd, identity);
}
```

The reason why the code can be so simple is that Dapper uses an `"interface-dependent"` design here to increase the flexibility of the program and allow users to customize the implementation logic they want. This point will be explained below. First, let's look at the implementation logic of the `AddParameters` method in Dapper's default implementation class `DynamicParameters`.

```C#
public class DynamicParameters : SqlMapper.IDynamicParameters, SqlMapper.IParameterLookup, SqlMapper.IParameterCallbacks
{
    protected void AddParameters(IDbCommand command, SqlMapper.Identity identity)
    {
        var literals = SqlMapper.GetLiteralTokens(identity.sql);

        foreach (var param in parameters.Values)
        {
            if (param.CameFromTemplate) continue;

            var dbType = param.DbType;
            var val = param.Value;
            string name = Clean(param.Name);
            var isCustomQueryParameter = val is SqlMapper.ICustomQueryParameter;

            SqlMapper.ITypeHandler handler = null;
            if (dbType == null && val != null && !isCustomQueryParameter)
            {
#pragma warning disable 618
                dbType = SqlMapper.LookupDbType(val.GetType(), name, true, out handler);
#pragma warning disable 618
            }
            if (isCustomQueryParameter)
            {
                ((SqlMapper.ICustomQueryParameter)val).AddParameter(command, name);
            }
            else if (dbType == EnumerableMultiParameter)
            {
#pragma warning disable 612, 618
                SqlMapper.PackListParameters(command, name, val);
#pragma warning restore 612, 618
            }
            else
            {
                bool add = !command.Parameters.Contains(name);
                IDbDataParameter p;
                if (add)
                {
                    p = command.CreateParameter();
                    p.ParameterName = name;
                }
                else
                {
                    p = (IDbDataParameter)command.Parameters[name];
                }

                p.Direction = param.ParameterDirection;
                if (handler == null)
                {
#pragma warning disable 0618
                    p.Value = SqlMapper.SanitizeParameterValue(val);
#pragma warning restore 0618
                    if (dbType != null && p.DbType != dbType)
                    {
                        p.DbType = dbType.Value;
                    }
                    var s = val as string;
                    if (s?.Length <= DbString.DefaultLength)
                    {
                        p.Size = DbString.DefaultLength;
                    }
                    if (param.Size != null) p.Size = param.Size.Value;
                    if (param.Precision != null) p.Precision = param.Precision.Value;
                    if (param.Scale != null) p.Scale = param.Scale.Value;
                }
                else
                {
                    if (dbType != null) p.DbType = dbType.Value;
                    if (param.Size != null) p.Size = param.Size.Value;
                    if (param.Precision != null) p.Precision = param.Precision.Value;
                    if (param.Scale != null) p.Scale = param.Scale.Value;
                    handler.SetValue(p, val ?? DBNull.Value);
                }

                if (add)
                {
                    command.Parameters.Add(p);
                }
                param.AttachedParam = p;
            }
        }

        // note: most non-priveleged implementations would use: this.ReplaceLiterals(command);
        if (literals.Count != 0) SqlMapper.ReplaceLiterals(this, command, literals);
    }
}
```

It can be found that Dapper has made many conditions and actions in AddParameters for convenience and compatibility with other functions, such as Literal Replacement and EnumerableMultiParameter functions, so the amount of code will be more than the previous version of ADO.NET, so `the efficiency will be slower`.

If you have demanding requirements for efficiency, you can implement the logic yourself, because this section of Dapper is specially designed to `"depend on the interface"`, and you only need to `implement the IDynamicParameters interface`.

The following is a demo I made, you can use ADO.NET SqlParameter to establish parameters to cooperate with Dapper

```C#
public class CustomPraameters : SqlMapper.IDynamicParameters
{
  private SqlParameter[] parameters;
  public void Add(params SqlParameter[] mParameters)
  {
    parameters = mParameters;
  }

  void SqlMapper.IDynamicParameters.AddParameters(IDbCommand command, SqlMapper.Identity identity)
  {
    if (parameters != null && parameters.Length > 0)
      foreach (var p in parameters)
        command.Parameters.Add(p);
  }
}
```

![image](https://user-images.githubusercontent.com/12729184/101188682-fe9fad00-3690-11eb-9553-37ca92e7b10e.png)



##