Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
 New Feature: Community Sites:
Create your own .NET community website and start earning from Google AdSense !
It's Free !
|
Exception Handling in SQL Server with .Net
Posted Date: 30 Dec 2007 Resource Type: Articles Category: Databases
|
Posted By: @@@ Hyderabadi Biryani @@@ Member Level: Diamond
Rating:  Points: 15
|
Introduction
Database-related errors can be handled by using either unstructured or structured exception handling. Unstructured exception handling may result in code that is difficult to debug and maintain. It may also degrade the application performance because the control of execution of code needs to be transferred back and forth among error handlers. Use structured exception handling in an application because it simplifies code maintenance.
Structured Exception Handling
Errors relating to data access are generated and handled by using structured exception handling. When a database server generates an error, the corresponding .NET data provider translates the database-specific error into a standard exception type, which can be used to handle the exception in the application. You can obtain the details of the error by using the properties of the standard exception type.
Structured exception-handling uses the Try, Catch, and the Finally blocks. The Try block contains the code that has a probability of raising errors and is followed by one or more Catch blocks, which specify the action to be performed when an error occurs. The Finally block specifies the action to be performed, regardless of whether or not an error occurs.
While writing Catch blocks to filter exceptions, order them from most specific to least specific exception types. For example, if there are two Catch blocks that filter SqlException and Exception, place the Catch block for SqlException before the one for Exception. This ensures that the Catch block with the most specific exception type is executed for that exception type.
Generating Errors From Stored Procedures
For SQL Server data sources, you can add a custom error message to the sysmessages table in the master database by using the sp_addmessage stored procedure, as shown in this example:
sp_addmessage 50001, 16, 'Failed to Add a Record'
This statement adds a message to the sysmessages table. The error number is 50001, severity level is 16, and the message text is Failed to Add a Record.
After adding a message to the sysmessages table, you can use the RAISERROR function in batches or stored procedures to generate custom errors. The RAISERROR function takes the error number, the severity level of the error, and the state as its parameters, as shown in this example:
RAISERROR(50001, 16, 1)
When you give this statement, SQL Server locates an error with the number 50001 and severity level 16 in the sysmessages table and displays the following error message:
Server: Msg 50001, Level 16, State 1, Line 1
Failed to Add a Record
The custom errors raised by using the RAISERROR method can be accessed in a .NET application by using the Errors collection of the SqlException class.
While using the sp_addmessage stored procedure, identify the impact of each severity level and use an appropriate severity level for an application.
severity levels and their impact:
Severity Level: 0-10
Represent informational messages that are not necessarily error conditions. For example, you may add an informational message indicating that a record has been added to a table. An error with a severity level between 0 and 10 does not close a connection and does not throw SqlException.
Severity Level: 11-16
Represent errors that can be corrected by users. For example, when a user attempts to add incorrect data to a table, you can use an error message with a severity level between 11 and 16. A user can correct the values and add them to a table. An error with a severity level between 11 and 16 does not close a connection but throws SqlException.
Severity Level: 17-19
Represent resource or system errors. For example, if the specified table does not exist in a database, an error with a severity level between 17 and 19 is raised. An error with a severity level between 17 and 19 does not close a connection but throws SqlException.
Severity Level: 20-25
Represent fatal system errors, such as hardware failure. An error with a severity level between 20 and 25 terminates a client connection and throws SqlException.
Severity levels between 0 and 10 do not throw SqlException. To access such messages in an application, subscribe to the InfoMessage event of the SqlConnection class. This event is generated when SQL Server sends an informational message. Use the AddHandler keyword to add an event handler for the InfoMessage event. The event handler takes two arguments, the object that raised the event and an object of the SqlInfoMessageEventArgs class, which contains the Errors collection to access the informational messages.
The following application uses the addshippers stored procedure to add records in the Shippers table in the Northwind database. Before creating the application, create the addshippers stored procedure. To do this:
1. Open the Query Analyzer window.
2. Execute the following statements to add a custom message to the sysmessages table in the master database:
Use master
Go
sp_addmessage 50010, 10, 'Added a record'
3. Execute the following statements to create the addshippers stored procedure:
create proc addshippers @CompanyName nvarchar(80), @Phone nvarchar(48)
as
begin
Insert Shippers(CompanyName, Phone) values (@CompanyName, @Phone)
RAISERROR(50010, 10, 1)
End
The stored procedure uses the RAISERROR function to provide an informational message.
To create an application that uses the addshippers stored procedure:
1. In VS.NET, create a new Visual Basic Windows Application project
.
2. Add two Label controls to Form1. Set their Text property to Company Name and Contact Number, respectively.
3. Add two TextBox controls to Form1. Set their Name property to txtCompanyName and txtPhone, respectively.
4. Add a Button control to Form1. Set its Name property to btnAddShipper and Text property to Add Record.
5. Double-click the btnAddShipper control to open the Code Editor window.
6. Add the code below to the btnAddShipper_Click event handler:
'Create a Connection object.
Dim objSqlCon As New System.Data.SqlClient.SqlConnection("Data Source=localhost; Database=Northwind; User ID=sa;Pwd=")
'Add a handler for the InfoMessage event of the Connection object.
AddHandler objSqlCon.InfoMessage, New System.Data.SqlClient.SqlInfoMessageEventHandler(AddressOf MyEventHandler)
'Create a Command object.
Dim objSqlCommand As New System.Data.SqlClient.SqlCommand("exec addshippers @CompanyName, @Phone", objSqlCon)
'Add parameters to the Command object.
objSqlCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 80, "CompanyName")
objSqlCommand.Parameters.Add("@Phone", SqlDbType.NVarChar, 48, "Phone")
'Set the values of the parameters.
objSqlCommand.Parameters("@CompanyName").Value = txtCompanyName.Text
objSqlCommand.Parameters("@Phone").Value = txtPhone.Text
'Open the connection.
objSqlCon.Open()
'Execute the command.
objSqlCommand.ExecuteNonQuery()
'Close the connection.
objSqlCon.Close()
7. To create the MyEventHandler event handler for the InfoMessage event of the SqlConnection object, add the below code immediately after the btnAddShippers_Click event handler:
Sub MyEventHandler(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs)
Dim err As System.Data.SqlClient.SqlError
For Each err In e.Errors
MessageBox.Show("Message Number: " & err.Number & ControlChars.CrLf & "Message: " & err.Message)
Next
End Sub
8. Run the application to display Form1. Enter values in the TextBox controls and click the Add Record button. The informational message will be displayed
|
Responses
|
| Author: Sandhya 31 Jul 2008 | Member Level: Bronze Points : 1 | Hi Vijaya,
Now a days i am learning a lot thru your articles.
It would be great if you put some light on Try..catch in SQL Server.
Regards
Sandhya
| | Author: Vivek 02 Oct 2008 | Member Level: Gold Points : 1 | Agree with Sandhya, It should be SQL Server 2005, I will write Exception handling in SQL Server 2005 if dotnetspider admin permit to do that...
|
|