How do I return multiple result sets with SqlCommand?

Question

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

Multiple SELECT queries? Or inserts/updates? if queries are SELECTs, do they return data having the same layout? Please, this question is too vague. — mbarthelemy
– mbarthelemy, Commented Oct 3, 2012 at 19:40
Multiple SELECT queries, all having the same layout. I can't use UNION because I have some T-SQL logic that prevents me from doing this. — Doug
– Doug, Commented Oct 3, 2012 at 23:01

Bernhard · Accepted Answer · 2019-05-29 11:44:50Z

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:

Dave Zych · Accepted Answer · 2012-10-03 19:40:32Z

59

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.

answered Oct 3, 2012 at 19:40

Dave Zych

22k7 gold badges53 silver badges67 bronze badges

3 Comments

user166390 Over a year ago

+1 Because this is the "easier way"; however, it doesn't have to be a Stored Procedure - multiple statements in batch should work just fine IIRC.

Dave Zych Over a year ago

I believe you're right, I just couldn't remember. I know it works for a stored procedure though.

Zeek2 Over a year ago

Looking at the dataset-equivalent variable for me, in DNSPY debugger, I see only one entry in the "Table"-array. Yet in my test script I see 2 returned by sproc; unfortunately I need (only) the second one :(

Marc Gravell · Accepted Answer · 2017-06-19 09:49:11Z

13

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

answered Jun 19, 2017 at 9:49

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Comments

Nikolai Shevchenko · Accepted Answer · 2020-01-12 18:47:21Z

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

Stephen Himes · Accepted Answer · 2018-08-24 12:58:00Z

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

Pete Koelbl · Accepted Answer · 2018-04-30 17:50:12Z

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"],
        };
    }
}

I can see lots of code copied from some proprietary solution that eventually comes to the idea of user166390
Never said I wrote it. It is an amalgamation of things I have seen plus a little of my stuff to get it to all work together.

Mohmd Nader · Accepted Answer · 2019-09-27 16:22:14Z

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

Navjyot · Accepted Answer · 2023-06-09 06:52:12Z

0

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();
            }

answered Jun 9, 2023 at 6:52

Navjyot

11 bronze badge

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

How do I return multiple result sets with SqlCommand?

8 Answers 8

Comments

3 Comments

Comments

Comments

Comments

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

Comments

3 Comments

Comments

Comments

Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related