Working with Connection Events

The Connection object has two events that notify the application if a connection state changes. You can use these events to retrieve informational messages from a data source or to determine if the state of a Connection has changed. These two events are InfoMessage and StateChange. The InfoMessage event occurs when an informational message is returned from a data source. The exception handling doesn't catch these messages. The StateChange event occurs only when the state of a connection changes.

The InfoMessage event receives an InfoMessageEventArgs object. The InfoMessageEventArgs is as follows:

 Public Delegate Sub SqlInfoMessageEventHandler(_    ByVal sender As Object, _    ByVal e As SqlInfoMessageEventArgs _ ) 

In this example, sender is the source of the event and e is an SqlInfoMessageEventArgs object that contains the event data.

Table 5-1 describes the InfoMessageEventArgs properties.

Table 5-1: InfoMessageEventArgs Properties

PROPERTY	DESCRIPTION
ErrorCode	Returns the HRESULT following the ANSI SQL standard for the database
Errors	Returns the collection of warnings sent from the data source
Message	Returns the full text of the error
Source	Returns the name of the object that generated the error

Caution

Some of these members may be different for other data providers. For example, the Sql data provider doesn't have the ErrorCode property.

The StateChange event occurs when the state of a Connection changes. The StateChangeEventHandler is as follows:

 Public Delegate Sub StateChangeEventHandler(_    ByVal sender As Object, _    ByVal e As StateChangeEventArgs _ ) 

As you can see from the definition of StateChangeEventHandler, this handler has a second argument called StateChangeEventArgs. You can use StateChangeEventArgs to determine the change in state of the Connection using the OriginalState and CurrentState properties. The OriginalState property represents the original state of the Connection, and the CurrentState property represents the current state of the connection. The ConnectionState enumeration defines members for connection states (see Table 5-2).

Table 5-2: The ConnectionState Enumeration Members

PROPERTY	DESCRIPTION
Broken	This state occurs if the connection is broken after it was opened.
Closed	The connection is closed.
Connecting	The connection is connecting to a data source.
Executing	The Connection object is executing a command.
Fetching	The Connection object is retrieving data.
Open	The connection is open.

To test events, create a Windows application and add a SqlConnection to the form by dragging a SqlConnection component from the Toolbox Data tab. Then add two Button controls to the form: an Open button and a Close button. Now write the InfoMessage and StateChange event handlers (see Listing 5-1). As you can see from Listing 5-1, you generate messages if any of the events occur.

Listing 5-1: Writing Event Handlers for the InfoMessage and StateChange Events

 ' The InfoMessage event handler   Private Sub SqlConnection1_InfoMessage(ByVal sender As System.Object, _   ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs) _   Handles SqlConnection1.InfoMessage     Dim err As SqlError     For Each err In e.Errors       MessageBox.Show(err.Source.ToString() + err.State.ToString() & _        err.Source.ToString() + err.Message.ToString())     Next   End Sub   'The state change event handler   Private Sub SqlConnection1_StateChange(ByVal sender As System.Object, _   ByVal e As System.Data.StateChangeEventArgs) _   Handles SqlConnection1.StateChange     MessageBox.Show("Original State: " + e.OriginalState.ToString() & _        ", Current State :" + e.CurrentState.ToString())   End Sub 

Now let's write code to call these events. You write code on the Open and Close button click event handlers. On the Open button click event handler, you open a SqlConnection, and on the Close event handler, you close the connection (see Listing 5-2).

Listing 5-2: Writing Code That Executes Connection Events

 ' Open button click event handler   Private Sub OpenBtn_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles OpenBtn.Click     ' Create a Connection Object     Dim ConnectionString As String = "Integrated Security=SSPI;" & _      "Initial Catalog=Northwind;Data Source=MCB;"     SqlConnection1.ConnectionString = ConnectionString     ' Open the connection     If SqlConnection1.State <> ConnectionState.Open Then       SqlConnection1.Open()     End If   End Sub   ' Close button click event handler   Private Sub CloseBtn_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles CloseBtn.Click     Try       SqlConnection1.Close()     Catch exp As SqlException       MessageBox.Show(exp.Message.ToString())     End Try   End Sub 

Note

You may notice one thing when you compile your code. Even though you added SqlConnection using the wizard, you still need to import the System.Data.SqlClient namespace using the following line: Imports System.Data.SqlClient.

The output of clicking the Open button looks like Figure 5-3. It shows that the original state of the connection was Closed, and the current state is Open.

Figure 5-3: Output of the Open button click

The output of the Close button click looks like Figure 5-4. It shows that the original state of the connection was Open, and the current state is Closed.

Figure 5-4: Output of the Close button click