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

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 2

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