dataSet Vs DataReader RRS feed

  • Question

  • Hi All,

    I have one question regarding implementing data reader and dataset in my project.

    My project is a windows application with the database in a separate server.

    I have numerous records to be retrieved and processed.My application has many master data tables and has many transaction tables too.

    Is it a good approach to frequently hit database server to get the data using datareader or to get the data using dataset with minimal round trips.

    Which is good approach ?

    Please advice me on this.

    Thanks in advance.

    Friday, October 13, 2006 3:26 PM

All replies

  • Hi!

    In my opinion you should use DataReaser. Since you have numerous number of records you might not be able to hold all of them in the DataSet. Remeber DataSet holds all information in the memory.

    Also, there are some ways to optimize data retrival using DataReader.

    Hope it helps,
    Friday, October 13, 2006 8:58 PM
  • Dear friend,

       I hope you find useful guidance at section "DataSet vs DataReader", chapter 12, "Improving ADO.NET Performance", Patterns and Practices book "Improving .NET Application Performance and Scalabiltity". It's based on .NET 1.1, actually. Not the 2.0 version but it seems to me that the aims and means didn't changed (at least, not notoriously)

       Also you have some more guidance at a section with the same name in "Best Practices for Using ADO.NET" (it's 101 -basic-, and maybe your context has more constraints to take into account)

       As some other folk suggested in this thread, with the info you told us it seems that the DataReader approach is the most suitable to apply. Question: once the records are read, don't you save them back after processing? You don't mention so, but if that is the case, the DataReader is useless in such scenario, and DataSet shares go up


    Hope it helps. More architecture guidance on data access techniques at the MSDN Solution Architecture Center

    Saturday, October 14, 2006 3:05 PM
  • Hi karun,


    According to Diego, you must first go for think about what you need ?

    Saving or not.

    According to that you can use datareader or dataset.

    Nice post.


    Thanks to MSDN



    Monday, October 16, 2006 4:09 AM
  • I always go with a DataSet instead of a datareader that way I can close off the connection immediately after retrieving the data
    Thursday, October 19, 2006 6:39 AM
  • How numerous is numerous? .NET 2.0 Datasets are designed to handle a large amount of records. You also don't have to load all the data at once. You can have one dataset in memory that fills up incrementally as more data is added to it. You can also use a hybrid approach for example load your master tables into memory ahead of time, and then incrementally load your transactional data as it is needed. One of the nice features of DataSets is the merge method.

    This method allows you to fetch a subset of data and then merge it back into the main DataSet. Merge supports relationships, etc. So you could fill a DataSet with multiple child tables for example if your record is a PO that has PO Lineitems, you can create a DataSet with PO and LineItem tables, then go and fill the PO along with it's LineItems and then merge that DataSet back into your transactional data.

    You might also consider even having 2 DataSets, 1 for your master data and 1 for your transactional data.   This way you can clean your cached transactional data more frequently then your master data which changes less often.


    Thursday, October 19, 2006 9:49 PM
  • I prefer DataReader unless I plan to manipulate various values in the results and then submit the changes back to the DB and I don't want to manage that state on my own.  If you are retrieving data that you then serialize to your own format (like a specific XML shape) or types you've declared, then I think DataReaders are the way to go.  If you are populating an XML stream with child nodes, you can also use the Multiple Active Result Sets to do a forward-only read / forward-only Infoset population with a single connection.

    An untyped Dataset will populate faster than some type of your own where you pull individual values in from a DataReader because the internal DataTable structures read the data stream very quickly.  The "hugeness" of the data is something you have to manage whichever mechanism you pick -- I don't think there is a connection between results size and which client API to pick.

    Thursday, October 19, 2006 10:53 PM
  • I agree that it depends on the scenario. Certainly if the data is being directly serialized into a different format then using datasets makes no sense. Also if you are just using the data for a moment in time and thre is no point in keeping it around, then datasets make no sense. If however you are not retrieving large amounts of data, and that data needs to be in a dataset anyway then using datasets is not really a problem. If there is a large amt of data that you are retrieving then loading directly into datasets can have a signficant perf hit, especially if you have relationshops in your data. If you do manually load substantial data into datasets by using a datareader, make sure that you call the BeingLoadData method on each table when you start loading data. Then make sure to call EndLoadData when you are done.

    Friday, October 20, 2006 1:00 AM
  • Also, if the data is going to handled by a user, for editing for example, you should think about more basic stuff, like how is the user going to like it I throw, say 250 records at him? Changes are your users won't like it. I've build a time registration based on typed datasets and limited the records that a user retrieves to the week that is requested. This way performance is almost instant.

    Now if you're building an application that just has to show some data and you can load the data in a single operation, then you should choose the datareader. Be aware that this is a forward only datastream that can only read with the connection open. As long as you're reading from the reader your connection is open. Think about the number of open connections that can occur and make shure that the DB can handle them.

    Friday, October 20, 2006 6:05 AM
  • Hi,

         As ur requirement says that u have to hit the db for more number of times so, i suggest u to u se dataset as u have planty of advantages when compared with data reader like...

             ->Dataset supports

    Bi-Directional Navigation

    supporst relation between tables

    puerly disconnected

    Xml Storage Internally









    Tuesday, December 28, 2010 8:28 AM