DAAB- Multiple Ref Cursors - DBCommand

Topics: Data Access Application Block
Mar 11, 2009 at 6:52 PM
Hi,

I am trying to code database independent code in my data access layer.. using System.Data.Common  namespace for it..

Currently we have Oracle 10g database from which I need to retrieve data. I was able to retrieve data if my stored procedures returns only one ref Cursor( as DAAB adds a ref cursor named cur_OUT automatically)..

I was wondering if there is any workaround  to handle stored procedures returning more than one ref cursor.??

I would appriciate if any one could give me some direction over it..


Thanks..
Mar 12, 2009 at 6:29 AM

Hi,

Please look at these thread links:  They might be helpful. J

http://entlib.codeplex.com/Thread/View.aspx?ThreadId=7470

http://entlib.codeplex.com/Thread/View.aspx?ThreadId=11176

 

Mar 16, 2009 at 7:06 PM
Thank you for your response...

The approach in following link seems to be useful..

http://entlib.codeplex.com/Thread/View.aspx?ThreadId=11176

but, I could not get it work with .net 3.5, ODP 10.2 and EntLib 4.0.. I would appeciate if any one could give me some direction over it..

thanks,


 




Mar 17, 2009 at 2:52 AM
Do you get the same error PLS-00306: wrong number or types of arguments in call to ... as discussed in the http://entlib.codeplex.com/Thread/View.aspx?ThreadId=11176?

Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
Mar 17, 2009 at 3:15 PM
Edited Mar 17, 2009 at 3:35 PM
I tried to replicate as mentioned in "http://entlib.codeplex.com/Thread/View.aspx?ThreadId=11176?"

I created wrappers to create cursor parameters and get datareader back as mentioned below: I used ODP.net here. I do not see any OracleCursor type in System.Data.OracleClient namespace

public static CreateCursorParameter(string cursorName)
{
    OracleParameter param = new OracleParameter(cursorName, OracleDbType.RefCursor, 0, ParameterDirection.InputOutput, true, 0, 0, String.Empty, DataRowVersion.Current, null);
    return param;

 //System.Data.OracleClient.OracleParameter xy = new System.Data.OracleClient.OracleParameter(cursorName, OracleType.Cursor, int.MaxValue, ParameterDirection.InputOutput, null, DataRowVersion.Current, true, Convert.DBNull);
//return xy;
}

public static IDataReader CreateDataReaderFromCursor(object cursor)
{
    OracleRefCursor oracleCursor = (OracleRefCursor)cursor;
    return oracleCursor.GetDataReader();
}

 

 

 

I tried using these wrappers as mentioned below:

 

Database db = DatabaseFactory.CreateDatabase("DBConnection");
DbParameter
x = DBWrapper.CreateCursorParameter("cur_OUT");
DbParameter y = DBWrapper.CreateCursorParameter("cur_OUT1");
DbParameter z = db.DbProviderFactory.CreateParameter();
z.DbType =
DbType.Int32;
z.Direction =
ParameterDirection.Input;
z.ParameterName =
"id";
z.Value = 263;
DbCommand c = db.GetStoredProcCommand("spname",z, x, y);
db.ExecuteNonQuery(c);

 

 

Following is the error message I got when executing  "db.ExecuteNonQuery(c);" statement:

"Failed to convert parameter value from a OracleParameter to a Decimal."


I would appreiciate if any one could give me some direction over it.

Mar 20, 2009 at 2:31 AM

Hi,

ODP.Net wrapper on Oracle client is not supported on Ent Lib as of the moment. Just found out through this link: http://entlib.codeplex.com/Thread/View.aspx?ThreadId=39533. J

 

 

Mar 20, 2009 at 3:28 PM
Going back to original issue

1. Is there any workaround in DAAB where it can read data from oracle stored procedure which returns multiple ref cursors[ sticking to System.Data.Common namespace classes where ever possible]

2. Writting wrapper classes to create ref cursor parameters and reading values from it seems to be good idea to isolate db dependent code.. Is there any way we can implement these wrappers using classes provided in System.Data.OracleClient[ rather than using ODP.net] ..

I would appreciate if any one could give me some direction over it..
Mar 23, 2009 at 1:50 AM
Edited Mar 23, 2009 at 2:13 AM
1.  The workaround is given in the links provided by MayonMan.  Regarding the error you're getting, is it the complete error message?  What are the parameter types of your stored procedure?  
2.  The wrapper class are already written in System.Data.OracleClient, not in ODP.    


Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
Mar 23, 2009 at 3:29 PM
Thank you for the support extended..

Approach 1: I tried as mentioned in "http://entlib.codeplex.com/Thread/View.aspx?ThreadId=7470". This approach is working fine for me.. I need to convert in to Dataset rather than reading reach ref curosr as a datareader.

Approach 2: I have trouble implementing the approach mentioned in "http://entlib.codeplex.com/Thread/View.aspx?ThreadId=11176". In the following code mentioned in the link mentioned...

public static IDataReader CreateDataReaderFromCursor(object cursor)
 {
OracleCursor oracleCursor = (OracleCursor) cursor;
return oracleCursor.GetDataReader();
}

I could not locate "OracleCursor" class in System.Data.OracleClient namespace. Where as ODP.net has class named "OracleRefCursor".

I will go ahead with Approch 1 as it solves the issue for now..


Mar 24, 2009 at 2:06 AM
It's because the provider used there is OraDirect, not the System.Data.OracleClient.  You can create an IDataReader by calling ExecuteReader against a command object (command.ExecuteReader()). 


Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
Jul 27, 2009 at 5:58 PM

http://www.codeproject.com/KB/database/MultipleRefCursors.aspx