Working with Array Binding

Using array binding feature can greatly speed up execution of the application on insert or update big volume 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 server only once that increases speed of update a lot.

Following is a sample of using ArrayBind.

For further explanations consider this table definition. 

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 session:

[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 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);
cmd.Parameters.Add("loc_p", OracleDbType.VarChar);
...
[Visual Basic] 
...
cmd.Parameters.Add("deptno_p", OracleDbType.Integer)
cmd.Parameters.Add("dname_p", OracleDbType.VarChar)
cmd.Parameters.Add("loc_p", OracleDbType.VarChar)
...

Besides name of the parameter you should also specify its type.

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)
...

Following is a sample code that executes several INSERT operations 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);
  cmd.Parameters.Add("loc_p", OracleDbType.VarChar);

  // 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)
  cmd.Parameters.Add("loc_p", OracleDbType.VarChar)

  ' 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 this sample demonstrates how you can insert several records 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.

dotConnect for Oracle
Buy