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:

Setting up Database Configuration in Enterprise Library Configuration Tool

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");
Advertisements

18 thoughts on “Enterprise Library 5 with Oracle Cursor

  1. The connection string example in the web.config is necessary for those of us who need to use EntLib5 and have never connected against Oracle.

  2. Here’s a connection string example:

      <connectionStrings>
        <add name="FooDatabase" connectionString="User ID=foo;Password=foo123;Data Source=fooDB" providerName="System.Data.OracleClient"/>
      </connectionStrings>
    
  3. Thanks, I appreciate it! Just to clarify tho, why are we not using the Oracle Connections section in the Database settings? What is intrinsically different about that section compared to the Database Instances section?

    1. You could use, but in my case I don’t store the connectionString in the web.config itself, I use a different file somewhere else, that’s why I use it in its simplest way. Besides, the connectionString has nothing special, why not use that field?

      1. Ok, that makes sense. We do use web.config and will have to encrypt that section. Just wondered why there was a specific Oracle section and then not using it. Thanks!

  4. Does this article assume that ODP.NET is being used? If so, I found this connection string example on connectionstrings.com that allows for bypassing the need for a tnsnames.ora, would this work with your example?

    Using ODP.NET without tnsnames.ora

    Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;

  5. This article assumes your not using ODP.NET, but that connection string would probably work anyway, that’s because the responsibility of handling the connection string is not from the client.

    I’d rather keep the connection strings in the tnsnames.ora though. By doing so, you have you all your connections in one place and not spread around all of your applications. If some server changes its IP or Hostname, you don’t have to change it in every application you maintain. Plus, the connection string you write into your application is simpler.

  6. SO if you aren’t using ODP.NETwhat are you using? MS Oracle client for DAB is no longer supported so we don’t want to touch that. Do you have anything that writes up the steps necessary to connect to Oracle using Ent Lib 5 with ODP.NET?

    1. Sorry to disappoint you but I’m using MS client. I’d also rather use ODP.NET but it’s not compatible with Ent Lib 5, at least so far. But Oracle is working on a version that will be. You actually can download the beta on their website.

    1. I already knew the EntLibContrib, but they released a new version compatible with ODP.NET less than two months ago. The last time I worked with Ent Lib was 3 months ago at the last company I worked in. Since then, i didn’t have time to look at it. Thank you for the update.

      I’ll update my post as soon as possible referencing the OPD.NET this time.

  7. IDataReader reader = db.ExecuteReader(“PKG_CLIENT.SP_LIST”, 1, null, null);

    This line of code not working when store procedure doesn’t have In parameters like

    PROCEDURE SP_LIST ( P_RESULT OUT T_CURSOR )
    IS
    BEGIN
    OPEN P_RESULT FOR
    SELECT
    ID_CLIENT,
    FULLNAME,
    EMAIL,
    BIRTHDATE,
    PHONENUMER
    FROM CLIENT ;

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

    By this example when don’t have In parameters we need to call like

    IDataReader reader = db.ExecuteReader(“PKG_CLIENT.SP_LIST”, null); its noit work

    Use below line of code
    IDataReader reader = db.ExecuteReader(“PKG_CLIENT.SP_LIST”, “”); Use Empty string instated of null.

    1. Thanks for pointing this out. I’ve noticed that sometimes using a 0, like this db.ExecuteReader(“PKG_CLIENT.SP_LIST”, 0); also works. I don’t know why. Didn’t have time to dig into it.

      As it’s been quite a while a don’t work with Oracle, I will update the post according to your example.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s