Enterprise Library 5 with ODP.NET

Enterprise Library 5 with Oracle

Introduction

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 11.1.0.7.20, and EntLibContrib 5. Different versions of Oracle and ODP.NET may work as well.

Step 1 – Environment

Oracle

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:

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

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

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

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

About these ads

23 responses to “Enterprise Library 5 with ODP.NET

  1. Thiago (Fenix)

    Congratulations, I already saw working this in my company and It very well.

  2. Yuva

    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.

    • devstuffs

      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”

  3. Anvil

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

  4. Renaud Langis

    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.

  5. Dmitry

    Hi

    I am using Oracle.DataAcces 2.102.2.20 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.

  6. Dmitry

    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” />

    • devstuffs

      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?

      • Dmitry

        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.

      • devstuffs

        When you receive the ActivationError exception, does it have any InnerException?

  7. Dmitry

    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.

  8. Dmitry

    The message that i have posted to you is message for InnerException

  9. JP

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

  10. But this doesn’t play with Enterprise library caching / logging modules…. version 505 will clash with 414 version available for download

  11. oranet

    Thank you for this..it helped me configure odp.net with enterprise library version 5!!

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

Follow

Get every new post delivered to your Inbox.

Join 79 other followers

%d bloggers like this: