Statement: ThisArticleFrom OTN (http://www.oracle.com/technology/global/cn/sample_code/tech/windows/odpnet/howto/arraybind/index.html)
Target
After reading this document, you should be able:
Prerequisites
Assume that you are familiar with Visual Studio. NET and have basic knowledge about ODP. NET and databases.
Introduction
This document shows how to use the "array binding" function of ODP. Net to execute a database Stored Procedure multiple times during a database return. "Array binding" function (its usage isOraclecommand
OfArraybindcount
Attribute specified) each value in the array can be used as a parameter and all values are passed in a return request.Arraybindcount
Attribute to determine the number of command executions andOraclecommand
The number of array elements bound to one part.
In this documentTest_arraybind
Database stored procedures. This stored procedure inserts data into the table and is applied by the ConsoleProgramCall.Pdeptno
AndPdname
Is two parameters passed to this stored procedure. The system changesDeptno
AndDname
Multiple rows are stored inOracleparameters
And the latter is added toOraclecommand
Object.Once executedTest_arraybind,
, The system uses multiple rowsIn
Parameter transfer demonstrates how to pass multiple rows to a database stored procedure in a return request.Note: although this document uses stored procedures, the array binding function can also be used for conventional SQL commands and PL/SQL blocks.
Value Positioning
The array binding function is used for batch operations. The number of times a stored procedure or SQL statement executes arraybindcount in a server return request is specified. Each execution uses the nth element in the parameter (array) and executes the stored procedure or SQL statement-this process is completed within the database, which is irrelevant to the stored procedure or SQL statement.
Compared with PL/SQL associated arrays, array binding is the best way to insert a large number of data from. net using ODP. net, especially because PL/SQL associated arrays have the following Disadvantages:
- You must write a PL/SQL process to insert data. Although this transfers data to the PL/SQL engine on the server in the form of a block, however, it only allows one row of data to be inserted into the SQL engine.
On the contrary, the array binding function is much easier to associate arrays with PL/SQL due to the following advantages:
- Batch Size Control: There is a built-in button to control the batch size.
- Increase speed: The row data array is directly copied to the SQL engine, so the speed is faster.
Yes
Create database objects
Use this method documentDepttab
Table andTest_arraybind
Database stored procedures. UseSQL * Plus
Connect to the database as any user and run the following command to create a database object:
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; |
|
CodeRehearsal
Including the required namespace:In. CS
Or. VB
It is worthwhile to add references to the namespace in the "general declarations" section of the file, so as to avoid limiting the usage in the script in the future:
C # |
Using system; using system. Data; using Oracle. dataaccess. client; |
Visual Basic. net |
Imports systemimports system. dataimports oracle. dataaccess. Client |
1. Use ODP. Net to establish a connection to the Oracle database:
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 connectionoracleconnection 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 connectiondim connection as oracleconnectionconnection = new oracleconnection (connectstr) connection. open () |
2. InitializationOraclecommand
Object:
C # |
// Step 2 // set command to execute test_arraybind database stored procedureoraclecommand cmd1 = new oraclecommand ("", connection); statement 1.commandtext = "test_arraybind"; statement 1.commandtype = commandtype. storedprocedure; |
Visual Basic. net |
'Step 2 'set command to execute test_arraybind database stored proceduredim cmd1 as oraclecommand = new oraclecommand ("", connection) limit 1.commandtext = "test_arraybind" Limit 1.commandtype = commandtype. storedprocedure |
3. Use multiple group values of deptno and dname to initialize the array. arraybindcount
attribute determines the number of command executions and the number of array elements bound as part of oraclecommand
:
C # |
// step 3 // initialize array with dataint [] myarraydeptno = new int [3] {1, 2, 3}; string [] myarraydeptname = {"Dev", "QA", "facility" };< B> // set the arraycount for command to 3 I. e. max. number of rows in the // preceding arrays. required 1.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. Set Oracle ParametersDeptnoparam and deptnameparam
To the created array:
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 parameters 1.parameters. add (deptnoparam); // similarly bind dept name parameteroracleparameter deptnameparam = new oracleparameter ("deptname", oracledbtype. varchar2); deptnameparam. direction = parameterdirection. input;Deptnameparam. value = myarraydeptname;Parameters 1.parameters. Add (deptnameparam ); |
Visual Basic. net |
'Step 4 'instantiate Oracle parameter corresponding to deptnodim deptnoparam as oracleparameter = new oracleparameter ("deptno", oracledbtype. int32) deptnoparam. ction = parameterdirection. Input'Bind array containing Department numbers "deptnoparam" Oracle parameterdeptnoparam. value = myarraydeptno'Add Oracle parameter to command limit 1.parameters. Add (deptnoparam) 'Similarly bind dept name parameterdim parameter as oracleparameter = new oracleparameter ("deptname", oracledbtype. varchar2) direction. Direction = parameterdirection. InputDeptnameparam. value = myarraydeptnameParameters 1.parameters. Add (deptnameparam) |
5. Once you execute the command to call the stored procedure, the stored procedure is called multiple times in a database return request:
C # |
// Step 5 // execute the command calling stored proceduretry {Statement 1.executenonquery ();Console. writeline ("{0} rows inserted", rule 1.arraybindcount);} catch (exception e) {console. writeline ("execution failed:" + E. Message );} |
Visual Basic. net |
'Step 5' execute the command calling stored proceduretryStatement 1.executenonquery ()Console. writeline ("{0} rows inserted", rule 1.arraybindcount) catch e as predictionconsole. writeline ("execution failed:" + E. Message) end try |
6. ClearDepttab
Table:
C # |
// Step 6 // cleanup depttab table dataoraclecommand cmd2 = new oraclecommand ("", connection );// Delete all the rows from the depttab table‑2.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; Parameters 2.parameters. add (param1); // execute the delete statement through commandtry {limit 2.executenonquery (); console. writeline ("cleaned depttab table data");} catch (exception e) {console. writeline ("cleanup failed: {0}", E. message);} finally {// dispose the oraclecommand objectscmd1.dispose (); pai2.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 table‑2.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 = myarraydeptno1_2.parameters. add (param1) 'execute the delete statement through commandtry1_2.executenonquery () console. writeline ("cleaned depttab table data") catch e as predictionconsole. writeline ("cleanup failed: {0}", E. message) Finally 'dispose the oraclecommand objectscmd1.dispose () limit 2.dispose () 'close and dispose the oracleconnection objectconnection. close () connection. dispose () end try |
Set and run this method Documentation Program
1. Open Visual Studio. NET.
2. Create a console application project:
C # |
Use C # To create a console application project. By defaultClass1.cs Add to project. |
Visual Basic. net |
Use Visual Basic. Net to create a console application project. By defaultModule1.vb Add to project. |
3. Make sure that your project containsSystem, Oracle. dataaccess
AndSystem. Data
Namespace reference. If these references do not exist, add references to these namespaces.
4. Copy the Code:
C # |
Use Solution Explorer to openClass1.cs . For a complete list of code written in C # For this method article, click here. Copy this code to overwriteClass1.cs . Save the file. |
Visual Basic. net |
Use Solution Explorer to openModule1.vb . For a complete list of codes written in VB. NET for this method article, click here. Copy this code to overwriteModule1.vb . Save the file. |
5. Modify the user ID, password, and data source according to the database settings in step 1 of the Code.
6. To compile and run this application, pressCTRL + F5
. This will be shown in figureFig 1.1
Output:
Fig 1.1-output screen