Calling a Stored Procedure

You call stored procedures using a Command object. When you issue a command on a stored procedure, you must set the CommandType of the Command object to StoredProcedure.

The following code example, which requires the emp table (see Sample Tables for Pervasive.SQL ), shows how to execute a stored procedure on a Pervasive.SQL database and read the results using a DataReader.

Create the following stored procedure:

CREATE PROCEDURE GetEmpSalary 
    (empno int, 
     sal char(7) output) 
AS 
SELECT sal = sal from emp where empno = empno

The following code example executes the GetEmpSalary stored procedure:

// Open connection to Pervasive.SQL database 
PsqlConnection  Conn; 
Conn = new PsqlConnection("ServerDSN=Demodata; 
								UID=test; 
								PWD=test; 
                           Server=localhost;"); 
                                 
Conn.Open(); 
// Make a command object for the stored procedure 
// You must set the CommandType of the Command object  
// to StoredProcedure  
PsqlCommand   DBCmd = new 
PsqlCommand("GetEmpSalary",Conn); 
DBCmd.CommandType = CommandType.StoredProcedure; 
PsqlDataReader  myDataReader; 
try 
{ 
    myDataReader = DBCmd.ExecuteReader() 
    myDataReader.Close(); 
} 
catch (Exception ex)  
{ 
    // Display any exceptions in a messagebox 
    MessageBox.Show (ex.Message); 
} 
//  Close the connection 
Conn.Close();