Github Link : Trace-Dapper.NET-Source-Code
11. Wrong SQL string concating will cause slow efficiency and memory leaks
Here's an important concept used by Dapper. Its SQL string is one of the important key values to cache. If different SQL strings are used, Dapper will create new dynamic methods and caches for this, so even if you use StringBuilder improperly can also cause slow query & memory leaks .
Why the SQL string is used as one of keys, instead of simply using the Handle of the Mapping type, one of the reasons is order of query column . As mentioned earlier, Dapper uses the 「result convert to code」 method to create a dynamic method, which means that the order and data must be fixed , avoid using the same set of dynamic methods with different SQL Select column order, there will be a A column value to b column wrong value problem.
The most direct solution is to establish a different dynamic method for each different SQL string and save it in a different cache.
For example, the following code is just a simple query action, but the number of Dapper Caches has reached 999999, such as image display
using (var cn = new SqlConnection(@"connectionString"))
{
for ( int i = 0; i < 999999 ; i ++ )
{
var guid = Guid.NewGuid();
for (int i2 = 0; i2 < 2; i2++)
{
var result = cn.Query<User>($"select '{guid}' ").First();
}
}
}
To avoid this problem, you only need to maintain a principle Reuse SQL string , and the simplest way is parametrization , for example: Change the above code to the following code, the number of caches is reduced to 1 , to achieve the purpose of reuse:
using (var cn = new SqlConnection(@"connectionString"))
{
for ( int i = 0; i < 999999 ; i ++ )
{
var guid = Guid.NewGuid();
for (int i2 = 0; i2 < 2; i2++)
{
var result = cn.Query<User>($"select @guid ",new { guid}).First();
}
}
}
12. Dapper SQL correct string concating method: Literal Replacement
If there is a need to splice SQL strings, for example: Sometimes it is more efficient to use string concating than not to use parameterization, especially if there are only a few fixed values .
At this time, Dapper can use the Literal Replacements function, how to use it: {=Attribute_Name} replace the value string to be concated, and save the value in the Parameter, for example:
void Main()
{
using (var cn = Connection)
{
var result = cn.Query("select N'Wei' Name,26 Age,{=VipLevel} VipLevel", new User{ VipLevel = 1}).First();
}
}
13. Why Literal Replacement can avoid caching problems?
First, trace the GetLiteralTokens method under the source code GetCacheInfo, you can find that before cache Dapper will get the data SQL string that match {=Attribute_Name} role .
private static readonly Regex literalTokens = new Regex(@"(?<![\p{L}\p{N}_])\{=([\p{L}\p{N}_]+)\}", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.CultureInvariant | RegexOptions.Compiled);
internal static IList<LiteralToken> GetLiteralTokens(string sql)
{
if (string.IsNullOrEmpty(sql)) return LiteralToken.None;
if (!literalTokens.IsMatch(sql)) return LiteralToken.None;
var matches = literalTokens.Matches(sql);
var found = new HashSet<string>(StringComparer.Ordinal);
List<LiteralToken> list = new List<LiteralToken>(matches.Count);
foreach (Match match in matches)
{
string token = match.Value;
if (found.Add(match.Value))
{
list.Add(new LiteralToken(token, match.Groups[1].Value));
}
}
return list.Count == 0 ? LiteralToken.None : list;
}
Then generate Parameter parameterized dynamic method in the CreateParamInfoGenerator method. The method IL of this section is as below:
IL_0000: ldarg.1
IL_0001: castclass <>f__AnonymousType1`1[System.Int32]
IL_0006: stloc.0
IL_0007: ldarg.0
IL_0008: callvirt System.Data.IDataParameterCollection get_Parameters()/System.Data.IDbCommand
IL_000d: pop
IL_000e: ldarg.0
IL_000f: ldarg.0
IL_0010: callvirt System.String get_CommandText()/System.Data.IDbCommand
IL_0015: ldstr "{=VipLevel}"
IL_001a: ldloc.0
IL_001b: callvirt Int32 get_VipLevel()/<>f__AnonymousType1`1[System.Int32]
IL_0020: stloc.1
IL_0021: ldloca.s V_1
IL_0023: call System.Globalization.CultureInfo get_InvariantCulture()/System.Globalization.CultureInfo
IL_0028: call System.String ToString(System.IFormatProvider)/System.Int32
IL_002d: callvirt System.String Replace(System.String, System.String)/System.String
IL_0032: callvirt Void set_CommandText(System.String)/System.Data.IDbCommand
IL_0037: ret
Then generate the Mapping dynamic method. To understand this logic, I will make a simulation example here:
public static class DbExtension
{
public static IEnumerable<User> Query(this DbConnection cnn, string sql, User parameter)
{
using (var command = cnn.CreateCommand())
{
command.CommandText = sql;
CommandLiteralReplace(command, parameter);
using (var reader = command.ExecuteReader())
while (reader.Read())
yield return Mapping(reader);
}
}
private static void CommandLiteralReplace(IDbCommand cmd, User parameter)
{
cmd.CommandText = cmd.CommandText.Replace("{=VipLevel}", parameter.VipLevel.ToString(System.Globalization.CultureInfo.InvariantCulture));
}
private static User Mapping ( IDataReader reader )
{
var user = new User();
var value = default(object);
value = reader[0];
if(!(value is System.DBNull))
user.Name = (string)value;
value = reader[1];
if (!(value is System.DBNull))
user.Age = (int)value;
value = reader[2];
if (!(value is System.DBNull))
user.VipLevel = (int)value;
return user;
}
}
After reading the above example, you can find that the underlying principle of Dapper Literal Replacements is string replace that it also belongs to the string concating way. Why can the cache problem be avoided?
This is because the replacement timing is in the SetParameter dynamic method, so the Cache SQL Key is unchanged can reuse the same SQL string and cache.
Also because it is a string replace method, only support basic value type if you use the String type, the system will inform you The type String is not supported for SQL literals.to avoid SQL Injection problems.
Top comments (0)