cmatskas

Using a dynamic DataReader with ADO.NET

Christos Matskas -

I've recently had to do some data parsing, pulling data from a CSV file, performing some validation and then dumping a series of SQL commands for our data population scripts. I know there are a myriad ways to do this but I wanted to do this as quickly and easy as possible.

The process is quite simple:

  1. Open the file using a StreamReader
  2. Use the awesome [CsvReader](https://github.com/MikeStall/DataTable" target="_blank)
  3. Map csv data to object ids from database
  4. Output SQL statements and error log

To make things fast, I load all the necessary data collections from the database at the beginning of the execution. For this, I use ADO.NET. No micro ORMs or EF/nHibernate. When it comes to pulling data from the database quickly and efficiently, ADO.NET is the tool of choice.

However, there is one thing that annoyed me and that was reading the retrieved datasets using a DataReader. It usually goes like this:

while (reader.Read())
{
    string name = reader["Name"] as string;
    string address = reader["Address"] as string;
//rest of code omitted

This involves reading one record at a time and then mapping and casting each field to the appropriate object property. If only there was a way to abstract and make this process a bit easier...

DynamicDataReader

Enter the DynamicDataReader, a neat utility provided by Rick Stahl. You can find some more information about the utility on his [website](https://weblog.west-wind.com/posts/2011/Dec/06/Creating-a-Dynamic-DataReader-for-easier-Property-Access#GoodNews,BadNews" target="_blank), but if you want to get started quickly, this is all you need to do:

Create a class, name it DynamicDataReader and paste this code:

This class has a dependency to another small utility - ReflectionUtils - which, as the name suggests, does some reflection magic to retrieve values when calling the TryInvokeMember() method. You can find the source code for ReflectionUtils below:

Again, you'll need to create a class in your project and add the code above to allow you to use the DynamicDataReader
With this in place, we can now code our ADO.NET methods to make use of the new DynamicDataReader:

You'll notice on the example above that there are certain benefits using this approach:

  • Uses <object>.PropertyName syntax
  • No more type casting
  • Automatic conversion of DBNull to .NET NULL

There are also a few drawbacks:

  • It relies on dynamic which is available > .NET 4
  • There's no strong typing. If you mess with the property name you'll get an exception
  • Using dynamic means that there's a performance penalty

Overall, this is a neat little utility that allows you to quickly and easily pull data from any DataReader. Thanks to Rick Stahl for making this utility available.