[转]将数组绑定到 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 SystemImports System.DataImports Oracle.DataAccess.Client

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

C#

// STEP 1// NOTE:Substitute User ID, Password, Data Source // as per your database setupstring connectStr = "User Id=scott; Password=tiger; Data Source=orcl9i";  // Initialize connectionOracleConnection connection;connection = new OracleConnection(connectStr);connection.Open();

Visual Basic .NET

' STEP 1' NOTE:Substitute User ID, Password, Data Source' as per your database setupDim connectStr As String = "User Id=Scott; Password=tiger; Data Source=orcl9i"' Initialize connectionDim connection As OracleConnectionconnection = New OracleConnection(connectStr)connection.Open()

2. 初始化 OracleCommand 对象：

C#

// STEP 2// Set command to execute Test_Arraybind database stored procedureOracleCommand 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 procedureDim 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 dataint[] 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 dataDim 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 arrayscmd1.ArrayBindCount = 3

4. 将 Oracle 参数 deptNoParam 和 deptNameParam 的值设置为所创建的数组：

C#

// STEP 4// Instantiate Oracle parameter corresponding to DeptNoOracleParameter deptNoParam = new OracleParameter("deptno",OracleDbType.Int32);deptNoParam.Direction = ParameterDirection.Input;// Bind Array containing Department numbers "deptNoParam" Oracle ParameterdeptNoParam.Value = myArrayDeptNo;// Add Oracle Parameter to Command cmd1.Parameters.Add(deptNoParam);// Similarly bind Dept Name parameterOracleParameter 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 DeptNoDim deptNoParam As OracleParameter = New OracleParameter("deptno", OracleDbType.Int32)deptNoParam.Direction = ParameterDirection.Input' Bind Array containing Department numbers "deptNoParam" Oracle ParameterdeptNoParam.Value = myArrayDeptNo' Add Oracle Parameter to Command cmd1.Parameters.Add(deptNoParam)' Similarly bind Dept Name parameterDim deptNameParam As OracleParameter = New OracleParameter("deptname",OracleDbType.Varchar2)deptNameParam.Direction = ParameterDirection.InputdeptNameParam.Value = myArrayDeptNamecmd1.Parameters.Add(deptNameParam)

5. 一旦执行调用存储过程的命令，则在一个数据库回程中多次调用该数据库存储过程：

C#

// STEP 5// Execute the command calling stored proceduretry{ 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 procedureTry cmd1.ExecuteNonQuery() Console.WriteLine("{0} Rows Inserted", cmd1.ArrayBindCount)Catch e As ExceptionConsole.WriteLine("Execution Failed:"+ e.Message)End Try

6. 从应用程序退出之前，先清除 DeptTab 表：

C#

// Step 6// Cleanup DeptTab table dataOracleCommand cmd2 = new OracleCommand("",connection);// Delete all the rows from the DeptTab tablecmd2.CommandText = "DELETE depttab WHERE deptno = :1";// Bind with an array of 3 itemscmd2.ArrayBindCount = 3;OracleParameter param1 = new OracleParameter();param1.OracleDbType = OracleDbType.Int32;param1.Value = myArrayDeptNo;cmd2.Parameters.Add(param1);// Execute the delete statement through commandtry{cmd2.ExecuteNonQuery(); Console.WriteLine("Cleaned DeptTab table data");}catch (Exception e){Console.WriteLine("Cleanup Failed:{0}" ,e.Message);}finally{// Dispose the OracleCommand objectscmd1.Dispose();cmd2.Dispose();// Close and Dispose the OracleConnection objectconnection.Close();connection.Dispose();}

Visual Basic .NET

' Step 6' Cleanup DeptTab table dataDim cmd2 As OracleCommand = New OracleCommand("", connection)' Delete all the rows from the DeptTab tablecmd2.CommandText = "DELETE depttab WHERE deptno = :1"' Bind with an array of 3 itemscmd2.ArrayBindCount = 3Dim param1 As OracleParameter = New OracleParameter()param1.OracleDbType = OracleDbType.Int32param1.Value = myArrayDeptNocmd2.Parameters.Add(param1)' Execute the delete statement through commandTrycmd2.ExecuteNonQuery()Console.WriteLine("Cleaned DeptTab table data")Catch e As ExceptionConsole.WriteLine("Cleanup Failed:{0}", e.Message)Finally' Dispose the OracleCommand objectscmd1.Dispose()cmd2.Dispose()' Close and Dispose the OracleConnection objectconnection.Close()connection.Dispose()End Try

 

 

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