1. SQL Server Stored Procedure Transaction Processing
A common error handling mode is roughly as follows:
Create procdure prinsertproducts
(
@ Intproductid int,
@ Chvproductname varchar (30 ),
@ Intproductcount int
)
As
Declare @ interrorcode int
Select @ interrorcode = @ Error
Begin transaction
If @ interrorcode = 0
Begin
-Insert Products
Insert products (productid, productname, productcount)
Values (@ intproductid, @ chvproductname, @ intproductcount)
Select @ interrorcode = @ error -- check every execution of a T-SQL statement and save the error code to a local variable.
End
If @ interrorcode = 0
Begin
-Update Products
Update products set productname = 'microcomputer' where productid = 5
Select @ interrorcode = @ Error
End
If @ interrorcode = 0
Commit transaction
Else
Rollback transaction
Return @ interrorcode -- it is best to return the error code to the called stored procedure or applicationProgram
2. Use transactions in. net
Sqlconnection myconnection = new sqlconnection ("Data Source = localhost; initial catalog = northwind; Integrated Security = sspi ;");
Myconnection. open ();
Sqltransaction mytrans = myconnection. begintransaction (); // use new to generate a transaction
Sqlcommand mycommand = new sqlcommand ();
Mycommand. Transaction = mytrans;
try
{< br> mycommand. commandtext = "Update address set location = '23 rain Street 'Where userid = '000000'";
mycommand. executenonquery ();
mytrans. commit ();
console. writeline ("record is udated. ");
}< br> catch (exception e)
{< br> mytrans. rollback ();
console. writeline (E. tostring ();
console. writeline ("sorry, record can not be updated. ");
}< br> finally
{< br> myconnection. close ();
}
note: In sqlserver, each SQL statement is executed as a transaction.. Net Code . You do not need to use transactions to execute a single SQL statement, the preceding section uses transactions for a single SQL statement to simplify the expression.