Vb. NET, using OracleTransaction to process transactions

Introduction to Database Transactions

A database transaction is a logical unit of work that consists of a set of SQL statements. You can think of a transaction as an irreducible set of SQL statements that are permanently recorded in the database or revoked as a whole. such as transferring money between bank accounts: An UPDATE statement subtracts a portion of the total amount of money from one account, and another UPDATE statement adds the money to another account. Operations and operations must be permanently recorded in the database, or must be revoked-otherwise the funds will be lost. This simple example uses only two update statements, but a more realistic transaction might contain many insert, UPDATE, and DELETE statements.

To permanently record the results of an SQL statement in a transaction, you can execute the commit by using a commit statement. To undo the results of an SQL statement, you can use the ROLLBACK statement to perform a rollback, which resets all rows to their original state. Any changes you make before you perform a rollback will be revoked as long as you are not disconnected from the database in advance. You can also set up a savepoint to roll the transaction back to that particular point while keeping the other statements in the transaction intact.

Using Database transactions (primarily for vb.net)

You can use an object of the OracleTransaction class to represent a transaction. The OracleTransaction class contains several properties, of which two are Connection (specify the database connections associated with the transaction) and IsolationLevel (Specify transaction ISOLATION Level)

Connection, specifies the OracleConnection object associated with the transaction;

IsolationLevel, specifies the IsolationLevel enumeration type for the transaction, which is used to lock things, chaos, readcommited, readuncommited, RepeatableRead, Serializable, Unspecified.

The application creates the OracleTransaction object by invoking BeginTransaction against the OracleConnection object. Performs all subsequent operations on the OracleTransaction object associated with the transaction (for example, committing or aborting the transaction).

Commit: Commit SQL database transaction;

Rollback: ROLLBACK TRANSACTION from suspend state;

You can also use Save () to set a save point in a transaction.

The following example creates a OracleConnection and a oracletransaction. It also demonstrates how to use the BeginTransaction, Commit, and Rollback methods. (This is an example from MSDN)

It should be noted that these operations require the introduction of namespaces: Oracle.DataAcess.Client

The Oracle.DataAccess.Client namespace is part of the odp.net, which contains a number of classes, including OracleConnection, OracleCommand, and OracleTransaction. These classes are used by the sample program.

Transaction operations

1Public Sub Runoracletransaction () Sub Runoracletransaction (myConnString as String)
2 Dim myconnection as New oracleconnection (myConnString)
3 Myconnection.open ()
4
5 Dim mycommand as OracleCommand = Myconnection.createcommand ()
6 Dim Mytrans as OracleTransaction
7
8 ' Start a local transaction
9 Mytrans = myconnection.begintransaction (isolationlevel.readcommitted)
Ten ' Assign transaction object for a pending local transaction
One mycommand.transaction = Mytrans
12
A Try
myCommand.CommandText = "INSERT into Dept (DeptNo, dname, Loc) VALUES (' TECHNOLOGY ', ' DENVER ')"
Mycommand.executenonquery ()
myCommand.CommandText = "INSERT into Dept (DeptNo, dname, Loc) VALUES (M, ' ENGINEERING ', ' KANSAS City ')"
Mycommand.executenonquery ()
Mytrans.commit ()
Console.WriteLine ("Both records are written to database.")
Catch e as Exception
Mytrans.rollback ()
Console.WriteLine (E.tostring ())
Console.WriteLine ("Neither record is written to database.")
Finally
Myconnection.close ()
Num End Try
27End Sub
28