Developers strive to properly handle the unexpected, so that users do not have to worry about heavenly-like system error messages. For this reason, exception handling is each. NET applications a standard part. Try / catch block allows you to catch exceptions and control application from the point of implementation. Interact with the database will occur when a lot of mistakes, but many developers do not know how to deal with the database layer of an error. In this paper we will explore how to use SQLServer and T-SQL code to handle errors in your database.
Handling errors in T-SQL
SQLServer's SQL dialog allows you to easily handle the process may be stored, function in non-fatal error occurred, but not all errors are easier to handle. In fact, fatal and non-fatal errors are many. What is fatal, what non-fatal, this is not very well-documented, but your application code can always use try / catch block to handle fatal database errors. For all other errors, you can use the following techniques.
Services
You should use the transaction code in the database to ensure that everything is no problem when all of the changes have been completed. SQLServer-line help to the task of a transaction described as a logical unit, which consists of a series of statements (select, insert, update, or delete, etc). If the transaction did not occur during any errors, then all the changes the transaction will become a permanent part of the database. If an error occurs during this period and will not make any changes to the database.
Services included in the BEGIN TRANSACTION and END TRANSACTION statements between. ROLLBACK TRANSACTION statement can cancel all changes, so any changes will not occur. COMMIT TRANSACTION statement can be used for permanent changes. Now, let us focus on how to deal with T-SQL errors in the.
@ @ Error
@ @ Error function so that you can implement T-SQL error handling. It will return the error code returned by the system. If no error is returned "0." @ @ Error function must immediately be called immediately after a statement, because it will be after each T-SQL statement be removed.
RAISERROR
RAISERROR statement allows you to generate a custom error message or use a form on the inside has been sysmessages news. You can view its syntax online, but its most basic form includes a message (for custom message) or message id (for existing sources) and its severity and state. SQLServer does not use state, so lightly passed to it a number on it. Severity that the severity of the error for 0 to-18 users, while the 19-25 reserved for administrators.
In the list A, the sample stored procedure used to update these features a sample Northwind database record:
CREATE PROCEDURE sp_UpdateCustomerPhone (@ id nvarchar (5), @ phone nvarchar (24), @ retvalueint output) ASBEGINBEGIN TRANSACTIONUPDATE [dbo]. [Customers] SET [Phone] = @ phone WHERE ([CustomerID] = @ id) IF (@ @ ERROR <> 0) - a non-negative value signals an errorBEGINROLLBACK TRANSACTION - changes are not permanentRAISERROR ('Update Customers Error', 1,1) - raise a custom error message - Custom error message appears if run from consoleSET @ retvalue = -1 - return negative value to signalproblemsENDCOMMIT TRANSACTION - make the changes permanent, so record is updatedSET @ retvalue = 1 - a positive return value signalssuccessRETURNEND
List A
If no error occurs, it took the telephone number listed is set to a value passed to the process. It uses a return value parameter, if there are problems it returns a negative value, if all of the things with the implementation of no problems, it will return a positive.
Using stored procedure return values
Through the stored procedure return value, we can use it in our. NET code for years. SqlCommand object allows you to easily add a parameter to the process and store the return value. Parameters of the Direction property is used to receive the value from stored procedure call. It has two property values: InputOutput and Output. In our example, we will use the Output to receive state value.
This code is a simple ASP.NET page for the Northwind database is used to transfer a customer to a data table in the new value. The id value is actually stored in a hidden field where you can be easily passed to the form, but this field is used to describe the. Enter the value in the text field is used to update the database table where the phone number field.
Parameter is added to the SqlCommand object (they must be accurately meet the stored procedure parameters). Command through ExecuteNonQuery method of SqlCommand object is executed. Once it has been executed, the return value can be parameters to retrieve.
Code checks the return value (-1 indicated a problem), and a message will be displayed in a Label control in. In addition, a try / catch block is used to capture interacting with the database that may occur during the operation of any fatal errors. See list B:
New Number: OnCommand = "btnUpdatePhone_Click" runat = "server" /> ALFKI
List B
List C in the corresponding VB.NET code:
New Number: OnCommand = "btnUpdatePhone_Click" runat = "server" /> ALFKI
List C
The database covers all
Used try / catch block to handle. NET application code in the exception is a simple process, but you can monitor the database layer exception. SQLServer's T-SQL language exception handling code to provide you everything you need.