Converting ADO.NET DataReader to a List: Possible Base for Cross-platform Data Access

I’ve been experimenting with different .NET ORM options recently, including mapping ADO.NET DataReader rows directly to strongly typed objects. I came across a Stackoverflow post by Jon Skeet that proved really useful, so I thought I would present it here in more detail.

I have a local instance of Sql Server that contains database objects for a publisher, including a stored procedure that returns book details. I started by creating an ORM class to hold the results of the stored procedure. I’ve also added a static method (Select) that accepts an IDataReader argument and maps the reader row values to my BookDetails class.

Click for larger view.

I then create an extension method that loops through the DataReader rows and projects the values into an object, using the Func (T, TResult) delegate.

Click for larger view.

Finally I tie it together in my method GetBookDetails(). This uses standard ADO.NET to call the “spGetBooks” stored procedure and return the results to a SqlDataReader. I then use the Select extension method to map the SqlDataReader rows to a List<BookDetails>. Note that the FromDataReader method is passed in as an argument in the Select method.

Click for larger view.

This approach is pretty simple. However, it can be used to drive a more legitimate DAL, in particular one that reaches out to different kinds of database servers. I have an upcoming integration project that requires accessing data from SQL Server as well as IBM Informix. It appears that Informix is compatible with Entity Framework,  but it also seems like one of those things that could be troublesome and buggy.

The IDataReader interface used in this approach will accept both SqlDataReader and OdbcDataReader (and I’m certain that Informix accepts ODBC). To that point, it might be interesting to leverage both IDataReader and the abstract class DbDataReader, as all of the different readers roll up to it. It could be the basis for a relatively painless cross-platform ORM.

As an aside, I would recommend Skeet’s C# in Depth. It’s a great read for an intermediate C# developer, and it’s proven to be a good reference when trying to write more elegant code, if you have the time for such indulgences.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s