Click here to Skip to main content
15,867,308 members
Articles / Web Development / ASP.NET
Article

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

Rate me:
Please Sign up or sign in to vote.
4.64/5 (11 votes)
14 Jun 20062 min read 94.9K   456   32   19
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.

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

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

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

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

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

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


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

Comments and Discussions

 
QuestionHow to implement TRANSACTIONS in the above code? Pin
savinir1126-Jun-07 6:27
savinir1126-Jun-07 6:27 
AnswerRe: How to implement TRANSACTIONS in the above code? Pin
Yunyou Yao27-Jun-07 7:38
Yunyou Yao27-Jun-07 7:38 
Generalstill facing errors Pin
savinir1121-Jun-07 5:34
savinir1121-Jun-07 5:34 
GeneralRe: still facing errors Pin
Yunyou Yao21-Jun-07 10:05
Yunyou Yao21-Jun-07 10:05 
GeneralRe: still facing errors Pin
savinir1121-Jun-07 11:58
savinir1121-Jun-07 11:58 
GeneralRe: still facing errors Pin
Yunyou Yao22-Jun-07 3:35
Yunyou Yao22-Jun-07 3:35 
GeneralRe: still facing errors Pin
savinir1122-Jun-07 3:46
savinir1122-Jun-07 3:46 
GeneralRe: still facing errors Pin
savinir1122-Jun-07 4:00
savinir1122-Jun-07 4:00 
GeneralRe: still facing errors Pin
Yunyou Yao22-Jun-07 5:04
Yunyou Yao22-Jun-07 5:04 
GeneralRe: still facing errors Pin
savinir1126-Jun-07 4:23
savinir1126-Jun-07 4:23 
GeneralCursor Handling Problem Pin
xilon200022-Feb-07 23:42
xilon200022-Feb-07 23:42 
GeneralRe: Cursor Handling Problem Pin
Yunyou Yao2-Mar-07 3:52
Yunyou Yao2-Mar-07 3:52 
QuestionDynamic Connection String retrieval in Application Block Pin
deepakOne22-Nov-06 19:39
deepakOne22-Nov-06 19:39 
AnswerRe: Dynamic Connection String retrieval in Application Block Pin
Faraz Shams27-Dec-06 20:16
Faraz Shams27-Dec-06 20:16 
GeneralDAAB 2.0 Pin
Annadatha20-Oct-06 7:37
Annadatha20-Oct-06 7:37 
GeneralRe: DAAB 2.0 Pin
Yunyou Yao20-Oct-06 8:57
Yunyou Yao20-Oct-06 8:57 
GeneralVery good Pin
linqunhui24-Sep-06 4:40
linqunhui24-Sep-06 4:40 
GeneralGood Article Pin
Ajith Cyriac6-Sep-06 19:56
Ajith Cyriac6-Sep-06 19:56 
GeneralRe: Good Article Pin
Annadatha20-Oct-06 7:20
Annadatha20-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.