听棠.NET

用积极乐观的心态,面对压力
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

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

Posted on 2005-01-12 12:02  听棠.NET  阅读(2670)  评论(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 的“数组绑定”功能,在一次数据库回程中多次执行某个数据库存储过程。“数组绑定”功能(其用法由 OracleCommandArrayBindCount 属性指定)允许将数组中的每个值作为一个参数,在一次回程中全部传递。ArrayBindCount 属性确定命令的执行次数及作为 OracleCommand 一部分绑定的数组元素的个数。

本文档中,使用一个称为 Test_Arraybind 的数据库存储过程。该存储过程将数据插入到表中,并由控制台应用程序调用。pdeptnopdname 是传递给此存储过程的两个参数。系统将 DeptNoDname 的多个行存储在绑定到 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”部分中添加对命名空间的引用非常值得,这样可避免以后在脚本中限定其使用:

using System;
using System.Data;
using Oracle.DataAccess.Client;
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();
' 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;
'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;
' 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);
' 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);}
' 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();}
' 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# 创建一个控制台应用程序项目。默认情况下,将 Class1.cs 添加到项目中。
用 Visual Basic .NET 创建控制台应用程序项目。默认情况下,将 Module1.vb 添加到项目中。

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

4. 复制代码:

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

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

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

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


图 1.1 – 输出的屏幕截图