DAAB: ODP.Net Provider - will this be part of release?

Topics: Data Access Application Block
Feb 8, 2007 at 10:21 AM
ODP.NET provider is mentioned as a high priority several times recently, but, since the latest Jan 2007 CTP, there appears to be no mention of it.

Has this dropped off the radar and been de-scoped? Or is someone beavering away at it as we speak?

I ask since my company is desperate to get an ODP.NET provider and we need to make some strategic decisions very soon about our data access strategy.
Feb 8, 2007 at 12:55 PM
Edited Feb 8, 2007 at 12:56 PM
A simple yes or no would suffice.... please... don't keep me on the edge of my seat! #;-)
Feb 8, 2007 at 4:08 PM
Sorry I'm afraid it's a No for v3, it was lowish on the list and we didn't get time. This may be a nice project for the community to work on?

Feb 9, 2007 at 7:47 AM
Edited Feb 9, 2007 at 7:48 AM
We might be interested.. how do we get involved.. anyone else interested?
Feb 9, 2007 at 10:44 AM


This is a discussion on the ODP.NET. Perhaps you could contact this person for some extra information on the subject.

Best regards,
Feb 22, 2007 at 1:51 AM
I'm interested. I'm working on a project that I had hoped to move toward using the DAAB, and it needs alot of the capability of ODP.Net that isn't evidently in the 2.0 DAAB block. Particularly the parameter discovery part, but that's pretty straight forward. David Hayden clarified alot of this for me earlier today, but I do find it strange that some of the easier stuff was not included, like parameter discovery for one, it's a pretty direct call to get that and pass it back. I just need to do more looking at the existing DAAB code to see how it does parameter discovery and see if I can roll ODP.net in w/o much trouble.

I'm either going to have to scrap the DAAB at this point, or build ODP.Net stuff around it. I'm not exactly the sharpest C# developer (no pun intended) and everything in my actual project will be VB.Net. But I'm more than willing to help w/ the ODP.Net side of things as much as I can.

Jun 23, 2007 at 3:26 AM
Hi Chris!

Can you email me what you have put together and learned?


Jun 25, 2007 at 8:58 PM

Sorry just now replying, just happened by the thread today and noticed your post.

To sum up, I've got it all working by simply extending the DAAB to use the ODP provider and support param discovery (which I take no credit for, found it in C# here or somewhere else, I forget, and ported it to VB.net.

Beyond that, everything else seems to work out of the box. We are running both 9i and 10g, seems to fully behave on both platforms. Haven't tested transactions yet, but getting ready to test some code that uses the DAAB transactions.

I'd be glad to share what I've done and learned so far, but it's pretty in-depth at this point now, 4 months of development incorporated in all that I've learned.

It may be of benefit to share it here, rather than over email, but it's up to you (and anyone else that wants to discuss it further).

Is there a need for full ODP.net support w/ it's own provider class in the EntLib, dunno, out of my league. Maybe one of the guru's here could answer that. All I know is that w/ this simple class extension I found, I'm able to do anything I've wanted to against ODP.net using generic System.Data.Common objects.

Jun 25, 2007 at 9:16 PM
Again, I take no credit for this, other than the porting to VB.net. I can't find the original place that I ran across this in C#. I think it was over on Oracle TechNet, but don't quote me on that.

This is the crux of what works, it just extends the DAAB to support the ODP.Net data provider. I have a DbHelper class that then wrappers the key DAAB features that I need in shared functions for the rest of my project to use. You also have to have a provider mappings section in your app.config to handle this, example of this is below the code...MyDatabaseComponents would be the name of my main project that houses all this code...you'll see that in the config entry as well.

- - - - -
<DatabaseAssembler(GetType(OracleOdpDatabaseAssembler))> _
Public Class OracleOdpDatabase
Inherits GenericDatabase

Public Sub New(ByVal connectionString As String, ByVal dbProviderFactory As DbProviderFactory)

MyBase.New(connectionString, dbProviderFactory)

End Sub

Protected Overrides Sub DeriveParameters(ByVal discoveryCommand As DbCommand)

OracleCommandBuilder.DeriveParameters(CType(discoveryCommand, OracleCommand))

End Sub

End Class

Public Class OracleOdpDatabaseAssembler
Implements IDatabaseAssembler

Public Function Assemble(ByVal name As String, ByVal connectionStringSettings As ConnectionStringSettings, ByVal configurationSource As IConfigurationSource) As Database Implements IDatabaseAssembler.Assemble

Dim providerFactory As DbProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName)

Return New OracleOdpDatabase(connectionStringSettings.ConnectionString, providerFactory)

End Function

End Class
- - - - -

- - - - -
<add databaseType="MyDataComponents.OracleOdpDatabase, MyDataComponents, Version=, Culture=neutral, PublicKeyToken=null"
name="Oracle.DataAccess.Client" />
<add name="MYCONNECTION" connectionString="Data Source=somedatasource;User Id=someuserid;Password=somepassword;"
providerName="Oracle.DataAccess.Client" />
Jun 26, 2007 at 11:53 AM
Edited Jun 26, 2007 at 2:11 PM
Chris, was OracleOdpDatabase the only that in the ODP namesapce?

We use Oracle extensively here and I would be interested in assisting with extending the EntLIb to include ODP. Interest on this project can probably also be gained from the Oracle ODP Forums found here: http://forums.oracle.com/forums/forum.jspa?forumID=146

Like it was said previously, the features of the ODP should be included in the provider, such as:

  • Support for Oracle Data Types
  • Failover
  • Parameter Discovery
  • Notifications
  • XML Queries

Also, isn't there a EntLib Extensions codeplex site for providing extensions to the EntLib? I couldn't find it. Or should the work be done here?
Jun 26, 2007 at 2:11 PM
Edited Jun 26, 2007 at 2:11 PM
As a starting point, I have ported the Microsoft Oracle Provider to use ODP.

If any of the EntLib team that was involved with creating the Oracle Provider can provide some insight as to how we should proceed, that would be great.
Jun 26, 2007 at 2:39 PM
Greif, not sure I understand your first question about OracleOdpDatabase. That is just the class name used to 'extend' the DAAB so that it goes there for the overload on DeriveParameters. Otherwise, it doesn't really do anything else. The provider mappings in the config file tie in w/ that.

Regarding your list of things above, if the DAAB doesn't already support those types of functionality against other databases, I'm not sure how I see them fitting into a full blown extension of the DAAB for ODP.net. Alot of this level of design and coding really runs on a higher plane than my brain operates. I guess I could see data types and parameter discovery, but the other things have pretty specific implementations and I would be lost as to how you'd really be able to include that into the generic DAAB design.

I didn't really want my final product to be Oracle dependent, so support for Oracle datatypes wasn't on my 'need to do' list. I simply wanted parameter discovery for convenience and then everything else falls out to System.Data.Common objects. I haven't run across a need to support other Oracle datatypes. Numeric and Varchar come across fine, REF CURSOR output comes across as type Object and loads to datasets just fine, so again, no need (at least for my needs) to know it's a cursor.

Again, I'd be glad to help out however I can, but my 'skills' are limited when it comes to stuff that's as complex as the data provider C# code down in the DAAB.


Jun 26, 2007 at 3:12 PM
I was just curios to see if you created a totally separate data provider - one which utilizes ODP.NET - for the EntLib or you extended the built-in Oracle provider.

I was assuming you created a separate provider and was wondering what other classes you had implemented.
Jun 26, 2007 at 4:36 PM
Edited Jun 26, 2007 at 4:40 PM
Ah, got it...no, not a separate/new DAAB data provider. I started down that route and it got so nasty I took a step back and said 'what do I really need'.

The answer to my question was that I wanted to deal with generic .NET data objects at the UI level (System.Data.Common), and I wanted parameter discovery in my business/data layer tso that I could again use 'generic' DbCommand objects. I did not want to be (heavily) tied to a particular database platform. From there, I decided that the Generic Database provider gave me everything I needed w/ the exception of parameter discovery.

So, I extended the Generic Database provider.

I only create a class (OracleOdpDatabase) that overloads the Generic Database Provider to allow for parameter discovery. I simply implemented the ODP.net call inside an overloads on DeriveParameters to use an Oracle Command object in ODP. That's all it does. The provider mapping in the config file then allows this class to then connect using ODP.net (Oracle.DataAccess.Client).

Hope that makes sense. All the code I have implemented is what I posted above, then a DbHelper class that is just a bunch of public shared functions, subs, etc.

Jun 27, 2007 at 4:19 AM
Hi Chris!

1. So, when you call an Oracle Stored Proc now, if the proc is named the same as it was in MSSQL with the same params and essensially datatypes, your code that calls the procs will remain the same? Please discuss this a bit so I can see how it is implemented. Maybe a code snippet would be good for both Oracle and MSSQL of a business object (or a method) calling to the DAAB to execute a stored proc for both Oracle and MSSQL. I was curious if there were some changes that would be needed from one to the other.

2. What features can you leverage using ODP.NET over the Microsoft Oracle Provider and still use the implementation of the DAAB as you have it?

Jun 27, 2007 at 2:48 PM
1. Yes, in theory. We use packages in Oracle, so I'd have to do some minor tweaking there to change to a straight procedure name. But otherwise yes, I could just change the connection string and data provider and go. I'll follow-up w/ a code snipped, I'm not in front of the code right now. It'll just be an Oracle code snippet, but it would be the exact same for a MSSQL proc (w/ the exception of the name of the proc as mentioned above).

2. I'd have to go back and look at that again, I seem to recall there were some issues w/ REF CURSORS w/ the MS provider, but don't quote me on that. By having overload capability, I could also leverage other pieces of ODP.net that are definitely not supported in the MS provider like batching up records and using the PLSQL Assoc Array (haven't had a need for it yet, but i did test it awhile back and it worked wonderfully fast). Also, as posted above, if ODP.Net has some other features (many of which I'm not aware of) you could leverage those if you have a custom provider written for it.

Again, I'm not using anything from ODP.net other than param discovery. Otherwise, I'm using the capabilities of the Generic Database Provider. I am only assuming at this point that my transaction stuff is going to work. I've written it to support it thru DbTransactions and the Generic Database Provider, but I have not tested the code yet, still messing w/ other stuff and transactions have been on a back burner. I may have to overload that as well when I get around to it, but haven't had time to test it yet.

Jun 27, 2007 at 4:17 PM
Hi Chris.

it would be nice if you could share with us some samples so we could get a better understanding of this
Jun 27, 2007 at 5:07 PM
Edited Jun 27, 2007 at 5:09 PM
Here is a sample of what I'm doing. This isn't by any means rocket science, I tend to try and not overthing OO design and always try to just plug existing stuff together logically, This doesn't necessarily follow any kind of pattern. Some folks might even think I'm kinda butchering the use of the DAAB (dunno) or that the DAAB is overkill based on what I'm doing. I'm simply making use of the EntLib in a way that makes sense for me and my client, YMMV.

In my Business Layer, I make the calls to my DbHelper class (which is contained in it's own project/dll). My Data Layer dll is simply comprised of references to the DAAB, wrappered w/ my DbHelper class, I only reference the DbHelper from my business layer, no direct access to the DAAB. The DbHelper simply returns objects from System.Data.Common.

So, let's take something like loading a dataset from a stored procedure.

One of my Business Layer call would look something like this. I can then do what I want w/ _ds, return it to the UI for display, process data, whatever. Regarding the stored proc itself, it's simply a proc w/ 1 input parameter and a half-dozen or so output REF CURSORS. Pretty standard PLSQL stuff.

Dim _cmd As DbCommand
cmd = DbHelper.GetCommand("pkgname.prcname", CONNECTIONNAME)
With _cmd
.Parameters("pcustomerid").Value = "12345"
End With
Dim _ds As New DataSet
ds = DbHelper.LoadDataSet(cmd, CONNECTION_NAME)

This references two procedures in my DbHelper (.GetCommand and .LoadDataSet), which both in turn reference the .GetDatabase procedure.

Public Shared Function GetCommand(ByVal commandText As String, ByVal connectionName As String) As DbCommand
Dim _db As Database = GetDatabase(connectionName)
Dim cmd As DbCommand = _db.GetStoredProcCommand(commandText)
Return cmd
Catch ex As Exception
Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, EXCEPTION_POLICY)
If (rethrow) Then
End If
Return Nothing
End Try
End Function

Public Shared Function LoadDataSet(ByVal cmd As DbCommand, ByVal connectionName As String) As DataSet
'assumes params have already been set
Dim _db As Database = GetDatabase(connectionName)
Dim _ds As DataSet = _db.ExecuteDataSet(cmd)
Return _ds
Catch ex As Exception
Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, EXCEPTION_POLICY)
If (rethrow) Then
End If
Return Nothing
End Try
End Function

Public Shared Function GetDatabase(ByVal connectionName As String) As Database
Return DatabaseFactory.CreateDatabase(connectionName)
Catch ex As Exception
Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, EXCEPTION_POLICY)
If (rethrow) Then
End If
Return Nothing
End Try
End Function
Jun 27, 2007 at 5:10 PM
The posting process here at codeplex seems to be goofing up some of the code, but you get the idea...
Mar 27, 2009 at 4:09 PM
Edited Mar 27, 2009 at 9:18 PM
See this work item - Grief posted a zip of his implementation of ODP.NET provider for DAAB.  Its attached to a work item in the EntLib Contrib project.  http://www.codeplex.com/entlibcontrib/WorkItem/View.aspx?WorkItemId=3167
Mar 30, 2009 at 9:58 AM


Hope this will help on issue with ODP.Net. Thanks for the link. J
Apr 1, 2009 at 9:54 AM
Hi everyone

We want to use DAAB 4.1 with Oracle ODP.net ver 11g. Can you help me iwth the starting point ? Is 4.1 compatible with Oracle ODP or do we need to write our own wrappers. PLease guide.

Apr 1, 2009 at 10:32 AM
Answer in this thread - http://entlib.codeplex.com/Thread/View.aspx?ThreadId=51935

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
Aug 26, 2009 at 1:28 PM

It seems that those that use Oracle will not be able to continue with DAAB unless the ODP.Net is supported SOON.  Microsoft is deprecating  OraclClient so I don't see how we can decide on using EL when this item is not on the radar anymore.  It's a show stopper

Aug 26, 2009 at 1:28 PM

It seems that those that use Oracle will not be able to continue with DAAB unless the ODP.Net is supported SOON.  Microsoft is deprecating  OraclClient so I don't see how we can decide on using EL when this item is not on the radar anymore.  It's a show stopper