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

Pre-built Virtual Machines

VirtualBox emulating Linux and running Oracle

Today I wanted to test a project which connects to an Oracle database but I just didn’t feel like installing Oracle on my machine (I don’t often use it and I formatted my OS recently). Then I remembered have read about the use of Pre-built VMs to test new OS’ and I thought: “Maybe I can find not only a fresh OS installation as well as Oracle already installed and configured”. And… guess what?! I found it!

It’s amazing how fast I had a new Operation System and Oracle up and running on my machine. And the best part is: whenever I think that I don’t need it anymore I can just delete it.

No worries about uninstalls leaving unused files in my machine.

There are many other kinds of pre-build systems around the internet. Don’t waste your time installing everything one by one or installing something in your machine just for testing purposes.

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

DbProviderFactory problem

I recently saw a new problem that showed up as the message below:

Unable to find the requested .Net Framework Data Provider.  It may not be installed.

After a long search, i found that the machine.config file (at Windows\Microsoft.Net\Framework\v2.0.50727\config) wasn’t correctly configured with my actual provider (in my case Oracle.DataAccess.Client) thus i had to add the following to my web.config:

	<system.data>
		<DbProviderFactories>
      <remove invariant="Oracle.DataAccess.Client" />
      <add name="Oracle Data Provider for .NET" invariant="Oracle.DataAccess.Client" description="Oracle Data Provider for .NET" type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=2.102.2.20, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
		</DbProviderFactories>
	</system.data>

Alternatively, you can add that to your machine.config file if you want it for all of your applications and not just for one specifically.

Cursor Oracle

When I started coding pl/sql this little piece of code really helped me so i decided to publish it here.

DECLARE 
    /* DECLARANDO O CURSOR DE PRODUTOS */
    CURSOR C_PRODUTOS IS 
        SELECT * FROM TB_PRODUTOS;
        
    /* DECLARANDO UMA VARIAVEL QUE SERA O REGISTRO DA TABELA */
    REG_PRODUTO C_PRODUTOS%ROWTYPE;
    
BEGIN
    /* ABRE CURSOR */
    OPEN C_PRODUTOS
    
    LOOP
        /* LÊ UM REGISTRO DO CURSOR */
        FETCH C_PRODUTOS INTO REG_PRODUTO;
        
        /* ABANDONA O LOOP CASO SEJA O FINAL DO CURSOR */
        EXIT WHEN C_PRODUTOS%NOTFOUND;
        
        /* 
        AQUI SERA INSERIDO O CODIGO QUE IRA MANIPULAR OS DADOS COMO: 
            - INSERIR EM OUTRA TABELA, 
            - FAZER ALGUM CALCULO, 
            - ETC. 
        */
    
    END LOOP;

Split Function for Oracle

create type ty_str_split as table of varchar2(100)
/

create or replace function str_split_as_table (p_str   in varchar2
                                              ,p_delim in varchar2 default ',') 
return ty_str_split pipelined as

  v_tab    ty_str_split;
begin
  v_tab := str_split(p_str,p_delim);
  
  for i in 1..v_tab.last loop
    pipe row(v_tab(i));
  end loop;
  return;
end;;
/

create or replace function str_split  (p_str   in varchar2
                                     ,p_delim in varchar2 default ',') 
return ty_str_split as
  v_str      varchar2(32767);
  v_fields   pls_integer;
  v_substr   varchar2(32767);
  v_return   ty_str_split := ty_str_split();
begin
  v_str := p_delim||trim(p_delim from p_str)||p_delim;
  v_fields := length(v_str) - length(replace(v_str,p_delim,'')) - 1;
  v_return.extend(v_fields);
  
  for i in 1..v_fields loop
    v_substr := substr(v_str
                      ,instr(v_str,p_delim,1,i)+1  
                      ,instr(v_str,p_delim,1,i+1) - instr(v_str,p_delim,1,i) -1);
    v_return(i) := v_substr;
  end loop;
  return v_return;
end;;
/        

select *
from   table(str_split_as_table('asd,456,trfgh,4567'));

select str_split('asd,456,trfgh,4567') from dual;

Using Enterprise Library 2 with Oracle ODP.NET

If you are using Enterprise Library 5 then you want this link Enterprise Library 5 with ODP.NET.

The Microsoft has discontinued the support for their Oracle client library, found in the namespace “System.Data.OracleClient“, and because of that many developers have switched to the Oracle library which they call ODP.Net, found in the namespace “Oracle.DataAccess.Client“. Oracle also claims that their client performs better. To be honest, I didn’t have performance issues with any of those libraries but I decided to follow with the ODP.NET, so let’s see how we can use it.

We first need to download the ODP.NET library. After that, find the files and add the “Oracle.DataAccess” as a referece into your project. You may be able to find it at the .Net tab from the Add Reference dialog.

Add Reference Dialog

Then we must configure the web.config file. You can use the Enterprise Library Configuration software to modify it or open the file and copy the code below:

<configSections>
    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data" />
  </configSections>
  <dataConfiguration defaultDatabase="FooDatabase">
    <providerMappings>
      <add databaseType="Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase, Microsoft.Practices.EnterpriseLibrary.Data"
        name="Oracle.DataAccess.Client" />
    </providerMappings>
  </dataConfiguration>
  <connectionStrings>
    <add name="FooDatabase" connectionString="User ID=my_user;Password=password;Data Source=my_schema"
      providerName="Oracle.DataAccess.Client" />
  </connectionStrings>

Then, to call the Database I’ve created an abstract class containing a property which return the Database object.

    public abstract class DataBase
    {
        protected Database DB
        {
            get
            {
                return DatabaseFactory.CreateDatabase();
            }
        }
    }

That done,  we can extend the data classes of our application and use that property when we need. See  the example below:

    public class UserData : DataBase
    {
        internal void Delete(int userId)
        {
            DB.ExecuteNonQuery("PKG_USER.DELETE", userId);
        }

For output parameters, pass null and then use DB.GetParameterValue to get the returned value.

You can notice that in my web.config, I’ve set a provider map. One issue that came up in the company I work is that we don’t store the database configuration in the web.config, instead we have only one file in the server with the connection parameters of all applications we have and only the server administrator can see/change it. This was my issue and you may have a diffent one that the solution i had can help as well.

I end up creating my own provider mapping file. My web.config file now looks like this:

<dataConfiguration defaultDatabase="FooDatabase">
    <providerMappings>
      <add databaseType="MyNamespace.Oracle.OracleOdpDatabase, MyNamespace"
        name="Oracle.DataAccess.Client" />
    </providerMappings>
  </dataConfiguration>
<connectionStrings>
    <add name="FooDatabase" connectionString="User ID={foo_user};Password={foo_password};Data Source={foo_source}"
      providerName="Oracle.DataAccess.Client" />
  </connectionStrings>

See that my connectionString now have variables instead of the values and my providerMappings is changed to a custom one.

The class that handles my custom providerMapping is written like this:

using System.Collections.Specialized;
using System.Configuration;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
using Oracle.DataAccess.Client;

namespace MyNamespace.Oracle
{
    [DatabaseAssembler(typeof(OracleOdpDatabaseAssembler))]
    public class OracleOdpDatabase : GenericDatabase
    {
        public OracleOdpDatabase(string connectionString, DbProviderFactory dbProviderFactory)
            : base(connectionString, dbProviderFactory)
        {
        }

        protected override void DeriveParameters(DbCommand discoveryCommand)
        {
            OracleCommandBuilder.DeriveParameters(discoveryCommand as OracleCommand);
        }
    }

    public class OracleOdpDatabaseAssembler : IDatabaseAssembler
    {
        private static NameValueCollection connectionStrings = new NameValueCollection();

        public Database Assemble(string name, ConnectionStringSettings connectionStringSettings, IConfigurationSource configurationSource)
        {
            DbProviderFactory providerFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);

            // checks whether there is a connection string already or needs to read it again
            if (connectionStrings[name] == null)
                connectionStrings[name] = ReplaceConfigVariables(connectionStringSettings.ConnectionString);

            return new OracleOdpDatabase(connectionStrings[name], providerFactory);
        }
    }
}

You can see that i replaced the connection string variables before passing it to my custom provider class. I don’t show the replacing code here so we don’t lose focus.

Update: I created a sample project so you can download the source code, take a look at it and test it quickly: Download the Sample

References:
http://entlibcontrib.codeplex.com/wikipage?title=OracleDataProvider41
http://forums.oracle.com/forums/thread.jspa?threadID=399700