How do I return multiple result sets with SqlCommand?

by

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, ContactId
FROM 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:

More Related Contents:

  1. ExecuteReader requires an open and available Connection. The connection’s current state is Connecting
  2. Call a stored procedure with parameter in c#
  3. Insert 2 million rows into SQL Server quickly
  4. Calling stored procedure with return value
  5. SQL command INSERT is working but the data not appear in table
  6. Return value from SQL Server Insert command using c#
  7. How to pass sqlparameter to IN()? [duplicate]
  8. SqlConnection SqlCommand SqlDataReader IDisposable
  9. SqlBulkCopy from a List
  10. Use of SqlParameter in SQL LIKE clause not working
  11. How to run multiple SQL commands in a single SQL connection?
  12. Connection timeout for SQL server
  13. MultipleActiveResultSets=True or multiple connections?
  14. WHERE IN (array of IDs)
  15. How can I retrieve a list of parameters from a stored procedure in SQL Server
  16. Creating a SQL Server table from a C# datatable
  17. SQL: Update a row and returning a column value with 1 query
  18. How to keep single SQL Server connection instance open for multiple request in C#?
  19. How to use SqlCommand to CREATE DATABASE with parameterized db name?
  20. ExecuteNonQuery returning -1 when using sql COUNT despite the query string
  21. Using SqlDataAdapter to insert a row
  22. What size do you use for varchar(MAX) in your parameter declaration?
  23. Add WHERE clauses to SQL dynamically / programmatically
  24. Unable to connect to localDB in VS2012 – “A network-related or instance-specific error occurred while establishing a connection to SQL Server…”
  25. Fastest method for SQL Server inserts, updates, selects
  26. Why does the SqlParameter name/value constructor treat 0 as null?
  27. How do I connect to a SQL database from C#?
  28. How to get efficient Sql Server deadlock handling in C# with ADO?
  29. Passing array to a SQL Server Stored Procedure
  30. How to give ADO.NET Parameters

Leave a Comment