Using EL DAAB to query Oracle SP

Topics: Building and extending application blocks, Data Access Application Block, Enterprise Library Core, General discussion
May 16, 2008 at 4:14 PM

I am looking for some help in quering stored procedures from SQL Server, Oracle and DB2 using EL DAB for a c# app. I need to be able to call a stored procedure that returns a result set. So far i could only figure out SQL but no luck with Oracle and DB2..

 

I need some help getting this to work with an Oracle database. Here are my constraints:

1)      The C# code has to be database-independent; I don’t know ahead of time what database the customer will be using.

2)      The result set will have to come from a stored procedure (and not directly from a SQL statement)

 

So far I’ve found some code samples on calling refcursurs using the OracleClient classes but that kills the purpose of DAAB. I will have to write seprate code for each database usign diffrent provider.

 

Can anyone on your team help with some samples or links to samples that meet my criteria?

May 16, 2008 at 9:22 PM

Hi,

Take a look at the OracleUpdateDataSetFixture class from the DAAB's test project. It uses a stored procedure that looks like this:

create procedure RegionSelect (cur_OUT OUT PKGENTLIB_ARCHITECTURE.CURENTLIB_ARCHITECTURE) as
BEGIN OPEN cur_OUT FOR select * from Region Order By RegionId; END;

to load a DataSet doing

   47         protected override DataSet GetDataSetFromTable()

   48         {

   49             DbCommand selectCommand = db.GetStoredProcCommand("RegionSelect");

   50             return db.ExecuteDataSet(selectCommand);

   51         }

Would that fit your needs?

Regards,
Fernando

<!--EndFragment-->

 

 


cooliou wrote:

I am looking for some help in quering stored procedures from SQL Server, Oracle and DB2 using EL DAB for a c# app. I need to be able to call a stored procedure that returns a result set. So far i could only figure out SQL but no luck with Oracle and DB2..

 

I need some help getting this to work with an Oracle database. Here are my constraints:

1)      The C# code has to be database-independent; I don’t know ahead of time what database the customer will be using.

2)      The result set will have to come from a stored procedure (and not directly from a SQL statement)

 

So far I’ve found some code samples on calling refcursurs using the OracleClient classes but that kills the purpose of DAAB. I will have to write seprate code for each database usign diffrent provider.

 

Can anyone on your team help with some samples or links to samples that meet my criteria?