Using ODP.NET to Insert Multiple Rows within a Single Round Trip
page 1 of 4
Published: 21 Mar 2005
Unedited - Community Contributed
Abstract
Have you ever needed to insert multiple rows into a database? Of course you have. In this article I will explain how to make the most of Oracle’s ODP.NET Data Provider to easily pull off such a task. The class we will be looking at is OracleCommand, which is part of the Oracle.DataAccess.Client namespace. The property which allows the developer to achieve a multiple row insert is called ArrayBindCount. In this article I will be using a stored procedure instead of a SQL statement.
by Steven Swafford
Feedback
Average Rating: 
Views (Total / Last 10 Days): 64442/ 108

Setup

[Download Sample]

Requirements

  1. Your favorite IDE or text editor (my screenshots are of Visual Studio .NET 2003).
  2. Oracle Database.
  3. ODP.NET Data Provider (Documentation can be found here).
  4. General knowledge of stored procedures and SQL.

In this example, I will show how to create a Web form that will allow you to insert three authors’ email addresses into an Oracle database by means of a stored procedure, all in a single round trip.

Establish your Database Schema

The first step is to set up our table and stored procedure.  I have included two PL/SQL scripts to aid you here. The first script will generate the table and stored procedure; the second script will drop the table and stored procedure.  I believe firmly that when you compose a script that will alter a database, you should immediately compose a script that will restore the database to its original state.

Figure 1 – PL/SQL Create Script

-- Author:        Steven M. Swafford
-- Date Created:  22-FEB-2005

-- Create table
create table ASPAllianceAuthors
(
  AuthorEmail      varchar2(100),
  AuthorFullName   varchar2(150)
)
;

-- Add comments to the table 
comment on table ASPAllianceAuthors
  is 'Holds suthor information';
  
-- Add comments to the columns 
comment on column ASPAllianceAuthors.AuthorEmail
  is 'email address';
comment on column ASPAllianceAuthors.AuthorFullName
  is 'full name';

-- Create procedure
create or replace procedure Authors_Array(pEmail VARCHAR2, pName VARCHAR2) is
begin
  INSERT INTO aspallianceauthors (authoremail, authorfullname) VALUES (pEmail, pName);
  Commit;
end Authors_Array;
/

Figure 2 – PL/SQL Revert Script

-- Author:        Steven M. Swafford
-- Date Created:  22-FEB-2005
      
-- Drop table(s)
DROP TABLE ASPAllianceAuthors;

-- Drop procedure
DROP procedure Authors_Array;

To run these scripts you may use SQL*Plus or any other product such as Toad or PL/SQL Developer (of these, I favor the latter).

Figure 3 – SQL*Plus Interface

Figure 3 – SQL*Plus Interface

  1. Enter the appropriate user name.
  2. Enter the appropriate password.
  3. Enter the appropriate host string.

Once you have successfully logged into your database, run the following:

@c:\yourscriptlocation\Article621_Apply_Table.sql

You will need to change the folder location to where you have extracted the sample code that accompanies this article. Also, if you’re not familiar with what the @ sign indicates in this instance, the answer is that a single @ is equivalent to the START command and is used to run a SQL*Plus command script.

Once again I prefer PL/SQL Developer, and all future screenshots of the database will be from this product. If your script ran successfully, you should now see something comparable to the following.

Figure 4 – ASPAllianceAuthors Table

Figure 4 – ASPALlianceAuthors Table

Figure 5 – Authors_Array Stored Procedure

Figure 5 – Authors_Array Stored Procedure


View Entire Article

User Comments

No comments posted yet.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2017 ASPAlliance.com  |  Page Processed at 2017-08-17 11:07:16 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search