How do I return multiple result sets with SqlCommand?

See SqlDataReader.NextResult (an SqlDataReader is returned from calling SqlCommand.ExecuteReader):

Advances the data reader to the next result [set], when reading the results of batch Transact-SQL statements.

Example:

string commandText = @"SELECT Id, ContactIdFROM dbo.Subscriptions;SELECT Id, [Name]FROM dbo.Contacts;";List<Subscription> subscriptions = new List<Subscription>();List<Contact> contacts = new List<Contact>();using (SqlConnection dbConnection = new SqlConnection(@"Data Source=server;Database=database;Integrated Security=true;")){    dbConnection.Open();    using (SqlCommand dbCommand = dbConnection.CreateCommand())    {        dbCommand.CommandText = commandText;        using(SqlDataReader reader = dbCommand.ExecuteReader())        {            while(reader.Read())            {                subscriptions.Add(new Subscription()                {                    Id = (int)reader["Id"],                    ContactId = (int)reader["ContactId"]                });            }            // this advances to the next resultset             reader.NextResult();            while(reader.Read())            {                contacts.Add(new Contact()                {                    Id = (int)reader["Id"],                    Name = (string)reader["Name"]                });            }        }    }}

Other examples:

• C# Multiple Result Sets
• Executing a Query That Returns Multiple Result Sets with SqlDataReader : SqlCommand Select « ADO.Net « C# / CSharp Tutorial

Create a Stored Procedure that has multiple selects, and fill the DataSet.

using (SqlConnection conn = new SqlConnection(connection)){    DataSet dataset = new DataSet();    SqlDataAdapter adapter = new SqlDataAdapter();    adapter.SelectCommand = new SqlCommand("MyProcedure", conn);    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;    adapter.Fill(dataset);    return dataset;}

The returned dataset will have a DataTable in it's Tables array for each select in the stored procedure.

Tools like "dapper" make this very easy, regardless of whether you use ad-hoc text queries or stored procedures; for example:

using(var multi = conn.QueryMultiple(sql, args)){    var customers = multi.Read<Customer>().AsList(); // first grid    var regionName = multi.ReadFirstOrDefault<string>(); // second grid    var addresses = multi.Read<Address>().AsList(); // third grid    // todo: use those things}

Individual grids can also be read without buffering (as an open IEnumerable<T> over the reader itself) via the optional parameters to Read[<T>].