Oracle and Array Binding with C#/VB Samples

Working with Oracle Array Binding in C# and Visual Basic

This topic explains how to use Array Binding in Oracle and contains C# and Visual Basic samples.

Using the Oracle array binding feature can greatly speed up execution of the application when inserting or updating big volumes of data. The main advantage is that array binding allows you to execute several INSERT SQL statements with the different parameters for the one time. Note that you access Oracle server only once that increases the speed of update a lot.

The following Oracle table definition will be used in our VB and C# samples:

CREATE TABLE dept
(
  deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY,
  dname VARCHAR2(14),
  loc VARCHAR2(13)
);

At first, you should create an object of OracleConnection class and open a connection to Oracle database:

[C#]

...
OracleConnection conn = new OracleConnection(
   "User Id=Scott;Password=tiger;Data Source=Ora");
conn.Open();
...

[Visual Basic]

...
Dim conn As OracleConnection = new OracleConnection( _
   "User Id=Scott;Password=tiger;Data Source=Ora")
conn.Open();
...

Please note that it is only a cut of C# and Visual Basic sample code. The full text will be given at the end of this article.

After that you should create an instance of OracleCommand class associated with the OracleConnection object and specify SQL statement for the execution:

[C#]

...
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO dept VALUES(:deptno_p, :dname_p, :loc_p)";
...

[Visual Basic]

...
Dim cmd As OracleCommand = conn.CreateCommand()
cmd.CommandText = "INSERT INTO dept VALUES(:deptno_p, :dname_p, :loc_p)"
...

Colons at SQL text mean parameters with the values which will be specified later.

Now for each parameter from SQL you should associate an object of OracleParameter class and add this object to the collection of the parameters of OracleCommand objects. Association is accomplished by the names of parameters in following way:

[C#]

...
cmd.Parameters.Add("deptno_p", OracleDbType.Integer);
cmd.Parameters.Add("dname_p", OracleDbType.VarChar, 200);
cmd.Parameters.Add("loc_p", OracleDbType.VarChar, 200);
...

[Visual Basic]

...
cmd.Parameters.Add("deptno_p", OracleDbType.Integer)
cmd.Parameters.Add("dname_p", OracleDbType.VarChar, 200)
cmd.Parameters.Add("loc_p", OracleDbType.VarChar, 200)
...

You should specify the name of each parameter and its type. For VarChar/NVarChar parameters it is also recommended to specify size of the parameter explicitly. This size should be more or equal to the size (in bytes) of the longest string in the array. You may omit specifying the size. In this case it will be determined automatically, however this may cause some performance overhead.

The next important point is assigning values to the parameters. Parameter values should be arrays with the length corresponding to the number of SQL statement executions. And each item of the array must correspond to the single execution of the SQL statement.

[C#]

...
cmd.Parameters["deptno_p"].Value = new int[] { 10, 20, 30, 40 };
cmd.Parameters["dname_p"].Value = new string[] { 
   "ACCOUNTING", "RESEARCH", "SALES", "OPERATIONS" };
cmd.Parameters["loc_p"].Value = new string[] { 
   "NEW YORK", "DALLAS", "CHICAGO", "BOSTON" };
...

[Visual Basic]

...
Dim deptno(4) As Integer
deptno(0) = 10
deptno(1) = 20
deptno(2) = 30
deptno(3) = 40

Dim dname(4) As String
dname(0) = "ACCOUNTING"
dname(1) = "RESEARCH"
dname(2) = "SALES"
dname(3) = "OPERATIONS"

Dim loc(4) As String
loc(0) = "NEW YORK"
loc(1) = "DALLAS"
loc(2) = "CHICAGO"
loc(3) = "BOSTON"

cmd.Parameters("deptno_p").Value = deptno
cmd.Parameters("dname_p").Value = dname
cmd.Parameters("loc_p").Value = loc
...

After accomplishing previous steps you should call ExecuteArray method that assumes a parameter specifying how many times SQL statement will be executed. Note that the value of this method argument must be equal to the number of elements of parameters value. Now with any Oracle tool (you can use OraTools for this purpose) execute SELECT * FROM Dept and you can see four new records appended.

[C#]

...
cmd.ExecuteArray(4);
...

[Visual Basic]

...
cmd.ExecuteArray(4)
...

The following sample code in Visual Basic and C# executes several INSERT operations against an Oracle database using array binding.

[C#]

public void ArrayBindInsert()
{
  // Create connection object
  OracleConnection conn = new OracleConnection(
     "User Id=Scott;Password=tiger;Data Source=Ora");

  // Open connection
  conn.Open();

  // Create command object with previously opened connection
  OracleCommand cmd = conn.CreateCommand();

  // Set command text property
  cmd.CommandText = "INSERT INTO dept VALUES(:deptno_p, :dname_p, :loc_p)";

  // Add parameters to command parameters collection
  cmd.Parameters.Add("deptno_p", OracleDbType.Integer);
  cmd.Parameters.Add("dname_p", OracleDbType.VarChar, 200);
  cmd.Parameters.Add("loc_p", OracleDbType.VarChar, 200);

  // Set parameters values
  cmd.Parameters["deptno_p"].Value = new int[] { 10, 20, 30, 40 };
  cmd.Parameters["dname_p"].Value = new string[] { 
     "ACCOUNTING", "RESEARCH", "SALES", "OPERATIONS" };
  cmd.Parameters["loc_p"].Value = new string[] { 
     "NEW YORK", "DALLAS", "CHICAGO", "BOSTON" };

  // Insert four records at one time
  cmd.ExecuteArray(4);

  // Dispose command
  cmd.Dispose();

  // Close connection
  conn.Close();
}

[Visual Basic]


Public Sub ArrayBindInsert()
  ' Create connection object
  Dim conn As OracleConnection = new OracleConnection( _
     "User Id=Scott;Password=tiger;Data Source=Ora")

  ' Open connection
  conn.Open()

  ' Create command object with previously opened connection
  Dim cmd As OracleCommand = conn.CreateCommand()

  ' Set command text property
  cmd.CommandText = "INSERT INTO dept VALUES(:deptno_p, :dname_p, :loc_p)";

  ' Add parameters to command parameters collection
  cmd.Parameters.Add("deptno_p", OracleDbType.Integer)
  cmd.Parameters.Add("dname_p", OracleDbType.VarChar, 200)
  cmd.Parameters.Add("loc_p", OracleDbType.VarChar, 200)

  ' Set parameters values
  Dim deptno(4) As Integer
  deptno(0) = 10
  deptno(1) = 20
  deptno(2) = 30
  deptno(3) = 40

  Dim dname(4) As String
  dname(0) = "ACCOUNTING"
  dname(1) = "RESEARCH"
  dname(2) = "SALES"
  dname(3) = "OPERATIONS"

  Dim loc(4) As String
  loc(0) = "NEW YORK"
  loc(1) = "DALLAS"
  loc(2) = "CHICAGO"
  loc(3) = "BOSTON"

  cmd.Parameters("deptno_p").Value = deptno
  cmd.Parameters("dname_p").Value = dname
  cmd.Parameters("loc_p").Value = loc

  ' Insert four records at one time
  cmd.ExecuteArray(4)

  ' Dispose command
  cmd.Dispose()

  ' Close connection
  conn.Close()
End Sub

So these Visual Basic and C# samples demonstrates how you can insert several records to an Oracle table by performing single execute operation. It is important to know that necessary condition of working with array binding feature is assigning array of the items to the parameter value and executing ExecuteArray(int iters) method of the OracleCommand class. The number of iterations should be less than 65535.

Note that use of array binding with OracleObject classes is not supported. The ExecuteArray method also can't be used with stored procedures in Direct mode.