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

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?

Have you considered deserializing the xml to a Type you define? You can return multiple result sets but most likely `XmlSerializer` is going to require the fewest changes. — Crowcoder, Jan 27 '19 at 16:00
Why do you even use DataSets? Can't you use a real concrete type instead? — DavidG, Jan 27 '19 at 16:06
This question is already answered please refer to this post https://stackoverflow.com/questions/40013747/return-multiple-datasets-from-sql-server-stored-procedure — agsk, Jan 27 '19 at 17:11

score 1 · Accepted Answer · answered Jan 27 '19 at 16:04

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?

1 Answers1