Binding query parameters by name with ODP.NET

Question

Hi,

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.

Answer 2

As for the discontinuation of the Microsoft ADO .NET provider for Oracle :

• I will go on using it instead of ODP .NET, your problem being only one of the numerous issues with it. And as it goes, it will still be available in .NET 4.0, though unsupported.
• If Oracle manages to render this provider unusable, I will probably go with a commercial alternative such as DataDirect ADO.NET Data Provider for Oracle or dotConnect for Oracle, that fully integrate into the ADO .NET framwork. And they already support the Entity Framework, by the way (I believe Oracle stated ODP .NET would not).

ODP .NET took too much of my time already.

Answer 3

Use indirection and inheritance! If you're performing data access through an abstract Database class, require the Database implementation handle parameter binding.

public abstract class Database
{
    private readonly DbProviderFactory factory;

    protected Database(DbProviderFactory factory)
    {
        this.factory = factory;
    }

    public virtual DbCommand CreateCommand(String commandText)
    {
        return CreateCommand(CommandType.Text, commandText);
    }

    public virtual DbCommand CreateCommand(CommandType commandType, String commandText)
    {
        DbCommand command = factory.CreateCommand();
        command.CommandType = commandType;
        command.Text = commandText;
        return command;
    }

    public virtual void BindParametersByName(DbCommand command)
    {

    }
}

And choose to create an Oracle specific implementation that overrides default command creation or provides the option to bind parameters by name.

public class OracleDatabase : Database
{
    public OracleDatabase()
        : base(OracleClientFactory.Instance)
    {

    }

    public override DbCommand CreateCommand(CommandType commandType, String commandText)
    {
        DbCommand command = base.CreateCommand(commandType, commandText);
        BindParametersByName(command);
        return command;
    }

    public override void BindParametersByName(DbCommand command)
    {
        ((OracleCommand)command).BindByName = true;
    }
}

Code based on the Data Access Application Block in the Enterprise Library.