Using a Ref Cursor in a .NET Application

A ref cursor is a cursor variable that contains a pointer to a query result set. The result set is determined by the execution of the OPEN FOR statement using the cursor variable. A cursor variable is not tied to a particular query like a static cursor. The same cursor variable may be opened a number of times with the OPEN FOR statement containing different queries and each time, a new result set will be created for that query and made available via the cursor variable. There are two ways to declare a cursor variable:

• Use the SYS_REFCURSOR built-in data type to declare a weakly-typed ref cursor.

• Define a strongly-typed ref cursor that declares a variable of that type.

SYS_REFCURSOR is a ref cursor type that allows any result set to be associated with it. This is known as a weakly-typed ref cursor. The following example is a declaration of a weakly-typed ref cursor:

name SYS_REFCURSOR;

Following is an example of a strongly-typed ref cursor:

TYPE <cursor_type_name> IS REF CURSOR RETURN emp%ROWTYPE;

Creating the Stored Procedure

The following sample code creates a stored procedure called refcur_inout_callee. 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
  refcur_inout_callee(v_refcur IN OUT SYS_REFCURSOR)
IS
BEGIN
   OPEN v_refcur FOR SELECT ename FROM emp;
END;

To use the above defined procedure from .NET code, you must specify the data type of the ref cursor being passed as an IN parameter, as shown in the above script.

The following C# code uses the stored procedure to retrieve employee names from the emp table:

using System;
using System.Data;
using EnterpriseDB.EDBClient;
using System.Configuration;

namespace EDBRefCursor
{
    class EmpRefcursor
    {
        [STAThread]
        static void Main(string[] args)
        {
            string strConnectionString =
            ConfigurationSettings.AppSettings["DB_CONN_STRING"];
            EDBConnection conn = new EDBConnection(strConnectionString);
            conn.Open();
            EDBTransaction tran = conn.BeginTransaction();
            try
            {
                EDBTransaction tran = conn.BeginTransaction();
                EDBCommand command = new EDBCommand("refcur_inout_callee",
                    conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Transaction = tran;
                command.Parameters.Add(new EDBParameter("refCursor",
                    EDBTypes.EDBDbType.Refcursor, 10, "refCursor",

                ParameterDirection.InputOutput, false, 2, 2,
                    System.Data.DataRowVersion.Current, null));

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

                command.ExecuteNonQuery();
                String cursorName = command.Parameters[0].Value.ToString();
                command.CommandText = "fetch all in \"" + cursorName + "\"";
                command.CommandType = CommandType.Text;

                EDBDataReader reader =
                    command.ExecuteReader(CommandBehavior.SequentialAccess);
                int fc = reader.FieldCount;
                while (reader.Read())
                {
                    for (int i = 0; i < fc; i++)
                    {
                        Console.WriteLine(reader.GetString(i));
                    }
                }
                reader.Close();
                tran.Commit();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message.ToString());
            }
        }
    }
}

The following .NET code snippet displays the result on the console:

for(int i = 0;i < fc; i++)
{
    Console.WriteLine(reader.GetString(i));
}

Please note that you must bind the EDBDbType.RefCursor type in EDBParameter() if you are using a ref cursor parameter.