Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

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.

Print | posted on Friday, February 22, 2008 12:30 PM | Filed Under [ .NET (C# / VB) ]

Feedback

Gravatar

# 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.
4/5/2010 11:31 AM | Bernard Ho-Jim
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET