Calling Stored Procedures that Return Output Parameters

If you have a stored procedure that returns values in output parameters, you can use one of the ExecSproc or ExecSprocScalar methods to retrieve these values. ExecSproc methods pass the command object used to execute the stored procedure back as the return value of the method. ExecSprocScalar methods return the command object as an output parameter. You can use this command object to retrieve output parameters returned from the stored procedure (see the sample code below).

Executes the specified stored procedure in the specified database, passing the specified parameters and returning a command object allowing access to any output parameters
```
protected virtual IDbCommand ExecSproc(string sprocName, string databaseKey,
	params IDbDataParameter[] dataParams)
```
Executes the specified stored procedure in the default database, passing the specified parameters and returning a command object allowing access to any output parameters
```
protected virtual IDbCommand ExecSproc(string sprocName,
	params IDbDataParameter[] dataParams)
```
Executes the specified stored procedure in the specified database, passing the specified parameters and returning a scalar value. Also returns the command object as an output parameter so any output parameters returned from the stored procedure can be accessed
```
protected virtual object ExecSprocScalar(string sprocName,
	string databaseKey,
	out IDbCommand cmd,
	params IDbDataParameter[] dataParams)
```
Executes the specified stored procedure in the default database, passing the specified parameters and returning a scalar value. Also returns the command object as an output parameter so any output parameters returned from the stored procedure can be accessed
```
protected virtual object ExecSprocScalar(string sprocName,
	out IDbCommand cmd,
	params IDbDataParameter[] dataParams)
```

Example:
Here is some sample code demonstrating how to retrieve output parameters from a command object returned from the ExecSprocScalar command:

In C#:

public void TestOutputParams()
{
	// Create the parameter and set the direction
	IDbDataParameter param1 = this.CreateParameter("@result", 0);
	param1.Direction = ParameterDirection.Output;
 
	// Call the stored procedure and pass the parameter
	IDbCommand cmd;
	this.ExecSprocScalar("OutputParamTest", out cmd, param1);
 
	// Retrieve the value from the command object
	IDbDataParameter param = (IDbDataParameter)cmd.Parameters["@result"];
	int x = (int)param.Value;
}

And in VB .NET:

Public Sub TestOutputParams()
	' Create the parameter and set the direction
	Dim param1 As IDbDataParameter = Me.CreateParameter("@result", 0)
	param1.Direction = ParameterDirection.Output
 
	' Call the stored procedure and pass the parameter
	Dim cmd As IDbCommand = Nothing
	Me.ExecSprocScalar("OutputParamTest", ByRef cmd, param1)
 
	' Retrieve the value from the command object
	Dim param As IDbDataParameter = CType(cmd.Parameters("@result"), IDbDataParameter)
	Dim x As Integer = CInt(param.Value)
End Sub