Trace Dapper.NET Source Code - Wrong SQL String Concat Will Cause Slow Efficiency And Memory Leaks
Tags: dotnet, csharp, dapper
Published: 2020-12-11 00:32:46

Github Link : [Trace\-Dapper.NET\-Source\-Code](https://github.com/shps951023/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` .

![image](https://user-images.githubusercontent.com/12729184/101131153-c8860d00-363f-11eb-9553-f50baafbb2a7.png)



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

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

![image](https://user-images.githubusercontent.com/12729184/101131682-aa6cdc80-3640-11eb-9b71-697496d29b3b.png)

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:

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

![image](https://user-images.githubusercontent.com/12729184/101131840-e9029700-3640-11eb-8f95-a4d317dc3842.png)

## 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:

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

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

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

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