Is it possible to return multiple resultsets from a stored procedure?

Question

Is it possible to return multiple resultsets from a stored procedure?

c# dapper dataset sql-server sql-server-2016

Question

Currently I have a stored procedure that returns an XML based on 2 tables. I then import the XML into a DataSet:

var xml = conn.QueryFirst<string>("myproc", new { id }, commandType: CommandType.StoredProcedure);

using (var sr = new StringReader(xml))
{
    var ds = new DataSet();
    ds.ReadXml(sr);

    return ds;
}

The problem with this approach is that I need to convert columns into the correct data types and this is not working as expected.

Instead of this extra step, is it possible to the return the results of the stored procedure into the DataSet directly? Obviously the stored procedure would then be changed accordingly so as to return the resultsets instead of XML.

EDIT

Dapper has QueryMultiple but how can I return each result into a table. Do I need an extra step to get then results as dynamic and then create a DataTable for each result?

Ivan-Mark Debono

View more on Stack Overflow

Dan Guzman · Accepted Answer · 1/27/2019 4:04:42 PM

You could use SqlDataAdapter.Fill to load a DataSet with multiple result sets, containing a table for each result returned by the stored procedure:

using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand("myproc", connection) {  CommandType = CommandType.StoredProcedure })
using (var dataAdapter = new SqlDataAdapter(command))
{
    command.Parameters.Add("@id", SqlDbType.Int).Value = id;
    dataAdapter.Fill(dataSet);
}

Is it possible to return multiple resultsets from a stored procedure?

Question

Fastest Dapper Plus Extensions

Accepted Answer

Performance

Expression Evaluator