Connecting To Oracle using MS Enterprise Libraries

I have had many queries from my colleagues who would like to access Oracle from MS Enterprise Libraries. So I have set up this quick easy example.
Hope this helps you to get started.

I have setup the code in layers

Configuration in web/app.config
<configSections>
<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data"/>
</configSections>
<connectionStrings>
<!--ORACLE-->
<add name="MYDBNAME" providerName="System.Data.OracleClient" connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=YourServerName)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));User Id=USERNAME;Password=PASSWORD"/>-->
</connectionStrings>
<dataConfiguration defaultDatabase="MYDBNAME">
</dataConfiguration>


Now in your Data Access Layer
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using System.Data;
using System.Configuration;


namespace MYAPP.DAL
{
public class SampleQuery
{
Database db = DatabaseFactory.CreateDatabase();

// this method will return the version of the DB if you have a version table in the database
public int GetDBVersion()
{
DbCommand dbCommand = db.GetStoredProcCommand("GetDBVersion");
DataSet ds = db.ExecuteDataSet(dbCommand);
return Convert.ToInt32( ds.Tables[0].Rows[0][0].ToString());
}
public DataTable GetSomeData(string param1, string param2)
{
DataSet ds = null;

DbCommand dbCommand = db.GetStoredProcCommand("GETSOMEDATA");
db.AddInParameter(dbCommand, "param1", DbType.String, param1);
db.AddInParameter(dbCommand, "param2", DbType.String, param2);
try
{
ds = db.ExecuteDataSet(dbCommand);
}
catch { }

return ds.Tables[0];
}
}
}


Procedures in Oracle
create or replace
procedure GETDBVERSION (cur_OUT OUT sys_refcursor)
is
BEGIN
OPEN cur_OUT FOR
SELECT version FROM DBVersionTable;
END;

create or replace
procedure GETSOMEDATA (param1 IN VARCHAR2,param2 IN VARCHAR2,cur_out OUT sys_refcursor)
is
PID1 varchar2(36);
PID2 varchar2(36);
BEGIN
PID1 :=param1;
PID2 :=param2;
OPEN cur_OUT FOR
SELECT * from SomeTable
where FieldID1=PID2
and FieldID2=PID1 ;
end;