c# - Execute multiple SQL commands in one round trip

Question

I am building an application and I want to batch multiple queries into a single round-trip to the database. For example, lets say a single page needs to display a list of users, a list of groups and a list of permissions.

So I have stored procs (or just simple sql commands like "select * from Users"), and I want to execute three of them. However, to populate this one page I have to make 3 round trips.

Now I could write a single stored proc ("getUsersTeamsAndPermissions") or execute a single SQL command "select * from Users;exec getTeams;select * from Permissions".

But I was wondering if there was a better way to specify to do 3 operations in a single round trip. Benefits include being easier to unit test, and allowing the database engine to parrallelize the queries.

I'm using C# 3.5 and SQL Server 2008.

Answer 1

Something like this. The example is probably not very good as it doesn't properly dispose objects but you get the idea. Here's a cleaned up version:

using (var connection = new SqlConnection(ConnectionString))
using (var command = connection.CreateCommand())
{
    connection.Open();
    command.CommandText = "select id from test1; select id from test2";
    using (var reader = command.ExecuteReader())
    {
        do
        {
            while (reader.Read())
            {
                Console.WriteLine(reader.GetInt32(0));
            }
            Console.WriteLine("--next command--");
        } while (reader.NextResult());

    }
}