Vb. NET uses OracleTransaction to process transactions

Oracle 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
　　 setting a transaction savepoint in a. NET program

As mentioned earlier in this article, you can set up a savepoint to roll the transaction back to that particular point while keeping the other statements in the transaction intact. You can set the SavePoint in a transaction by using the Save () method of the OracleTransaction class.

If you have a very long transaction and want to be able to roll back only to a specific point in time, you may want to use a savepoint. For example, you might want to make some changes to 10 products, then set up a savepoint, and then make changes to another 10 products, and if you have an error while making the second batch of changes, you can roll back to the savepoint to make your first batch of changes intact.

A few things to note when using OracleTransaction objects:

1 You need to have only one oracleconnection in the execution of your entire transaction, oraclecommand,oracletransaction, In other words, if you need to work with a database in a transaction, you can only execute it in this unique command, similar to the following:

Imgcommand.commandtext = sSQL
Imgcommand.executenonquery () or other action
If you create a new connection to perform other database operations, the entire transaction process throws an exception

2 If you need to add parameters to your SQL statement, you must clear the parameters of its command after you perform the commit or related database operation, and then lift the transaction function in the actual project:

Transaction functions

1 "' <summary>
2 "' Save hotspot text file information to database
3 "' </summary>
4 Private function Savetextfile () function Savetextfile () as Boolean
5 Dim sSQL as String
6 sSQL = "Select type_id from Sys_file_type where file_extname= ' TXT '"
7 Try
8 Imgcommand.commandtext = sSQL
9 Dim typeid as Int32 = Convert.ToInt32 (Imgcommand.executescalar ()) ' File type
10 ' Read text information
One Dim Textblob () as Byte = GetText ()
12
sSQL = "INSERT into t_watch_textcontent (image_id,text_content,type_id) VALUES (: Imageid,:textcontent,:typeid)"
14 ' Add SQL parameter
Dim Param as Oracleclient.oracleparameter
Param = New Oracleclient.oracleparameter ("imageID", Snewimageid)
IMGCOMMAND.PARAMETERS.ADD (Param)
Param = New Oracleclient.oracleparameter ("Textcontent", Textblob)
IMGCOMMAND.PARAMETERS.ADD (Param)
Param = New Oracleclient.oracleparameter ("typeID", typeID)
IMGCOMMAND.PARAMETERS.ADD (Param)
22
23 ' Submit information
Imgcommand.commandtext = sSQL
If imgcommand.executenonquery () > 0 Then
num Bresult = True
27 ' The key is here, you need to clear the parameters manually
ImgCommand.Parameters.Clear ()
End If
Catch ex as Exception
Me.exceptionmessage = ex
Bresult = False
The End Try
34
Return Bresult
The End Function