How to: Execute Stored Procedures Returning Multiple Result Sets

This sample demonstrates how to execute stored procedures that return multiple result sets and materialize entities from each result set. The first sample demonstrates how to execute a simple stored procedure returning two result sets. The second sample demonstrates a more complex stored procedure. It accepts an input parameter, further it returns a parameter (an output parameter), returns a value and a result set.

To run the samples in this topic, you need to use\import the System.Data, System.Data.Common and Telerik.OpenAccess.Data.Common namespaces.

Optionally, if the execution of the stored procedure is expected to take long time, you can set an appropriate value for the CommandTimeout property of oaCommand. By default, it is the one specified in Runtime Configuration. If the command exceeds the timeout it will be terminated and a backend specific exception will be thrown.

Suppose, you have Cars and Categories tables in the database:

And a stored procedure that returns both Categories and Cars.

CREATE PROCEDURE GetCarsAndCategories
AS
BEGIN
SELECT * FROM Cars
SELECT * FROM Categories
END

The current version of the Telerik Data Access ADO API does not directly support working with multiple result sets. To solve this problem, you need to read the data using the familiar pattern. It involves creating a new OAConnection, creating an OACommand, setting the command text to the name of the stored procedure, and calling the ExecuteReader method to get a data reader. With the reader in hand, you use the generic Translate method on the OpenAccessContext to materialize instances of the Car entity from the reader. To advance to the next result set, you need to call the NextResult method of the data reader. Then, you use the generic Translate method again, to materialize instances of the Category entity.

private static void StoredProcedureMultipleResultSets()
{
   // 1. Create a new instance of the OpenAccessContext.
   using ( FluentModel dbContext = new FluentModel() )
   {
       // 2. Retrieve the OAConnection instance.
       using ( IDbConnection oaConnection = dbContext.Connection )
       {
           // 3. Create a new instance of the OACommand class.
           using ( IDbCommand oaCommand = oaConnection.CreateCommand() )
           {
               // 4. Set the CommandType property.
               oaCommand.CommandType = CommandType.StoredProcedure;

               // 5. Set the CommandText property.
               oaCommand.CommandText = "GetCarsAndCategories";

               // 6. Execute the command and materialize the car entities
               using ( IDataReader dataReader = oaCommand.ExecuteReader() )
               {
                   List<Car> cars = dbContext.Translate<Car>( dataReader as DbDataReader ).ToList();

                   // 7. Advance to the next result sets
                   dataReader.NextResult();
                   List<Category> categories = dbContext.Translate<Category>( dataReader as 
                        DbDataReader ).ToList();
               }
           }
       }
   }
}

Private Sub StoredProcedureMultipleResultSets()
    ' 1. Create a new instance of the OpenAccessContext.
    Using dbContext As New FluentModel()
        ' 2. Retrieve the OAConnection instance.
        Using oaConnection As IDbConnection = dbContext.Connection
            ' 3. Create a new instance of the OACommand class.
            Using oaCommand As IDbCommand = oaConnection.CreateCommand()
                ' 4. Set the CommandType property.
                oaCommand.CommandType = CommandType.StoredProcedure

                ' 5. Set the CommandText property.
                oaCommand.CommandText = "GetCarsAndCategories"

                ' 6. Execute the command and materialize the car entities
                Using dataReader As IDataReader = oaCommand.ExecuteReader()
                    Dim cars As List(Of Car) = dbContext.Translate(Of Car)(TryCast(dataReader, DbDataReader)).
                        ToList()

                    ' 7. Advance to the next result sets
                    dataReader.NextResult()
                    Dim categories As List(Of Category) = dbContext.Translate(Of Category) _
                        (TryCast(dataReader, DbDataReader)).ToList()
                End Using
            End Using
        End Using
    End Using
End Sub

Consider the following stored procedure. It accepts an input parameter, further it returns a parameter (an output parameter), returns a value and a result set.

CREATE PROCEDURE GetCarInfoAndCategories(
@CarId INT,
@Make varchar(50) OUTPUT)
AS
BEGIN
DECLARE @CarYear SMALLINT

SELECT @Make = Make, @CarYear = CarYear
FROM Cars
WHERE CarID = @CarId

SELECT * FROM Categories

return @CarYear
END

The following code snippet shows how to execute the stored procedure and consume the result.

private static void StoredProcedureOutParamResultSets()
{
   // 1. Create a new instance of the OpenAccessContext.
   using ( FluentModel dbContext = new FluentModel() )
   {
       // 2. Initialize parameters.
       // 2.1. The 'CarId' parameter is IN parameter.
       DbParameter carIdParameter = new OAParameter
       {
           ParameterName = "@CarId",
           Value = "1"
       };

       // 2.2. The 'CarMake' parameter is OUT parameter.
       DbParameter carMakeParameter = new OAParameter
       {
           ParameterName = "@Make",
           Direction = System.Data.ParameterDirection.Output,
           DbType = System.Data.DbType.String,
           Size = 50
       };

       // 2.3. This is the return value.
       System.Data.SqlClient.SqlParameter carYearParameter = 
           new System.Data.SqlClient.SqlParameter()
           {
               Direction = ParameterDirection.ReturnValue
           };

       // 3. Execute the query and consume the result.
       IEnumerable<Category> categories = dbContext.ExecuteQuery<Category>(
           "GetCarInfoAndCategories", CommandType.StoredProcedure, carIdParameter, 
                carMakeParameter, carYearParameter );
       Console.WriteLine( carMakeParameter.Value );
       Console.WriteLine( carYearParameter.Value );
   }
}

Private Sub StoredProcedureOutParamResultSets()
 ' 1. Create a new instance of the OpenAccessContext.
 Using dbContext As New FluentModel()
  ' 2. Initialize parameters.
  ' 2.1. The 'CarId' parameter is IN parameter.
  Dim carIdParameter As DbParameter = New OAParameter _
        With {.ParameterName = "@CarId", .Value = "1"}

  ' 2.2. The 'CarMake' parameter is OUT parameter.
  Dim carMakeParameter As DbParameter = New OAParameter With {
   .ParameterName = "@Make",
   .Direction = System.Data.ParameterDirection.Output,
   .DbType = System.Data.DbType.String,
   .Size = 50}

  ' 2.3. This is the return value.
  Dim carYearParameter As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter With {
   .Direction = ParameterDirection.ReturnValue}

  ' 3. Execute the query and consume the result.
  Dim categories As IEnumerable(Of Category) =
   dbContext.ExecuteQuery(Of Category)("GetCarInfoAndCategories",
            CommandType.StoredProcedure,
            carIdParameter,
            carMakeParameter,
            carYearParameter)

  Console.WriteLine(carMakeParameter.Value)
  Console.WriteLine(carYearParameter.Value)
 End Using
End Sub