Dataset or Datareader or Other? RRS feed

  • Question

  • I’m new at .NET but have been programming for a while.
    I am rebuilding an application in .NET using ADO.NET. I have a few questions.

    The DB for the app for one client will be in Access the other in SQL Server. Let’s keep this conversation to access for now. The App will be migrated to ASP.NET in a little while so I want to take full advantage of the disconnected nature of ADO.NET

    First, I have a large database table that’s around 200,000 records that the user will be querying and filtering. The end user will be seeing subsets of this table in a datagridview for the main part of the app. The app has other functionality but it is all driven from the subset in the datagrid

    So… the best way to do this..?

    First, I thought I would just do a dataset and let the user filter the binding source but the .fill() takes forever. Also, the limitation of not being able to querying the dataset with SQL is a pain because I may need that capability. I looked into LINQ to Dataset but it looks pretty taxing to learn so I would like to avoid it.. I’m still learning .NET.

    Second, I though of using a datareader where I can use SQL statements and feed it into a datatable then bind to datagridview. The problem is I saw no speed increase from the dataset. Because I am going to go to ASP.NET in the future it seems that the datareader is the way to go? I guess what frustrates me about the datareader is the inability to loop through it and do meaningful things like you could with good old ADO.

    ?Do I need to load all 200000 records? No, not really only as a result to the users query or filter.

    I guess these are the only two options I have been able to think of. I know there are many ways to do things in .NET so I thought I would ask and maybe it would save me some pain down the road.

    Monday, June 29, 2009 10:33 PM


  • You can do one of two things with the fill if its large, you can only query some of it at a time, paging essentially, or you can load the data async using a BackGroundWorker, or a third way would be to use different select queries for specific user actions you want to load. For example perhaps ther form looks at who is logged in and only queries back what is needed for that person.

    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, June 30, 2009 8:04 PM