Enterprise Library 5 with ODP.NET

Enterprise Library 5 with Oracle


This tutorial is an update from my previous post Enterprise Library 5 with Oracle Cursor, in which I demonstrated how to use Enterprise Library 5 with Oracle but NOT using ODP.NET, using MS Client instead. That’s because ODP.NET wasn’t compatible with Enterprise Library 5 when I wrote that post.

Well, luckily things have changed and now we can use them together, so, I created this step-by-step tutorial to show you how you can setup your project to use Enterprise Library 5 with ODP.NET. The steps shown here were tested on Oracle 11g, Enterprise Library 5, ODP.NET, and EntLibContrib 5. Different versions of Oracle and ODP.NET may work as well.

Step 1 – Environment


I assume you already have some Oracle installed, but in you case you don’t you can download a Pre-built Virtual Machine that will make your life easier when setting up the environment.

Oracle Data Provider for .NET – ODP.NET

It will install the .NET client which communicates with the database – pretty obvious, I know, sorry for that. You can download it here.

I recommend you to really install it and not just copy the DLLs. I’m saying because I tried it. And it works! I just don’t think it’s worth having to copy DLLs with hundreds MBs into your project. In case you still want opt for that, you will need to copy the DLLs from Oracle Instant Client into your project’s bin folder.

Step 2 – Installing Libraries

I’m going to start by creating a new Project, going to File, New Project…New project window

Give the project a name and now, we install the libraries. I’m going to do that by using NuGet. It’s the easiest and fastest way and if you’ve been avoiding using NuGet all this time, then you should stop doing that!

From the NuGet Package Manager Console, run the following command:
Install-Package EntLibContrib.Data.OdpNetInstalling ODP.NET via NuGet Command

Or, you can right click the project and click on Manage NuGet PackagesInstalling ODP.NET via NuGet Window

For those who prefer manually install everything, download the EntLibContrib and add the necessary assembly references to the project.

Also, add a reference the Oracle.DataAccess.dll (you can find it in your oracle folder), usually c:\oracle\product\11.x.xx.x\odp.net\bin.
Adding Oracle Client library

In the end, regardless the installation type you chose, you should have your references as shown below:
References added to the project

Step 3 – Configuration

In the Web.Config / App.config, we need to tell Enterprise Library not to use its default implementation of Oracle Client and use the EntLibContrib one, which will work with ODP.NET. The code below does that by setting the Provider Mapping:

    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.505.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
  <dataConfiguration defaultDatabase="DefaultConnectionString">
      <add databaseType="EntLibContrib.Data.OdpNet.OracleDatabase, EntLibContrib.Data.OdpNet, Version=5.0.505.0, Culture=neutral, PublicKeyToken=null" name="Oracle.DataAccess.Client"/>

After that, also add the Connection String to your database, below is an example:

    <add name="DefaultConnectionString" connectionString="Data Source=;User ID=scott;Password=tigger;Persist Security Info=True;" providerName="Oracle.DataAccess.Client"/>

If you have problems configuring your connection string, see more different ways of setting it here.

Step 4 – Code

At this point, everything is set up and we can start coding. The code is pretty simple and it’s just to illustrate the concept.

First, I’m going to create a User class that will hold the user’s info.

using System;

namespace EntLib5ODP.NET
    public class User
        public int Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public DateTime? BirthDate { get; set; }
        public string Phone { get; set; }

Now, we need to ask Enterprise Library for a Database object. That Database object contains the methods that allows us to talk to the database. The line that does that is this one:

var database = EnterpriseLibraryContainer.Current.GetInstance<Database>();

With that object, we are able to call the method ExecuteReader which queries the database. One of the overloads of that method expect a Stored Procedure name and the arguments of that procedure. It will use the order of the arguments to bind them. My stored procedure has 3 arguments: id, name and result. Notice, we need to supply all parameters, so we set the ones we don’t want as null just to satisfy the parameters count, otherwise we would receive the error “The wrong number of parameters does not match number of values for stored procedure”. Here’s how we call the procedure to return a User by Id.

database.ExecuteReader("pkg_client.sp_list", id, null, null));

This will return an IDataReader, which is a HashTable with some helper methods. Now let’s put all together:

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

namespace EntLib5ODP.NET
    public class UserData
        private readonly Database database;

        public UserData()
            database = EnterpriseLibraryContainer.Current.GetInstance<Database>();

        public User GetById(int id)
            User user = null;

            using(var reader = database.ExecuteReader("pkg_client.sp_list", id, null, null))
                if (reader.Read())
                    user = MapUser(reader);
            return user;

        private static User MapUser(IDataReader reader)
            var user = new User
                Id = (int)reader["id"],
                Name = (string)reader["name"],
                Email = reader["email"] as string,
                BirthDate = reader["birthdate"] as DateTime?,
                Phone = reader["phone"] as string
            return user;

and the program running…
Program running

Download Sample

The links below contain the sample code with all that I showed you here. If you also want to test it on your machine, you need to download the SQL scripts and execute them in your database. The scripts will create a table with 3 items and a package with one procedure to list the items from that table. In case you’re using an schema, don’t forget to set them as well.

Dropbox => http://dl.dropbox.com/u/6963935/samples/EntLib5_ODP.NET/EntLib5ODP.NET.zip
GitHub => https://github.com/stanleystl/EntLib5ODP.NET
Scripts SQL => http://dl.dropbox.com/u/6963935/samples/EntLib5_ODP.NET/scripts.zip

Other Download Links

NuGet – http://nuget.codeplex.com/
Enterprise Library – http://entlib.codeplex.com/
EntLibContrib – http://entlibcontrib.codeplex.com/
ODP.NET – http://www.oracle.com/technetwork/topics/dotnet/index-085163.html
Oracle – http://www.oracle.com/technetwork/database/express-edition/overview/index.html
Pre-built Oracle Virtual Machine – http://www.oracle.com/technetwork/community/developer-vm/index.html

25 thoughts on “Enterprise Library 5 with ODP.NET

  1. Hi

    Thanks for the detailed step by step approach.
    I have question :

    How can i confirm that it did use the ODP.Net for oracle connectivity?

    Please clarify

    Thanks in Advance.

    1. Because you specified it as your provider in the App.config/Web.config
      You can debug the application and look at the concrete type of the classes it created to see if they inherit/use the Oracle classes. You will see they do. You can also see that the EntLib makes use of OracleDatabase which uses the Oracle classes which connects through ODP.NET.
      Another thing, try the Oracle Connection Strings from this link http://www.connectionstrings.com/oracle in the section “Using ODP.NET without tnsnames.ora”

  2. Question: how did you handle passing an Oracle Cursor type to the store procedure? Or how did you build your store procedure?

      1. Thank you very much, it was absent-minded of me not to check the previous post you had mentioned in the beginning.

  3. Suppose i have 5 environment with 4-5 cells each and around 10+ websites (mostly for WS). I am storing database connection strings in a separate encrypted file. How can i feed the connection string to the provider? The ConnectionString property of the database object is readonly.

    1. You will need to create a custom Provider by creating a new Class which inherits from OracleDatabase from the assembly EntLibContrib.Data.OdpNet. Then, take a look at the Step 3 of this post and change it to use your custom Provider instead of the EntLibContrib one.

      Your custom Provider implementation should override some method and define the ConnectionString, very similar to what I did on this post https://devstuffs.wordpress.com/2010/02/04/using-enterprise-library-with-oracle-odp-net-2/ where I also read the ConnectionString from a separate file.

  4. Hi

    I am using Oracle.DataAcces for Oracle 8I and Visual Studio 2010. When I try to start you project, I got following error: “Activation error occured while trying to get instance of type Database, key “””. Is there solution with database = EnterpriseLibraryContainer.Current.GetInstance(); and not with fabric?

    Thanks in Advance.

      1. Hey,

        I solved my problem with changing:

        1. Target framework from .Net Framework 4 to .Net Framework 3.5

        2. Section in App.config



        I am using the project from example with reference to the same version 5.0.505.0 of Enterprise Library.

        But, I still want to use .Net Framework 4 as Target framework.
        Where I can obtain set of Enterprise Library assemblies for using in .Net Framework 4?

        Thanks in Advance.

  5. I am sory. The new section for providerMappings is

    <add databaseType=”Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase, Microsoft.Practices.EnterpriseLibrary.Data” name=”Oracle.DataAccess.Client” />

    1. Congratulations for solving the issue and thank you for posting the solution.

      The sample I posted here is using .Net 4.0 though. Maybe the ODP.NET you are using is out of date?

      1. Hey,

        Thank you for fast answer.

        After all upgrades, now i am using Visual Studio 10 with Service Pack 1 and
        Enterprise Library 5.0.505.0.

        Unfortunally, the sample of this post dosn’t work on my computer.

        Have you any suggestions?

        Thanks in Advance.

  6. Hey,

    The message for the ActivationError exception is:

    Resolution of the dependency failed, type = “Microsoft.Practices.EnterpriseLibrary.Data.Database”, name = “DefaultConnectionString”.
    Exception occurred while: while resolving.
    Exception is: InvalidOperationException – The type Database cannot be constructed. You must configure the container to supply this value.
    At the time of the exception, the container was:
    Resolving Microsoft.Practices.EnterpriseLibrary.Data.Database,DefaultConnectionString

    Thanks in Advance.

      1. As I haven’t been working with EntLib for a while I completely forgot that.

        Thank you for posting the solution.

  7. I don’t see how you have gotten this to work without somehow defining an output parameter for the cursor. I contiinue to get the error “The number of parameters does not match number of values for stored procedure.”

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