How to implement a transaction mechanism in .NET? It is usually possible to use 2 ways: directly written to SQL; use ADO.NET implementation. Let's take a presentation following:
Method 1: Write directly into SQL
Use Begin Trans, Commit Trans, Rollback Trans, ROLLBACK TRANS implementation:
E.g
Begin Trans
Declare @orderdetailSerror Int, @ProductError Int
Delete from "Order Details" Where ProductId = 42
SELECT @OrderDetailSerror = @@ error
Delete from Products Where ProductId = 42
SELECT @ProductError = @@ error
IF @orderdetailserror = 0 and @ProductUcTerror = 0
Commit trans
Else
Rollback TRANS
This method is relatively simple, and you can check the relevant SQL Server Help.
Method 2: Using the ADO.NET implementation, the advantage of using this way is that the transaction can be managed in the middle layer, of course, you can also choose to implement in the data layer.
SqlConnection and OLEDBConnection objects have a begintransaction method that returns a SQLTRANSACTION or OLEDBTRANSACTION object. And this object has a commit and rollback method to manage transactions, and 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 same effect as method 1 can be achieved by the example above.
Concurrent problems:
If there is no lock and multiple users accesses a database, problems may occur when their transactions use the same data simultaneously. Concurrency problems include: Loss or override updates, unrecognized correlation (dirty reading), inconsistent analysis (non-repetitive reading), phantom read. But how to avoid the problem of dirty reading when data reading?
You can refer to the article I wrote later: How to implement a transaction in .NET (2)
