Returning Multiple Result Sets from a SQL Stored Procedure for use in a C# Application.

With much frustration and trial and error I have recently discovered that I can not return all results of a multi-result set stored procedure using the generated fill method of a dataset. It appears that typed datasets are only allowed to return a single result set as per the dataset architecture. However, thankfully there is a workaround, if the dataset is a non-typed dataset then it is possible to access the result sets by means of the DataSet.Tables collection.

If you already have a stored procedure which returns multiple result sets then here is the code for accessing each result set that the dataset contains.

Code:

using System.Data;
using System.Data.SqlClient;

public DataSet GetDataFor(int param1, int param2)
{
      SqlConnection sqlConnection;
      dataSet = new DataSet();
      SqlDataAdapter sqlDataAdapter;

      sqlConnection = new SqlConnection(Properties.Settings.Default.AEORIONSMSConnectionString);
      sqlConnection.Open();

      sqlDataAdapter = new SqlDataAdapter([Replace with your sp name], sqlConnection);
      sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

      sqlDataAdapter.SelectCommand.Parameters.AddWithValue("@param1", param1);
      sqlDataAdapter.SelectCommand.Parameters.AddWithValue("@param2", param2);

      sqlDataAdapter.Fill(dataSet);
      sqlDataAdapter.Dispose();

      return dataSet;
}

Using the returned DataSet:

void GetColumns()
{
     var dataSet = GetDataFor(1, 3);

     string[] columnNames1 = dataSet.Tables[0].Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray();
     string[] columnNames2 = dataSet.Tables[1].Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray();
     string[] columnNames3 = dataSet.Tables[2].Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray();
}
Advertisements