Liu Yanqing from: Yesky
With the exception handling mechanism provided by the SQL-Transaction class and .NET, we can handle the problem and discovery system exception in a reliable manner. This small article will explain the concept and usage of transaction processing and exception handling. What is a transaction? Transaction processing is a series of operations completed by a single logical unit, which can consist of a series of SQL statements, select, insert, update, delete, if there is no error after the operation is completed, then it The change in the database is permanent. If there is an error, it will not modify or change the database. To define a transaction, you need to use the Begin TRAN command, any statement after this command will be considered part of the transaction. Command Commital is used to complete the transaction and make the transaction to the changes to the database permanently. Use the ROLLBACK command to cancel a transaction and restore the transaction to the changes to the database. Below 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 an abnormality?
Develop an error message processing mechanism and provide useful, clear, meaningful information is one of the tasks of programmers, and exception handling is a mechanism that provides this service. Once the transaction fails, the server will issue a database error message to the system to help users discover and repair the State Union. We can handle such exception information in an abnormality and fix the faults. The use of exception handling functions is as follows:
[c #]
Try {// Database Operation Command} Catch (Exception E) {// If there is an abnormality, this part of the statement will be executed} Finally {// Whether there is any abnormality, this part of the statement will be executed}
How to achieve a transaction? 1. Write a transaction statement during a stored procedure and use the following control to find whether or not there is an error, return the corresponding value, the Internet application displays the correct and easy-to-understand error message according to the returned value. Below is an example of a transaction:
[Store Procedure] CREATE PROCEDURE PRODUCT_SAVE (ASDECLARE (@USERID CHAR (5), @ LOCATION VARCHAR (50), @ RETURNS INT OUTPUT) BEGIN TRANUPDATE ADDRESS SET LOCATION = @ LOCATION WHERE USERID = @ USERIDIF (@@ ERROR> 0) BEGIN @ Returns = -1 / * fail to update * / rollbackendelse @ returns = 0 / * succeed to update * / commitreturn @returns [Web Application In C #] int value; dbclass dbc = new dbclass (); // use new command generation A database class VALUES = dbc.Updatedb ("0001", "23 rain street"); // and call its function member to update recordif (value == 0) Lable_Message.Text = "Update successfully"; elselable_message.text = " Sorry, Can Not Update this Record, please contact your DBA. "The above example is ideal for DBA, etc., which is very familiar with database programming, and they prefer to complete exception handling functions during the stored procedure. If you are not familiar with database programming, The following method can be taken:
2. In the .NET framework, we can use the SQLTransaction class to define a transaction. Since then, we can use the commit or rollback function to control the transaction. Of course, we can also use the .NET framework to obtain the system exception. Below 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 generating a new 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 ();
