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 an improvement in performance by using stored procedures. A stored procedure needs to be parsed, compiled, and optimized only once on the server side. A SQL statement that's included in an application might be parsed, compiled, and optimized each time it's executed from a .NET application.
To use a stored procedure in your .NET application you must:
Create an SPL stored procedure on the EDB Postgres 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
This 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 EDB Postgres Advanced Server host database. Enter the following SPL code at the command line:
When EDB Postgres Advanced Server has validated the stored procedure, it echoes CREATE PROCEDURE.
Using the EDBCommand object to execute a stored procedure
The CommandType property of the EDBCommand object indicates 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 named storedProc.aspx in a web root directory.
To invoke the sample code, enter the following in a browser: http://localhost/storedProc.aspx
Example: Executing a stored procedure with IN parameters
The following example shows calling a stored procedure that includes IN parameters. To create the sample procedure, invoke EDB-PSQL and connect to the EDB Postgres Advanced Server host database. Enter the following SPL code at the command line:
When EDB Postgres Advanced Server has validated the stored procedure, it echoes CREATE PROCEDURE.
Passing input values to a stored procedure
Calling a stored procedure that contains parameters is 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 shows the process of executing a parameterized stored procedure from a C# script.
Save the sample code in a file named storedProcInParam.aspx in a web root directory.
To invoke the sample code, enter the following in a browser: 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 executes, 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 show 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 EDB Postgres Advanced Server host database. Enter the following SPL code at the command line:
When EDB Postgres Advanced Server has validated the stored procedure, it echoes CREATE PROCEDURE.
Receiving output values from a stored procedure
When retrieving values from OUT parameters, you must explicitly specify the direction of those 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 can retrieve the OUT and INOUT parameter values from the command.Parameters[] array.
The following code shows using the ExecuteReader method to retrieve a result set:
The following code shows using the ExecuteNonQuery method to retrieve a result set: