DataSet or DataReader?

I’ve been developing websites in ASP.NET for quite a while now. During my initial projects in ASP.NET I like many others liberally used DataSets whenever possible and hardly went for DataReaders. Now the reason I didn’t prefer DataReaders was because i often forget to close it’s connection object and then I receive a “all pooled connections were in use and max pool size was reached…” error from the SQL Server after some trial runs. Silly reason uh! Now, I know why the error occured but the lazy me wanted a option where I don’t have to worry about these issues.

Then one fine day I felt an invisible slap on my head and I began to seriously consider best practices, performance, robust architecure etc.. seriously. I guess my old programming habits in ASP were reluctant to let me go. But something unusual happened, I won!

Now back to the real issue. DataSets are really powerful and easy to use. But when it comes to developing performant websites, DataSets should never be an option. DataReaders are fast and I mean really fast when compared to a DataSet or for that matter a DataTable. Eventhough DataReaders are fast, there was one fact which worried me. DataReaders need a open connection object while retrieving data. Now would it be a problem if I try to read a large number of records, say more than 10,000 using a DataReader as it needs the open connection to execute? Well, yes if you don’t close the connection object as soon as you are done with it.

I came across this link A Speed Freak’s Guide to Retrieving Data in ADO.NET by Craig Davis and it clearly explains the advantage of using DataReader over DataSet or DataTable. But are DataSets a complete no no when it comes to ASP.NET? Well not really. If you have checked the IBuySpy portal’s architecure, the use of strongly-typed DataSet makes a lot of sense. Since the data is cached most of the time and rarely updated, the performance issue is not really a huge threat. More over it is more easier to handle the large XML file that defines the portals structure using the strongly-typed DataSet.

So does that mean that as long as you cache the DataSet it is fine to use them? Well I would still go for DataReaders. You cannot cache a DataReader object but you can cache the data you receive from DataReader. We can create a class with public properties that mirrors the table structure, iterate  through the records in the DataReader, populate the relevant data to the object instance of the custom class and then add it preferably to a strongly-type collection or an ArrayList, which can be cached. Obviously we need to enter few extra lines of code to do this but its worth the effort as we have performance in mind.

DataReaders should therefore be the first choice when it comes to data access in ASP.NET. DataSets could be used if you don’t worry about the performance factor or if there is situation where DataSet is the only option, which in my opinion is very unlikely.

Technorati: asp.net, C#, web development