Replacing TableAdapters with Oracle.DataAccess.Client (ODP.NET)

Question

I have a legacy DAL with like 100 TableAdapters (DataSet xsd) but because we got new servers with Oracle client 12c I had to made the switch to the Oracle.DataAccess.Client (ODP.NET) from the old deprecated System.Data.OracleClient.

The only problem I have now is that I always get an error: ORA-01008: Not all variables bound when calling the Table Adapters.

I read that I have to set BindByName to true in OracleCommand for each TableAdapter. But how can I do that when the only place where OracleCommand is used is in the designer of the TableAdapter itself?

Is there some way to do this without extending each TableAdapter, because I have like 100 of them.

So, you say, this is designer. This means that on each of your form or control you have one? How about using some data access layer? — T.S., Jul 18 '14 at 23:50
As I said, this is a legacy codebase which wasn't designed very well in the beginning. There is a DAL with the TableAdapters, then there is a BLL with WCF services which returns those TableAdapters, so all the TableAdapters go all the way up to the UI. But that's not a concern now, as we are trying to move one step at a time, and this time it's switching from deprecated `System.Data.OracleClient` to new `Oracle.DataAccess.Client`. But this `BindByName` is making it hard, because we have like 100 TableAdapters. — Gaui, Jul 19 '14 at 00:26
Can you post some code snippets with the exact code yielding that error? It's possible the problem is not where you think it is. I recall that System.Data.OracleClient was AWFUL at managing bind variables, although I don't recall if the syntax was that different from the mainstream ODP syntax. — Hambone, Jul 19 '14 at 01:55
As far as I can remember, `BindByName` is property of command, not adapter. Therefore, you can keep on passing your adapters as before. And all you need to do in your data access is replace each command with a method returning command and in this method do just one time for all code base "bindbyname" — T.S., Jul 19 '14 at 02:03
@T.S. The OracleCommand is hardwired into each TableAdapter designer. That creates a problem. — Gaui, Jul 19 '14 at 15:47
But this is why you're programmer. Refactor the thing and make it reusable. — T.S., Jul 19 '14 at 18:27
Yeah, I could extend each TableAdapter and add a function that sets `BindByName=true` on each item in the CommandCollection, but we're talking about well over 100 TableAdapters, so I was looking for some easier and more generic method. — Gaui, Jul 20 '14 at 14:46
I found [this](http://stackoverflow.com/a/13949553/1053611) answer, could this work? — Gaui, Jul 21 '14 at 13:48

score 0 · Accepted Answer · answered Aug 30 '15 at 00:19

What I did was I extended each TableAdapter and created a new SetBindByName() method, where I forced BindByName = true on the OracleCommand collection.

Like so...

public partial class V_CUSTOMER_GLOBALTableAdapter
{
    public void SetBindByName(bool value = true)
    {
        foreach (Oracle.DataAccess.Client.OracleCommand cmd in this.CommandCollection)
        {
            cmd.BindByName = value;
        }
    }
}

Then when I created an instance of the TableAdapter, I called the new SetBindByName() method.

V_CUSTOMER_GLOBALTableAdapter ta = new V_CUSTOMER_GLOBALTableAdapter();
ta.SetBindByName(true);

Replacing TableAdapters with Oracle.DataAccess.Client (ODP.NET)

1 Answers1