DataReader to DataTable Happy Ending
I'm doing some .Net and MySQL work and I've got a data access layer that does all the heavy SQL lifting. One of the needs that came up was to convert a DataReader (IDataReader, really) to a DataTable. In good XP style, I wrote “the quickest implementation that solves the business need” for creating a DataTable from an IDataReader. This involved looping through the fields and was a bit cumbersome -- but it worked. Again, in good XP style [Darrell Norton, correct me if I'm wrong], I went back and refactored the code after passing all my unit tests. This time it included digging through documentation and weblogs on the DataReader-to-DataTable issue because I didn't like all the looping and verbosity of the code. Rather quickly, this post by Roy Osherove turned up.
Roy was tackling the same issue and, in the comments to Roy's post, Josh (last name and additional info was not posted) pointed out the base DBDataAdapter class includes an overload of the Fill() method that includes a DataTable and a DataReader. The OdbcDataAdapter (and all the provider specific adapters I checked out -- not even for SQL Server) don't include this overload -- but if you create your own class deriving from DBDataAdapter directly, you're all set! No more loops and explicit DataColumns to create -- the .Net Framework has a solution baked in; the trick was finding the base DBDataAdapter to begin with.
My custom class required implementations to the 4 abstract methods in DBDataAdapter (CreateRowUpdatedEvent, CreateRowUpdatingEvent, OnRowUpdating,OnRowUpdated) -- but that only took a few minutes. Add another minute or two to run the unit tests with happy green results, and I was all set. I'd developed a much leaner solution.
Without the blog-o-sphere this would've been a tougher task; I would've been stuck weeding through the documentation to find a solution (or checking out books or online articles for the tidbit I needed). Granted, maybe it would've occurred to more of you to check for base DataAdapter classes to see if they expose a richer set of Fill() method overloads, but that thought didn't occur to me. To be certain, for future data access challenges, I'll be sure to keep the base data access classes in mind.
Happy .Netting!