[转]将数组绑定到 ODP.NET 数据库命令

简介

此文档展示如何使用 ODP.NET 的“数组绑定”功能，在一次数据库回程中多次执行某个数据库存储过程。“数组绑定”功能（其用法由 OracleCommand 的 ArrayBindCount 属性指定）允许将数组中的每个值作为一个参数，在一次回程中全部传递。ArrayBindCount 属性确定命令的执行次数及作为 OracleCommand 一部分绑定的数组元素的个数。

本文档中，使用一个称为 Test_Arraybind 的数据库存储过程。该存储过程将数据插入到表中，并由控制台应用程序调用。pdeptno 和 pdname 是传递给此存储过程的两个参数。系统将 DeptNo 和 Dname 的多个行存储在绑定到 OracleParameters 的数组中，而后者又被添加到执行存储过程的 OracleCommand 对象中。一旦执行 Test_Arraybind,，系统将多个行作为 IN 参数传递，演示了在一次回程中如何将多个行传递给某个数据库存储过程。注意：尽管此文档使用存储过程，数组绑定功能还可用于常规 SQL 命令和 PL/SQL 块。

价值定位

数组绑定功能用于批量操作，其中一个存储过程或 SQL 语句在一个服务器回程中执行 ArrayBindCount 所指定的次数。每次执行使用参数（数组）中的第 n 个元素并执行存储过程或 SQL 语句 — 这个过程在数据库内部完成，它与存储过程或 SQL 语句无关。

与 PL/SQL 关联数组相比，数组绑定是使用 ODP.NET 从 .NET 中进行大量插入的最佳方法，尤其是因为 PL/SQL 关联数组有以下缺点：

• 必须编写一个 PL/SQL 过程来实现插入 — 虽然这将把数据以一个块的形式传送给服务器上的 PL/SQL 引擎，但它只允许一次一行地将数据插入到 SQL 引擎中

相反，由于以下优点，使用数组绑定功能要比使用 PL/SQL 关联数组简单得多：

• 控制批量大小：内置有一个控制批量大小的按钮。
• 提高速度：由于一并将行数据数组直接复制到 SQL 引擎中，因此速度更快。

 

创建数据库对象

此方法文档使用 DeptTab 表和 Test_Arraybind 数据库存储过程。使用 SQL*Plus 以任意用户身份连接到数据库，然后运行如下命令来创建数据库对象：

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;

代码预演

包括所需命名空间：在 .cs 或 .vb 文件中的“general declarations”部分中添加对命名空间的引用非常值得，这样可避免以后在脚本中限定其使用：

C#

using System; using System.Data; using Oracle.DataAccess.Client;

Visual Basic .NET

Imports System Imports System.Data Imports Oracle.DataAccess.Client

1. 使用 ODP.NET 建立到 Oracle 数据库的连接：

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. 初始化 OracleCommand 对象：

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. 用 Deptno 和 Dname 的多组值初始化数组。ArrayBindCount 属性确定命令执行次数及作为 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. 将 Oracle 参数 deptNoParam 和 deptNameParam 的值设置为所创建的数组：

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. 一旦执行调用存储过程的命令，则在一个数据库回程中多次调用该数据库存储过程：

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. 从应用程序退出之前，先清除 DeptTab 表：

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

 

 

转自：http://www.oracle.com/technology/global/cn/sample_code/tech/windows/odpnet/howto/arraybind/index.html