Doug Reilly's Weblog

Embedded Reporting of the Information Age...

DataSet or DataReader: Best Practices?

I started out using .NET and ADO.NET using almost exclusively DataReaders for reading data, and Stored Procedures for inserting/updating data.  In general, I have found this to be scalable and entirely reasonable performance wise.

I have recently started using DataSets on occasion.  My criteria for use is either that the content of the resulting dataset is reasonably static and can be cached, or if it needs to be used several times in a single request (for instance, a DataSet to be bound to several different Drop Down Lists or a DataSet where I will use a number of different views to feed a User Control).  So, I use DataReaders in many cases, because much of the data I am using is not terribly static, and often something that is very user specific.  Are people out there doing session-based caching of datasets?

I have started using IDataReader as the return value from any of my utility classes when returning a DataReader is required rather than returning a SqlDataReader.  This in theory isolates the SQL Server specific stuff in my Data Access Layer, and I could change databases more reasonably (though in practice, it seems very unlikely any current projects would actually change databases).  In addition, none of my applications are likely to need to place the Data Access Layer on a different physical machine, so the disconnectedness of a DataSet is not likely to be required.

Several recent books I have read (Pragmatic ADO.NET, for instance) seem to minimize the utility of the Data Reader.  Other than the awkwardness of needing to verify that ExecuteReader() is called with the correct parameter and that I verify that I close the returned DataReader (and I am comfortable that I have a pattern in place that works reliably), am I missing something? 

Posted: Sep 27 2003, 12:11 PM by douglas.reilly | with 5 comment(s)
Filed under: ,

Comments

denny said:

sounds a lot like what I do....

if I need to hold say 50 rows for a few steps I do a f() that empties the reader into a simple datatable and return just a table.

that way I can For Each ..... on the table and not hold the reader's connection open.

and I am using "SignleRow" when the reader is only getting one row.... not sure what it does inside but they have that param for a reason I hope?
# September 27, 2003 1:38 PM

Paul Wilson said:

I pretty much do the same thing you described with a custom dal that's easy to use and returns IDataReaders also, occasionally using DataSets instead when useful. I always make sure I open my datareaders in a try block, where the finally clause guarantees that its closed, and this has worked exceptionally well.

Until yesterday! I've recently started using ActiveReports, which supports data-binding against the datareaders, but unlike other situations where I use data-binding there is no explicit DataBind method to call. Instead, you just make sure you open your datareader in the reportstart method and trust everything works, which usually does just fine I might add.

The problem occurred in one report that has a sub-report, effectively opening more datareaders, possibly hundreds of them, all within a fraction of a second. I mistakenly placed my datareader's close method in the reportdispose method instead of the reportend method, which worked well in isolated testing. Oops, in beta testing I maxed out the number of pooled connections pretty quickly and had to figure out why.

It was easy to fix, once I realized what was going on, and it was my fault, not anything related to ActiveReports. But it did make me think about the datareader vs. dataset issue a little again. There's no doubt that the datareader is better performing and lower overhead, but it does expose you to higher risk if you ever deviate from best practices.
# September 27, 2003 3:13 PM

Scott said:

I try to use DataTables over DataSets, when possible.
# September 27, 2003 8:54 PM

JonR said:

helpful post. i've always used dataReaders, sprocs, custom objects and collections, but felt that by not using datasets, i was somehow "not getting" the "asp.net way of doing things".

so i'm experimenting with typed datasets and dataadapters at the moment.....i'm still not convinced by it.
# June 4, 2004 9:09 AM

Kailash Rana said:

I use datareader more often as it consume very less resources of the server but if i got a situation where i need to make lots of processing on the resultset then i use datasets as it runs in disconnected envionment and gives more functionality as compare to datareader
# July 5, 2004 3:44 AM
Leave a Comment

(required) 

(required) 

(optional)

(required)