|
How to: Bind an Array to an ODP.NET Database Command
Date: 19-Jun-2003
Objective
After reading this how-to document you should be able to:
- Call a database stored procedure using ODP.NET
- Use the Array Binding feature of ODP.NET
Prerequisite
The reader is assumed to be familiar with Visual Studio.NET, and also possess an understanding of the rudimentaries of ODP.NET and databases.
Introduction
This document demonstrates how to execute a database stored procedure, multiple times in a single database round trip, using the "Array Binding" feature of ODP.NET. The "Array Binding" feature, whose usage is specified by the ArrayBindCount property of OracleCommand, allows each value in an array to be passed as a parameter all in a single round-trip. The ArrayBindCount property determines the number of command executions, as well as the number of array elements to bind as part of the OracleCommand.
In this document, a database stored procedure Test_Arraybind is used. It inserts data into a table and is called from a console application. pdeptno and pdname are the parameters that are passed to this stored procedure. Multiple rows of DeptNo and Dname are stored in arrays that are bound to OracleParameters, which are added to OracleCommand object that executes the stored procedure. On execution of Test_Arraybind, multiple rows are passed as IN parameter demonstrating how multiple rows are passed to a database stored procedure in a single round-trip. Note: Even though this document uses stored procedure, the array binding feature can be used with regular SQL commands and PL/SQL blocks also.
Value Proposition
The Array Binding feature is used for bulk operations where a stored procedure or SQL statement executes the ArrayBindCount time in one server round trip. Each execution uses the nth element in the parameter (array) and executes the stored procedure or SQL statement - this procedure is done internally in the database, it isn't related to the stored procedure or SQL statement.
Array Binding is the best method for large inserts from .NET using ODP.NET as compared to PL/SQL Associative Arrays, especially since PL/SQL Associative Arrays have the following drawback:
- A PL/SQL procedure must be written to effect the inserts - while this will get the data to the PL/SQL engine on the server in a block, it can only allow to cross the data into the SQL engine one row at a time
On the other hand, using Array Binding feature is simpler than using PL/SQL Associative Arrays because of the following advantages:
- Controlling the Batch Size: There's a built-in knob to control the batch size.
- Enhancing the Speed: The array of row data is copied all the way into the SQL engine together, so it is faster.
Requirements
Creating Database Objects
This How-to document uses the DeptTab table and Test_Arraybind database stored procedure. Connect to the database as any user using SQL*Plus and run the following commands to create the database objects:
DROP TABLE depttab;
CREATE TABLE depttab (deptno NUMBER(2), dname VARCHAR2(14));
CREATE OR REPLACE PROCEDURE Test_Arraybind(pdeptno NUMBER,
pdname VARCHAR2) IS
BEGIN
INSERT INTO depttab (deptno, dname) VALUES ( pdeptno, pdname);
COMMIT;
END;
Code Walk-Through
Include Required Namespaces: It is worthwhile to add references of the namespaces in the 'general declarations' section of the .cs or .vb file, to avoid qualifying their usage later in the script:
|
C# |
|
using System;
using System.Data;
using Oracle.DataAccess.Client; |
|
Visual Basic .NET |
|
Imports System
Imports System.Data
Imports Oracle.DataAccess.Client |
1. Establish connection to an Oracle database using ODP.NET:
|
C# |
|
// STEP 1
// NOTE: Substitute User ID, Password, Data Source
// as per your database setup
string connectStr = "User Id=scott; Password=tiger; Data Source=orcl9i";
// Initialize connection
OracleConnection connection;
connection = new OracleConnection(connectStr);
connection.Open(); |
|
Visual Basic .NET |
|
' STEP 1
' NOTE:Substitute User ID, Password, Data Source
' as per your database setup
Dim connectStr As String = "User Id=Scott; Password=tiger; Data Source=orcl9i"
' Initialize connection
Dim connection As OracleConnection
connection = New OracleConnection(connectStr)
connection.Open() |
2. Initialize the OracleCommand object:
|
C# |
|
// STEP 2
// Set command to execute Test_Arraybind database stored procedure
OracleCommand cmd1 = new OracleCommand("",connection);
cmd1.CommandText= "Test_Arraybind";
cmd1.CommandType = CommandType.StoredProcedure; |
|
Visual Basic .NET |
|
'STEP 2
' Set command to execute Test_Arraybind database stored procedure
Dim cmd1 As OracleCommand = New OracleCommand("", connection)
cmd1.CommandText = "Test_Arraybind"
cmd1.CommandType = CommandType.StoredProcedure |
3. Initialize the array with multiple values of Deptno and Dname. The ArrayBindCount property determines the number of command executions as well as the number of array elements to bind as part of OracleCommand:
|
C# |
|
// STEP 3
// Initialize array with data
int[] myArrayDeptNo = new int[3]{1, 2, 3};
String[] myArrayDeptName = {"Dev", "QA", "Facility"};
// Set the ArrayCount for command to 3 i.e. max. number of rows in the
// preceding arrays.
cmd1.ArrayBindCount = 3; |
|
Visual Basic .NET |
|
' STEP 3
' Initialize array with data
Dim myArrayDeptNo As Int16() = {1, 2, 3}
Dim myArrayDeptName As String() = {"Dev", "QA", "Facility"}
' Set the ArrayCount for command to 3 i.e. max.' number of rows in the
' preceding arrays
cmd1.ArrayBindCount = 3 |
4. The value of deptNoParam and deptNameParam Oracle parameters are set to the array created:
|
C# |
|
// STEP 4
// Instantiate Oracle parameter corresponding to DeptNo
OracleParameter deptNoParam = new OracleParameter("deptno",OracleDbType.Int32);
deptNoParam.Direction = ParameterDirection.Input;
// Bind Array containing Department numbers "deptNoParam" Oracle Parameter
deptNoParam.Value = myArrayDeptNo;
// Add Oracle Parameter to Command
cmd1.Parameters.Add(deptNoParam);
// Similarly bind Dept Name parameter
OracleParameter deptNameParam = new OracleParameter("deptname",
OracleDbType.Varchar2);
deptNameParam.Direction = ParameterDirection.Input;
deptNameParam.Value = myArrayDeptName;
cmd1.Parameters.Add(deptNameParam); |
|
Visual Basic .NET |
|
' STEP 4
' Instantiate Oracle parameter corresponding to DeptNo
Dim deptNoParam As OracleParameter = New OracleParameter("deptno", OracleDbType.Int32)
deptNoParam.Direction = ParameterDirection.Input
' Bind Array containing Department numbers "deptNoParam" Oracle Parameter
deptNoParam.Value = myArrayDeptNo
' Add Oracle Parameter to Command
cmd1.Parameters.Add(deptNoParam)
' Similarly bind Dept Name parameter
Dim deptNameParam As OracleParameter = New OracleParameter("deptname",
OracleDbType.Varchar2)
deptNameParam.Direction = ParameterDirection.Input
deptNameParam.Value = myArrayDeptName
cmd1.Parameters.Add(deptNameParam) |
5. On executing the command calling Stored Procedure, the stored procedure is called multiple times in a single database round-trip:
|
C# |
|
// STEP 5
// Execute the command calling stored procedure
try
{
cmd1.ExecuteNonQuery();
Console.WriteLine("{0} Rows Inserted" , cmd1.ArrayBindCount);}
catch (Exception e)
{
Console.WriteLine("Execution Failed:" + e.Message);} |
|
Visual Basic .NET |
|
' STEP 5
' Execute the command calling stored procedure
Try
cmd1.ExecuteNonQuery()
Console.WriteLine("{0} Rows Inserted", cmd1.ArrayBindCount)
Catch e As Exception
Console.WriteLine("Execution Failed:" + e.Message)
End Try |
6. Clean-up DeptTab table before exit from application:
|
C# |
|
// Step 6
// Cleanup DeptTab table data
OracleCommand cmd2 = new OracleCommand("",connection);
// Delete all the rows from the DeptTab table
cmd2.CommandText = "DELETE depttab WHERE deptno = :1";
// Bind with an array of 3 items
cmd2.ArrayBindCount = 3;
OracleParameter param1 = new OracleParameter();
param1.OracleDbType = OracleDbType.Int32;
param1.Value = myArrayDeptNo;
cmd2.Parameters.Add(param1);
// Execute the delete statement through command
try
{
cmd2.ExecuteNonQuery();
Console.WriteLine("Cleaned DeptTab table data");
}
catch (Exception e)
{
Console.WriteLine("Cleanup Failed:{0}" ,e.Message);}
finally
{
// Dispose the OracleCommand objects
cmd1.Dispose();
cmd2.Dispose();
// Close and Dispose the OracleConnection object
connection.Close();
connection.Dispose();} |
|
Visual Basic .NET |
|
' Step 6
' Cleanup DeptTab table data
Dim cmd2 As OracleCommand = New OracleCommand("", connection)
' Delete all the rows from the DeptTab table
cmd2.CommandText = "DELETE depttab WHERE deptno = :1"
' Bind with an array of 3 items
cmd2.ArrayBindCount = 3
Dim param1 As OracleParameter = New OracleParameter()
param1.OracleDbType = OracleDbType.Int32
param1.Value = myArrayDeptNo
cmd2.Parameters.Add(param1)
' Execute the delete statement through command
Try
cmd2.ExecuteNonQuery()
Console.WriteLine("Cleaned DeptTab table data")
Catch e As Exception
Console.WriteLine("Cleanup Failed:{0}", e.Message)
Finally
' Dispose the OracleCommand objects
cmd1.Dispose()
cmd2.Dispose()
' Close and Dispose the OracleConnection object
connection.Close()
connection.Dispose()
End Try |
Set up and Run the How-To
1. Open Visual Studio.NET.
2. Create a Console Application Project:
|
C# |
|
Create a Console Application Project in C#. Class1.cs is added to the project by default. |
|
Visual Basic .NET |
|
Create a Console Application Project in Visual Basic .NET. Module1.vb is added to the project by default. |
3. Ensure that your project contains references to the System, Oracle.DataAccess and System.Data Namespaces. Add references to these Namespaces if they do not exist.
4.Copy the code:
|
C# |
|
Using Solution Explorer open Class1.cs. For complete listing of code for this How-to article in C# click here. Copy this code and overwrite the contents of Class1.cs. Save this file. |
|
Visual Basic .NET |
|
Using Solution Explorer open Module1.vb. For the complete listing of code for this How-to article in VB.NET click here. Copy this code and overwrite the contents of Module1.vb. Save this file. |
5. Modify User Id, Password and Data Source as per your database setup in Step 1 of the code.
6.To compile and run this application press Ctrl+F5. This displays the output as shown in Figure 1.1:
Figure 1.1 - Screenshot of output
Resources
|
