Bind the array to the ODP. NET database command

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:

• Use ODP. Net to call database Stored Procedures

• Use the ODP. Net array binding function

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 isOraclecommandOfArraybindcountAttribute specified) each value in the array can be used as a parameter and all values are passed in a return request.ArraybindcountAttribute to determine the number of command executions andOraclecommandThe number of array elements bound to one part.

In this documentTest_arraybindDatabase stored procedures. This stored procedure inserts data into the table and is applied by the ConsoleProgramCall.PdeptnoAndPdnameIs two parameters passed to this stored procedure. The system changesDeptnoAndDnameMultiple rows are stored inOracleparametersAnd the latter is added toOraclecommandObject.Once executedTest_arraybind,, The system uses multiple rowsInParameter 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

• Oracle data provider for. Net (ODP. Net)

• Run oracle9 of SQL * Net TCP/IP listenerIDatabase or later

• C #/VB. NET installed with Microsoft Visual Studio. NET (including Microsoft. NET Framework Version 1.0 or later)

Create database objects

Use this method documentDepttabTable andTest_arraybindDatabase stored procedures. UseSQL * PlusConnect 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. CSOr. VBIt 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. InitializationOraclecommandObject:

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 deptnameparamTo 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. ClearDepttabTable:

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.csAdd to project.

Visual Basic. net

Use Visual Basic. Net to create a console application project. By defaultModule1.vbAdd to project.

3. Make sure that your project containsSystem, Oracle. dataaccessAndSystem. DataNamespace 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.1Output:

Fig 1.1-output screen