Returning multiple tables from a stored procedure

Question

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?

Solution 1

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];

Solution 2

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.