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…
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.OdpNet
Or, you can right click the project and click on Manage NuGet Packages…
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.
In the end, regardless the installation type you chose, you should have your references as shown below:
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…
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
Like this:
Like Loading...
