A generic data reader with ILGenerator

• Download GenericReader.zip - 50.4 KB
• Download GenericReaderBenchmark.zip - 11.8 KB

Introduction

When doing a data access layer, have you ever wondered if it was be possible to receive a list of objects instead of a DataReader. This is possible with refection and code generation. This saves a lot of time and if you think that this method is slower that the traditional one, it's false. The performance is somewhat equal to the traditional method.

Background

The last time I worked on a data access layer, I found myself repeating the same lines of code over and over. I executed a DataReader and read each column in a while statement. This method leads to many runtime errors. We have to remember the index of each column. What if we add a column at the beginning without changing the indexes? So I developed a method that binds a class to a query. By analysing the class though reflection, we are able to auto generate the code that uses the data reader.

I have made a little project to test the performance of the GenericReader. To do so, I implemented a IDataReader that is working only in memory in order to have the most reliable benchmarks.

Using the code

In order to do this, we can use the reflection. The idea is to bind a field to a column of a select statement.

As an example, I declare a class with 4 fields. Each field has an attribute with a column name. We can see that the fields are read only; this is possible with the code IL. Furthermore, we can see that I use a double? in order to tell that a value type can be null. The verification is made with the DBNull and the program sets the value to null. This is easier to use.

public struct TestClass
{
    [ReaderName("Field1")]
    public readonly long Field1;
    [ReaderName("Field2")]
    public readonly string Field2;
    [ReaderName("Field3")]
    public readonly double? Field3;
    [ReaderName("Field4")]
    public readonly bool Field4;
}

The last thing we have to do is to use the GenericReader. The following method lets us see how easy it is.

GenericReader genericReader = new GenericReader();
private List<testclass /> ReaderExample(SqlConnection connection)
{
   SqlCommand command = new SqlCommand("SELECT Field1, Field2, Field3, Field4 " +
                                       "FROM AnyTable");
   return genericReader.ReadReader<testclass />(command.ExecuteReader());
}

The GenericReader is using the codeIL to generate a delegate the first time we use a Type. This delegate is use over an over to read the DataReader.

Points of Interest

The most useful thing is that the code is somewhat easier to maintain. Especially when we need to add an information in a select statement. In this case, it's very easy to make mistakes with the indexed of the DataReader.

History

At first, I use reflection to read the data instead of creating a delegate with codeIL. The need came quickly to have a faster way. So I change it a bit. CodeIL is a lot harder to implement, but it was worth it. The people that used the GenericReader told me that they saved a lot of time coding there data access layer.