8

Since the System.Data.OracleClient library has been deprecated, we are in the process of migrating our code base to use Oracle Data Provider for .NET (ODP.NET) instead. One of the issues that we have encountered is that the System.Data.OracleClient uses parameter name binding as opposed to binding by position and all of the code directly access the System.Data.OracleClient.OracleCommand as opposed to using an intermediate data layer.

Since there is quite a bit of code, is there an easy way to force the ODP.NET OracleCommand.BindByName to be true by default, or must we go through and set the value each time that it is used? Failing at that, is there an easy way to insert that line of code in Visual Studio 2008?

rjzii
  • 14,236
  • 12
  • 79
  • 119
  • 2
    There is no way to set `OracleCommand.BindByName` to true by default. – Vadim K. Feb 01 '10 at 15:33
  • @Vadim K. - That's kind of what I was afraid of, looks like we need a way to find all the spots in the code that need it then. – rjzii Feb 01 '10 at 15:41
  • Are the commands stored procedure calls, or just text queries? I only ask because if they are stored procedure calls, then BindByName won't work anyway - you will have to use correct parameter order. – Igby Largeman Mar 24 '10 at 20:42
  • @Charles M - They are all text queries. – rjzii Mar 25 '10 at 12:55
  • For an even more curious Oracle default, wait until you try to read a LONG! Suggest you always set InitialLONGFetchSize = -1 as well. – Sam Sep 28 '11 at 17:51

7 Answers7

13

I didn't try it but,

I have seen something like

"cmd.GetType().GetProperty("BindByName").SetValue(cmd,true,null);" in PetaPoco.cs file.

Maybe it can help.

deniz
  • 131
  • 1
  • 3
  • 1
    This also allows working with commands without having to have a dependency on the Oracle specific data objects. – Richard Collette Jun 19 '13 at 20:40
  • 1
    Tested and works. Thanks. It is a low-down dirty shame that one has to resort to the black magick of reflection in order to obtain a behavior that should have been there by default. I wish they will expose the `BindByName` parameter in the connection string. – Anton Shepelev Jul 10 '20 at 17:39
10

I know this thread is old, but I had the same problem today and thought I would share my solution in case someone else had this problem. Since OracleCommand is sealed (which sucks), I created a new class that encapsulates the OracleCommand, setting the BindByName to true on instantiation. Here's part of the implementation:

public class DatabaseCommand
{
    private OracleCommand _command = null;

    public DatabaseCommand(string sql, OracleConnection connection)
    {
        _command = new OracleCommand(sql, connection)
        {
            BindByName = true
        };
    }

    public int ExecuteNonQuery()
    {
        return _command.ExecuteNonQuery();
    }

    // Rest of impl removed for brevity
}

Then all I had to do to cleanup the commands was do a search for OracleCommand and replace with DatabaseCommand and test.

BrandonZeider
  • 8,014
  • 2
  • 23
  • 20
  • Not the answer I was hoping for back when I wrote the question, but it is the best one in that other than setting the value yourself each time, it is the only option that you have. – rjzii Sep 20 '11 at 14:41
  • 11
    DatabaseCommand should implement IDisposable, as OracleCommand is derived from DbCommand which implements this. – Sam Sep 28 '11 at 17:41
  • 1
    Is there still no better solution than this? Every Oracle answer seems to involve creating some sort of shim to make up for Oracle client deficiencies. – EKW Feb 14 '18 at 00:15
5

I had the same problem with SqlDataSource Update commands after porting ASPX code to Oracle.DataAcees.Client and solved it by changing OracleCommand.BindByName property in SqlDataSource OnUpdating handler like this:

protected void SqlDataSource_Updating(object sender, SqlDataSourceCommandEventArgs e)
{
    Oracle.DataAccess.Client.OracleCommand b_OracleCommand = 
                  (Oracle.DataAccess.Client.OracleCommand)e.Command;
    b_OracleCommand.BindByName = true;
}
Andrew Barber
  • 39,603
  • 20
  • 94
  • 123
Bore
  • 51
  • 1
  • 1
  • This is the only sensible way to do this without having to refactor and change bunch of code. – Gaui Jul 21 '14 at 09:16
5

With Oracle.ManagedDataAccess.Client, you can configure in app.config:

<oracle.manageddataaccess.client>
<version number="*">
  <dataSources>
    <dataSource alias="SampleDataSource" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL))) " />
  </dataSources>
  <settings>
    <setting name="BindByName" value="True"/>
  </settings>
</version></oracle.manageddataaccess.client>
Sadao
  • 51
  • 1
  • 1
1

I resolved this issue setting the BindByName property in the handler of the SqlDataSource Updating event:

protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e)
{
    ((Oracle.ManagedDataAccess.Client.OracleCommand)e.Command).BindByName = true;
    // ...
}
ARoller
  • 11
  • 1
0

Add partial class for your TableAdapter, and add method, or property, as you want, with this code: 

        for (int i = 0; (i < this.CommandCollection.Length); i = (i + 1))
        {
            if ((this.CommandCollection[i] != null))
            {
                ((global::Oracle.DataAccess.Client.OracleCommand)(this.CommandCollection[i])).BindByName = value;
            }
        }
DerSkythe
  • 429
  • 6
  • 11
-2

To reduce # lines of code

VB.NET

Dim command As OracleCommand = New OracleCommand(query, connection) With {.CommandType = CommandType.StoredProcedure, .BindByName = True}

C#

OracleCommand command = new OracleCommand(query, connection) { CommandType = CommandType.StoredProcedure, BindByName = true };
Szymon
  • 42,577
  • 16
  • 96
  • 114
Glenn
  • 1