Go to: http://bbs.chinaunix.net/archiver/tid-2331601.html
[B] Using transactions (C # programming) in three layers of web development [/B]
Many database operations require transactions. There are roughly three levels of transactions under Asp.net:
(1) transactions at the stored procedure level
(2) ADO. net transactions
(3) ASP. NET page-level transactions
The following are examples:
First, create the trantest table, field ID (INT), test (char)
Set a primary key for the ID (transaction test is performed using the primary key feature that does not allow repeated requests)
Assume that the database has a record ID = 1, test = 'test'
(1)
Create procedure tran1
As
Begin tran
Set xact_abort on
Insert into trantest (ID, test) values (1, 'test ')
Insert into trantest (ID, test) values (2, 'test ')
Commit tran
Go
Set xact_abort on indicates rolling back immediately when an error occurs.
You can also write
Create procedure tran1
As
Begin tran
Insert into trantest (ID, test) values (1, 'test ')
If (@ error <> 0)
Rollback tran
Else
Begin
Insert into trantest (ID, test) values (2, 'test ')
If (@ error <> 0)
Rollback tran
Else
Commit tran
End
Go
2)
Sqlconnection conn = new sqlconnection (system. configuration. configurationsettings. deleettings ["conn"]);
Sqlcommand cmd1 = new sqlcommand ("insert into trantest (ID, test) values (1, 'test')", Conn );
Sqlcommand cmd2 = new sqlcommand ("insert into trantest (ID, test) values (1, 'test')", Conn );
Conn. open ();
Sqltransaction TRAN = conn. begintransaction ();
Statement 1.transaction = Tran;
Export 2.transaction = Tran;
Try
{
Statement 1.executenonquery ();
Listen 2.executenonquery ();
Tran. Commit ();
}
Catch (sqlexception failed T)
{
Tran. rollback ();
Response. Write (Response T. Message );
}
Finally
{
Conn. Close ();
}
Oledbtransaction. Commit Method
Commit database transactions.
Public Virtual void commit ();
Oledbtransaction. rollback method
Roll back a transaction from the pending state.
Public Virtual void rollback ();
Oledbconnection. begintransaction Method
Start database transactions.
Public oledbtransaction begintransaction ();
Start database transactions with the current isolationlevel value.
Public oledbtransaction begintransaction (isolationlevel );
Isolationlevel enumeration?
Specifies the transaction lock behavior of the connection. When a transaction is executed, the. NET Framework data provider uses the isolationlevel value. Isolationlevel remains valid before explicit changes, but can be changed at any time. The new value is used for execution rather than analysis. If it is changed during the transaction, the server's expected behavior is to apply a new lock level to all other statements.
Readcommitted, isolationlevel Member
Keep the shared lock when reading data to avoid dirty reading, but you can change the data before the transaction ends, resulting in non-repeated reading or phantom data.
Oledbconnection. createcommand Method
Create and return an oledbcommand object associated with oledbconnection.
Public oledbcommand createcommand ();
Oledbcommand. Connection attributes
Obtain or set oledbconnection used by this instance of oledbcommand.
Public oledbconnection connection {Get; set ;}
(3)
Add reference system. enterpriseservices. dll
[Code] using system. enterpriseservices;
Create a button as needed and perform the following operations in the button:
Try
{
Work1 ();
Work2 ();
Contextutil. setcomplete ();
}
Catch (system. Exception doesn't)
{
Contextutil. setabort ();
Response. Write (Response T. Message );
}
Then, add two operations to the page to simulate calling operations of different classes at the logic layer.
Private void work1 ()
{
Sqlconnection conn = new sqlconnection (system. configuration. configurationsettings. deleettings ["conn"]);
Sqlcommand cmd1 = new sqlcommand ("insert into trantest (ID, test) values (1, 'test')", Conn );
Conn. open ();
Statement 1.executenonquery ();
Conn. Close ();
}
Private void work2 ()
{
Sqlconnection conn = new sqlconnection (system. configuration. configurationsettings. deleettings ["conn"]);
Sqlcommand cmd2 = new sqlcommand ("insert into trantest (ID, test) values (2, 'test')", Conn );
Conn. open ();
Listen 2.executenonquery ();
Conn. Close ();
}
Modify the foreground page and add transaction = "required" to <% PAGE.
The above are some of my personal experiences in my actual work. I still need your comments. If there are any shortcomings, I hope you can help me to point them out.