Using SPL Stored Procedures in your .NET Application v4.1.6.1

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:

CREATE OR REPLACE PROCEDURE list_dept10
IS
  v_deptname VARCHAR2(30);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Dept No: 10');
  SELECT dname INTO v_deptname FROM dept WHERE deptno = 10;
  DBMS_OUTPUT.PUT_LINE('Dept Name: ' || v_deptname);
END;

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:

<% @ Page Language="C#" Debug="true"%>
<% @Import Namespace="EnterpriseDB.EDBClient" %>
<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Configuration" %>

<script language="C#" runat="server" >

private void Page_Load(object sender, System.EventArgs e)
{
  string strConnectionString = ConfigurationManager.AppSettings
  ["DB_CONN_STRING"];
  EDBConnection conn = new EDBConnection(strConnectionString);


  try
  {
      conn.Open();

      EDBCommand cmdStoredProc = new EDBCommand("list_dept10",conn);
      cmdStoredProc.CommandType = CommandType.StoredProcedure;

      cmdStoredProc.Prepare();
      cmdStoredProc.ExecuteNonQuery();


      Response.Write("Stored Procedure Executed Successfully");

  }
  catch(Exception exp)
{
      Response.Write(exp.ToString());
  }
  finally
{
      conn.Close();
  }
}
</script>

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:

CREATE OR REPLACE PROCEDURE
  EMP_INSERT
  (
     pENAME IN VARCHAR,
     pJOB IN VARCHAR,
     pSAL IN FLOAT4,
     pCOMM IN FLOAT4,
     pDEPTNO IN INTEGER,
     pMgr IN INTEGER
   )
AS
DECLARE
  CURSOR TESTCUR IS SELECT MAX(EMPNO) FROM EMP;
  MAX_EMPNO INTEGER := 10;
BEGIN

  OPEN TESTCUR;
  FETCH TESTCUR INTO MAX_EMPNO;
  INSERT INTO EMP(EMPNO,ENAME,JOB,SAL,COMM,DEPTNO,MGR)
    VALUES(MAX_EMPNO+1,pENAME,pJOB,pSAL,pCOMM,pDEPTNO,pMgr);
  CLOSE testcur;
END;

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#.

<% @ Page Language="C#" Debug="true"%>
<% @Import Namespace="EnterpriseDB.EDBClient" %>
<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Configuration" %>

<script language="C#" runat="server" >

private void Page_Load(object sender, System.EventArgs e)
{
  string strConnectionString = ConfigurationManager.AppSettings
  ["DB_CONN_STRING"];
  EDBConnection conn = new EDBConnection(strConnectionString);

  string empName    = "EDB";
  string empJob     = "Manager";
  double salary     = 1000;
  double commission = 0.0;
  int    deptno     = 20;
  int    manager    = 7839;

  try
  {
      conn.Open();

      EDBCommand cmdStoredProc = new EDBCommand
      ("emp_insert(:EmpName,:Job,:Salary,:Commission,:DeptNo,
      :Manager)",conn);
      cmdStoredProc.CommandType = CommandType.StoredProcedure;

      cmdStoredProc.Parameters.Add(new EDBParameter
      ("EmpName", EDBTypes.EDBDbType.Varchar));
      cmdStoredProc.Parameters[0].Value = empName;

      cmdStoredProc.Parameters.Add(new EDBParameter
      ("Job", EDBTypes.EDBDbType.Varchar));
      cmdStoredProc.Parameters[1].Value = empJob;

      cmdStoredProc.Parameters.Add(new EDBParameter
      ("Salary", EDBTypes.EDBDbType.Real));
      cmdStoredProc.Parameters[2].Value = salary;

      cmdStoredProc.Parameters.Add(new EDBParameter
      ("Commission", EDBTypes.EDBDbType.Real));
      cmdStoredProc.Parameters[3].Value = commission;

      cmdStoredProc.Parameters.Add(new EDBParameter
      ("DeptNo", EDBTypes.EDBDbType.Integer));
      cmdStoredProc.Parameters[4].Value = deptno;

      cmdStoredProc.Parameters.Add
      (new EDBParameter("Manager", EDBTypes.EDBDbType.Integer));
      cmdStoredProc.Parameters[5].Value = manager;

      cmdStoredProc.Prepare();

      cmdStoredProc.ExecuteNonQuery();

      Response.Write("Following Information Inserted Successfully
      <br>");
      string  empInfo  = "Employee Name: " + empName + "<br>";
      empInfo += "Job: " + empJob + "<br>";
      empInfo += "Salary: " + salary + "<br>";
      empInfo += "Commission: " + commission + "<br>";
      empInfo += "Manager: " + manager + "<br>";

      Response.Write(empInfo);

  }

  catch(Exception exp)
{
      Response.Write(exp.ToString());
  }
  finally
{
      conn.Close();
  }
}

</script>

</script>

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.

EDBCommand cmdStoredProc = new EDBCommand
("emp_insert(:EmpName,:Job,:Salary,:Commission,:DeptNo,:Manager)",conn);
cmdStoredProc.CommandType = CommandType.StoredProcedure;

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:

CREATE OR REPLACE PROCEDURE
  DEPT_SELECT
  (
    pDEPTNO IN  INTEGER,
    pDNAME  OUT VARCHAR,
    pLOC    OUT VARCHAR
  )
AS
DECLARE
  CURSOR TESTCUR IS SELECT DNAME,LOC FROM DEPT;
  REC RECORD;
BEGIN

  OPEN TESTCUR;
  FETCH TESTCUR INTO REC;

  pDNAME  := REC.DNAME;
  pLOC    := REC.LOC;

  CLOSE testcur;
END;

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:

<% @ Page Language="C#" Debug="true"%>
<% @Import Namespace="EnterpriseDB.EDBClient" %>
<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Configuration" %>

<script language="C#" runat="server" >

private void Page_Load(object sender, System.EventArgs e)
{
  string strConnectionString =
        ConfigurationManager.AppSettings["DB_CONN_STRING"];
  EDBConnection conn = new EDBConnection(strConnectionString);

  try
  {

    conn.Open();
    EDBCommand command = new EDBCommand("DEPT_SELECT
          (:pDEPTNO,:pDNAME,:pLOC)", conn);
    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new EDBParameter("pDEPTNO",
          EDBTypes.EDBDbType.Integer,10,"pDEPTNO",
          ParameterDirection.Input,false ,2,2,
          System.Data.DataRowVersion.Current,1));

    command.Parameters.Add(new EDBParameter("pDNAME",
          EDBTypes.EDBDbType.Varchar,10,"pDNAME",
          ParameterDirection.Output,false ,2,2,
          System.Data.DataRowVersion.Current,1));

    command.Parameters.Add(new EDBParameter("pLOC",
          EDBTypes.EDBDbType.Varchar,10,"pLOC",
          ParameterDirection.Output,false ,2,2,
          System.Data.DataRowVersion.Current,1));

    command.Prepare();

    command.Parameters[0].Value = 10;
    EDBDataReader result = command.ExecuteReader();

    int fc = result.FieldCount;

    for (int i = 0; i < 3; i++)
    {
        Console.WriteLine("RESULT[" + i + "]=" + Convert.ToString(command.Parameters[i].Value));
        Console.WriteLine("\n");
    }

  }

  catch(EDBException exp)
  {
    Response.Write(exp.ToString());
  }
  finally
  {
    conn.Close();
  }
}

</script>

The following code listing demonstrates using the ExecuteNonQuery method to retrieve a result set:

<% @ Page Language="C#" Debug="true"%>
<% @Import Namespace="EnterpriseDB.EDBClient" %>
<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Configuration" %>

<script language="C#" runat="server" >

private void Page_Load(object sender, System.EventArgs e)
{
  string strConnectionString = ConfigurationManager.AppSettings
  ["DB_CONN_STRING"];
  EDBConnection conn = new EDBConnection(strConnectionString);

  try
  {

    conn.Open();
    EDBCommand command = new EDBCommand("DEPT_SELECT
      (:pDEPTNO,:pDNAME,:pLOC)", conn);
    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new EDBParameter("pDEPTNO",
      EDBTypes.EDBDbType.Integer,10,"pDEPTNO",
      ParameterDirection.Input,false ,2,2,
      System.Data.DataRowVersion.Current,1));

    command.Parameters.Add(new EDBParameter("pDNAME",
      EDBTypes.EDBDbType.Varchar,10,"pDNAME",
      ParameterDirection.Output,false ,2,2,
      System.Data.DataRowVersion.Current,1));

    command.Parameters.Add(new EDBParameter("pLOC",
      EDBTypes.EDBDbType.Varchar,10,"pLOC",
      ParameterDirection.Output,false ,2,2,
      System.Data.DataRowVersion.Current,1));

    command.Prepare();
    command.Parameters[0].Value = 10;
    command.ExecuteNonQuery();

    Response.Write(command.Parameters["pDNAME"].Value.ToString());
    Response.Write(command.Parameters["pLOC"].Value.ToString());
  }

  catch(EDBException exp)
  {
    Response.Write(exp.ToString());
  }
  finally
  {
    conn.Close();
  }
}
</script>