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.