Binding query parameters by name with ODP.NET

c# oracle ado.net odp.net
Thomas Levesque picture Thomas Levesque · Jun 26, 2009 · Viewed 15.6k times · Source

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

Theo picture Theo · Jun 26, 2009

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.

Related questions

ODP.NET for both 32bit and 64bit

I am developing application which uses ODP.NET to connect to Oracle DB. I would like to have one version for both 32bit and 64bit machines. The problem is that I couldn't figure out how to build projetc with anycpu …

Read More
Handling ExecuteScalar() when no results are returned

I am using the following SQL query and the ExecuteScalar() method to fetch data from an Oracle database: sql = "select username from usermst where userid=2" string getusername = command.ExecuteScalar(); It is showing me this error message: System.NullReferenceException: Object reference …

Read More
Batch multiple select statements when calling Oracle from ADO.NET

I want to batch multiple select statements to reduce round trips to the database. The code looks something like the pseudo code below. It works perfectly on SQL Server, but does not work on Oracle - Oracle complains about the …

Read More