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.
