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