DataReader or DataSet when pulling multiple recordsets in ASP.NET

Question

I've got an ASP.NET page that has a bunch of controls that need to be populated (e.g. dropdown lists).

I'd like to make a single trip to the db and bring back multiple recordsets instead of making a round-trip for each control.

I could bring back multiple tables in a DataSet, or I could bring back a DataReader and use '.NextResult' to put each result set into a custom business class.

Will I likely see a big enough performance advantage using the DataReader approach, or should I just use the DataSet approach?

Any examples of how you usually handle this would be appreciated.

Answer 1

I have gone to a method that uses DataReaders for all calls, I have noticed a marked performance impovement, especially in cases when I am loading drop down lists, and other simple items like that.

Personally with multiple drop downs, I typically go to pullling individual chunks of data to get it, rather than say a stored procedure that returns 5 result sets.

Answer 2

Always put your data into classes defined for the specific usage. Don't pass DataSets or DataReaders around.

Answer 3

If you are not interested in updating or deleting the records you fetched from database, I would suggest using DataReader. Basically DataSet internally uses multiple Datareaders, so DataReader should give you good performance advantage.

Answer 4

Map the DataReader to intermediate objects and then bind your controls using those objects. It can be ok to use DataSets in certain circumstances, but those are few and far between when you have strong reasons for "just getting data". Whatever you do, don't pass a DataReader to your controls to bind off of (not that you said that you were considering that).

My personal preference would be to use an ORM, but if you are going to hand roll your data access, by all means I think you should prefer mapping DataReaders to objects over using DataSets. Using the .NextResult as a way to limit yourself from hitting the database multiple times is a double edged sword however so choose wisely. You will find yourself repeating yourself if you try to create procs that always grab exactly what you need using only one call to the database. If your application is only a few pages, it is probably OK, but things can get out of control quickly. Personally I'd rather have one proc per object type and then hit the database multiple times (once for each object type) in order to maximize maintainability. This is where an ORM shines because a good one will generate Sql that will get you exactly what you want with one call in most cases.

Answer 5

In almost every situation DataReaders are the best solution for reading from a database. DataReaders are faster and require less memory than DataTables or DataSets.

Also, DataSets can often lead to situations in which the OO model is broken. It's not very object oriented to be passing around relational data/schemata instead of objects that know how to manipulate that data.

So, for extensibility, scalability, modularity, and performance reasons, always use DataReaders if you consider yourself a Real Programmer™ ;)

Check the links for facts and discussion about the two in practice and theory.

Answer 6

Take a look into the TableAdapters that are available with .NET 2.0 and up. What they do is give you the strength of a strongly-typed DataTable and allow you to map a Fill method to it that will use a DataReader to load it up. Your fill method can be existing stored procedures, your own AdHoc SQL, or even let the wizard generate the AdHod or Stored Procedure for you.

You can find this by starting up a new XSD DataSet object within your project. For tables that are used for more than just lookup, you can also map insert/update/delete methods to the TableAdapter.

Answer 7

If your stored proc returns multiple sets, use the DataReader.NextResult to advance to the next chunk of data. This way you can get all your data, load it to your objects, and close the reader as soon as possible. This will be the fastest method to get your data.

Answer 8

1. If you have more than 1000 record to bring from your DataBase.
2. If you are not very interested with custom storing and custom paging "For GridView"
3. If your server have a memory stress.
4. If there is no problem to connect to your DataBase every time that page called.

Then i think the better is to use DataReader.

else

1. If you have less than 1000 record to bring from your DataBase.
2. If you are interested with storing and paging "For GridView"
3. If your server haven't a memory stress.
4. If you want to connect to your DataBase just one time and get the benefits of Caching.

Then i think the better is to use DataSet.

I hop that i'm right.

Answer 9

Irrespective of whether you're fetching a single result-set or multiple result-sets, the consensus seems to be to use a DataReader instead of a DataSet.

In regards to whether you should ever bother with multiple result-sets, the wisdom is that you shouldn't, but I can conceive of a reasonable class of exceptions to that rule: (tightly) related result-sets. You certainly don't want to add a query to return the same set of choices for a drop-down list that's repeated on hundreds or even dozens of pages in your app, but several sets narrowly-used may be sensibly combined. As an example, I'm currently creating a page to display several sets of 'discrepancies' for a batch of ETL data. None of those queries are likely to be used elsewhere, so it would be convenient to encapsulate them as a single 'get discrepancies' sproc. On the other hand, the better performance of doing so may be insignificant compared to working-around the natural one-sproc-one-result-set architecture of your ORM or hand-rolled data-access code.