Click here to Skip to main content
Click here to Skip to main content

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 = 
  odb.GetStoredProcCommand("schema_name.package_name.procedure_name");

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>();
packages.Add(op);
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”.

TYPE t_cursor IS REF CURSOR;
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)
IS
  v_cursor  t_cursor;
BEGIN
  BEGIN
    OPEN cur_OUT
    FOR
      SELECT * 
      FROM your_table
      WHERE your_condition;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      OPEN cur_OUT
      FOR
        SELECT * 
        FROM your_table
        WHERE 1 <> 1;
      o_retcode := -1;
      o_message := 'No Data Found';
      RETURN;
    WHEN OTHERS THEN
      o_retcode := SQLCODE;
      o_message := SQLERRM;
      RETURN;
  END;
  o_retcode := SQLCODE;
  o_message := 'selection is successful';
  EXCEPTION
    WHEN OTHERS THEN
      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.

License

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.

Comments and Discussions

 
GeneralCursor Handling Problem Pinmemberxilon200022-Feb-07 23:42 
GeneralRe: Cursor Handling Problem PinmemberYunyou Yao2-Mar-07 3:52 
QuestionDynamic Connection String retrieval in Application Block PinmemberdeepakOne22-Nov-06 19:39 
AnswerRe: Dynamic Connection String retrieval in Application Block PinmemberFaraz Shams27-Dec-06 20:16 
GeneralDAAB 2.0 PinmemberAnnadatha20-Oct-06 7:37 
GeneralRe: DAAB 2.0 PinmemberYunyou Yao20-Oct-06 8:57 
GeneralVery good Pinmemberlinqunhui24-Sep-06 4:40 
GeneralGood Article PinmemberAjith Cyriac6-Sep-06 19:56 
GeneralRe: Good Article PinmemberAnnadatha20-Oct-06 7:20 

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

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

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