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

最新推荐文章于 2021-04-05 19:43:25 发布

tintown

最新推荐文章于 2021-04-05 19:43:25 发布

阅读量693

点赞数

分类专栏：技术积累文章标签：数据库 basic command oracle .net cmd

本文链接：https://blog.csdn.net/tintown/article/details/4605793

版权

技术积累专栏收录该内容

82 篇文章 0 订阅

订阅专栏

申明：此文章摘自OTN（http://www.oracle.com/technology/global/cn/sample_code/tech/windows/odpnet/howto/arraybind/index.html）
目标

在阅读此方法文档后，您应能够：

使用 ODP.NET 调用数据库存储过程
使用 ODP.NET 的数组绑定功能

前提

假设读者熟悉 Visual Studio.NET，还了解 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 引擎中，因此速度更快。

需要

Oracle Data Provider for .NET (ODP.NET)
运行 SQL*Net TCP/IP 监听器的 Oracle9i 数据库或更高版本
随 Microsoft Visual Studio .NET（包括 Microsoft .NET 框架 1.0 或更高版本）安装的 C# / VB.NET

创建数据库对象

此方法文档使用 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”部分中添加对命名空间的引用非常值得，这样可避免以后在脚本中限定其使用：

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 数据库的连接：

// 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 对象：

// 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 的一部分绑定的数组元素的个数：

// 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 的值设置为所创建的数组：

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

// 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 表：

// 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

设置并运行此方法文档程序

1. 打开 Visual Studio.NET。

2. 创建控制台应用程序项目：

C#
用 C# 创建一个控制台应用程序项目。默认情况下，将 Class1.cs 添加到项目中。
Visual Basic .NET
用 Visual Basic .NET 创建控制台应用程序项目。默认情况下，将 Module1.vb 添加到项目中。

3. 请确保您的项目包含对 System、Oracle.DataAccess 及 System.Data 命名空间的引用。如果这些引用不存在，则添加对这些命名空间的引用。

4. 复制代码：

C#

使用 Solution Explorer 打开 Class1.cs。有关为此方法文章用 C# 编写的代码的完整清单，请单击这里。复制此代码，覆盖 Class1.cs 的内容。保存此文件。

Visual Basic .NET

使用 Solution Explorer 打开 Module1.vb。有关为此方法文章用 VB.NET 编写的代码的完整清单，请单击这里。复制此代码，覆盖 Module1.vb 的内容。保存此文件。

5. 按照代码的步骤 1 中的数据库设置修改用户 Id、口令及数据源。

6. 要编译并运行此应用程序，请按下 Ctrl+F5。这将如图 1.1 所示显示输出：

图 1.1 – 输出的屏幕截图