Use ORACLE database transactions in. net

Author: Jason price
Address: http://www.oracle.com/technology/global/cn/pub/articles/price_dbtrans_dotnet.html

Understand the basics of writing database transactions in Visual Basic. NET and C.

ExampleCode: Http://www.oracle.com/technology/pub/files/price_dbtrans_src.zip

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 learn about database transactions in the. NETProgramUse the oracletransaction object and how to set the transaction retention point. All scripts and files referenced in this article are provided here. This document assumes that you are familiar with C # and VB. NET programming.

Required Software

If you want to follow our steps to complete the example given in this article, you need to install the following software:

Windows NT 4.0, Windows 2000, Windows XP Professional, or Windows Server 2003
Access an installed Oracle Database (Oracle8i Version 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 plan to use enterprise service transactions or distributed transactions to develop and run applications, you also need to install Oracle services for Microsoft Transaction Server (10.1.0.2.0 or later ).

You need to download and install the. NET Framework and SDK respectively (first install the framework ). You also need to download and install the Oracle Database 10 Gb, which includes ORACLE data provider for. Net (ODP. net ). You can install ODP. NET and database servers on different computers or on the same computer.

Note: ODP. the. NET driver is optimized for Oracle database access, so it can obtain the best performance. It also supports a wide range of features of Oracle databases, such as bfile, blob, clob, and xmltype. If you are developing a. NET application based on the Oracle database, ODP. NET is undoubtedly the best choice in terms of features and performance.

Database Mode settings

First, you need to set the database mode. Here we use a simplified Web store example. You must first create a user named store and grant the required permissions to the user as follows (you must first log on to the database as a user with the create user permission ):

Create user store identified by store;
Grant connect, resource to store;
Note:Source codeIn the file db1. SQL, find the first two statements and other statements in the section that set the store mode.

The following statement is connected as a store user:

Connect store/store;
The following statement creates two 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 have created these database tables for store users in a different mode, you will need to modify the schema name in the sample configuration file (you will see later.

Table product_types is used to store the name of the product type that the sample online store may stock. Table products contains detailed information about the products sold.

The following insert statement adds rows for the table product_types and products:

Insert into product_types (
Product_type_id, name
) Values (
1, 'book'
);
Insert into product_types (
Product_type_id, name
) Values (
2, 'dvds'
);

Insert into products (
Product_id, product_type_id, name, description, price
) Values (
1, 1, 'modern unscientific ', 'A description of modern unscientific, 19.95
);
Insert into products (
Product_id, product_type_id, name, description, price
) Values (
2, 1, 'chemistry ', 'troduction to chemistry', 30.00
);
Insert into products (
Product_id, product_type_id, name, description, price
) Values (
3, 2, 'supernova ', 'A star exists', 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.

Database transactions

A database transaction is a logical unit of work composed of a group of SQL statements. You can think of a transaction as a group of SQL statements that cannot be divided. These statements are permanently recorded in the database as a whole or withdrawn together. For example, transfer funds between bank accounts: One update statement will subtract a portion from the total amount of funds in one account, and the other update statement will add funds to another account. The subtraction and addition operations must be permanently recorded in the database or withdrawn together-otherwise, the loss of funds will be incurred. This simple example only uses two update statements, but a more practical transaction may contain many insert, update, and delete statements.

To permanently record the results of an SQL statement in a transaction, you can run the commit statement through the commit statement. To cancel the results of an SQL statement, you can use the rollback statement to perform rollback. This will reset all rows to their original state. As long as you are not disconnected from the database, any modifications you made before performing the rollback will be revoked. You can also set a save point to roll back the transaction to this specific point, while keeping other statements in the transaction intact.

Use Database transactions in C # and VB. NET

You can use an object of the oracletransaction class to represent a transaction. The oracletransaction class contains multiple attributes, two of which are connection (specifying the database connection associated with the transaction) and isolationlevel (specifying the transaction isolation level ); this article will introduce you more about the transaction isolation level later.

The oracletransaction class contains many methods to manipulate transactions. You can use the Commit () method to submit SQL statements permanently and use rollback () to undo these statements. You can also use save () to set a save point in the transaction.

Now I will take you through two sample programs: one written in C # (transexample1.cs) and the other written in VB. NET (transexample1.vb ). These programs demonstrate how to execute a transaction that contains two insert statements. The first insert statement adds a row to the table product_types, and the second statement adds a row to the table products.

Import namespace

The following C # program statement specifies that the system and Oracle. dataacess. Client namespaces are used in the program:

Using system;
Using Oracle. dataaccess. client;
The following is an equivalent VB. NET statement:

Imports system
Imports oracle. dataaccess. Client
The Oracle. dataaccess. Client namespace is part of ODP. NET and contains many classes, including oracleconnection, oraclecommand, and oracletransaction. The example program uses these classes.

Step 2

Create an oracleconnection object to connect to the Oracle database, and then open the connection.

In C:

Oracleconnection myoracleconnection =
New oracleconnection (
"User ID = store; Password = store; Data Source = orcl"
);
Myoracleconnection. open ();
In VB. NET:

Dim myoracleconnection as new oracleconnection (_
"User ID = store; Password = store; Data Source = orcl ")
Myoracleconnection. open ()
The user ID and password attributes specify the database user and password for the mode you want to connect. The data source attribute specifies the Oracle Net service name of the database. The default service name of the initial database is orcl. If you are not using the initial database or your service name is different, you need to modify the data source attribute settings in the program.

Step 2

Create an oracletransaction object and call the oracleintransaction () method of the oracleconnection object to start the transaction.

In C:

Oracletransaction myoracletransaction =
Myoracleconnection. begintransaction ();
In VB. NET:

Dim myoracletransaction as oracletransaction = _
Myoracleconnection. begintransaction ()
Step 2

Create an oraclecommand object to store SQL statements.

In C:

Oraclecommand myoraclecommand = myoracleconnection. createcommand ();
In VB. NET:

Dim myoraclecommand as oraclecommand =
Myoracleconnection. createcommand
Because the oraclecommand object is created using the createcommand () method of the oracleconnection object, it automatically uses the transaction set for the oracleconnection object in step 1.

Step 2

Set the commandtext attribute of the oraclecommand object to the First insert statement that adds a row to the table product_types.

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 2

Run the insert statement using the executenonquery () method of the oraclecommand object.

In C:

Myoraclecommand. executenonquery ();

In VB. NET:

Myoraclecommand. executenonquery ()

Steps 2 and 3

Set the commandtext attribute of the oraclecommand object to add the second insert statement of a row to the table products 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 2

Use the Commit () method of the oracletransaction object to submit transactions in the database.

In C:

Myoracletransaction. Commit ();

In VB. NET:

Myoracletransaction. Commit ()

After the Commit () method is completed, the two rows added by the insert statement are permanently recorded in the database.

Step 2

Use the close () method to close the oracleconnection object.

In C:

Myoracleconnection. Close ();

In VB. NET:

Myoracleconnection. Close ()

Compile and run the sample program

If you encounter the following errors:

Example1.cs (): Error cs0246: the type or namespace name 'oracle'
Cocould not be found (Are you missing a using directive or an assembly reference ?)

This indicates that you have not correctly specified the Oracle Data Access DLL in the compilation command. (For configuration information, see John Paul Cook's technology.Article"Build. NET applications on the Oracle database ".)

The following is an equivalent command used to compile 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, enter the following command to run the example:

Transexample1

You will see the output of the program. However, if you encounter exceptions similar to the following:

An exception was thrown
Message = ORA-12514: TNS: Listener does not currently know
Of Service requested in connect Descriptor

This indicates that the data source settings in the connection string of the oracleconnection object are incorrect. You shall consult your DBA or consult the oracle net documentation for more details.

If you are using vs. net, you can follow the instructions below to compile and run the C # program transexample1.cs:

Create a new C # console application. File> new project, and then select Visual C # projects and console application.
Name the project transexample1.
Replace all the code generated by vs. Net with the code in transexample1.cs.
Select project> add reference to add oracle. dataaccess. DLL reference, and then browse to your installation of ODP. net directory (on my computer, it is c: \ oracle \ product \ 10.1.0 \ client_1 \ bin \ oracle. dataaccess. DLL), and double-click oracle. dataaccess. DLL.
Select debug> Start without debugging to run the program.
To compile and run transexample1.vb, you can perform a series of similar steps, but step 2 should select a visual basic console application and replace the generated code with the code in transexample1.vb in step 2.

View program running results

After running the C # Or VB. NET program, you can use the following SELECT statement in SQL * Plus to view the transaction result:
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 results:
Product_id product_type_id name
----------------------------------------------------------
Description price
------------------------------------------------------------
5 3 magazine Oracle magazine
Magazine about (Oracle 4.99)

Next, you will learn how to set transaction retention points.

Set the transaction retention point in the. NET Program

As mentioned earlier in this article, you can set a save point to roll back the transaction to this specific point, while keeping other statements in the transaction intact. You can use the SAVE () method of the oracletransaction class to set the save point in the transaction.

If you have a very long transaction and want to roll back to a specific point in time, you may need to use the save point. For example, you may want to make some changes to 10 products, set a save point, and then make changes to the other 10 products; if an error occurs during the second batch of changes, you can roll back to the Save point so that your first batch of changes will not change.

I will show you step by step how to use the save point C # (transexample2.cs) sample program and the related new steps in the VB. NET (transexample2.vb) sample program. These programs Add a row to the table products, set a save point, add another row to the table products, roll back to the Save point, and then read these rows from the table products. After rollback to the Save point, only the first row added to the table products is retained: the second row has been deleted.

Steps 1st to 3rd are the same as those shown in "use database transactions in C # and VB. NET", so they are omitted here.

Step 2

Add a row to the table products. The product ID of this row is 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 global', 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 global', 24.99 "&_
")"
Myoraclecommand. executenonquery ()

Step 2

Use the SAVE () method of oracletransaction to set a save point named saveproduct.

In C:

Myoracletransaction. Save ("saveproduct ");

In VB. NET:

Myoracletransaction. Save ("saveproduct ")

Step 2

Add another row to the table products. The product ID of this row is 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 2

Roll back to the saveproduct save point set in step 1.

In C:

Myoracletransaction. rollback ("saveproduct ");

In VB. NET:

Myoracletransaction. rollback ("saveproduct ")

After the rollback is completed, the second row added in step 6th has been deleted, and the first row added in step 4th is retained.

The remaining steps in transexample2.cs and transexample2.vb show the table Products content, roll back the entire transaction and disconnect from the database.

Quick description of Oracle Transaction Service for Microsoft Transaction Server

Microsoft Transaction Server is a proprietary transaction processing system running on Internet or network servers. Microsoft Transaction Server deploys and manages application and database transaction requests for client computers.

Microsoft Transaction Server is a server-centric layer-3 architecture model component. This method clearly distributes application representations, business logic, and data elements to different computers connected to a network. Without dedicated integration, you can deploy a component in Microsoft Transaction Server connected to Oracle Database Server 8.0.6 or later, but you must first install Oracle services for Microsoft Transaction Server.

Conclusion

In this article, you systematically learned how to use database transactions in. net programs. You learned how to create oracletransaction objects and use them to submit transactions to the database, how to use the save point to partially roll back a transaction, and how to separate concurrent transactions from the Oracle database.

analyst
technical consultant and writer Jason price is a Microsoft certified expert, Oracle certified database administrator, and application developer. He has over 10 years of experience in the software industry. He has written "Oracle Database 10g SQL" (McGraw-Hill/Osborne, 2004), "proficient in C # database programming" (sybex, 2004), and "proficient in Visual C #. NET Programming (sybex, 2003), Oracle9i JDBC programming (McGraw-Hill/Osborne, 2002), and Oracle sqlj for Java programming (O 'Reilly, 2001 ).