Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

DataTable, DataView and CreateDataReader

Here's something I was not aware of in .NET 2.0+ that I recently discovered.

There is a new DataTableReader class that implements IDataReader so that you can loop through an in-memory DataTable using the same interface that you would to loop through a SqlDataReader.  You can create this object by using the CreateDataReader() method of a DataTable.  This is great because now you can create a generic ReadFromDatabase() method for your classes that accepts an IDataReader parameter and you can create those objects from pretty much any data source, including DataTables, which wasn't as easy to do previously.

There is one catch: There is no CreateDataReader() method for the DataView class, meaning if you want to use a DataTableReader to loop through a DataTable that is sorted or filtered, you are out of luck.  This was pretty disappointing – in fact, I would think that CreateDataReader(), if only implemented in one place, makes more sense to implement in the DataView class rather than the DataTable class, since you would then get the best of both worlds.

The way to solve this is that you unfortunately need create another DataTable that contains the data loaded from your sorted and/or filtered DataView.  Luckily, there is a ToTable() method in the DataView class that allows you to do this.

Here's an example of creating a DataTableReader that enumerates a DataView:

DataTable t = … some data table here …
t.DefaultView.Sort = … your sort here …
t.DefaultView.RowFilter = … your filter here …

DataTableReader r = t.DefaultView.ToTable().CreateDataReader();

Not ideal, but it works.  The benefit of being able to use clean, generic code that accepts IDataReader objects and therefore allows you to use very efficient SqlDataReaders whenever possible makes it worth it.

Update: I have created a simple DataViewReader class here if you need this functionality. Using this will be much more efficient than creating a brand new DataTable if you would like to create an IDataReader on a DataView.

Legacy Comments

Bernard Ho-Jim
re: DataTable, DataView and CreateDataReader
This is exactly what I was looking for. I couldn't figure out how to convert a datatable to datatablereader until I find your article. I needed to unit test a method that gets a datareader and returns a generic list. Thanks a lot.