How do I return multiple result sets with SqlCommand?

Question

Can I execute multiple queries and return their results executing a SqlCommand just once?

Answer 1

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:

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

Answer 2

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.

Answer 3

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

Answer 4

I am using a dictionary approach. You can use Newton Json to convert it to json. This way you are not tied to types and IDataRecord

public List<List<Dictionary<string, object>>> ExecuteSqlReader(string cmd, int commandTimeout = 30, CommandType commandType = CommandType.Text)
        {
           var sqlCmd = new SqlCommand(cmd);
           var allRecord = new List<List<Dictionary<string, object>>>();
            using (var connection = GetSqlConnection(commandTimeout, commandType, ref sqlCmd))
            {
                using (var reader = sqlCmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        var result = new List<Dictionary<string, object>>();
                        while (reader.Read())
                        {
                            result = GetTableRowData(reader);
                        }

                        allRecord.Add(result);
                    }
                    while (reader.NextResult())
                    {
                        if (reader.HasRows)
                        {
                            var result = new List<Dictionary<string, object>>();
                            while (reader.Read())
                            {
                                result = GetTableRowData(reader);
                            }
                            allRecord.Add(result);
                        }
                    }
                }
            }
            return allRecord;
        }

Answer 5

I call a sproc and get multiple result sets with object, so you end up with a

List<List<Dictionary<string, object>>>

In the multiResultsSet each results set is then

List<Dictionary<string, object>>

They can be cast to their types and transformed into the models as needed.

After you set up the sproc command with everything needed, pass it to this:

    private static List<List<Dictionary<string, object>>> ProcessReader(SqlCommand command)
    {
        var tables = new List<List<Dictionary<string, object>>>();
        using (var reader = command.ExecuteReader())
        {
            do
            {
                var table = new List<Dictionary<string, object>>();
                while (reader.Read())
                    table.Add(Read(reader));
                tables.Add(table);
            } while (reader.NextResult());
        }
        return tables;
    }

and Read() is fairly straight forward.

    private static Dictionary<string, object> Read(IDataRecord reader)
    {
        var row = new Dictionary<string, object>();
        for (var i = 0; i < reader.FieldCount; i++)
        {
            var val = reader[i];
            row[reader.GetName(i)] = val == DBNull.Value ? null : val;
        }
        return row;
    }

Answer 6

This is what i have been using for returning multiple result sets.

public abstract class BaseRepo
{
    private string _connectionString;

    protected BaseRepo(string connectionString)
    {
        _connectionString = connectionString;
    }

    private SqlConnection GetSqlConnection(int commandTimeout, CommandType commandType, ref SqlCommand sqlCmd)
    {
        var connection = new SqlConnection(_connectionString);
        connection.Open();
        sqlCmd.Connection = connection;
        sqlCmd.CommandTimeout = commandTimeout;
        sqlCmd.CommandType = commandType;
        return connection;
    }

    protected int ExecuteSql(SqlCommand sqlCmd, int commandTimeout = 30, CommandType commandType = CommandType.Text)
    {
        using (var connection = GetSqlConnection(commandTimeout, commandType, ref sqlCmd))
        {
            return sqlCmd.ExecuteNonQuery();
        }
    }

    protected IEnumerable<T> ExecuteSqlReader<T>(Func<IDataRecord, T> CreateObject, SqlCommand sqlCmd, int commandTimeout = 30, CommandType commandType = CommandType.Text)
    {
        using (var connection = GetSqlConnection(commandTimeout, commandType, ref sqlCmd))
        {
            using (var reader = sqlCmd.ExecuteReader())
                return ExecuteReader(CreateObject, reader);
        }
    }

    protected Tuple<IEnumerable<T1>, IEnumerable<T2>> ExecuteSqlReader<T1,T2>(Func<IDataRecord, T1> CreateObject1, Func<IDataRecord, T2> CreateObject2,  SqlCommand sqlCmd, int commandTimeout = 30, CommandType commandType = CommandType.Text)
    {
        using (var connection = GetSqlConnection(commandTimeout, commandType, ref sqlCmd))
        {
            using (var reader = sqlCmd.ExecuteReader())
            {
                var result1 = ExecuteReader(CreateObject1, reader).ToList();
                var result2 = ExecuteReader(CreateObject2, reader).ToList();
                return Tuple.Create<IEnumerable<T1>, IEnumerable<T2>>(result1, result2);
            }
        }
    }

    protected Tuple<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>> ExecuteSqlReader<T1, T2, T3>(Func<IDataRecord, T1> CreateObject1, Func<IDataRecord, T2> CreateObject2, Func<IDataRecord, T3> CreateObject3, SqlCommand sqlCmd, int commandTimeout = 30, CommandType commandType = CommandType.Text)
    {
        using (var connection = GetSqlConnection(commandTimeout, commandType, ref sqlCmd))
        {
            using (var reader = sqlCmd.ExecuteReader())
            {
                var result1 = ExecuteReader(CreateObject1, reader).ToList();
                var result2 = ExecuteReader(CreateObject2, reader).ToList();
                var result3 = ExecuteReader(CreateObject3, reader).ToList();
                return Tuple.Create<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>>(result1, result2, result3);
            }
        }
    }

    protected Tuple<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>, IEnumerable<T4>> ExecuteSqlReader<T1, T2, T3, T4>(Func<IDataRecord, T1> CreateObject1, Func<IDataRecord, T2> CreateObject2, Func<IDataRecord, T3> CreateObject3, Func<IDataRecord, T4> CreateObject4, SqlCommand sqlCmd, int commandTimeout = 30, CommandType commandType = CommandType.Text)
    {
        using (var connection = GetSqlConnection(commandTimeout, commandType, ref sqlCmd))
        {
            using (var reader = sqlCmd.ExecuteReader())
            {
                var result1 = ExecuteReader(CreateObject1, reader).ToList();
                var result2 = ExecuteReader(CreateObject2, reader).ToList();
                var result3 = ExecuteReader(CreateObject3, reader).ToList();
                var result4 = ExecuteReader(CreateObject4, reader).ToList();
                return Tuple.Create<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>, IEnumerable<T4>>(result1, result2, result3, result4);
            }
        }
    }

    private IEnumerable<T> ExecuteReader<T>(Func<IDataRecord, T> CreateObject, SqlDataReader reader)
    {
        while (reader.Read())
        {
            yield return CreateObject(reader);
        }
        reader.NextResult();
    }
}

Then I just inherit it like so:

public class ReviewRepo : BaseRepo
{
    public ReviewRepo(string connectionString) : base(connectionString) { }

    public ReviewPageableResult GetAllReviews(string productType, string serviceType, int pageNumber, int itemsPerPage, string sortBy, string sortDirection)
    {
        var parameters = new List<SqlParameter>
        {
            new SqlParameter("ProductRefDescription", productType),
            new SqlParameter("ServiceRefDescription", serviceType),
            new SqlParameter("ZipCodes", "NULL"),
            new SqlParameter("PageNumber", pageNumber),
            new SqlParameter("ItemsPerPage", itemsPerPage),
            new SqlParameter("SortBy", sortBy),
            new SqlParameter("SortDirection", sortDirection)
        };
        var cmd = new SqlCommand("dbo.GetReviews");
        cmd.Parameters.AddRange(parameters.ToArray());
        var results = ExecuteSqlReader(CreateReview, CreateReviewPageableResult, cmd, commandType: CommandType.StoredProcedure);
        var reviewResult = results.Item2.Single();
        reviewResult.Items = results.Item1;
        return reviewResult;
    }

    public ReviewPageableResult GetReviewsByZip(string productType, string serviceType, string zipCodes, int pageNumber, int itemsPerPage, string sortBy, string sortDirection)
    {
        var parameters = new List<SqlParameter>
        {
            new SqlParameter("ProductRefDescription", productType),
            new SqlParameter("ServiceRefDescription", serviceType),
            new SqlParameter("ZipCodes", zipCodes),
            new SqlParameter("PageNumber", pageNumber),
            new SqlParameter("ItemsPerPage", itemsPerPage),
            new SqlParameter("SortBy", sortBy),
            new SqlParameter("SortDirection", sortDirection)
        };
        var cmd = new SqlCommand("dbo.GetReviewsByZipCodes");
        cmd.Parameters.AddRange(parameters.ToArray());
        var results = ExecuteSqlReader(CreateReview, CreateReviewPageableResult, cmd, commandType: CommandType.StoredProcedure);
        var reviewResult = results.Item2.Single();
        reviewResult.Items = results.Item1;
        return reviewResult;
    }

    private Review CreateReview(IDataRecord record)
    {
        return new Review
        {
            PageReviewId = (int)record["PageReviewId"],
            ProductRefId = (Guid)record["ProductRefId"],
            ServiceTypeRefId = Convert.IsDBNull(record["ServiceTypeRefId"]) ? Guid.Empty : (Guid)record["ServiceTypeRefId"],
            TerritoryId = Convert.IsDBNull(record["TerritoryId"]) ? Guid.Empty : (Guid)record["TerritoryId"],
            FirstName = $"{record["FirstName"]}",
            LastName = $"{record["LastName"]}",
            City = $"{record["City"]}",
            State = $"{record["State"]}",
            Answer = $"{record["Answer"]}",
            Rating =(double)record["Rating"],
            SurveyDate = (DateTime)record["SurveyDate"]
        };
    }

    private ReviewPageableResult CreateReviewPageableResult(IDataRecord record)
    {
        return new ReviewPageableResult
        {
            AverageRating = (double)record["AverageRating"],
            Count1Stars = (int)record["Count1Stars"],
            Count2Stars = (int)record["Count2Stars"],
            Count3Stars = (int)record["Count3Stars"],
            Count4Stars = (int)record["Count4Stars"],
            Count5Stars = (int)record["Count5Stars"],
            ItemsPerPage = (int)record["ItemsPerPage"],
            PageNumber = (int)record["PageNumber"],
            TotalCount = (int)record["TotalCount"],
        };
    }
}

Answer 7

Try this

Dim dt1, dt2, dt3As New DataTable
    Dim command As SqlCommand
    Dim adapter As New SqlDataAdapter
    Dim ds As New DataSet
    Dim Sql1, Sql2, Sql3 As String

    Sql1 = "select id, CurName from Table1 where IS_Deleted=0 order by id"
    Sql2 = "select id ,Item from Table2 order by id"
    Sql3 = "select id ,SellPrice from Table3 order by id"

    Try

        conn1.Open()
        command = New SqlCommand(Sql1, conn1)
        command.CommandType = CommandType.Text
        adapter.SelectCommand = command
        adapter.Fill(ds, "dt1")

        adapter.SelectCommand.CommandText = Sql2
        adapter.Fill(ds, "dt2")

        adapter.SelectCommand.CommandText = Sql3
        adapter.Fill(ds, "dt3")


        adapter.Dispose()
        command.Dispose()
        conn1.Close()


        cmbCurrency.DataSource = ds.Tables("dt1") 
        cmbCurrency.DisplayMember = "CurName"
        cmbCurrency.ValueMember = "id"
        cmbCurrency.SelectedIndex = -1 
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''|

        cmbGroups.DataSource = ds.Tables("dt2") 
        cmbGroups.DisplayMember = "Item"                                    
        cmbGroups.ValueMember = "id"                                        
        cmbGroups.SelectedIndex = -1                                        
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''|

        cmbUnits.DataSource = ds.Tables("dt3")  
        cmbUnits.DisplayMember = "SellPrice" 
        cmbUnits.ValueMember = "id" 
        cmbUnits.SelectedIndex = -1                                         
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''|

    Catch ex As Exception
       MessageBox.Show(ex.ToString())
    End Try

For More Help http://vb.net-informations.com/dataset/dataset-multiple-tables-sqlserver.htm

Answer 8

Use QueryMultiple of IDbConnection Interface -

 using (var connection = OpenConnection())
            {
                var result = connection.QueryMultiple("sqlStoredProcedure",new
                {
                    Id = item.Id,
                   
                }, commandTimeout: 3600
                , commandType: CommandType.StoredProcedure);

                res1 = result.Read<T>().ToList();
                res2 = result.Read<T>().ToList();
            }