Liu Yanqing compiled from: Yesky
Using the exception handling mechanism provided by the Sql-transaction class and. NET, we are able to handle problems in the running of the database in a reliable manner and discover system exceptions. This small article will explain the concepts and usage of transaction processing and exception handling.
What is a transaction.
A transaction is a series of operations done in a single logical unit that can consist of a series of SQL statements, SELECT, INSERT, UPDATE, and delete, and the change to the database is permanent if no errors occur after the operation that the unit contains is executed. If an error occurs, it does not make any changes or changes to the database.
To define a transaction, you need to use the BEGIN TRAN command, and 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 permanent for the changes made to the database. The rollback command is used to cancel a transaction and restore the changes that the transaction made to the database.
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 handling mechanism and providing users with useful, clear, meaningful information is also one of the programmers ' tasks, and 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 Confederacy. We can handle the exception handling function to get this exception information and fix the problem. The usage of the exception handling feature is as follows:
[C #]
Try
{
Database Operations Command
}
catch (Exception e)
{
?//If an exception occurs, this part of the statement will be executed
}
Finally
{
?//Whether or not an exception occurs, this part of the statement will be executed
} |
how transactions are implemented.
1, write a transaction statement in a stored procedure, and use the following control to find out if there is an error, return the corresponding value, the Internet application will display the correct and easy to understand error message according to the value returned. The following is an example of a transaction:
[Store Procedure]
CREATE PROCEDURE Product_save (as
(@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 (); To generate a database class using the new command
Values=dbc.updatedb ("0001", "Rain Street"); and call it 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 ideal for programmers who are familiar with database programming, such as DBAs, who prefer to complete the exception handling function in stored procedures. If you are unfamiliar with database programming, you can take the following approach:
2. In the. NET Framework, we can use the SqlTransaction class to define a transaction. After that, we can use the commit or rollback functions to control the transaction. Of course, we can also use. NET Framework to obtain system exceptions. Here is a related 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 freshman to become a transaction
SqlCommand mycommand = new SqlCommand ();
Mycommand.transaction = Mytrans;
Try
{
myCommand.CommandText = "Update address set location= ' 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 ();
} |
It is important to note that if you use the OLE DB Class instead of the SqlClient class to define SQL commands and connections, we must use Oletransation to define the transaction.
