Using Oracle db with Entlib

Topics: Data Access Application Block
Nov 21, 2014 at 11:49 AM
Hi All,
I am using Entlib 5.0 in a application with Oracle db as back end.
However i am facing some issue with fetching data from stored procedure which has Cursor as a output parameter.
I did some research online and they say "if you want to use Entlib with Oracle db then we have to do some code changes in Entlib source code and use it for Cursor type". Like replace System.Data.OracleClient with Oracle.DataAccess.Client and some other code changes i.e Oracle provider instead of Microsoft. (link http://www.codeproject.com/Articles/19581/Microsoft-Enterprise-Library-Data-Access-Block-DAA . however they are using Entlib 3.1)

Do we have any other way of achieving the same without doing any changes to Entlib source code.Or does Entlib 6.0 have these feature??

Please help.
Nov 21, 2014 at 7:04 PM
That article is old and no longer applies to Enterprise Library 4+.

What you will need is ODP.NET, Enterprise Library 5, and Enterprise Library Contrib - 5.0 for the ODP.NET Data Provider. Preferably, get these from NuGet: Enterprise Library 5.0 - Data Access Application Block, EntLibContrib 5.0 - Oracle ODP.NET Data

You can find the version 4 documentation: Oracle ODP.NET Data Provider. It applies to version 5 and 6 as well (except some specifics such as version number will be different).

The above assumes you are using Enterprise Library 5. If you move to the latest version (v6) then you would need the appropriate version 6 equivalents.

The EntLibContrib OracleDatabase implementation will take care of parameter prefixes and refcursors have to be named "cur_OUT".

~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to
Nov 24, 2014 at 9:53 AM
Hi Randy,
Thanks for help.

Do you have some sample code example for same? If yes, could you please share.
Also as you mention that refcursors have to be named as "cur_Out" so what if we have stored procedure already written and we can't change them.

Thanks in advance!
Nov 24, 2014 at 9:10 PM
If you can't change the cursor name then you can modify the EntLibContrib OracleDatabase class source code to use an appropriate name and use that custom assembly for the OracleDatabase.


~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to
Nov 27, 2014 at 11:49 AM
Edited Nov 27, 2014 at 11:51 AM
Hi Randy,
Thanks for helping!

I downloaded Enterprise Library 5.0 - Data Access Application Block, EntLibContrib 5.0 - Oracle ODP.NET Data and did settings in Web.config(after some online research). I was able to get output from the Oracle stored procedure.

I created one procedure in Oracle db 10g as :
procedure "USP_GETPROPOSALDATABYPRDID"
(prd_id IN NUMBER,
prdData OUT SYS_REFCURSOR )
is
begin

begin
open prdData for
select * 
from PROPOSAL_TABLE
where PRODUCTID=prd_id;
end;

end;
and below is the code in CS file
 DatabaseProviderFactory dbFactory = new DatabaseProviderFactory();
 Database db=dbFactory.Create("ConnectionString");

 DbCommand dbcmd=null;
 dbcmd = db.GetStoredProcCommand("USP_GETPROPOSALDATABYPRDID"); 
 db.AddInParameter(dbcmd, "prd_id", DbType.Int32,1);

 using (var reader = db.ExecuteReader(dbcmd))
             {
                 while (reader.Read())
                 {
                     ar.Add(reader[2]);//randomly adding 2nd col only.
                 }
             }
            gdvData2.DataSource= db.ExecuteDataSet(dbcmd);

            gdvData.DataSource = ar;//ar is ArrayList.
            gdvData.DataBind();
            gdvData2.DataBind();

lblInfo.Text = db.GetType().ToString();//To know the type of Database object created.
As you mentioned previously that parameter has to be named as "cur_OUT", however in this case parameter is named as "prdData" in stored procedure and it is still working. i did some debugging and observe that inside classs OracleDatabase.cs a call to method QueryProcedureNeedsCursorParameter(DbCommand) is made to check if parameter is needed or not and if yes it add a oracle parameter of RefCursor type and name it cur_OUT.

So does it mean name of parameter in stored proc dosen't make any difference??
I wonder how it handle two refcursor parameter.(Currently working on same).

Just wanted your opinion on same.
Once again thank you very much!!!