Github Link : 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:
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
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:
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:
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?
Check whether the attribute of the parameter is a subclass of IEnumerable
If yes, use the parameter name + regular format to find the parameter string in SQL
(regular format: ([?@:]Parameter name)(?!\w)(\s+(?i)unknown(?- i))?)Replace the found string with
()+ multipleattribute names + serial numberCreateParameter> SetValue in order of serial number
Key Code part
The following uses sys.objects to check SQL Server tables and views as an example of tracking:
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
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.
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.
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.
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:
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:
- Determine the type of the set parameter (if it is a string, the default size is 4000)
- Regular role of SQL parameters are replaced with serial number parameter strings
- Creation of DbCommand Paramter
The replacement logic of the SQL parameter string is also written here, such as the picture
22. DynamicParameter underlying logic and custom implementation
For example:
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:
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
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.
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.
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
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);
}
}
Top comments (0)