Using Oracle Database Transactions in .NET

In this article, you will learn how to use database transactions in Visual Basic .NET (VB.NET) and Visual C# .NET (C#). Specifically, you will explore database transactions, use OracleTransaction objects in .NET programs, and learn how to set a transaction savepoint. This article assumes familiarity with C# and VB.NET programming in general.

Required Software

Before you can follow along with the examples shown in this article, you will need to install the following software:

• Windows NT 4.0, Windows 2000, Windows XP Professional, or Windows Server 2003
• Access to an installation of the Oracle database (Oracle8i release 3 8.1.7 or later)
• Oracle Client (version 10.1.0.2.0 or later)
• Oracle Net (version 10.1.0.2.0 or later)
• Oracle Data Providers for .NET (version 10.1.0.2.0 or later)
• Microsoft .NET Framework (version 1.0 or later)
• Microsoft .NET Framework SDK (version 1.0 or later)

If you intend to develop and run applications using Enterprise Services transactions or distributed transactions then you will also need to install Oracle Services for Microsoft Transaction Server (10.1.0.2.0 or later).

You will need to download and install the .NET Framework as well as the SDK separately, and install the framework first. You will also need to download and install Oracle Database 10g, which includes Oracle Data Provider for .NET (ODP.NET). You can choose to install ODP.NET and the database server on separate machines or the same machine.

Note: The ODP.NET drivers are tuned for maximum performance when accessing an Oracle database, and also support the rich features of an Oracle database such as the BFILE, BLOB, CLOB, XMLType, and so on. If you are developing .NET applications running against an Oracle database, ODP.NET is your best data access solution for both features and performance.

Database Schema Setup

First you need to set up your database schema, which is a simplified example of a web store. You must first create a user named store and grant the required privileges to that user as follows (you must first log into the database as user with the CREATE USER privilege to create a user):

CREATE USER store IDENTIFIED BY store;

GRANT connect, resource TO store;

Note: You will find the two previous statements and the others shown in this section to set up the store schema in the source code file db1.sql.

The next two statements connect as the store user:

CONNECT store/store;

The following statements create the four required database tables named product_types and products:

CREATE TABLE product_types (

  product_type_id INTEGER

    CONSTRAINT product_types_pk PRIMARY KEY,

  name VARCHAR2(10) NOT NULL

);

CREATE TABLE products (

  product_id INTEGER

    CONSTRAINT products_pk PRIMARY KEY,

  product_type_id INTEGER

    CONSTRAINT products_fk_product_types

    REFERENCES product_types(product_type_id),

  name VARCHAR2(30) NOT NULL,

  description VARCHAR2(50),

  price NUMBER(5, 2)

);

Note: If you create the tables in a different schema to store, you will need change the schema name in the sample configuration files that you will see later.

The product_types table is used to store the names of the product types that may be stocked by an example online store, and the products table contains detailed information about the products sold.

The following INSERT statements add rows to the product_types and products tables:

INSERT INTO product_types (product_type_id, name) VALUES (1, 'Book');

INSERT INTO product_types (product_type_id, name) VALUES (2, ‘DVD’);

INSERT INTO products (product_id, product_type_id, name, description, price

) VALUES (1, 1, 'Modern Science', 'A description of modern science', 19.95);

INSERT INTO products ( product_id, product_type_id, name, description, price)

VALUES (2, 1, 'Chemistry', 'Introduction to Chemistry', 30.00);

INSERT INTO products (product_id, product_type_id, name, description, price)

VALUES (3, 2, 'Supernova', 'A star explodes', 25.99);

INSERT INTO products (product_id, product_type_id, name, description, price)

VALUES (4, 2, 'Tank War', 'Action movie about a future war', 13.95);

COMMIT;

Next, you will learn about database transactions.

Introducing Database Transactions

A database transaction is a group of SQL statements that are a logical unit of work. You can think of a transaction as an inseparable set of SQL statements that should be made permanent in the database (or undone) as a whole. An example would be a transfer of money between bank accounts: One UPDATE statement would subtract from the total amount of money from one account, and another UPDATE would add money to the other account. Both the subtraction and the addition must either be permanently recorded in the database, or they both must be undone—otherwise money will be lost. This simple example uses only two UPDATE statements, but a more realistic transaction may consist of many INSERT, UPDATE, and DELETE statements.

To permanently record the results of the SQL statements in a transaction, you perform a commit with the COMMIT statement. To undo the results of the SQL statements, you perform a rollback with the ROLLBACK statement, which resets all the rows back to what they were originally. Any changes you make prior to performing a rollback will be undone, as long as you haven’t disconnected from the database beforehand. You can also set a savepoint that may be used to rollback a transaction to a specific point, while leaving the other statements in the transaction intact.

Using Database Transactions in C# and VB.NET

You may use an object of the OracleTransaction class to represent a transaction. Two of the properties of the OracleTransaction class include Connection, which specifies the database connection associated with the transaction, and IsolationLevel, which specifies the transaction isolation level; you will learn more about transaction isolation levels later in this article.

The OracleTransaction class contains a number of methods to control a transaction. You use the Commit() method to permanently commit your SQL statements, and you use Rollback() to undo them. You may also use Save() to set a savepoint in a transaction.

I will now walk you through the steps in two example programs, one written in C# (TransExample1.cs) and the other in VB.NET (TransExample1.vb). These programs illustrate how to execute a transaction that contains two INSERT statements. The first INSERT will add a row to the product_types table, and the second will add a row to the products table.

Importing the Namespaces

The following C# program statements specify that the System and Oracle.DataAcess.Client namespaces are used in the program:

using System;

using Oracle.DataAccess.Client;

Here are the equivalent VB.NET statements:

Imports System

Imports Oracle.DataAccess.Client

The Oracle.DataAccess.Client namespace forms part of ODP.NET, and contains the OracleConnection, OracleCommand, and OracleTransaction classes, among many others. These classes are used in the example programs.

Step 1

The first step is to create an OracleConnection object to connect to the Oracle database and then open the connection.

In C#:

OracleConnection myOracleConnection =

  new OracleConnection(

    "User;Password=store;Data Source=ORCL"

  );

myOracleConnection.Open();

In VB.NET:

Dim myOracleConnection As New OracleConnection( _

  "User;Password=store;Data Source=ORCL")

myOracleConnection.Open()

The User Id and Password attributes specify the database user and password of the schema you want to connect to. The Data Source attribute specifies the Oracle Net service name for the database; the default service name for starter database is ORCL. If you are using a different database from the starter or your service name is different, then you will need to change the setting for the Data Source attribute in your program.

Step 2

The second step is to create an OracleTransaction object and start the transaction by calling the BeginTransaction() method of the OracleConnection object.

In C#:

OracleTransaction myOracleTransaction =

  myOracleConnection.BeginTransaction();

In VB.NET:

Dim myOracleTransaction As OracleTransaction = _

  myOracleConnection.BeginTransaction()

Step 3

The third step is to create an OracleCommand object to store a SQL statement.

In C#:

OracleCommand myOracleCommand = myOracleConnection.CreateCommand();

In VB.NET:

Dim myOracleCommand As OracleCommand =

  myOracleConnection.CreateCommand

Because the OracleCommand object was created using the CreateCommand() method of the OracleConnection object, it automatically uses the transaction for the OracleConnection object set in Step 2.

Step 4

The fourth step is to set the CommandText property of the OracleCommand object to the first INSERT statement that adds a row to the product_types table.

In C#:

myOracleCommand.CommandText =

  "INSERT INTO product_types (" +

  "  product_type_id, name" +

  ") VALUES (" +  "  3, 'Magazine'" +  ")";

In VB.NET:

myOracleCommand.CommandText = _

  "INSERT INTO product_types (" & _

  "  product_type_id, name" & _ ") VALUES (" & _

  "  3, 'Magazine'" & _  ")"

Step 5

The fifth step is to run the INSERT statement using the ExecuteNonQuery() method of the OracleCommand object.

In C#:

myOracleCommand.ExecuteNonQuery();

In VB.NET:

myOracleCommand.ExecuteNonQuery()

Steps 6 and 7

The sixth and seventh steps are to set the CommandText property of the OracleCommand object to the second INSERT statement that adds a row to the products table and run it.

In C#:

myOracleCommand.CommandText =

  "INSERT INTO products (" +

  "  product_id, product_type_id, name, description, price" +

  ") VALUES (" +

  "  5, 3, 'Oracle Magazine', 'Magazine about Oracle', 4.99" + ")";

myOracleCommand.ExecuteNonQuery();

In VB.NET:

myOracleCommand.CommandText = _

  "INSERT INTO products (" & _

  "  product_id, product_type_id, name, description, price" & _

  ") VALUES (" & _

  "  5, 3, 'Oracle Magazine', 'Magazine about Oracle', 4.99" &   ")"

myOracleCommand.ExecuteNonQuery()

Step 8

The eighth step is to commit the transaction in the database using the Commit() method of the OracleTransaction object.

In C#:

myOracleTransaction.Commit();

In VB.NET:

myOracleTransaction.Commit()

After the Commit() method has completed, the two new rows added by the INSERT statements will be made permanent in the database.

Step 9

The ninth step is to close the OracleConnection object using the Close() method.

In C#:

myOracleConnection.Close();

In VB.NET:

myOracleConnection.Close()

Compiling and Running the Example Programs

To compile the example C# program, you may use the csc command to run the C# compiler. Because the program uses the Oracle Data Access DLL, you use the /r option to specify the full path to that DLL; for example:

csc TransExample1.cs /r:C:\oracle\product\10.1.0\

  Client_1\bin\Oracle.DataAccess.dll

Note: You will need to replace the path to the DLL with the appropriate one for your computer. Also, if your computer cannot find the csc compiler, you may need to run the Microsoft sdkvars.bat script to first set the environment variables for the .NET SDK; you will find that script in the bin directory where you installed the .NET SDK.

If you get the following error:

Example1.cs(10,7): error CS0246: The type or namespace name 'Oracle'

could not be found (are you missing a using directive or an assembly reference?)

you haven’t correctly specified the Oracle Data Access DLL in your compilation command. (See John Paul Cook’s Technical Article “Building a .NET Application on the Oracle Database” for setup information.)

Here is the equivalent command for compiling the VB.NET program:

vbc TransExample1.vb /r:C:\oracle\product\10.1.0\

  Client_1\bin\Oracle.DataAccess.dll /r:system.dll /r:system.data.dll

Next, to run the example you enter the following:

TransExample1

You should see the output from the program. If, however, you get an exception similar to the following:

An exception was thrown

Message = ORA-12514: TNS:listener does not currently know

  of service requested in connect descriptor

your setting for the Data Source in the connection string to the OracleConnection object is incorrect. You should speak with your DBA or consult the Oracle Net documentation for further details.

• If you are using VS .NET, you can do the following to compile and run the C# program TransExample1.cs:
• Create a new C# console application. File>New Project, then pick Visual C# Projects, Console Application.
• Name the project TransExample1.
• Replace all the code generated by VS .NET with the code in TransExample1.cs.
• Add a reference to the Oracle.DataAccess.dll by selecting Project>Add Reference, then browse to the directory where you installed ODP.NET (on my machine it is in C:\oracle\product\10.1.0\Client_1\bin\Oracle.DataAccess.dll), and then double-click on Oracle.DataAccess.dll.
• MRun the program by selecting Debug>Start without Debugging.

To compile and run TransExample1.vb you perform a similar set of steps, except that you pick a Visual Basic console application in Step 1 and you replace the generated code with the code in TransExample1.vb at Step 3. Checking the Results of the Program

When you’ve run the C# or VB .NET program, you can check the results of the transaction using the following SELECT statement in SQL*Plus:

SELECT p.product_id, p.product_type_id, pt. name, p.name, p.description, p.price

FROM products p, product_types pt

WHERE p.product_type_id = pt.product_type_id

AND p.product_id = 5;

You will see the following result:

PRODUCT_ID PRODUCT_TYPE_ID NAME       NAME

---------- --------------- ---------- -----------------------

DESCRIPTION                                             PRICE

-------------------------------------------------- ----------

         5               3 Magazine   Oracle Magazine

Magazine about Oracle                                    4.99

Next, you will learn how to set a transaction savepoint.

Setting a Transaction Savepoint in a .NET Program

As mentioned earlier in this article, you can set a savepoint that may be used to rollback a transaction to a specific point, while leaving the other statements in the transaction intact. You use the Save() method of the OracleTransaction class to set a savepoint in a transaction.

You might want to use a savepoint if you have a very long transaction and you want to have the ability to rollback only to a specific point. For example, you might want to make some changes to 10 products, then set a savepoint, and then make changes to 10 more products; if you make a mistake in the second batch of changes, you can rollback to the savepoint and keep your first batch of changes intact.

I will now walk you through the relevant new steps in example C# (TransExample2.cs) and VB.NET (TransExample2.vb) programs that illustrate how to use a savepoint. The programs add a row to the products table, sets a savepoint, adds another row to the products table, performs a rollback to the savepoint, and then reads the rows from the products table. Only the first row added to the products table will remain after the rollback to the savepoint: the second row will have been removed.

Steps 1 to 3 are the same as those shown in the section “Using Database Transactions in C# and VB.NET” and are therefore omitted here.

Step 4

The fourth step adds a row to the products table with a product ID of 6.

In C#:

myOracleCommand.CommandText =

  "INSERT INTO products (" +

  "  product_id, product_type_id, name, description, price" +

  ") VALUES (" +

  "  6, 2, 'Man from Another World', 'Man from Venus lands on Earth', 24.99" +

  ")";

myOracleCommand.ExecuteNonQuery();

In VB.NET:

myOracleCommand.CommandText = _

  "INSERT INTO products (" & _

  "  product_id, product_type_id, name, description, price" & _

  ") VALUES (" & _

  "  6, 2, 'Man from Another World', 'Man from Venus lands on Earth', 24.99" & _

  ")"

myOracleCommand.ExecuteNonQuery()

Step 5

The fifth step sets a savepoint named SaveProduct using the Save() method of the OracleTransaction object.

In C#:

myOracleTransaction.Save("SaveProduct");

In VB.NET:

myOracleTransaction.Save("SaveProduct")

Step 6

The sixth step adds another row to the products table with a product ID of 7.

In C#:

myOracleCommand.CommandText =

  "INSERT INTO products (" +

  "  product_id, product_type_id, name, description, price" +

  ") VALUES (" +

  "  7, 2, 'Z-Files', 'Mysterious stories', 14.99" +

  ")";

myOracleCommand.ExecuteNonQuery();

In VB.NET:

myOracleCommand.CommandText = _

  "INSERT INTO products (" & _

  "  product_id, product_type_id, name, description, price" & _

  ") VALUES (" & _

  "  7, 2, 'Z-Files', 'Mysterious stories', 14.99" & _

  ")"

myOracleCommand.ExecuteNonQuery()

Step 7

The seventh step performs a rollback to the SaveProduct savepoint previously set in Step 5.

In C#:

myOracleTransaction.Rollback("SaveProduct");

In VB.NET:

myOracleTransaction.Rollback("SaveProduct")

After the rollback is complete, the second row added in Step 6 is removed, but the first row added in Step 4 remains.

The remaining steps in TransExample2.cs and TransExample2.vb display the contents of the products table, rollback the entire transaction, and disconnect from the database.

A Quick Note on Oracle Transaction Services for Microsoft Transaction Server

Microsoft Transaction Server is a proprietary transaction processing system that runs on an internet or network server. Microsoft Transaction Server deploys and manages application and database transaction requests on behalf of a client computer.

Microsoft Transaction Server is a component of the three-tiered, server-centric architecture model. This approach enables the presentation, business logic, and data elements of applications to be clearly separated onto different computers connected in a network. Without any special integration, you can deploy a component in Microsoft Transaction Server that connects to an Oracle Database server release 8.0.6 or later;