How to be in. NET implementation Transaction (1)

How to be in. NET to implement the transaction mechanism? There are usually 2 ways to write directly to SQL, using Ado.net implementations. Here is a brief introduction:

Method 1: Write directly to SQL



Using BEGIN TRANS, COMMIT TRANS, ROLLBACK TRANS implementation:

For example

BEGIN TRANS

DECLARE @orderDetailsError int, @productError int

DELETE from ' Order Details ' WHERE productid=42

SELECT @orderDetailsError = @ @ERROR

DELETE from the products WHERE productid=42

SELECT @productError = @ @ERROR

IF @orderDetailsError = 0 and @productError = 0

COMMIT TRANS

ELSE

ROLLBACK TRANS

This is a simpler approach, and you can check out the relevant SQL Server Help



Method 2: Using the Ado.net implementation, the advantage of this approach is that you can manage transactions in the middle tier, and of course you can choose to implement them at the data layer.

The SqlConnection and OleDbConnection objects have a BeginTransaction method that can return SqlTransaction or OleDbTransaction objects. And this object has a Commit and Rollback method to manage the transaction, the specific examples are as follows:

Cnnorthwind.open ()



Dim trans as SqlTransaction = Cnnorthwind.begintransaction ()



Dim Cmdel as New SqlCommand ()

Cmdel.connection = Cnnorthwind

Cmdel.transaction = trans



Try



Cmdel.commandtext = _

"DELETE [order Details] WHERE ProductID = 42"



Cmdel.executenonquery ()



Cmdel.commandtext = "DELETE products WHERE ProductID = 42"



Cmdel.executenonquery ()



Trans.commit ()



Catch XCP as Exception



Trans. Rollback ()



Finally



Cnnorthwind.close ()



End Try

Ok, the example above can achieve the same effect as Method 1.

Concurrency issues:

If there are no locks and multiple users access a database at the same time, problems may occur when their transactions use the same data at the same time. Concurrency issues include missing or overwriting updates, unacknowledged dependencies (dirty reads), inconsistent parsing (not repeating), and phantom reading. But how do you avoid the problem of dirty reading when data is read?

You can refer to the following article I wrote: How To. NET implementation Transaction (2)