From within .NET (ODP.NET) how can I access an oracle parameter by name?

Question

Using ODP & VB.NET, I create insert statements with parameters, similar to the following code:

Dim objOracleParameter As OracleParameter
Dim objCommand As New OracleCommand

objOracleParameter = New OracleParameter
objOracleParameter.ParameterName = ":TestField"


objOracleParameter.OracleDbType = [Enum].Parse(GetType(OracleDbType), "Varchar2")
objOracleParameter.Value = "Data1"
Dim strSQL As String = "INSERT INTO MyTable (TestField) Values (:TestField)"
objCommand.Parameters.Add(objOracleParameter)
objCommand.CommandText = strSQL
objCommand.ExecuteNonQuery()

objOracleParameter = New OracleParameter
objOracleParameter.ParameterName = ":TestField"

objCommand = New OracleCommand

objOracleParameter.OracleDbType = [Enum].Parse(GetType(OracleDbType), "Varchar2")
objOracleParameter.Value = "Data2"
Dim strSQL As String = "INSERT INTO MyTable (TestField) Values (:TestField)"
objCommand.Parameters.Add(objOracleParameter)
objCommand.CommandText = strSQL
objCommand.ExecuteNonQuery()

Currently, I execute this code for each record I want to insert, which I believe is inefficient. Instead, I would like to create the command once and then change the parameter value for each new value I want to insert. However, I have been unable to find an example of how to do this and I am not sure if it can't be done. My pseudo code describing what I would like to do is below. Please note that I would like to update only the parameter values and leave everything else unchanged:

Dim objOracleParameter As OracleParameter
Dim objCommand As New OracleCommand

objOracleParameter = New OracleParameter
objOracleParameter.ParameterName = ":TestField"

objOracleParameter.OracleDbType = [Enum].Parse(GetType(OracleDbType), "Varchar2")
objOracleParameter.Value = "Data1"
Dim strSQL As String = "INSERT INTO MyTable (TestField) Values (:TestField)"
objCommand.Parameters.Add(objOracleParameter)
objCommand.CommandText = strSQL
objCommand.ExecuteNonQuery()

objOracleParameter(":TestField").Value = "Data2"
objCommand.ExecuteNonQuery()

Update:

I simplified what I am doing above. The actual use case is in the creation of a generalized routine which allows validating and importing data into various tables by defining the data layout in a table and using it to parse & validate the data. The routine currently works, but I am trying to improve performance.

The largest file I have found which I need to import has > 700,000 lines with ~45 fields each. Implementing performance improvements is critical.

Did you try updating the Command's parameter and executing it again? — Étienne Laneville, Jun 16 '23 at 21:27
See Joe White's answer to [Is it better to reuse SqlCommand when executing the same SQL query several times?](https://stackoverflow.com/questions/4621261/is-it-better-to-reuse-sqlcommand-when-executing-the-same-sql-query-several-times). — Étienne Laneville, Jun 16 '23 at 21:30
And this question: [Reusing SqlCommand](https://stackoverflow.com/questions/670407/reusing-sqlcommand) — Étienne Laneville, Jun 16 '23 at 21:31
You are right to want to avoid reparsing each time, but you still would be better off with a different approach. For that many rows, I suggest looking into how to do bulk binds / array inserts via ODP.NET rather than one row per execution. — Paul W, Jun 17 '23 at 12:21

Joel Coehoorn · Answer 1 · 2023-06-17T02:16:43.070

The largest file I have found which I need to import has > 700,000 lines with ~45 fields each.

That sounds like a job for the OracleBulkCopy type. Performance will be MUCH better.

But sometimes you do have a reasonable set of records to process in a tight loop. For that purpose I would structure the code like below.

Dim SQL As String = "
INSERT INTO MyTable (StringField1, IntField2, DateTimeField3) 
VALUES 
( :StringField1, :IntField2, :DateTimeField3);"

' Returns a collection of objects with properties matching the query above
Dim data = GetDataForInsert() 

Using cn As New OracleConnection("connection string here"), _
      cmd As New OracleCommand(SQL, cn)

    Dim sf1 = cmd.Parameters.Add(":StringField1", OracleDbType.NVarchar2, 30)
    Dim if2 = cmd.Parameters.Add(":IntField2", OracleDbType.Int32)
    Dim df3 = cmd.Parameters.Add(":DateTimeField3", OracleDbType.Date)

    cn.Open()
    For Each item In Data
        sf1.Value = item.StringField1
        if2.Value = item.IntField2
        df3.Value = item.DateTimeField3

        ' OR
        cmd.Parameters(":StringField1").Value = item.StringField1
        cmd.Parameters(":IntField2").Value = item.IntField2
        cmd.Parameters(":DateTimeField3").Value = item.DateTimeField3
 
        ' One or the other of the above blocks, not both

        cmd.ExecuteNonQuery()
    Next
End Using

No idea why it got down votes. Should be a decent solution. – Wernfried Domscheit Jun 17 '23 at 08:46 — Wernfried Domscheit, Jun 17 '23 at 08:46

From within .NET (ODP.NET) how can I access an oracle parameter by name?

1 Answers1