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 3 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:

Private Sub ConnectToDatabase(ByVa1 connectionString As String) Dim connection As New SqlConnection(connectionString) Try connecti on.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("Inva1id Login Detected, please provide valid credentials!")

End If

Next

MessageBox.Show(errorMessage) Finally connecti on.Close () End Try End Sub

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:

Private Sub GoodConnectButton_Click _

(ByVal sender As System.Object, ByVal Handles GoodConnectButton.Click ' This is a valid connection string Dim GoodConnection As String = _

"Data Source=.\sqlexpress;Initial 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 e As System.EventArgs) _

Catalog=Northwind;Integrated Security=True;"

' This connection string has an invalid/unavailable database Dim InvalidDatabaseConnection As String = _

"Data Source=.\sqlexpress;Initial Catalog=InvalidDatabase;Integrated Security=True"

ConnectToDatabase(InvalidDatabaseConnecti on) End Sub

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(InvalidDatabaseConnecti on);

7. Run the application.

8. Click the Connect (valid connection string) button and no errors should be raised.

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

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

Was this article helpful?

0 0

Post a comment