How to: Retrieve Out Parameters and Return Value

This topic provides an example of how to execute stored procedures with a return value and out parameters. In this tutorial:

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 spCommand. 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.

For the examples in this topic, a Cars table like the one shown on the figure below will be used.

How to Retrieve Out Parameters

For this demo, the following stored procedure with out parameters will be used:

CREATE PROCEDURE GetCarModelAndMake
@CarId INT,
@CarModel varchar(50) OUTPUT,
@CarMake varchar(50) OUTPUT
AS
BEGIN
SELECT @CarModel = Model, @CarMake = Make
FROM Cars
WHERE CarID = @CarId
END

Suppose, you have created a model connected to the database containing that stored procedure. The following code snippet provides an example of how to execute the GetCarModelAndMake stored procedure and retrieve the out parameters.

private static void GetOutParametersFromStoredProcedure()
{
   // 1. Create a new instance of the generated OpenAccessContext.
   using ( FluentModel dbContext = new FluentModel() )
   {
       // 2. Get connection.
       using ( IDbConnection dbConnection = dbContext.Connection )
       {
           // 3. Initialize and execute OACommand.
           using ( IDbCommand spCommand = dbConnection.CreateCommand() )
           {
               spCommand.CommandText = "GetCarModelAndMake";
               spCommand.CommandType = System.Data.CommandType.StoredProcedure;

               // 4. Initialize parameters.
               // 4.1. The 'CarId' parameter is IN parameter.
               IDbDataParameter carIdParameter = spCommand.CreateParameter();
               carIdParameter.ParameterName = "@CarId";
               carIdParameter.Value = "1";
               spCommand.Parameters.Add( carIdParameter );

               // 4.2. The 'CarModel' parameter is OUT parameter.
               IDbDataParameter carModelParameter = spCommand.CreateParameter();
               carModelParameter.ParameterName = "@CarModel";
               carModelParameter.Direction = System.Data.ParameterDirection.Output;
               carModelParameter.DbType = System.Data.DbType.String;
               carModelParameter.Size = 50;
               spCommand.Parameters.Add( carModelParameter );

               // 4.3. The 'CarMake' parameter is OUT parameter.
               IDbDataParameter carMakeParameter = spCommand.CreateParameter();
               carMakeParameter.ParameterName = "@CarMake";
               carMakeParameter.Direction = System.Data.ParameterDirection.Output;
               carMakeParameter.DbType = System.Data.DbType.String;
               carMakeParameter.Size = 50;
               spCommand.Parameters.Add( carMakeParameter );

               spCommand.ExecuteNonQuery();

               // 5. Consume the out parameters.
               Console.WriteLine( carModelParameter.Value );
               Console.WriteLine( carMakeParameter.Value );
           }
       }
   }
}

Private Sub GetOutParametersFromStoredProcedure()
    ' 1. Create a new instance of the generated OpenAccessContext.
    Using dbContext As New FluentModel()
        ' 2. Get connection.
        Using dbConnection As IDbConnection = dbContext.Connection
            ' 3. Initialize and execute OACommand.
            Using spCommand As IDbCommand = dbConnection.CreateCommand()
                spCommand.CommandText = "GetCarModelAndMake"
                spCommand.CommandType = System.Data.CommandType.StoredProcedure

                ' 4. Initialize parameters.
                ' 4.1. The 'CarId' parameter is IN parameter.
                Dim carIdParameter As IDbDataParameter = spCommand.CreateParameter()
                carIdParameter.ParameterName = "@CarId"
                carIdParameter.Value = "1"
                spCommand.Parameters.Add(carIdParameter)

                ' 4.2. The 'CarModel' parameter is OUT parameter.
                Dim carModelParameter As IDbDataParameter = spCommand.CreateParameter()
                carModelParameter.ParameterName = "@CarModel"
                carModelParameter.Direction = System.Data.ParameterDirection.Output
                carModelParameter.DbType = System.Data.DbType.String
                carModelParameter.Size = 50
                spCommand.Parameters.Add(carModelParameter)

                ' 4.3. The 'CarMake' parameter is OUT parameter.
                Dim carMakeParameter As IDbDataParameter = spCommand.CreateParameter()
                carMakeParameter.ParameterName = "@CarMake"
                carMakeParameter.Direction = System.Data.ParameterDirection.Output
                carMakeParameter.DbType = System.Data.DbType.String
                carMakeParameter.Size = 50
                spCommand.Parameters.Add(carMakeParameter)

                spCommand.ExecuteNonQuery()

                ' 5. Consume the out parameters.
                Console.WriteLine(carModelParameter.Value)
                Console.WriteLine(carMakeParameter.Value)
            End Using
        End Using
    End Using
End Sub

How to Retrieve a Return Value

For this demo, the following stored procedure with a return value will be used:

CREATE PROCEDURE GetCarYear
@CarId INT
AS
BEGIN
DECLARE @CarYear SMALLINT

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

return @CarYear
END

Suppose, you have created a model connected to the database containing that stored procedure. The following code snippet demonstrates how to execute the stored procedure and retrieve the return value.

private static void GetReturnValueFromStoredProcedure()
{
   // 1. Create a new instance of the generated OpenAccessContext.
   using ( FluentModel dbContext = new FluentModel() )
   {
       // 2. Get connection.
       using ( IDbConnection dbConnection = dbContext.Connection )
       {
           // 3. Initialize and execute OACommand.
           using ( IDbCommand spCommand = dbConnection.CreateCommand() )
           {
               spCommand.CommandText = "GetCarYear";
               spCommand.CommandType = System.Data.CommandType.StoredProcedure;

               // 4. Initialize parameters.
               // 4.1. The 'CarId' parameter is IN parameter.
               IDbDataParameter carIdParameter = spCommand.CreateParameter();
               carIdParameter.ParameterName = "@CarId";
               carIdParameter.Value = "1";

               // 4.2. This is the return value.
               IDbDataParameter carYearParameter = spCommand.CreateParameter();
               carYearParameter.Direction = ParameterDirection.ReturnValue;

               spCommand.Parameters.Add( carIdParameter );
               spCommand.Parameters.Add( carYearParameter );

               spCommand.ExecuteNonQuery();

               // 5. Consume the return value.
               Console.WriteLine( carYearParameter.Value );
           }
       }
   }
}

Private Sub GetReturnValueFromStoredProcedure()
    ' 1. Create a new instance of the generated OpenAccessContext.
    Using dbContext As New FluentModel()
        ' 2. Get connection.
        Using dbConnection As IDbConnection = dbContext.Connection
            ' 3. Initialize and execute OACommand.
            Using spCommand As IDbCommand = dbConnection.CreateCommand()
                spCommand.CommandText = "GetCarYear"
                spCommand.CommandType = System.Data.CommandType.StoredProcedure

                ' 4. Initialize parameters.
                ' 4.1. The 'CarId' parameter is IN parameter.
                Dim carIdParameter As IDbDataParameter = spCommand.CreateParameter()
                carIdParameter.ParameterName = "@CarId"
                carIdParameter.Value = "1"

                ' 4.2. This is the return value.
                Dim carYearParameter As IDbDataParameter = spCommand.CreateParameter()
                carYearParameter.Direction = ParameterDirection.ReturnValue

                spCommand.Parameters.Add(carIdParameter)
                spCommand.Parameters.Add(carYearParameter)

                spCommand.ExecuteNonQuery()

                ' 5. Consume the return value.
                Console.WriteLine(carYearParameter.Value)
            End Using
        End Using
    End Using
End Sub

How to Execute a Stored Procedure with a Return Value and Out Parameters

The last example in this topic demonstrates how to execute a stored procedure mixing both, a return value and out parameters. For this demo, the following stored procedure will be used:

CREATE PROCEDURE GetCarInfo
@CarId INT,  
@CarModel varchar(50) OUTPUT,
@CarMake varchar(50) OUTPUT
AS
BEGIN
DECLARE @CarYear SMALLINT

SELECT @CarModel = Model, @CarMake = Make, @CarYear = CarYear
FROM Cars
WHERE CarID = @CarId

return @CarYear
END

Suppose, you have created a model connected to the database containing that stored procedure. The following code snippet demonstrates how to execute the stored procedure, retrieve the return value and the out parameters.

private static void GetReturnValueAndOutParametersFromStoredProcedure()
{
   // 1. Create a new instance of the generated OpenAccessContext.
   using ( FluentModel dbContext = new FluentModel() )
   {
       // 2. Get connection.
       using ( IDbConnection dbConnection = dbContext.Connection )
       {
           // 3. Initialize and execute OACommand.
           using ( IDbCommand spCommand = dbConnection.CreateCommand() )
           {
               spCommand.CommandText = "GetCarInfo";
               spCommand.CommandType = System.Data.CommandType.StoredProcedure;

               // 4. Initialize parameters.
               // 4.1. The 'CarId' parameter is IN parameter.
               IDbDataParameter carIdParameter = spCommand.CreateParameter();
               carIdParameter.ParameterName = "@CarId";
               carIdParameter.Value = "1";
               spCommand.Parameters.Add( carIdParameter );

               // 4.2. The 'CarModel' parameter is OUT parameter.
               IDbDataParameter carModelParameter = spCommand.CreateParameter();
               carModelParameter.ParameterName = "@CarModel";
               carModelParameter.Direction = System.Data.ParameterDirection.Output;
               carModelParameter.DbType = System.Data.DbType.String;
               carModelParameter.Size = 50;
               spCommand.Parameters.Add( carModelParameter );

               // 4.3. The 'CarMake' parameter is OUT parameter.
               IDbDataParameter carMakeParameter = spCommand.CreateParameter();
               carMakeParameter.ParameterName = "@CarMake";
               carMakeParameter.Direction = System.Data.ParameterDirection.Output;
               carMakeParameter.DbType = System.Data.DbType.String;
               carMakeParameter.Size = 50;
               spCommand.Parameters.Add( carMakeParameter );

               // 4.4. This is the return value.
               IDbDataParameter carYearParameter = spCommand.CreateParameter();
               carYearParameter.Direction = ParameterDirection.ReturnValue;
               spCommand.Parameters.Add( carYearParameter );

               spCommand.ExecuteNonQuery();

               // 5. Consume the out parameters and the return value.
               Console.WriteLine( carModelParameter.Value );
               Console.WriteLine( carMakeParameter.Value );
               Console.WriteLine( carYearParameter.Value );
           }
       }
   }
}

Private Sub GetReturnValueAndOutParametersFromStoredProcedure()
    ' 1. Create a new instance of the generated OpenAccessContext.
    Using dbContext As New FluentModel()
        ' 2. Get connection.
        Using dbConnection As IDbConnection = dbContext.Connection
            ' 3. Initialize and execute OACommand.
            Using spCommand As IDbCommand = dbConnection.CreateCommand()
                spCommand.CommandText = "GetCarInfo"
                spCommand.CommandType = System.Data.CommandType.StoredProcedure

                ' 4. Initialize parameters.
                ' 4.1. The 'CarId' parameter is IN parameter.
                Dim carIdParameter As IDbDataParameter = spCommand.CreateParameter()
                carIdParameter.ParameterName = "@CarId"
                carIdParameter.Value = "1"
                spCommand.Parameters.Add(carIdParameter)

                ' 4.2. The 'CarModel' parameter is OUT parameter.
                Dim carModelParameter As IDbDataParameter = spCommand.CreateParameter()
                carModelParameter.ParameterName = "@CarModel"
                carModelParameter.Direction = System.Data.ParameterDirection.Output
                carModelParameter.DbType = System.Data.DbType.String
                carModelParameter.Size = 50
                spCommand.Parameters.Add(carModelParameter)

                ' 4.3. The 'CarMake' parameter is OUT parameter.
                Dim carMakeParameter As IDbDataParameter = spCommand.CreateParameter()
                carMakeParameter.ParameterName = "@CarMake"
                carMakeParameter.Direction = System.Data.ParameterDirection.Output
                carMakeParameter.DbType = System.Data.DbType.String
                carMakeParameter.Size = 50
                spCommand.Parameters.Add(carMakeParameter)

                ' 4.4. This is the return value.
                Dim carYearParameter As IDbDataParameter = spCommand.CreateParameter()
                carYearParameter.Direction = ParameterDirection.ReturnValue
                spCommand.Parameters.Add(carYearParameter)

                spCommand.ExecuteNonQuery()

                ' 5. Consume the out parameters and the return value.
                Console.WriteLine(carModelParameter.Value)
                Console.WriteLine(carMakeParameter.Value)
                Console.WriteLine(carYearParameter.Value)
            End Using
        End Using
    End Using
End Sub