DB Transactions using .NET Data Providers

Oracle and SQL Server 2000 Database Transactions  using .NET Framework
with C# and Sql Server and Oracle Data Provider

Martin Zahn, Akadia AG, Information Technology, CH-3604 Thun
January 2003, Copyright © 2003, Akadia AG, all rights reserved

Contents

1.   Introduction
2.   Available .NET Framework Data Providers
3.   Setup of the .NET Framework
4.   Transactions using SqlClient Data Provider to access SQL Server 2000
5.   Transactions using Oracle .NET Data Provider to access Oracle 9i

More Information:

Oracle and SQL Server DB Access with SqlNet, OleDb and Odbc Data Provider using .NET Framework and C#

 
1.  Introduction

Summary

Implementing a basic transaction using ADO.NET in an application can be fairly straightforward. The most common sequence of steps that would be performed while developing a transactional application is as follows:

• Open a database connection using the Open method of the connection object.
• Begin a transaction using the BeginTransaction method of the connection object. This method provides us with a transaction object that we will use later to commit or rollback the transaction. Note that changes caused by any queries executed before calling the BeginTransaction method will be committed to the database immediately after they execute.
• Set the Transaction property of the command object to the above mentioned transaction object.
• Execute the SQL commands using the command object. We may use one or more command objects for this purpose, as long as the Transaction property of all the objects is set to a valid transaction object.
• Commit or roll back the transaction using the Commit or Rollback method of the transaction object.
• Close the database connection.

A data provider in the .NET Framework serves as a bridge between an application and a data source. A .NET Framework data provider enables you to return query results from a data source, execute commands at a data source, and propagate changes in a DataSet to a data source. This article includes tips on which .NET Framework data provider is best suited for your needs.

2.  Available .NET Framework Data Providers

Which .NET Framework Data Provider to Use?

To achieve the best performance for your application, use the .NET Framework data provider that is most appropriate for your data source. There are a number of data provider options for use in your applications. The following table provides information about the available data providers and which data sources a data provider is most appropriate for.

Provider	Details
SQL Server .NET Data Provider	Found in the System.Data.SqlClient namespace. Recommended for middle-tier applications using Microsoft SQL Server version 7.0 or later. Recommended for single-tier applications using the Microsoft Data Engine (MSDE) or Microsoft SQL Server 7.0 or later.
OLE DB .NET Data Provider	Found in the System.Data.OleDb namespace. Recommended for middle-tier applications using Microsoft SQL Server 6.5 or earlier, or any OLE DB provider that supports the OLE DB interfaces listed in OLE DB Interfaces Used by the OLE DB .NET Data Provider in the .NET Framework SDK. For Microsoft SQL Server 7.0 or later, the .NET Framework Data Provider for SQL Server is recommended. Recommended for single-tier applications using a Microsoft® Access database. Use of an Access database for a middle-tier application is not recommended.
ODBC .NET Data Provider	Found in the Microsoft.Data.Odbc namespace. The ODBC .NET Data Provider is available for download. Provides access to data sources that are connected to using an ODBC driver.
Oracle .NET Data Provider	Found in the System.Data.OracleClient namespace. 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. The Oracle .NET Data Provider is available for download

3.   Setup of the .NET Framework

The Microsoft® .NET Framework is the infrastructure for the overall .NET Platform. The common language runtime and class libraries (including Microsoft Windows® Forms, ADO.NET, and ASP.NET) combine to provide services and solutions that can be easily integrated within and across a variety of systems.

The .NET Framework provides a fully managed, protected, and feature-rich application execution environment, simplified development and deployment, and seamless integration with a wide variety of languages.

Microsoft .NET Framework Software Development Kit

The Microsoft® .NET Framework Software Development Kit (SDK) includes the .NET Framework, as well as everything you need to write, build, test, and deploy .NET Framework applications - documentation, samples, and command-line tools and compilers.

Download and Installation

Goto:

http://msdn.microsoft.com/downloads/

Select:

-> Software Development Kits
-> Microsoft .NET Framework SDK

You'll get the whole framework and a C# command line compiler. Run the downloaded setup.exe if you haven't installed yet Microsoft .NET and follow the installation steps. You will be asked for Server Components which you don't need. If the installation asks for Microsoft Data Access Components MDAC you may continue or quit the installation. Anyway, you need to install MDAC 2.7 or higher prior ODBC data access is going to work.

4.    Transactions using SqlClient Data Provider to access SQL Server 2000

The SqlClient provider ships with ADO.NET and resides in the System.Data.SqlClient namespace. It should be used to access SQL Server 2000. The classes within SqlClient provider all begin with "Sql", so the connection class is SqlConnection, the command class is SqlCommand, and so on. Lets look at an example to update SQL Server 2000.

using System;
using System.Data;
using System.Data.SqlClient;

namespace TranDemoSqlClient
{
 class TranDemoSqlClient
 {
  static void Main(string[] args)
  {
   // Declare database objects such as connection,
   // command and transaction
   string connectionString = "server=localhost;"
     + "database=Northwind;uid=sa;pwd=manager";
   SqlConnection cnn;
   SqlCommand cmd;
   SqlTransaction tran;

   // Open a connection to SQL Server 2000
   cnn = new SqlConnection(connectionString);
   cnn.Open();

   // Begin the transaction
   tran = cnn.BeginTransaction();

   // Configure command object to use the transaction
   cmd=new SqlCommand();
   cmd.Connection=cnn;
   cmd.Transaction=tran;

   // Put transaction commands in a try ... catch block
   try
   {
    // INSERT row in Orders Table
    cmd.CommandText = "INSERT INTO Orders " +
      "(CustomerID,OrderDate,RequiredDate) " +
      "VALUES('ALFKI',GetDate(),DATEADD(d,15,GetDate()))";
    long rows = cmd.ExecuteNonQuery();
    Console.WriteLine("Rows inserted in Orders: {0} ", rows);

    // Get and Store IDENTITY (Primary Key) for further
    // INSERTS in child table [Order Details]
    cmd.CommandText = "SELECT @@identity";
    string id = cmd.ExecuteScalar().ToString();

    // INSERT row in [Order Details]
    cmd.CommandText = "INSERT INTO [Order Details] " +
      "(OrderID,ProductID,UnitPrice,Quantity) " +
      "VALUES(" + id + ",1,18,25)";
    rows = cmd.ExecuteNonQuery();
    Console.WriteLine("Rows inserted in [Order Details]: {0} ", rows);

    // UPDATE Orders Table with values from commandline
    string customerid = args[0];
    decimal freight = decimal.Parse(args[1]);

    string query="UPDATE Orders SET freight = "
    + freight + " WHERE customerid = '" + customerid + "'";
    cmd.CommandText = query;
    rows = cmd.ExecuteNonQuery();
    Console.WriteLine("Rows updated in [Orders]: {0} ", rows);

    // Commit the transaction ....
    tran.Commit();
    Console.WriteLine("Commit complete");
   }
   // ... or Rollback everything in case of an error
   catch(Exception e)
   {
    tran.Rollback();
    Console.WriteLine("Transaction failed - Rolled Back!");
    Console.WriteLine(e.Message);
   }
  }
 }
}

This file is called TranDemoSqlClient.cs, we can compile it from the command line simply by typing csc TranDemoSqlClient.cs. There is no need to add any references.

7.    Transactions using Oracle .NET Data Provider to access Oracle 9i

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.

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.

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.

Installation and Configuration

The Microsoft® .NET Framework Data Provider for Oracle is an add-on component to the Microsoft .NET Framework that provides access to an Oracle database using the Oracle Call Interface (OCI) as provided by Oracle Client software. Oracle 8i Release 3 (8.1.7) Client or later must be installed for this provider to function.

The Oracle .NET Data Provider is available for download

The following files are installed by Setup:

File name	Description
Eula.rtf	.NET Framework Data Provider for Oracle end-user license agreement.
Oracleref.chm	.NET Framework Data Provider for Oracle documentation.
Oracleref.chi	Index file that accompanies Oracleref.chm (.NET Framework Data Provider for Oracle documentation).
Readme.txt	Additional product information that is not contain in Oracleref.chm (.NET Framework Data Provider for Oracle documentation).
System.Data.OracleClient.dll	The .NET Framework Data Provider for Oracle.
Mtxoci8.dll	DLL that provides distributed transaction support.

All of these files, except Mtxoci8.dll, are installed in C:\Program Files\Microsoft.NET\OracleClient.Net by default (assuming that C:\Program Files is your default Program Files folder location). Mtxoci8.dll is installed in the windows system directory (for example, C:\Windows\System32 on a Windows 2000 computer on which C: is the system drive).

As part of Setup, the System.Data.OracleClient namespace is added to the global assembly cache.

The classes that compose the provider are in the System.Data.OracleClient namespace and all have the prefix "Oracle".   Lets look at an example to update Oracle 9.2.0.

using System;
using System.Data;
using System.Data.OracleClient;

namespace TranDemoOra
{
  class TranDemoOra
  {
    static void Main(string[] args)
    {
      // Declare database objects such as connection,
      // command and transaction
      string connectionString = "Data Source=ARK2;"
      + "User ID=scott; Password=tiger";
      OracleConnection cnn;
      OracleCommand cmd;
      OracleTransaction tran;

      // Open a connection to Oracle 9i
      cnn=new OracleConnection(connectionString);
      cnn.Open();

      // Begin the transaction
      tran=cnn.BeginTransaction();

      // Configure command object to use the transaction
      cmd=new OracleCommand();
      cmd.Connection=cnn;
      cmd.Transaction=tran;

      // Put transaction commands in a try ... catch block
      try
      {

        // UPDATE EMP Table with values from commandline
        int  empno=int.Parse(args[0]);
        string job=args[1];

        string query="UPDATE emp SET job = '"
        + job + "' WHERE empno = " + empno;
        cmd.CommandText=query;
        long rows = cmd.ExecuteNonQuery();

        // Commit the transaction ....
        tran.Commit();
        Console.WriteLine("Commit complete, {0} ", rows + " Rows updated");
      }

      // ... or Rollback everything in case of an error
      catch(Exception e)
      {
        tran.Rollback();
        Console.WriteLine("Transaction failed - Rolled Back!");
        Console.WriteLine(e.Message);
      }
    }
  }
}

This file is called TranDemoOra.cs, we can compile it from the command line by typing csc /r:System.Data.OracleClient.dll TranDemoOra.cs. The reference to the System.Data.OracleClient.dll is needed.