Click here to Skip to main content
13,053,276 members (59,884 online)
Click here to Skip to main content
Add your own
alternative version


32 bookmarked
Posted 14 Jun 2006

An Implementation of Dynamic Connection String and Cursor Handling using Enterprise Library 2.0 in an ASP.NET 2.0 Application with an Oracle Database

, 14 Jun 2006
Rate this:
Please Sign up or sign in to vote.
This article will explore some solutions for dynamic connection string and cursor handling using Enterprise Library 2.0 in an ASP.NET 2.0 applications with an Oracle database.

1. Introduction

Enterprise Library is a library containing seven general purpose application blocks, which are reusable, extensible source-code components that provide guidance for common development challenges. Its major newest release is Enterprise Library 2.0 (EL 2.0) in January 2006, which has been redesigned to use the new capabilities of the .NET Framework 2.0. The Data Access Application Block (DAAB) is one of application blocks in the Enterprise Library which simplifies development tasks that implement common data access functionality.

In my ASP.NET 2.0 projects with Oracle database, EL 2.0 is selected as the base application block. This decision met the following challenges: one is an issue about dynamic connection strings, which passes the Oracle user ID, password, and the database instance to Oracle, other than static connection string retrieved from the web.config; the other is dealing with cursors returned by Oracle procedures.

2. Dynamic connection string

EL 2.0 now supports dynamic connection strings that can be created through business logic. A simple case is as follows, where “_user_id” is the Oracle user ID, “_password” is the Oracle password, and “_data_source” is the name of the Oracle database instance.

string dBConnection = "USER ID=" + _user_id + 
       ";PASSWORD=" + _password + ";DATA SOURCE=" + _data_source;
Database odb = new OracleDatabase(dBConnection);
string sqlCommand = "Select * From your_table";
DbCommand dbCommand = odb.GetSqlStringCommand(sqlCommand);
DataSet ds = odb.ExecuteDataSet(dbCommand);

Stored procedures in a package can be called as follows, where “schema_name” is the Oracle schema, and “procedure_name” is the procedure name in a package “package_name”.

Database odb = new OracleDatabase(dynamic_Connection_string);
DbCommand dbCommand = 

You can also use the interface IOraclePackage to access Oracle.

class OraclePackage : IOraclePackage
    string _name, _prefix;
    public OraclePackage()
        _name = string.Empty;
        _prefix = string.Empty;
    public OraclePackage(string name, string prefix)
        _name = string.Empty;
        _prefix = string.Empty;
    public string Name
        get { return _name; }
        set { _name = value; }
    public string Prefix
        get { return _prefix; }
        set { _prefix = value; }
IOraclePackage op = new OraclePackage(package_name, prefix);
IList<IOraclePackage> packages = new List<IOraclePackage>();
OracleDatabase odb = new OracleDatabase(
   dynamic_Connection_string, packages);

3. Cursor Handling

Cursor is a mechanism by which you can assign a name to a "select statement" and manipulate the information within that SQL statement. It is convenient for .NET to handle returned reference cursors from Oracle by a DataSet. EL 2.0 deals with Oracle cursors in a special way. First, we need to declare a reference cursor, and a procedure returns a cursor in a procedure spec. The name of this reference cursor must be “cur_OUT”, and it must be “IN OUT”.

PROCEDURE get_data_set (
  cur_OUT   IN OUT t_cursor,
  o_retcode OUT    PLS_INTEGER,
  o_message OUT    VARCHAR2

Then, we need to retrieve a dataset from the database, and return a reference cursor in a procedure body.

PROCEDURE get_data_set (
  cur_OUT   IN OUT t_cursor,
  o_retcode OUT    PLS_INTEGER,
  o_message OUT    VARCHAR2)
  v_cursor  t_cursor;
    OPEN cur_OUT
      SELECT * 
      FROM your_table
      WHERE your_condition;
      OPEN cur_OUT
        SELECT * 
        FROM your_table
        WHERE 1 <> 1;
      o_retcode := -1;
      o_message := 'No Data Found';
      o_retcode := SQLCODE;
      o_message := SQLERRM;
  o_retcode := SQLCODE;
  o_message := 'selection is successful';
      o_retcode := SQLCODE;
      o_message := SQLERRM;
END get_data_set;

On the .NET side, we don’t need to add any IN/OUT cursor parameters to DbCommand. It can handle them automatically for us.

OracleDatabase odb = new OracleDatabase(dynamic_Connection_string);
DbCommand dbCommand = odb.GetStoredProcCommand("get_data_set");
odb.AddOutParameter(dbCommand, "o_retcode", DbType.Int32, 10);
odb.AddOutParameter(dbCommand, "o_message", DbType.String, 100);
DataSet ds = odb.ExecuteDataSet(dbCommand);

4. Conclusion

This article illustrates our solutions for dynamic connection strings and handling cursors using the Enterprise Library 2.0 in ASP.NET 2.0 applications with an Oracle database. They work fine in our web applications.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Yunyou Yao
Web Developer
United States United States
Yunyou Yao is a Senior IT Specialist in Houston, USA.

You may also be interested in...


Comments and Discussions

GeneralCursor Handling Problem Pin
xilon200022-Feb-07 23:42
memberxilon200022-Feb-07 23:42 
GeneralRe: Cursor Handling Problem Pin
Yunyou Yao2-Mar-07 3:52
memberYunyou Yao2-Mar-07 3:52 
QuestionDynamic Connection String retrieval in Application Block Pin
deepakOne22-Nov-06 19:39
memberdeepakOne22-Nov-06 19:39 
AnswerRe: Dynamic Connection String retrieval in Application Block Pin
Faraz Shams27-Dec-06 20:16
memberFaraz Shams27-Dec-06 20:16 
GeneralDAAB 2.0 Pin
Annadatha20-Oct-06 7:37
memberAnnadatha20-Oct-06 7:37 
GeneralRe: DAAB 2.0 Pin
Yunyou Yao20-Oct-06 8:57
memberYunyou Yao20-Oct-06 8:57 
GeneralVery good Pin
linqunhui24-Sep-06 4:40
memberlinqunhui24-Sep-06 4:40 
GeneralGood Article Pin
Ajith Cyriac6-Sep-06 19:56
memberAjith Cyriac6-Sep-06 19:56 
GeneralRe: Good Article Pin
Annadatha20-Oct-06 7:20
memberAnnadatha20-Oct-06 7:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170713.1 | Last Updated 14 Jun 2006
Article Copyright 2006 by Yunyou Yao
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid