Binding query parameters by name with ODP.NET

Question

I'm currently using the Microsoft ADO.NET provider for Oracle (System.Data.OracleClient). I'm aware that it is certainly not the best Oracle provider available and that it will soon be deprecated, I should be using Oracle's ODP.NET instead. The reason why I still use the MS provider is because ODP.NET binds parameters by position, not by name. This can really be a PITA when you use many parameters in a query, because you have to be careful to add them in the right order, which can easily lead to bugs. It's also annoying when you use the same parameter multiple times in the same query, for instance :

SELECT A,B,C FROM FOO WHERE X = :PARAM_X OR :PARAM_X = 0

With ODP.NET, I have to add two parameters to the OracleCommand, which I think is stupid...

ODP.NET's OracleCommand has a property to change that default behavior : BindByName. When set to true, the parameters are bound by name, which is what I want. Unfortunately this doesn't really help me, because :

• It is set to false by default
• I almost never use concrete ADO.NET classes explicitly, I prefer to use ADO.NET 2.0 abstraction layer (DbProviderFactory, DbConnection, DbCommand...) to reduce coupling to any specific RDBMS. So I don't have access to the BindByName property, unless I cast explicitly to OracleCommand, losing all the benefits or the abstraction.
• When using an ASP.NET SqlDataSource, I don't create the DbCommand myself, so I don't get a chance to set BindByName to true (I could do it in the Selecting event, but it really is a pain to do it for each SqlDataSource...)

How am I supposed to handle that issue ? Is there a BindByNameByDefault setting somewhere ? (I didn't find anything like that, but I may have missed it...)

Answer 1

I think you can create your own provider that uses the defaults you want to use. You could create that provider easily by inheriting all the classes from odp.net, just adjust some properties like BindByName.

The DbProviderfactory will create your classes instead of the normal odp.net classes.