|
Using the SQL-transaction class and the exception handling mechanism provided by. net, we can handle database running problems and discover system exceptions in a reliable way. This article is shortArticleThe concepts and usage of transaction processing and exception handling will be explained. What is a transaction?
Transaction processing is a series of operations completed in a single logical unit. It can be composed of a series of SQL statements, select, insert, update, and delete, if no error occurs after the operations in this unit are completed, the changes it makes to the database are permanent. If an error occurs, the database will not be modified or changed.
To define a transaction, you must use the tran in TRAN command. Any statements after this command will be considered part of the transaction. The command commit is used to complete the transaction and make the transaction's modifications to the database permanent. The rollback command is used to cancel a transaction and restore the database modifications made by the transaction.
The following is an example of a transaction:
[SQL Server7.0 or SQL Server2000]
Begin tran
Insert into product (productid, productname) values ("0001", "keyboard ")
Update product set price = 12 where productid = "0002"
If (@ error> 0)
Rollback
Else
Commit What is exception handling?
Developing an error message processing mechanism and providing useful, clear, and meaningful information to users is also one of the tasks of programmers. Exception Handling is a mechanism that can provide this service. Once the transaction fails, the server sends a database error message to the system to help the user discover and fix the association. We can use the exception handling function to obtain the exception information and fix the fault. The exception handling function is used as follows:
[C #]
Try
{
// Database Operation Command
}
Catch (exception E)
{
? // If an exception occurs, this part of the statement will be executed
}
Finally
{
? // This part of the statement is executed no matter whether an exception occurs.
}
How to Implement transactions?
1. Write a transaction statement in a stored procedure and use the following control to check whether an error has occurred. The corresponding value is returned for Internet applications.ProgramA correct and understandable error message is displayed based on the returned value. The following is an example of a transaction:
[Store procedure]
Create procedure product_save (
Declare
(@ Userid char (5 ),
@ Location varchar (50 ),
@ Returns int output
)
Begin tran
Update address set location = @ location where userid = @ userid
If (@ error> 0)
Begin
@ Returns =-1/* fail to update */
Rollback
End
Else
@ Returns = 0/* succeed to update */
Commit
Return @ returns [Web application in C #]
Int values;
Dbclass DBC = new dbclass (); // use the new command to generate a Database Class
Values = DBC. updatedb ("0001", "23 rain Street"); // and call its function member to update record
If (values = 0)
Lable_message.text = "Update successfully ";
Else
Lable_message.text = "Sorry, can not update this record, please contact your DBA ."
The above example is very suitable for DBAs and other programmers who are very familiar with database programming. They prefer to complete the exception handling function in the stored procedure. If you are not familiar with database programming, you can use the following method:
2. In the. NET Framework, we can use the sqltransaction class to define a transaction. Afterwards, we can use the commit or rollback function to control transactions. Of course, we can also use the exception handling function provided by the. NET Framework to obtain system exceptions. The following is an example:
[Web Applicaion in C #]
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
{
Mycommand. commandtext = "Update address set location =" 23 rain Street "where userid =" 0001 "";
Mycommand. executenonquery ();
Mytrans. Commit ();
Console. writeline ("record is udated .");
}
Catch (exception E)
{
Mytrans. rollback ();
Console. writeline (E. tostring ());
Console. writeline ("sorry, record can not be updated .");
}
Finally
{
Myconnection. Close ();
}
Note that if you use the oledb class instead of the sqlclient class to define SQL commands and connections, we must use oletransation to define transactions. |
