parameter discover with Oracle...

Topics: Data Access Application Block
Feb 21, 2007 at 10:06 PM
I'm guessing I've got a configuration issue, but for the life of me, haven't found it yet.

Does DAAB by default handle parameter discovery w/ ODP.Net as my provider (Oracle.DataAccess.Client). When I execute the db.DiscoverParameters(cmd), I get an error indicating it's not supported when it's a generic database. Am I missing something in my config file (being loaded from a data.config) that tells the DAAB that I'm dealing with Oracle.

My data.config is pretty basic...

<configuration>
<configSections>
<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null" />
</configSections>
<dataConfiguration defaultDatabase="Default" />
<connectionStrings>
<add name="Default" connectionString="Data Source=devbcs;User Id=finance_code;Password=fc001;"
providerName="Oracle.DataAccess.Client" />
</connectionStrings>
</configuration>
Feb 21, 2007 at 10:23 PM
Your configuration is fine.

By default, ODP.Net will use the GenericDatabase Class to communicate with the Oracle Database. If you take a peek at the source code, ParameterDiscovery is not supported in the GenericDatabase Class:

protected override void DeriveParameters(DbCommand discoveryCommand)
{
	throw new NotSupportedException(Resources.ExceptionParameterDiscoveryNotSupportedOnGenericDatabase);
}

The hope was that there would be enough time for the EntLib Team to create a specific Database Class for ODP.NET in Enterprise Library 3.0, but they ran out of time.

It is talked about more here:

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

Regards,

Dave

_________________________

David Hayden
Microsoft MVP C#
Feb 22, 2007 at 1:44 AM
Thanks, that makes sense. I just wanted to make sure I wasn't missing something. I had traced thru the code to the exact line you posted above and then just started thinking maybe something wasn't 'mapping' the ODP.net objects to the equivalent System.Data.Common object and that it was due to something I wasn't setting in the configuration.

It's pretty easy to use ODP.Net to do the discovery, so I can just 'roll my own'. It's too bad though, one of the main reasons I wanted to move to the DAAB was to get alot of this type capability coupled w/ the caching. If I have to add a ton of code to discover the params and then learn the caching blocks to handle the parameters that I pull back manually, it's gonna be a lot more work than I planned.

Thanks for the help (again)...I've been quite full of questions on my first day on this forum.

Great site though, glad I ran across it in my search today, the info here has been worth it's weight in gold for me today.

Chris
Feb 22, 2007 at 2:10 AM
Wait, as I read more...

So, can I change my provider to the MS oracle provider and get parameter discovery ?

Just reading some other stuff on MSDN and there are 3 database provider classes down deep in the DAAB, Sql, Oracle, and Generic...

I'll have to give this a try when I get back to the office tomorrow.

Chris
Feb 22, 2007 at 3:32 PM
If the MS Oracle Provider provides what you need by all means use it.

The ProviderName is "System.Data.OracleClient" and when detected will engage the DAAB to use the OracleDatabase Class.

The OracleDatabase Class appears to support Parameter Discovery:

protected override void DeriveParameters(DbCommand discoveryCommand)
{
	OracleCommandBuilder.DeriveParameters((OracleCommand)discoveryCommand);
}

Regards,

Dave

_____________________

David Hayden
Microsoft MVP C#
Feb 22, 2007 at 5:11 PM
Edited Feb 22, 2007 at 5:17 PM
Thanks, that was exactly where I was heading. I ended up extending the block this morning, w/ some help from a coworker and some C# code found over on Tech Net. Basically, just created a new OracleOdpDatabase class derived from GenericDatabase and then overloaded the db.DeriveParameters similar to how it's being overloaded in the OracleDatabase.

Just had to tweak my configuration file to handle the provider mapping to the new class.

Here is the source of my solution, for anyone interested...although we converted it to VB.Net, same result.
http://forums.oracle.com/forums/thread.jspa?threadID=399700&tstart=90

I'm learning...

Chris
Feb 23, 2007 at 3:06 AM
Can one of you guys clarify yet another question I have. Regarding parameter caching. What is the scope of the cache? When it is destroyed/cleared?

I am basically isolating my data access layer completely from the UI and I have share/stateless helper functions that wrapper DAAB functionality. With each call to one of my functions (get table, update table, execute proc) I create a database object and do whatever. The database object is local to each function call. In the case of executing a proc, I'm doing discovery, which should be adding it's parameters to the parameter cache.

I'm just curious what the life of that cache is since I'm only keeping that database alive for the life of my function call?

Thanks,
Chris
Feb 23, 2007 at 2:40 PM
The ParameterCache is a static member of the Database Class so it essentially lives the entire life of your application. As new Database Instances are created and garbage collected throughout the lifetime of your application, the ParameterCache lives on.

The ParameterCache is never cleared. It is a Hashtable that you need to clear manually if you feel it necessary:

Database.ClearParameterCache();

Regards,

Dave

_______________________

David Hayden
Microsoft MVP C#
Feb 23, 2007 at 3:08 PM
Great, thanks for answering that.

Chris