Using .NET Framework Data Provider for Oracle to Improve .NET Application Performance

 

Gregory Leake
Microsoft Corporation

July 2002

Summary: Presents benchmark data that assesses the performance gains for a typical data-driven Web application (Nile) when using the new .NET Framework Data Provider for Oracle instead of an OLE DB provider. (26 printed pages)

To download the new .Net Framework Data Provider for Oracle, go to the Microsoft Download Center.

Download Nile3.0andSimpleSample.exe (as included in the body of this article, but without line breaks necessary for online presentation).

Note Some lines of code within this article have line breaks inserted for readability. A complete working version of the code is included in the download.

Introduction

If you are building applications using Microsoft® .NET against an Oracle backend database, you will want to take a close look at the new .NET Framework Data Provider for Oracle released on MSDN in June 2002. The goal of the provider is to boost the performance and scalability of .NET applications with Oracle databases by providing a native .NET interface to Oracle databases that bypasses the need to use an OLE DB provider.

The .NET Framework Data Provider for Oracle, unlike the Microsoft OLE DB provider for Oracle, also supports new Oracle 9i datatypes, as well as ref cursors (useful for running Oracle stored procedures that return result sets). This provider, System.Data.OracleClient, is similar to the .NET Framework Data Provider for SQL Server, System.Data.SqlClient. This article includes benchmark data that assesses the performance gains for a typical data-driven Web application (Nile). The article includes simple sample code to illustrate the basic use of and differences between the OLE DB provider for Oracle, the new .NET Framework Data Provider for Oracle, and the .NET SQL Server Data Provider. See the top of this article for the download link to the complete sample application (Nile) used in the benchmark testing.

Using ADO.NET with Oracle

Until recently, the primary mechanism developers used to access Oracle databases from .NET application was OLE DB, channeling database requests through the System.Data.OleDb data classes. However, developers writing .NET data-driven applications against SQL Server have been able to take advantage of the super-fast System.Data.SqlClient data classes, which provide data access to SQL Server via a SQL Server provider written in managed .NET code. This provider communicates to SQL Server via the native SQL Server client libraries, and derives very fast speeds. While OLE DB provided an adequate data access mechanism for .NET applications to communicate with Oracle backends, certainly developers have been asking for a faster, more scalable Oracle data access mechanism to get better performance for the .NET applications. The new .NET Framework Data Provider for Oracle, recently released on MSDN, provides just this.

Basically, developers now have a much faster database access mechanism for ADO.NET in the form of new System.Data.OracleClient framework classes that work in much the same way as the System.Data.SqlClient classes. In both cases, the fastest database read mechanism will be ADO.NET Data Readers, as opposed to Data Sets, although both are fully functional using the new Oracle Managed Provider. The good news is the new OracleClient classes should provide significant performance improvements for .NET applications, and migrating code between OLE DB data classes and OracleClient data classes is not very difficult, although some work is required. However, the performance boost can be dramatic. In internal testing of the Nile 3.0 benchmark application (a typical data-driven Web application provided as sample code with this article), we find over 200-percent performance improvements under load for the new NET Framework Data Provider for Oracle vs. the Microsoft OLE DB Data Provider for Oracle. Benchmark results for the two providers, based on the Nile benchmark application, are provided at the end of this article.

Where Is the Performance Boost Coming From?

The OLE DB client classes are designed to provide a database-independent layer for accessing generic databases. While the value of a generic layer is nearly universal access, it is difficult to deliver database-specific optimizations in this generic access layer. Also, the OLE DB layer is implemented as a COM library, so the System.Data.Oledb namespace works through COM interop. To achieve the significant performance boost described here, the .NET Framework Data Provider for Oracle avoids the cost of COM interop, and also employs Oracle-specific optimizations.

Sample Code: Reading from a Database

We created a simple sample page that shows how to use an ADO.NET Datareader with the new .NET Framework Data Provider for Oracle. We also created versions of this page for OLE DB/Oracle as well as SQL Server, to highlight the similarities and differences. The page performs a basic read using first a parameterized dynamic SQL statement (that will be properly pre-prepared and cached by the database for multiple executions); and then a stored procedure written in PL/1. One notable difference between the new .NET Framework Data Provider for Oracle and the Microsoft OLE DB Provider for Oracle is that the managed provider directly supports Ref cursors, while the OLE DB provider does not. Hence, you can now work much more easily with result set–returning stored procedures. The new NET Framework Data Provider for Oracle also supports new Oracle 9i datatypes.

ASP.NET Page Listing 1: Using the .NET Microsoft Oracle Managed Provider

<%@ Page Trace="False" Debug="True" Language="C#" %>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OracleClient"%>
<%-- Sample  --%>
<script language="C#" runat="server">


      //**************************************************************
      //This Page illustrates the use of the MS .NET Managed 
      //Provider for Oracle.  It performs a select using a    
      //parameterized dynamic SQL statement, displaying the 
      //results in the Web page. It requires the Nile Oracle 
      //database be installed on your network//and the Oracle 
      //Client be installed on the .NET application server 
      //machine.  Make sure to adjust the connection string to 
      //point to your named Oracle DB machine as configured for 
      //the Oracle client in the tnsnames file.
      //**************************************************************

void Page_Load(Object sender, EventArgs evArgs)
{
   //initialize Connection and DataReader to null
   OracleConnection myConnection = null;
   OracleDataReader myDataReader1 = null;
   OracleDataReader myDataReader2 = null;

   string connectionString = "Data Source=Ora-DB02;User 
ID=NILE;Password=NILE;";
   
   try {

      //Open connection to Database
      myConnection = new OracleConnection(connectionString);   
      myConnection.Open();         
      
      //get list of specials into a datareader either by 
      //executing dynamic SQL or calling a stored procedure
      myDataReader1 = GetSpecials_DynamicSQL(myConnection);
      

      //Read through First DataReader and list to page
      // display product specials
      if (!myDataReader1.Read()) 
      {
         specialsList1.InnerHtml = "There are currently no 
books on special.";
      }
      else 
      {
         string bookID;
         string bookTitle;
         string author;
         
         //Since we have already called Read on DataReader to 
         //verify that products exist we use a do..while loop 
         //to read the rest of the records.
      
         StringBuilder bookString = new StringBuilder("<p>");
         do
         {
            //Performance Tip:  Rather than using column 
            //names as Ordinal values, use the column 
            //ordinal. Issue is that if table structure, 
            //calling SQL, or Stored Procedure change
            //then we could be outputting incorrect values. 
            //A way around this is to call GetOrdinal once, 
            //storing the Ordinal in a variable and from 
            //then on referring to it by ordinal using the 
            //variable value.

            bookTitle = myDataReader1.GetString(1);
         //["booktitle"]
            author = myDataReader1.GetString(2);
            //["author"]
            bookString.Append("\n<b> ");
            bookString.Append(bookTitle);
            bookString.Append(" </b><br>by ");
            bookString.Append(author);
            bookString.Append(" <br><br>");
            bookString.Append("\n</li>");
         }while (myDataReader1.Read());
         myDataReader1.Close();
         bookString.Append("\n</p>");
         specialsList1.InnerHtml = bookString.ToString();

      }

      myDataReader2 = GetSpecials_Procedure(myConnection);   
      
      //Read through Second DataReader and list to page
      // display product specials
      if (!myDataReader2.Read()) 
      {
         specialsList2.InnerHtml = "There are currently no 
books on special.";
      }
      else 
      {
         string bookID;
         string bookTitle;
         string author;
         
         //Since we have already called Read on DataReader to 
         //verify that products exist we use a do..while loop 
         //to read the rest of the records.
      
         StringBuilder bookString = new StringBuilder("<p>");
         do
         {
            //Performance Tip:  Rather than using column 
            //names as Ordinal values, use the column 
            //ordinal. Issue is that if table structure, 
            //calling SQL, or Stored Procedure change
            //then we could be outputting incorrect values. 
            //A way around this is to call GetOrdinal once, 
            //storing the Ordinal in a variable and from 
            //then on referring to it by ordinal using the 
            //variable value.

            bookTitle = myDataReader2.GetString(1);
            //["booktitle"]
            author = myDataReader2.GetString(2);
            //["author"]
            bookString.Append("\n<b> ");
            bookString.Append(bookTitle);
            bookString.Append(" </b><br>by ");
            bookString.Append(author);
            bookString.Append(" <br><br>");
            bookString.Append("\n</li>");
         }while (myDataReader2.Read());
         myDataReader2.Close();
         bookString.Append("\n</p>");
         specialsList2.InnerHtml = bookString.ToString();

      }                           
   }
   catch (OracleException e) {
      //list exception message
      //add any exception handling 
      throw e;
         
   }   
   finally {
         //check if Connection is not null we need to close it.
         if (myConnection != null){
            myConnection.Close();
            myConnection = null;
         }
         
         //check if DataReader is not null we need to close it.
         if (myDataReader1 != null){
            myDataReader1.Close();
            myDataReader1 = null;
         }
         if (myDataReader2 != null){         
            myDataReader2.Close();
            myDataReader2 = null;
         }
   }
}

OracleDataReader GetSpecials_DynamicSQL(OracleConnection currentConnection) 
{
   OracleDataReader myDataReader;
   int subjectidin = 21;
   Object my_DBNull;

   
   try {
      
      my_DBNull = Convert.DBNull;
      OracleCommand myCommand = new OracleCommand();
      myCommand.Connection = currentConnection;
      myCommand.CommandType = CommandType.Text;

      //Get List of Specials      
      subjectidin = 21;
      myCommand.Parameters.Add(new OracleParameter("SUBJECTIDIN", 
OracleType.Int32, 0, ParameterDirection.Input, 
true,0,0,"",DataRowVersion.Default, my_DBNull) );         
      myCommand.CommandText = "SELECT BOOKID, BOOKTITLE, AUTHOR, 
PRICE, RETAIL FROM PRODUCTS WHERE SUBJECTID = :SUBJECTIDIN AND ROWNUM 
<= 5";
      
      //set SUBJECTIDIN parameter value to 21 (Specials)
      myCommand.Parameters[0].Value = subjectidin;
   
      myDataReader = myCommand.ExecuteReader();
      return (myDataReader);
   }   
   catch (OracleException e) {
      throw e;
   }
}

OracleDataReader GetSpecials_Procedure(OracleConnection currentConnection) 
{
   OracleDataReader myDataReader;
   Object my_DBNull;

   try {
      my_DBNull = Convert.DBNull;

      //**************************************************************
      //Get List of Specials by calling Procedure 
      //LISTBYSUBJECT_SAMPLE
      //**************************************************************
      //Procedure LISTBYSUBJECT_SAMPLE
      //   (   P_CURSOR OUT NILE_TYPES.NILE_CURSOR,
      //      SUBJECTIDIN INTEGER
      //   )
      //   AS
      //   BEGIN
      //   OPEN P_CURSOR FOR
      //   SELECT BOOKID, BOOKTITLE, AUTHOR, PRICE, RETAIL
      //   FROM PRODUCTS
      //   WHERE SUBJECTID = SUBJECTIDIN
      //   AND ROWNUM <= 5;
      //   END LISTBYSUBJECT_SAMPLE;
      //**************************************************************


      OracleDataReader myReader;
      int subjectidin = 21;


      OracleCommand myCommand = new OracleCommand();
      myCommand.Connection = currentConnection;
      myCommand.CommandType = CommandType.StoredProcedure;
      myCommand.CommandText = "LISTBYSUBJECT_SAMPLE";
      
      //Oracle Procedure Requires RefCursor output parameter
      myCommand.Parameters.Add(new OracleParameter("P_CURSOR", 
OracleType.Cursor, 2000, ParameterDirection.Output, 
true,0,0,"",DataRowVersion.Default, my_DBNull) );         
      myCommand.Parameters.Add(new OracleParameter("SUBJECTIDIN", 
OracleType.Int32, 0, ParameterDirection.Input, 
true,0,0,"",DataRowVersion.Default, my_DBNull) );         
      
      //set SUBJECTIDIN parameter value to 21 (Specials)
      myCommand.Parameters[1].Value = subjectidin;
   
      myDataReader = myCommand.ExecuteReader();

      return (myDataReader);
   }   
   catch (OracleException e) {
      throw e;
   }
}
</script>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Oracle Managed Provider Client</title>
</head>
<body>
<p>
Result Set From Dynamic SQL (Oracle Managed Provider)
</p>
<span id="specialsList1" runat="server">;</span>
<p>
Result Set From Stored Procedure (Oracle Managed Provider)
</p>
<span id="specialsList2" runat="server">;</span>
</body>
</html>

ASP.NET Page Listing 2: Using the Microsoft OLE DB Provider for Oracle

<%@ Page Trace="False" Debug="True" Language="C#" %>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<%

      //**************************************************************
      //This Page illustrates the use of the MS OLE DB Provider 
      //For Oracle.  It performs a select using a parameterized
      //dynamic SQL statement, displaying the results in the Web page.  
      //It requires the Nile Oracle database be installed on your network
      //and the Oracle Client be installed on the .NET application server 
      //machine.  Make sure to adjust the connection string to point to
      //your named Oracle DB machine as configured for the Oracle client
      //in the tnsnames file.
      //**************************************************************
%>
<script language="C#" runat="server">

void Page_Load(Object sender, EventArgs evArgs)
{
   //initialize Connection and DataReader to null
   OleDbConnection myConnection = null;
   OleDbDataReader myDataReader1 = null;
   OleDbDataReader myDataReader2 = null;


   string connectionString = "provider=MSDAORA;Data Source=MGB;User 
ID=NILE3;Password=NILE3;";
      
   try {

      //Open connection to Database
      myConnection = new OleDbConnection(connectionString);   
      myConnection.Open();         
      
      //get list of specials into a datareader either by 
executing dynamic SQL or calling a stored procedure
      myDataReader1 = GetSpecials_DynamicSQL(myConnection);
      

      //Read through DataReader and list to page
      // display product specials
      if (!myDataReader1.Read()) 
      {
         specialsList1.InnerHtml = "There are currently no 
books on special.";
      }
      else 
      {
         string bookID;
         string bookTitle;
         string author;
         
         //since we have already called Read on DataReader to 
verify that products exist
         //we use a do..while loop to read the rest of the 
records.
      
         StringBuilder bookString = new StringBuilder("<p>");
         do
         {
            //Performance Tip:  Rather than using column 
names as Ordinal values use the column ordinal. 
            //Issue is that if table structure, calling 
SQL, or Stored Procedure change
            //than we could be outputting incorrect values. 
A way around this is to call GetOrdinal once, 
            //storing the Ordinal in a variable and from 
then on referring to it by ordinal using the variable value.

            bookTitle = myDataReader1.GetString(1);      
   //["booktitle"]
            author = myDataReader1.GetString(2);      
   //["author"]
            bookString.Append("\n<b> ");
            bookString.Append(bookTitle);
            bookString.Append(" </b><br>by ");
            bookString.Append(author);
            bookString.Append(" <br><br>");
            bookString.Append("\n</li>");
         }while (myDataReader1.Read());
         myDataReader1.Close();   
         bookString.Append("\n</p>");
         specialsList1.InnerHtml = bookString.ToString();

      }   
      
      
      myDataReader2 = GetSpecials_Procedure(myConnection);   
      
      //Read through Second DataReader and list to page
      // display product specials
      if (!myDataReader2.Read()) 
      {
         specialsList2.InnerHtml = "There are currently no 
books on special.";
      }
      else 
      {
         string bookID;
         string bookTitle;
         string author;
         
         //since we have already called Read on DataReader to 
verify that products exist
         //we use a do..while loop to read the rest of the 
records.
      
         StringBuilder bookString = new StringBuilder("<p>");
         do
         {
            //Performance Tip:  Rather than using column 
names as Ordinal values use the column ordinal. 
            //Issue is that if table structure, calling 
SQL, or Stored Procedure change
            //than we could be outputting incorrect values. 
A way around this is to call GetOrdinal once, 
            //storing the Ordinal in a variable and from 
then on referring to it by ordinal using the variable value.

            bookTitle = myDataReader2.GetString(1);      
   //["booktitle"]
            author = myDataReader2.GetString(2);      
      //["author"]
            bookString.Append("\n<b> ");
            bookString.Append(bookTitle);
            bookString.Append(" </b><br>by ");
            bookString.Append(author);
            bookString.Append(" <br><br>");
            bookString.Append("\n</li>");
         }while (myDataReader2.Read());
         myDataReader2.Close();
         bookString.Append("\n</p>");
         specialsList2.InnerHtml = bookString.ToString();

      }

                  
   }
   catch (OleDbException e) {
      //list exception message
      //add any exception handling 
      int temp = 3;   
   }   
   finally {
         //check if Connection is not null we need to close it.
         if (myConnection != null){
            myConnection.Close();
            myConnection = null;
         }
         
         //check if DataReader is not null we need to close it.
         if (myDataReader1 != null){
            myDataReader1.Close();
            myDataReader1 = null;
         }

         if (myDataReader2 != null){         
            myDataReader2.Close();
            myDataReader2 = null;
         }

         
   }
}

OleDbDataReader GetSpecials_DynamicSQL(OleDbConnection currentConnection) 
{
   OleDbDataReader myDataReader;
   int subjectidin = 21;
   Object my_DBNull;

   
   try {
      
      my_DBNull = Convert.DBNull;
      OleDbCommand myCommand = new OleDbCommand();
      myCommand.Connection = currentConnection;
      myCommand.CommandType = CommandType.Text;

      //Get List of Specials      
      subjectidin = 21;
      //myCommand.Parameters.Add(new 
OleDbParameter("SUBJECTIDIN", OleDbType.Integer, 0, 
ParameterDirection.Input, true,0,0,"",DataRowVersion.Default, 
my_DBNull) );         
      myCommand.CommandText = "SELECT BOOKID, BOOKTITLE, AUTHOR, 
PRICE, RETAIL FROM PRODUCTS WHERE SUBJECTID = 21 AND ROWNUM <= 5";
      
      //set SUBJECTIDIN parameter value to 21 (Specials)
      //myCommand.Parameters[0].Value = subjectidin;
   
      myDataReader = myCommand.ExecuteReader();
      return (myDataReader);
   }   
   catch (OleDbException e) {
      throw e;
   }
   
}

OleDbDataReader GetSpecials_Procedure(OleDbConnection 
currentConnection) 
{
   OleDbDataReader myDataReader;
   Object my_DBNull;

   try {
      my_DBNull = Convert.DBNull;

      //**************************************************************
      //Get List of Specials by calling Procedure 
LISTBYSUBJECT_SAMPLE
      //**************************************************************
      //Procedure LISTBYSUBJECT_SAMPLE
      //   (   P_CURSOR OUT NILE_TYPES.NILE_CURSOR,
      //      SUBJECTIDIN INTEGER
      //   )
      //   AS
      //   BEGIN
      //   OPEN P_CURSOR FOR
      //   SELECT BOOKID, BOOKTITLE, AUTHOR, PRICE, RETAIL
      //   FROM PRODUCTS
      //   WHERE SUBJECTID = SUBJECTIDIN
      //   AND ROWNUM <= 5;
      //   END LISTBYSUBJECT_SAMPLE;
      //**************************************************************


      OleDbDataReader myReader;
      int subjectidin = 21;


      OleDbCommand myCommand = new OleDbCommand();
      myCommand.Connection = currentConnection;
      myCommand.CommandType = CommandType.StoredProcedure;
      myCommand.CommandText = "LISTBYSUBJECT_SAMPLE";
      
      //Oracle Procedure Requires RefCursor output parameter
      //myCommand.Parameters.Add(new OracleParameter("P_CURSOR", 
OracleType.Cursor, 2000, ParameterDirection.Output, 
true,0,0,"",DataRowVersion.Default, my_DBNull) );         
      myCommand.Parameters.Add(new OleDbParameter("SUBJECTIDIN", 
OleDbType.Integer, 0, ParameterDirection.Input, 
true,0,0,"",DataRowVersion.Default, my_DBNull) );         
      
      //set SUBJECTIDIN parameter value to 21 (Specials)
      myCommand.Parameters[0].Value = subjectidin;
   
      myDataReader = myCommand.ExecuteReader();

      return (myDataReader);
   }   
   catch (OleDbException e) {
      throw e;
   }
}



</script>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Ole Db Client </title>
</head>
<body>
<p>
Result Set from Dynamic SQL
</p>
<span id="specialsList1" runat="server">;</span>

<p>
Result Set from Stored Procedure (Ole DB)
</p>
<span id="specialsList2" runat="server">;</span>
</body>
</html>

ASP.NET Page Listing 3: Using the MS .NET Provider for SQL Server

<%@ Page Trace="False" Debug="True" Language="C#" %>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<%-- Sample  --%>
<script language="C#" runat="server">


      //**************************************************************
      //This Page illustrates the use of the MS .NET Managed 
      //Provider for SQL Server. It performs a select using a 
      //parameterized dynamic SQL statement, and a stored 
      //procedure, displaying the results in the Web page.  
      //It requires the Nile SQL Server database be installed on 
      //your network. Make sure to adjust the connection string 
      //to point to your named SQL DB machine.
      //**************************************************************

void Page_Load(Object sender, EventArgs evArgs)

{
   //initialize Connection and DataReader to null
   SqlConnection myConnection = null;
   SqlDataReader myDataReader1 = null;
   SqlDataReader myDataReader2 = null;

   string connectionString = "Data Source=DB01;Initial 
Catalog=NILE;User ID=NILE;Password=NILE;";
      
   try {

      //Open connection to Database
      myConnection = new SqlConnection(connectionString);   
      myConnection.Open();         
      
      //get list of specials into a datareader, either by 
      //executing dynamic SQL or calling a stored procedure
      myDataReader1 = GetSpecials_DynamicSQL(myConnection);

      //Read through DataReader and list to page
      // display product specials
      if (!myDataReader1.Read()) 
      {
         specialsList1.InnerHtml = "There are currently no books on special.";
      }
      else 
      {
         string bookID;
         string bookTitle;
         string author;
         
         //Since we have already called Read on DataReader to 
         //verify that products exist, we use a do..while loop 
         //to read the rest of the records.
      
         StringBuilder bookString = new StringBuilder("<p>");
         do
         {
            //Performance Tip:  Rather than using column 
            //names as Ordinal values use the column ordinal. 
            //Issue is that if table structure, calling 
            //SQL, or Stored Procedure change
            //than we could be outputting incorrect values. 
            //A way around this is to call GetOrdinal once, 
            //storing the Ordinal in a variable and from 
            //then on referring to it by ordinal using the 
            //variable value.

            bookTitle = myDataReader1.GetString(1);
            //["booktitle"]
            author = myDataReader1.GetString(2);
            //["author"]
            bookString.Append("\n<b> ");
            bookString.Append(bookTitle);
            bookString.Append(" </b><br>by ");
            bookString.Append(author);
            bookString.Append(" <br><br>");
            bookString.Append("\n</li>");
         }while (myDataReader1.Read());

         bookString.Append("\n</p>");
         specialsList1.InnerHtml = bookString.ToString();

         myDataReader1.Close();

      }      

      myDataReader2 = GetSpecials_Procedure(myConnection);         

      //Read through DataReader and list to page
      // display product specials
      if (!myDataReader2.Read()) 
      {
         specialsList2.InnerHtml = "There are currently no 
         books on special.";
      }
      else 
      {
         string bookID;
         string bookTitle;
         string author;
         
         //since we have already called Read on DataReader to 
         //verify that products exist
         //we use a do..while loop to read the rest of the records.
      
         StringBuilder bookString = new StringBuilder("<p>");
         do
         {
            //Performance Tip:  Rather than using column 
names as Ordinal values use the column ordinal. 
            //Issue is that if table structure, calling 
SQL, or Stored Procedure change
            //than we could be outputting incorrect values. 
A way around this is to call GetOrdinal once, 
            //storing the Ordinal in a variable and from 
then on referring to it by ordinal using the variable value.

            bookTitle = myDataReader2.GetString(1);
            //["booktitle"]
            author = myDataReader2.GetString(2);
            //["author"]
            bookString.Append("\n<b> ");
            bookString.Append(bookTitle);
            bookString.Append(" </b><br>by ");
            bookString.Append(author);
            bookString.Append(" <br><br>");
            bookString.Append("\n</li>");
         }while (myDataReader2.Read());

         bookString.Append("\n</p>");
         specialsList2.InnerHtml = bookString.ToString();

         myDataReader2.Close();


      }      
   }
   catch (SqlException e) {
      //list exception message
      //add any exception handling 
      throw e;
   }   
   finally {         
         //check if DataReader is not null we need to close it.
         if (myDataReader1 != null){
            myDataReader1.Close();
            myDataReader1 = null;
         }
         if (myDataReader2 != null){
            myDataReader2.Close();
            myDataReader2 = null;
         }

         //check if Connection is not null we need to close it.
         if (myConnection != null){
            myConnection.Close();
            myConnection = null;
         }
   }
}

SqlDataReader GetSpecials_DynamicSQL(SqlConnection currentConnection) 
{
   SqlDataReader myDataReader = null;
   int subjectidin = 21;
   Object my_DBNull;
   
   try {
      my_DBNull = Convert.DBNull;
      SqlCommand myCommand = new SqlCommand();
      myCommand.Connection = currentConnection;
      myCommand.CommandType = CommandType.Text;

      //Get List of Specials      
      //Specials subjectid = 21
      myCommand.Parameters.Add(new SqlParameter("@SubjectID", 
SqlDbType.Int, 4, ParameterDirection.Input, 
true,0,0,"",DataRowVersion.Default, my_DBNull) );         
      myCommand.CommandText = "SELECT TOP 5 BOOKID, BOOKTITLE, 
AUTHOR, PRICE, RETAIL FROM PRODUCTS WHERE SUBJECTID = @SubjectID";


      //set SUBJECTIDIN parameter value to 21 (Specials)
      myCommand.Parameters[0].Value = subjectidin;

      myDataReader = myCommand.ExecuteReader();
      return (myDataReader);
   }   
   catch (SqlException e) {

   //check if Connection is not null we need to close it.
      if (currentConnection != null){
            currentConnection.Close();
            currentConnection = null;
         }
         
         //check if DataReader is not null we need to close it.
      if (myDataReader != null){
            myDataReader.Close();
            myDataReader = null;
         }
      
      throw e;
   }
}

SqlDataReader GetSpecials_Procedure(SqlConnection currentConnection) 
{
   SqlDataReader myDataReader;
   Object my_DBNull;

   try {
      my_DBNull = Convert.DBNull;

      //**************************************************************
      //Get List of Specials by calling Procedure LISTBYSUBJECT_SAMPLE
      //**************************************************************
      //   Stored Procedure ListBySubject_Sample
      //      CREATE procedure ListBySubject_Sample
      //      (
      //         @SubjectID Integer
      //      )
      //      AS
      //      SELECT top 5 BookID, BookTitle, Author, Price, Retail
      //      FROM Products
      //      where SubjectID = @SubjectID
      //      RETURN
      //
      //      GO
      //**************************************************************


      SqlDataReader myReader;
      int subjectidin = 21;


      SqlCommand myCommand = new SqlCommand();
      myCommand.Connection = currentConnection;
      myCommand.CommandType = CommandType.StoredProcedure;
      myCommand.CommandText = "LISTBYSUBJECT_SAMPLE";
      
      
      myCommand.Parameters.Add(new SqlParameter("@SubjectID", 
      SqlDbType.Int, 4, ParameterDirection.Input, 
      true,0,0,"",DataRowVersion.Default, my_DBNull) );         
      
      //set @SubjectID parameter value to 21 (Specials)
      myCommand.Parameters[0].Value = subjectidin;
   
      myDataReader = myCommand.ExecuteReader();

      return (myDataReader);
   }   
   catch (SqlException e) {
      throw e;
   }
}
</script>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>SQL Server Client (SqlClient) </title>
</head>
<body>
<p>
Result Set From Dynmaic SQL (SqlClient)
</p>
<span id="specialsList1" runat="server">;</span>
<p>
Result Set From Stored Procedure (SqlClient)
</p>
<span id="specialsList2" runat="server">;</span>
</body>
</html>

The Nile 3.0 Application

To show developers how to build applications using the new NET Framework Data Provider for Oracle, this article includes sample code for the Nile application. The Nile application was originally written in C# for SQL Server data access, and took advantage of the Data Access Application Block (DAAB) for SQL Server (see https://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp), a set of helper classes for ADO.NET published on MSDN to help developers use ADO.NET using high-performance programming practices. For this article, we have ported the Nile application for SQL Server to Oracle, and provided a version that uses the NET Framework Data Provider for Oracle as well as a version that uses OLE DB. All three versions are available in the single download at the top of this article, along with the install scripts for the databases. Because the SQL Server version of Nile used the Data Access Code Blocks (DAAB), we also ported the DAAB classes to work with Oracle, and are providing these with the sample Nile 3.0 application (see the OracleHelper.cs and OleDBHelper.cs in the sample Nile 3.0 download for more). The DAAB classes for Oracle can be used as a set of general classes to help you use ADO.NET with System.Data.OracleClient in a streamlined fashion, and if you are already using the DAAB with SQL Server, then moving your code to work with Oracle will be even easier.

The Benchmark Tests

The benchmark was conducted using the Nile 3.0 application, which is a typical data-driven, 3-tier Web application. The application was tested with Web clients accessing a middle-tier application server, which in turn accessed a separate machine running the database. The benchmark software was Benchmark Factory 3.1 from Quest Software, and a heavy load was generated by over 100 client machines making constant requests to the Nile application. Clients were run without think times to generate even more stress. The data tier consisted of Oracle 9i running on a Compaq ProLiant 8500 system with 8 Pentium III 550 Mhz CPUs and 4 GB RAM. The application server running the Nile application and the .NET Framework was a Compaq ProLiant 8500 system with 2 and 4 Pentium III 550 Mhz CPUs, and 2 GB RAM. The design of the benchmark tests was such that the database never was pushed beyond 30-percent CPU utilization, and was not the bottleneck. The middle tier, however, was pushed in all cases to 100-percent CPU utilization, at which point throughput (measured in pages served per second) peaked. The results are summarized below.

Note This is not a database benchmark, but rather a benchmark of the MS OLE DB Data Provider for Oracle vs. .NET Framework Data Provider for Oracle databases.

Nile Application with .NET Framework Data Data Provider for Oracle Throughput (Oracle 9i)

1-CPU System

Users Pgs/Sec Errors
0 0 0
25 190.22 0
50 191.86 0
100 186.61 0
200 185.09 0
500 180.47 0

4-CPU System

Users Pgs/Sec Errors
0 0 0
25 214.21 0
50 428.14 0
100 526.78 0
200 529.74 0
500 532.3 0

Nile Application with Microsoft OLE DB Data Provider for Oracle Throughput (Oracle 9i)

1-CPU System

Users Pgs/Sec Errors
0 0 0
25 103.11 1
50 101.64 20
100 103.17 14
200 103.11 12
500 87.93 18

4-CPU System

Users Pgs/Sec Errors
0 0 0
25 180.53 0
50 153.91 0
100 145.04 0
200 153.43 0
500 136.63 0

Figure 1. Throughput curve for .NET Framework Data Provider for Oracle and Microsoft OLE DB Data Provider for Oracle on a single-CPU Compaq Proliant 8500 system.

Figure 2. Throughput curve for .NET Framework Data Provider for Oracle and Microsoft OLE DB Data Provider for Oracle on a 4-CPU Compaq Proliant 8500 system.

Figure 3. Comparison of peak throughput and vertical scalability for .NET Framework Data Provider for Oracle Managed Provider vs. Microsoft OLE DB Data Provider for Oracle. This graph shows that the .NET Framework Data Provider scales much better as multiple CPUs are added to the system.

Summary

The new NET Framework Data Provider for Oracle enables .NET applications to access Oracle databases in a much more efficient, scalable manner than possible with OLE DB. The Nile application provides sample code that illustrates how to use the NET Framework Data Provider for Oracle with ADO.NET via the System.Data.OracleClient classes that extend the .NET Framework. The sample application also includes the Data Access Application Block helper classes based on the OracleClient classes that mirror the functionality provided by the SQL Server Data Access Application Block also available on MSDN. The Data Access Application Block for the .NET Framework Data Provider for Oracle (see the OracleHelp.cs file in the sample) can be used with any .NET application to speed development of .NET applications that access Oracle back-end databases.