You can include SQL statements in an application in two ways:
By adding the SQL statements directly in the .NET application code.
By packaging the SQL statements in a stored procedure, and executing the stored procedure from the .NET application.
In some cases, a stored procedure can provide advantages over embedded SQL statements. Stored procedures support complex conditional and looping constructs that are difficult to duplicate with SQL statements embedded directly in an application.
You can also see a significant improvement in performance by using stored procedures; a stored procedure only needs to be parsed, compiled and optimized once on the server side, while a SQL statement that is included in an application may be parsed, compiled and optimized each time it is executed from a .NET application.
To use a stored procedure in your .NET application you must:
Create an SPL stored procedure on the Advanced Server host.
Import the EnterpriseDB.EDBClient namespace.
Pass the name of the stored procedure to the instance of the EDBCommand.
Change the EDBCommand.CommandType to CommandType.StoredProcedure.
Prepare() the command.
Execute the command.
Example - Executing a Stored Procedure without Parameters
Our sample procedure prints the name of department 10; the procedure takes no parameters, and returns no parameters. To create the sample procedure, invoke EDB-PSQL and connect to the Advanced Server host database. Enter the following SPL code at the command line:
When Advanced Server has validated the stored procedure it will echo CREATE PROCEDURE.
Using the EDBCommand Object to Execute a Stored Procedure
The CommandType property of the EDBCommand object is used to indicate the type of command being executed. The CommandType property is set to one of three possible CommandType enumeration values:
Use the default Text value when passing a SQL string for execution.
Use the StoredProcedure value, passing the name of a stored procedure for execution.
Use the TableDirect value when passing a table name. This value passes back all records in the specified table.
The CommandText property must contain a SQL string, stored procedure name, or table name depending on the value of the CommandType property.
The following example executes the stored procedure:
Save the sample code in a file in a web root directory named:
storedProc.aspx
To invoke the sample code, open a web-browser, and browse to:
http://localhost/storedProc.aspx
Example - Executing a Stored Procedure with IN Parameters
The following example demonstrates calling a stored procedure that includes IN parameters. To create the sample procedure, invoke EDB-PSQL and connect to the Advanced Server host database. Enter the following SPL code at the command line:
When Advanced Server has validated the stored procedure it wEDBTypes.EDBDbType.Floatill echo CREATE PROCEDURE.
Passing Input Values to a Stored Procedure
Calling a stored procedure that contains parameters is very similar to executing a stored procedure without parameters. The major difference is that when calling a parameterized stored procedure you must use the EDBParameter collection of the EDBCommand object. When the EDBParameter is added to the EDBCommand collection, properties such as ParameterName, DbType, Direction, Size, and Value are set.
The following example demonstrates the process of executing a parameterized stored procedure from a C#.
Save the sample code in a file in a web root directory named:
storedProcInParam.aspx
To invoke the sample code, open a web-browser, and browse to:
http://localhost/storedProcInParam.aspx
In the example, the body of the Page_Load method declares and instantiates an EDBConnection object. The sample then creates an EDBCommand object with the properties needed to execute the stored procedure.
The example then uses the Add method of the EDBCommand Parameter collection to add six input parameters.
It assigns a value to each parameter before passing them to the EMP_INSERT stored procedure
The Prepare() method prepares the statement before calling the ExecuteNonQuery() method.
The ExecuteNonQuery method of the EDBCommand object executes the stored procedure. After the stored procedure has executed, a test record is inserted into the emp table and the values inserted are displayed on the webpage.
Example - Executing a Stored Procedure with IN, OUT, and INOUT Parameters
The previous example demonstrated how to pass IN parameters to a stored procedure; the following examples demonstrate how to pass IN values and return OUT values from a stored procedure.
Creating the Stored Procedure
The following stored procedure passes the department number, and returns the corresponding location and department name. To create the sample procedure, open the EDB-PSQL command line, and connect to the Advanced Server host database. Enter the following SPL code at the command line:
When Advanced Server has validated the stored procedure it will echo CREATE PROCEDURE.
Receiving Output Values from a Stored Procedure
When retrieving values from OUT parameters you must explicitly specify the direction of out parameters as Output. You can retrieve the values from Output parameters in two ways:
Call the ExecuteReader method of the EDBCommand and explicitly loop through the returned EDBDataReader, searching for the values of OUT parameters.
Call the ExecuteNonQuery method of EDBCommand and explicitly get the value of a declared Output parameter by calling that EDBParameter value property.
In each method, you must declare each parameter, indicating the direction of the parameter (ParameterDirection.Input, ParameterDirection.Output or ParameterDirection.InputOutput). Before invoking the procedure, you must provide a value for each IN and INOUT parameter. After the procedure returns, you may retrieve the OUT and INOUT parameter values from the command.Parameters[] array.
The following code listing demonstrates using the ExecuteReader method to retrieve a result set:
The following code listing demonstrates using the ExecuteNonQuery method to retrieve a result set: