Stored Procedures

To enable stored procedures in the application, do the following:

Set the CommandText property in the PsqlCommand object to the stored procedure name.

MyCommand.CommandText = "GetEmpSalary";

Set the CommandType property in the PsqlCommand object to StoredProcedure.

MyCommand.CommandType = CommandType.StoredProcedure;

Specify parameter arguments, if needed. The application should add the parameters to the parameter collection of the PsqlCommand object in the order of the arguments to the stored procedure. The application does not need to specify the parameter markers in the CommandText property of the PsqlCommand object.

To retrieve the return value from a stored procedure, the application should add an extra parameter to the parameter collection for the PsqlCommand object. This parameter's ParameterDirection property should be set to ParameterDirection.ReturnValue. The return value parameter can be anywhere in the parameter collection because it does not correspond to a specific parameter marker in the Text property of the PsqlCommand object.

The following C# code fragment creates a PsqlCommand for the stored procedure GetEmpSalary and sets some of its properties:

public void CreateMyPsqlCommand() 
{ 
    PsqlCommand   DBCmd = new 
PsqlCommand("GetEmpSalary",Conn); 
    DBCmd.CommandText = CommandText.GetEmpSalary; 
    DBCmd.CommandType = CommandType.StoredProcedure; 
     
    DBCmd.Parameters.Add("inempno", 
PervasiveDbType.Integer, 7, "inempno"); 
    DBCmd.Parameters[0].Value = 1; 
    DBCmd.Parameters.Add("outsal", 
PervasiveDbType.Decimal, 7, "outsal"); 
    DBCmd.Parameters[1].Direction = 
ParameterDirection.ReturnValue; 
}

If the stored procedure does not produce a return value, parameters bound with the ParameterDirection property are ignored.

If the stored procedure returns a ReturnValue from the database and the application has not bound a parameter for it, the data provider discards the value.

See Calling a Stored Procedure for a code example on creating and calling a stored procedure.