Best Practices with Large DataSet in C#

Question

Currently I'm working in desing and implement a software who has to implement CRUD operations over two tables with master-detail arquitecture. Header has about half million of rows and detail about million of rows.

Fill all this data in a dataset is crazy, also data can change and I'm not interested in have a local copy of database. I'm interested in that software works fluently. Although dataset may be not the best solution, I should use this to be consistent with other software parts.

First I think to use a TypedDataset and some methos like GetNext() , GetFirst() , GetByCod() but I'm not sure if is the best solution.... I'm doing a little test and don't work very fluently.

I'm interested in know how other developers do this , Best practices and what's "the best choice" to do operations with large data.

I'm using Visual Studio 2008 and Sql Server 2005.

ADDED: When you talk about of using SqlDataReader you're referring something like this ?

using (SqlConnection con = new SqlConnection(CON)) {

            con.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM TABLE");
            cmd.Connection = con;
            SqlDataReader rd = cmd.ExecuteReader();
            BindingSource bindingSource = new BindingSource();
            bindingSource.DataSource = rd;
            bindingNavigator1.BindingSource = bindingSource;

            txtFCOD.DataBindings.Add("Text", bindingSource, "FIELD");
        }

Answer 1

I think there is no way to manage so big dataset.

You need DataReader, not DataSet.

Local copy of database with really big amount of data is effective way to reach something like this (fast response from your app), but you will have problems with synchronization (replication), concurrency etc..

Best practice is getting from server only that data which user really need. You have to use server-side processing, by stored procedures etc.

I still dont know what data you want to manipulate and what is the purpose of your app, but there is another disadvantage of big amounts of data on client side - your app will need a lot of ram and fast CPU. Maybe your computer is fast and capable of handling this, but consider what happens when somebody install your app on tablet with 1GHz Atom CPU. That will be disaster.

Answer 2

There should rarely be a scenario where you need to retrieve all the data at once.

You could consider the following:

• Use Views for serving specific smaller sets of data.
• Consider using paging using the OVER() functionality introduced in SQL Server 2005
• Do not use datasets for large amounts of data. DataReaders are much more efficient in this case.

Personally I think you should avoid loading large amounts of data into memory unless you have complete control over how much is being loaded and when it is being disposed. Remember, if handling data server-side, you are using the resources that other process may need.

You should always try and work with smaller chunks at a time, and preferably for as short a time as possible. This prevents your process from hogging any resources for long periods of time.