Enterprise Library 5 with Oracle Cursor

UPDATE: One of my commenters informed about an update of EntLibContrib and thank to them I wrote an update of this post but now using ODP.NET instead of the MS Oracle client. Thank you CD Smith for the contribution and thanks EntLibContrib for the update. Click here to go to the updated post.

In this post, I’m going to show you how to use the Enterprise Library 5 with Oracle Cursor. This is the way we’ve been doing where I work and it’s been working pretty well so far.

So, here’s the PL/SQL script that creates our Oracle Package:

CREATE OR REPLACE
PACKAGE      PKG_CLIENT
IS
  TYPE T_CURSOR IS REF CURSOR;

  PROCEDURE SP_LIST (
    P_ID_CLIENT   IN     CLIENT.ID_CLIENTE%TYPE,
    P_FULLNAME   IN     CLIENT.FULLNAME%TYPE,

    P_RESULT	              OUT T_CURSOR
  );
END;
/

CREATE OR REPLACE
PACKAGE BODY      PKG_CLIENT
IS
    PROCEDURE SP_LIST (
        P_ID_CLIENT   IN     CLIENT.ID_CLIENT%TYPE,
        P_FULLNAME   IN     CLIENT.FULLNAME%TYPE,

        P_RESULT	              OUT T_CURSOR
      )
    IS
    BEGIN
    	OPEN P_RESULT FOR
          SELECT
                   ID_CLIENT,
                   FULLNAME,
                   EMAIL,
                   BIRTHDATE,
                   PHONENUMER
           FROM CLIENT
           WHERE ((P_ID_CLIENT IS NULL)  OR (ID_CLIENT = P_ID_CLIENT))
           AND ((P_FULLNAME IS NULL)  OR (FULLNAME = P_FULLNAME))
    ;

    EXCEPTION
    	WHEN OTHERS
    	THEN
    		RAISE_APPLICATION_ERROR (-20001, 'PKG_CLIENT.SP_LIST '
    								 || '[Code: '
    								 || TO_CHAR (SQLCODE)
    								 || '] '
    								 || '[Description: '
    								 || SQLERRM
    								 || ']'
    								);
    END;
END;
/

In this code, we are creating our Package Spec and our Package Body. This package contains only one method called SP_LIST, which will perform a select statement and put it into the P_RESULT cursor – that is passed as an OUT parameter and is of type REF CURSOR.  The key points of this code is highlighted.

Before starting coding, we need to set up the Database Configuration in our Web.config, so, inside Visual Studio, right click on the Web.config file and hit Edit Enterprise Library V5 Configuration.

In the new Enterprise Library Configuration Tool, expand the Database Settings or add one if it doesn’t exist already, by pointing to menu Blocks -> Add Data Settings.

Then click on the plus sign at the left corner and click on “Add Database Connection String”. A new Database Connection String will be created. Give it a name, fill the Connection String Field and as we are using Oracle, we’ll choose the System.Data.OracleClient provider. After that, set this database as default. Your screen should be looking the like this one:

If everything is right, save the configuration.

Now, in your project, add a reference to the assemblies:

Microsoft.Practices.EnterpriseLibrary.Common.dll
Microsoft.Practices.EnterpriseLibrary.Data.dll
Microsoft.Practices.ServiceLocation.dll

Then, add the following usings:

using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;

and the Enterprise Library code to load the Database will be:

Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>();

This code creates an instance of the Database object. The Database object contains the methods that you’ll use to call the database.

Here’s a full code that calls the Oracle package we created earlier:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using System.Data;

namespace EnterpriseLibraryDatabaseBlock
{
    class Program
    {
        static void Main(string[] args)
        {
            Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>();

            List<Client> clients = new List<Client>();
            using(IDataReader reader = db.ExecuteReader("PKG_CLIENT.SP_LIST", 1, null, null))
            {
                while (reader.Read())
                {
                    Client client = new Client();
                    client.Id = reader["ID_CLIENT"] as Int32;
                    client.FullName = reader["FULLNAME"].ToString();
                    client.Email = reader["EMAIL"].ToString();

                    clients.Add(client);
                }
            }

            foreach (var item in clients)
            {
                Console.WriteLine("Client {0} - email: {1}", item.FullName, item.Email);
            }
        }
    }
}

You might want to tell the Enterprise Library which database you want to use, in case you have more than one database or you don’t want to set a default one for example. In these cases you can use an overload method to instantiate your Database class:

Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("MY_DB2");