AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=621&pId=-1
Using ODP.NET to Insert Multiple Rows within a Single Round Trip
page
by Steven Swafford
Feedback
Average Rating: 
Views (Total / Last 10 Days): 34035/ 32

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

Create the Authors Entry Web Form

[Download Sample]

The next and most essential step is to add a reference to the ODP.NET Data Provider to your project. To achieve this, right-click on the References node and choose Add Reference.

Figure 6 – References Node

Figure 6 – References Node

Figure 7 – Add Reference

Figure 7 – Add Reference Dialog

  1. Scroll down until you see Oracle.DataAccess.dll.
  2. Double-click Oracle.DataAccess.dll or highlight this DLL and click Select.
  3. You will now notice this library show up under Selected Components.

As I previously stated, I am using Visual Studio .NET 2003 as my IDE. While you can use any other IDE (or even a text editor), this may or may not add to your development time.

Figure 8 – Authors Web Form Entry

Figure 8 – Authors Web Form Entry

As you can see there is nothing complex about this form. I have used three different server controls: the TextBox, Label, and Button. As you know, all the work happens in the code-behind file, so let us now take the time to look at the code for this Web form.

The Code Behind the Authors Entry Web Form

[Download Sample]

The initial thing we need is to create our database connection string. In most cases (if not all), I have a preference to employ the Web.config file to define my database connection string. If you are not familiar with this approach, take a moment to look at page 5 of my previous article, titled Read and Write BLOB Data to a Database Table with ODP.NET.

Figure 9 – Define the database connection string

public static readonly string CONN_STRING = ConfigurationSettings.AppSettings["OracleConnectionString"];

This now brings us to the point of creating a method that will process the data entry from our Web form that we previously created. For the purpose of this article we I created a private method named ProcessAuthors that will handle this task.

Figure 10 – ProcessAuthors() Method

private void ProcessAuthors()
{
      // Establish your database connection
      OracleConnection dbConn = new OracleConnection(CONN_STRING);
      dbConn.Open();
 
      // Establish a new Oracle Command
      OracleCommand cmd = new OracleCommand("",dbConn);
 
      // Set the Command text type
      cmd.CommandText = "Authors_Array";
 
      // Set the Command type
      cmd.CommandType = CommandType.StoredProcedure;
 
      // Grab author1 form element values
      string author1Email = EmailTextBox1.Text;
      string author1Name = NameTextBox1.Text;
 
      // Grab author2 form element values
      string author2Email = EmailTextBox2.Text;
      string author2Name = NameTextBox2.Text;
 
      // Grab author3 form element values
      string author3Email = EmailTextBox3.Text;
      string author3Name = NameTextBox3.Text;
 
      // Now initialize the author email array from the web form
      string[] authorsEmailArray = {author1Email, author2Email, author3Email};
 
      // Now initialize the author name array from the web form
      string[] authorsNameArray = {author1Name, author2Name, author3Name};
 
      // Set the ArrayCount for command to 3 i.e. max. number of rows
      cmd.ArrayBindCount = 3;
 
      OracleParameter authorsEmailParam = new OracleParameter("Authors Email", OracleDbType.Varchar2);
      authorsEmailParam.Direction = ParameterDirection.Input;
      authorsEmailParam.Value = authorsEmailArray;
      cmd.Parameters.Add(authorsEmailParam);
 
      OracleParameter authorsNameParam = new OracleParameter("Authors Name", OracleDbType.Varchar2);
      authorsNameParam.Direction = ParameterDirection.Input;
      authorsNameParam.Value = authorsNameArray;
      cmd.Parameters.Add(authorsNameParam);
 
      try
      {
            cmd.ExecuteNonQuery();
            LabelStatus.Text = "There was a total of " + cmd.ArrayBindCount + " authors added.";
            LabelStatus.Visible = true;
      }
      catch (Exception ex)
      {
            LabelStatus.Text = ex.Message.ToString();
            LabelStatus.Visible = true;
      }
      finally
      {
            // DB Connection is placed back into the pool
            dbConn.Dispose();
      }
}

Detailed Explanation of the ProcessAuthors Method

This method performs numerous actions as follows:

  1. A database connection is established.
  2. An OracleCommand object is instantiated.
  3. The CommandText is set to the stored procedure name Authors_Array. (CommandText may be either a SQL statement or a stored procedure.)
  4. The CommandType is defined as a StoredProcedure.
  5. The data input via the Web Form is captured. I achieve this by defining a string named author1Email, author1Name, and so on. In this case I have three entries for author information.
  6. Two separate string arrays are initialized, one for the author’s email address and the other for the author’s name.
  7. This now brings us to the vital step that must be defined correctly for all this to work as a single round trip to our database. The OracleCommand member ArrayBindCount is the array binding feature that in turn specifies the number of array elements that will be bound to our OracleParameter value. In this case, it is three since we have three elements in our arrays.
  8. Next an OracleParameter is established for each item that is to be inserted into the database.
  9. The database call is executed by using the ExecuteNonQuery method of the OracleCommand class. Notice that I have wrapped the statement in a try-catch-finally statement because you always want to catch and handle any exceptions that may be thrown.
  10. Finally, the database connection is returned back to the pool with the finally statement by using the Dispose method of the OracleConnection class.
See It In Action

[Download Sample]

Execute the Application

Now that we have completed the steps necessary to design the Web form and create the appropriate methods to handle interaction with the database, it is time to test. Within the Visual Studio .NET IDE, press the F5 key to run the application.  Enter test data and click the Add Authors button. If the insert into the database is successful, a screen similar to the following should be displayed.

Figure 11 – Success Web Form Execution

Figure 11 – Authors Web Form Successful Execution

To take this a step further, query the database to ensure the data was successfully inserted.

Figure 12 – Database Query Results

Figure 12 – Database Query Results

Conclusion

As you can see, using the ArrayBindCount property allows the developer to make a single call to the database using a stored procedure or SQL statement. The beauty of this is that the execution happens at the database level with a single call versus a loop, which would require a total of three round trips. While this may not be feasible in every situation, e.g., where you do not know ahead of time how many rows will be processed, it is a perfect solution for those times that you do know how many rows you are to process.

Additional Reading

Using Oracle Data Provider for .NET

Oracle Data Provider for .NET Developers Guide (Requires Free Registration)

Access Oracle-Specific Features

Feel free to discuss this article at my Blog.



©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-19 1:51:13 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search