29

In my winform application I have the following scenario:

I want to get multiple tables on a single event. Returning all tables as dataset in single server cycle, or getting one table at time and using separate server cycle for each table which one is better? What are the advantages one over another?

CMedina
  • 4,034
  • 3
  • 24
  • 39
Nithesh Narayanan
  • 11,481
  • 34
  • 98
  • 138
  • 4
    Getting them all at once reduces number of requests - but you'll need a lot more memory. Getting them only when needed might be better, if there are some tables you don't need very often - only load those when you really need them – marc_s Aug 30 '11 at 07:43
  • 2
    why -1?? who down voted? – Nithesh Narayanan Jan 21 '14 at 13:32
  • This is a design decision that changes depending on the application size, resource availability and business requirement. Both techniques are possible. It is for you to choose one over the other based on the business context. If you want to know how to choose, you should provide more specific information about client environment, DB load, online/offline states, cost of making a DB call, business readiness to fetch data on need basis. – KSK May 14 '18 at 06:06

2 Answers2

58

The normal way is to get all at once.

just construct your SELECT's and you will have a DataSet filled with all tables.

using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(myConnString))
{
    using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
    {
        cmd.CommandText = "myMultipleTablesSP";
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;

        conn.Open();

        System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(cmd);

        DataSet ds = new DataSet();
        adapter.Fill(ds);

        conn.Close();
    }
}

if for example you return 2 tables in your SP, like:

SELECT * FROM [TableA];
SELECT * FROM [TableB];

you would access this tables as:

DataTable tableA = ds.Tables[0];
DataTable tableB = ds.Tables[1];
balexandre
  • 73,608
  • 45
  • 233
  • 342
1

If you load each table separately and use threads you can greatly improve the performance.

Datasets are also very heavy weight... so try avoiding them if possible.

c0deNinja
  • 3,956
  • 1
  • 29
  • 45
  • 2
    this is a windows application, you don't need to avoid DataSets, they exist for a reason! and in a Windows Application you have all the power of the CPU just for your App! – balexandre Aug 30 '11 at 06:33
  • 2
    I agree that DataSets can be very heavy - but the overhead of managing multiple threads to load multiple tables (i.e., one thread per table) could be a headache, and you're also making *x* connections to the db server, rather than one. – Tim Aug 30 '11 at 06:34
  • 6
    @C0deNinja - I don't think balexandre was recommending being inefficient; there's a fine line between efficiency and maintainability when it comes to code. If code is so efficient that it's not easy to maintain, then it's not really efficient (IMO). – Tim Aug 30 '11 at 06:37
  • 1
    @tim i agree... i was just stating one advantage of making separate calls. – c0deNinja Aug 30 '11 at 06:40
  • 1
    @c0deNinja Fell free to read, it's a very old article: http://www.4guysfromrolla.com/articles/050405-1.aspx - **In a desktop, WinForms application.** Consider a desktop-based data entry-type program. A user might fire up the program, load up the sales data from some database server, make some changes, and then want to save those changes. **This is an ideal situation for the DataSet.** It allows the data to be read into a DataSet residing in the client's memory, which affords the user the ability to work on the data without needing to constantly make trips back to the database. – balexandre Aug 30 '11 at 06:45
  • 3
    Do you have a link RE: dataset performance as opposed to alternate collections? I often see that stated but have never seen any figures on it. – Martin Smith Aug 30 '11 at 08:01