Using ODP.NET to Insert Multiple Rows within a Single Round Trip
page 3 of 4
by Steven Swafford
Feedback
Average Rating: 
Views (Total / Last 10 Days): 104348/ 94

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.

View Entire Article

User Comments

No comments posted yet.






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


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 8/22/2014 5:47:43 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search