Configuring Connections and Connecting to Data in Microsoft .NET Framework 3.5

Lesson 4: Handling Connection Errors

This lesson explains how to handle errors that are thrown while you are working with SQL Server. ADO.NET provides two classes specifically for processing errors: the SqlException class and the SqlError class. Let’s see how to work with these classes and how to catch and handle errors that might be returned from the data source.

When SQL Server returns a warning or an error, the .NET Framework Data Provider for SQL Server creates and throws a SqlException that you can catch in your application to deal with the problem. When SqlException is thrown, inspect the SqlException.Errors property to access the collection of errors that is returned from the SQL server. The SqlException.Errors property is a SqlErrorCollection class (a collection of SqlError classes) that always contains at least one SqlError object.

Lab: Handling Database Connection Errors

In this lab you will practice catching a SqlException in your application.

EXERCISE 1: Handling Database Connection Errors

In this lab you will practice working with database connection errors (specifically, the SqlException and SqlError objects) in your application. To do this, let’s create a Windows application.

1. Create a new Windows application and name it HandlingConnectionErrors.

2. Add three buttons to the form and set the following properties:

   Button1:

   • Name = GoodConnectButton

   • Text = Connect (valid connection string)

   Button2:

   • Name = ConnectToInvalidUserButton

   • Text = Connect to invalid user

   Button3:

   • Name = ConnectToInvalidDatabaseButton

   • Text = Connect to invalid database

3. Double-click each button to create the button click event handlers and switch to code view.

4. Add an Imports statement (using in C#) for the System.Data.SqlClient namespace.

5. The following code creates a new connection based on the connection string passed into it, attempts to open the connection, and then displays any errors it encounters. Add this code below the button click event handlers:

   ' VB
   Private Sub ConnectToDatabase(ByVal connectionString As String)
   
       Dim connection As New SqlConnection(connectionString)
   
       Try
           connection.Open()
       Catch ex As SqlException
           Dim errorMessage As String = ""
   
           ' Iterate through all errors returned
           ' You can check the error numbers to handle specific errors
           For Each ConnectionError As SqlError In ex.Errors
               errorMessage += ConnectionError.Message & " (error: " & _
                   ConnectionError.Number.ToString & ")" & Environment.NewLine
               If ConnectionError.Number = 18452 Then
                   MessageBox.Show( _
                       "Invalid Login Detected, please provide valid credentials!")
               End If
           Next
           MessageBox.Show(errorMessage)
       Finally
           connection.Close()
       End Try
   End Sub
   
   // C#
   private void ConnectToDatabase(string connectionString)
   {
       SqlConnection connection = new SqlConnection(connectionString);
       try
       {
           connection.Open();
       }
       catch (SqlException ex)
       {
           string errorMessage = "";
           // Iterate through all errors returned
           // You can check the error numbers to handle specific errors
           foreach (SqlError ConnectionError in ex.Errors)
           {
               errorMessage += ConnectionError.Message + " (error: " +
                   ConnectionError.Number.ToString() + ")" + Environment.NewLine;
               if (ConnectionError.Number == 18452)
               {
                   MessageBox.Show(
                       "Invalid Login Detected, please provide valid credentials!");
               }
           }
           MessageBox.Show(errorMessage);
       }
       finally
       {
           connection.Close();
       }
   
   }

6. Add the following code so the three button click event handlers look like the following:

   ' VB
   Private Sub GoodConnectButton_Click _
       (ByVal sender As System.Object, ByVal e As System.EventArgs) _
       Handles GoodConnectButton.Click
       ' This is a valid connection string
       Dim GoodConnection As String = _
           "Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated
   Security=True;"
       ConnectToDatabase(GoodConnection)
   End Sub
   
   Private Sub ConnectToInvalidUserButton_Click _
       (ByVal sender As System.Object, ByVal e As System.EventArgs) _
       Handles ConnectToInvalidUserButton.Click
       ' This connection string has invalid credentials
       Dim InvalidUserConnection As String = _
           "Data Source=.\sqlexpress;Initial Catalog=Northwind;User ID = InvalidUser"
       ConnectToDatabase(InvalidUserConnection)
   End Sub
   
   Private Sub ConnectToInvalidDatabaseButton_Click _
       (ByVal sender As System.Object, ByVal e As System.EventArgs) _
       Handles ConnectToInvalidDatabaseButton.Click
       ' This connection string has an invalid/unavailable database
       Dim InvalidDatabaseConnection As String = _
           "Data Source=.\sqlexpress;Initial Catalog=InvalidDatabase;" & _
           "Integrated Security=True"
       ConnectToDatabase(InvalidDatabaseConnection)
   End Sub
   
   // C#
   private void GoodConnectButton_Click(object sender, EventArgs e)
   {
       // This is a valid connection string
       String GoodConnection =
           "Data Source=.\\sqlexpress;Initial Catalog=Northwind;Integrated
   Security=True";
       ConnectToDatabase(GoodConnection);
   }
   
   private void ConnectToInvalidUserButton_Click(object sender, EventArgs e)
   {
       // This connection string has invalid credentials
       String InvalidUserConnection =
           "Data Source=.\\sqlexpress;Initial Catalog=Northwind;User ID =
   InvalidUser";
       ConnectToDatabase(InvalidUserConnection);
   }
   
   private void ConnectToInvalidDatabaseButton_Click(object sender, EventArgs e)
   {
       // This connection string has an invalid/unavailable database
       String InvalidDatabaseConnection =
           "Data Source=.\\sqlexpress;Initial Catalog=InvalidDatabase;" +
           "Integrated Security=True";
       ConnectToDatabase(InvalidDatabaseConnection);
   }

7. Run the application.

8. Click the Connect button. No errors should be raised.

9. Click the Connect To Invalid User button. The code to catch the specific login error (error 18452) is executed.

10. Click the Connect To Invalid Database button. You can see that an error was raised and is displayed in the message box.

Lesson Summary

• A SqlException object is created when an error is detected on the SQL server.

• Every instance of a SqlException exception contains at least one SqlError warning that contains the actual error information from the server.

Lesson Review

The following questions are intended to reinforce key information presented in this lesson. The questions are also available on the companion CD if you prefer to review them in electronic form.

1. What types of errors will cause a SqlConnection object to close? (Choose all that apply.)

   1. Errors wth a severity level of 1 through 9

   2. Errors wth a severity level of 10 through 19

   3. Errors wth a severity level of 20 through 29

   4. Errors wth a severity level of 30 or greater

2. What property contains the actual error message returned by SQL Server? (Choose all that apply.)

   1. SqlException.Source

   2. SqlException.Message

   3. SqlError.Class

   4. SqlError.Message