CODEWITHSUNDEEP
c#.netsql-serverwinformsstored-procedures
Nithesh Narayanan
Nithesh Narayanan

Reputation: 11765

Returning multiple tables from a stored procedure

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?

Upvotes: 29

Views: 79306

Answers (2)

balexandre
balexandre

Reputation: 75073

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

Upvotes: 60

c0deNinja
c0deNinja

Reputation: 3986

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.

Upvotes: 1

Related Questions