Returning Multiple Ref Cursors from Oracle using DAAB

• Download source - 252.2 KB

Introduction

This article explains how to work with Oracle stored procedure which returns multiple ref cursors using Data Access Application Block (DAAB) of Enterprise Library.

Background

DAAB is used in a variety of situations such as reading data for display, passing data through application layers, and submitting changed data back to the database system. It includes support for both stored procedures and in-line SQL. In other words, Data Access Application Block provides access to the most often used features of ADO.NET in simple-to-use classes.

Using the Code

(1) For Procedure Returning Single Ref Cursor

• Add the reference to Microsoft.Practices.EnterpriseLibrary.Data.
• Create the database object.
• Create the DbCommand object and pass the name of the stored procedure.
• If stored procedure takes any input parameter, pass that as shown in below code:

  Database db = DatabaseFactory.CreateDatabase("Connection String");
  DbCommand cmd = db.GetStoredProcCommand("PKG_TEST.EMAIL");
  db.AddInParameter(cmd, "varMODULEID", DbType.String);
  db.SetParameterValue(cmd, "varMODULEID", 1);
  DataSet ds = db.ExecuteDataSet(cmd);
  GridView1.DataSource = ds.Tables[0];
  GridView1.DataBind(); 

Now, stored procedure "PKG_TEST.EMAIL" takes one input parameter varMODULEID and returns a single ref cursor. But as in the above code, I have not created any parameter for ref cursor.

So, the question arises, how it will come to know about the ref cursor ????

The answer is in the code of DAAB.

When we call the stored procedure which returns a single ref cursor, DAAB automatically creates a parameter of type cursor. The name of the automatically created parameter is cur_OUT. Here, one point should be kept in mind that the name of the ref cursor in your stored procedure must be cur_OUT. So, in case of stored procedure which returns single ref cursor, you need not pass ref cursor as parameter to DbCommand object.

(2) For Procedure Returning Multiple Ref Cursors

• Add the reference to Microsoft.Practices.EnterpriseLibrary.Data.
• Create the database object.
• Create the DbCommand object and pass the name of the stored procedure.
• If stored procedure takes any input parameter, set the value of that as shown in below code:

  Database db = DatabaseFactory.CreateDatabase("Connection String");
  object[] results = new object[3];
  DbCommand cmd = db.GetStoredProcCommand("PKG_TEST.DEPARTMENT", results);
  db.SetParameterValue(cmd, "varDEPTID", 1);
  DataSet ds = db.ExecuteDataSet(cmd);
  GridView1.DataSource = ds.Tables[0];
  GridView1.DataBind();
  GridView2.DataSource = ds.Tables[1];
  GridView2.DataBind();

When you create a DbCommand object for a stored procedure that returns multiple cursors, you must pass an array of objects to the GetStoredProcCommand method. The size of the array must be equal to the number of parameters required by the stored procedure.

In this case, store procedure "PKG_TEST.DEPARTMENT" takes one input parameter and returns two ref cursors. Here:

object[] results = new object[3];

I create an array of size three which signifies that my stored procedure requires three parameters.

So, when your stored procedure returns more than one ref cursor, you have to create an array of object where size of array should be equal to the number of parameters taken by your stored procedure.

History

• 9^th June, 2009: Initial post