Throwback Thursday: Typed SQL reader

Akos Nagy
Apr 19, 2018

I've found an old HDD of mine and I got curious: what's on it? I had an old 3,5" HDD to USB adapter lying around and to my surprise, it was still working :) So I put the HDD in it, connected it to my 21st century notebook (actually, the HDD was also from the 21st century) and started browsing through the contents. And I did find something awsome :)

Back in the day I was really into runtime code generation. Some remnants of my exploits into this world can still be found on Github, like this repository Since I've just restarted these exploits with the ILSpy plugin that I wrote, I was especially happy to find this little piece of code.

So to get to point: back in the day I got this challenge that I should write a typed wrapper to get the results of an SQL query from an SqlDataReader (I guess LinqToSQL was already around at the time, but I'm not sure — and I don't think I cared :) ).

So the challenge goes something like this: create a class, called TypedSqlReader<T>, and then if I pass an SqlDataReader to and instance of this class, I can get results in the form of a list of T, using the predefined convention that every record is a new object, and the value of every column is written to the property with the same name.

Now, obviously, this is not that hard: you run the query, you iterate through the records, you create a new object at each iteration, then iterate through the columns and use reflection to get the property of the object and write the value to the property. And this results in a solution that's slow as hell :)

The better way? Runtime IL-code generation. Lo' and behold:

public class TypedSQLReader<T> where T : class, new()
  private readonly Type storeType;
  private readonly Func<SqlDataReader, T, bool> readerDelegate;

  public TypedSQLReader()
    this.storeType = typeof(T);
    this.readerDelegate = this.BuildReaderMethod();

  public IEnumerable<T> Map(SqlDataReader reader)
     var temp = new T();
     while (readerDelegate(reader, temp))
       yield return temp;
       temp = new T();

   private Func<SqlDataReader, T, bool> BuildReaderMethod()
      var readerMethod = new DynamicMethod("Read", typeof(bool), new[] { typeof(SqlDataReader), storeType });
      var readerGenerator = readerMethod.GetILGenerator();
      var readOk = readerGenerator.DefineLabel();
      readerGenerator.Emit(OpCodes.Callvirt, typeof(SqlDataReader).GetMethod(nameof(SqlDataReader.Read)));
      readerGenerator.Emit(OpCodes.Brtrue, readOk);

       var properties = storeType.GetProperties();
       var sqlDataReaderIndexer = typeof(SqlDataReader)
                                      .GetProperty("Item", new[] { typeof(string) })

      foreach (var property in properties)
          string columName = property.Name;
          Label next = readerGenerator.DefineLabel();
          LocalBuilder temp = readerGenerator.DeclareLocal(typeof(object));
          readerGenerator.Emit(OpCodes.Ldstr, columName);
          readerGenerator.Emit(OpCodes.Callvirt, sqlDataReaderIndexer);
          readerGenerator.Emit(OpCodes.Stloc, temp);
          var columnType = property.PropertyType;
          readerGenerator.Emit(OpCodes.Ldloc, temp);
          readerGenerator.Emit(OpCodes.Brtrue, next);
          readerGenerator.Emit(OpCodes.Ldloc, temp);
          if (columnType.IsValueType)
            readerGenerator.Emit(OpCodes.Unbox_Any, columnType);
            readerGenerator.Emit(OpCodes.Castclass, columnType);
          readerGenerator.Emit(OpCodes.Callvirt, property.GetSetMethod());

           (Func<SqlDataReader, T,bool>)
                readerMethod.CreateDelegate(typeof(Func<SqlDataReader, T, bool>));

So what's going on in here? Well, the idea is simple. If you have a type like this:
public class Person
  public string Name { get; set; }
  public int Age { get; set; }

Then, given an instance of SqlDataReader, the best and most efficient way to do the conversion is this:

Person p = new Person();
p.Age = (int)reader["Age"];
p.Name = (string)reader["Name"];

Obviously, this works only for the Person type, so the solution is not generic at all. Here's where reflection could come in, but again, that results in slow execution time.

So here's the idea: why not generate the code that's required to do the simple mapping at runtime? And that's exactly what the code above does (and yes, this is actually the same code as I wrote back then, just added a little C# 7 to it where it felt right).

The first couple of lines are responsible for calling the Read method of the SqlDataReader instance (which is the second parameter of the method). If the result is false, the method returns with false (the Ldc_I4_0 loads the the false value and the Ret opcode returns it), otherwise, the execution continues.

Then the properties are queried from the type, and in a foreach, the property setter code is generated. The indexer of the SqlDataReader is called (that's the property called Item), the result is stored in a local variable. If the result is null, then this property is skipped (the code jumps to the label which is marked at the end of the generating foreach). If the result is a value type then it is unboxed, otherwise cast and finally stored in the appropriate property by calling the setter method of the object that was the other parameter of the generated method.

Now, this code might not be easy to read (especially if you are not fluent in IL), but it's fast, efficient and fun to write — - at least it was for me.

Akos Nagy
Posted in C# .NET