Events are useful for notifying a program of when an action happens to an object. An event can have a method associated with it. This method is an event handler. When an event occurs, the associated method executed. An event can have multiple event handlers.
A class defines an event by providing an event declaration, which is of type delegate. The following defines an event handler:
Public Delegate Sub EventHandler(ByVal sender As Object, ByVal e As System.EventArgs)
EventHandler takes two arguments: one of type Object and the other of type System.EventArgs. As you can see from figure 9-1, each of the event handlers has an event argument type that corresponds to it. The event argument class contains the data
related to the event, which can be accessed through event argument class members.
In this article, I'll show you how can handle events for ADO.NET objects.
Introducing ADO.NET events
You can divine ADO.NET classes into groups: disconnected classes and data provider classes. The System.Data namespace defines the common event handlers and event argument classes for all data providers. As you can see from figure 9-1, event handler classes come from the Delegate class, and event argument classes come from the EventArgs class.
Figure 9-1 Event handler and event argument classes defines in the System.Data namespace
You use the DataColumnChangeEventHandler as an event handler for DataColumn when you are changing a value for a column in a DataRow. You use DataRowChangeEventHandler as an event handler that handles the RowChanging, RowChanged, RowDeleting, and RowDeleted events of a DataTable. You use the FillErrorEventHandler as an event handler that handles a data adapter's Fill event. If an error occurs during the fill operation, then FillError fires, which executes FillErrorEventHandler. MergeFailedEventHandler handles a dataset's MergeFailed event, which occurs when a dataset's merge operation fails. StateChangeEventHandler handles the StateChange event, which occurs when the connection state changed. Besides the Data namespace defining the event handler and event argument classes, the data provider-specific namespace also define the data provider event handler and event argument classes. Some of these classes and their usage may vary from provider to provider. Figure 9-2 shows the OleDbdata provider event handlers and event argument classes.
Figure 9-2 OleDb data provider event handler and event arguments
OleDbInfoMessageEventHandler handles the InfoMessage event of OleDbConnection. An InfoMessage event occurs when a provider sends a warning or an informational message to the application.
OleDbRowUpdateEventHandler handles the RowUpdate event of OleDbDataAdapter. The RowUpdated event occurs when you call an Update method on a data adapter.
OleDbRowUpdatingEventHandler handles the RowUpdating event of OleDbDataAdapter. The RowUpdating event occurs before a command executes against the data source.
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 Connection changes. 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 OleDbInfoMessageEventHandler(ByVal sender As Object, ByVal e As OleDbInfoMessageEventArgs)
Where Sender is the source of the event of the event and e is an OleDbInfoMessageEventArgs object that contains the event data. Table 9-1 defines the InfoMessageEventArgs properties.
Table 9-1 The InfoMessageEventArgs properties
|
PROPERTY
|
DESCRIPTION
|
|
ErrorCode
|
Returns HRESULT following the ANSI SQL standard for the database
|
|
Errors
|
Returns the collection of warnings sent from the data source
|
|
Message
|
Return the full text of the error
|
|
Source
|
Return the name of the object that generated the error
|
Caution: Some if these members may vary for other data providers. For example, the Sql data provider doesn't have an ErrorCode property.
The StateChange event occurs when the state of Connection changes. The StateChange event receives StateChangeEventArgs, which enables you to determine the changes in state of 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 properties for connection states (see Table 9-2).
Table 9-2 The ConnectionState Properties
|
PROPERTY
|
DESCRIPTION
|
|
Broken
|
This state occurs when the connection is broken after it was opened.
|
|
Closed
|
The connection is closed.
|
|
Connecting
|
The connection is connected to a data source.
|
|
Executing
|
The connection object is execution a command.
|
|
Fetching
|
The connection object is retrieving data.
|
|
Open
|
The connection is open.
|
To test the connection events, I created a windows application and added two buttons to the form. I then set the name of the button control to StateChangeEventBtn and InfoMessageEventBtn. I also set the text of the buttons to "StateChange Event" and "InfoMessage Event". The final form looks like figure 9-3.
Figure 9-3 Windows application for testing connection events
You can add both connection events programmatically as well as at design-time. To add these events from VS.NET, just drag an SqlConnection (or OleDbConnection) from toolbox's Data tab to a form and write events from the properties windows. As you can see in figure 9-4, you merely double-click the InfoMessage and StateChange events to write event handlers for them.
Figure 9-4 Adding the InfoMessage and StateChange event
Write the code in listing 9-1 for both the events.
Listing 9-1 Writing event handlers for the InfoMessage and StateChange events
' Connection InfoMessage Event Handler
Private Sub sqlConnection1_InfoMessage(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs)
Dim i As Integer
For i = 0 To e.Errors.Count - 1
MessageBox.Show(e.Errors(i).Message)
Next
End Sub
Private Sub sqlConnection1_StateChange(ByVal sender As Object, ByVal e As System.Data.StateChangeEventArgs)
MessageBox.Show(("Original State:" & e.OriginalState.ToString() & ", New state = ") + e.CurrentState.ToString())
End Sub
Note: Don't forget to add a reference to the data provider namespace.
To test these events, write code on the StateChange event and InfoMessage event button-click handlers (see listing 9-2). As you can see, the StateChangeEventBtn_Click handler opens a connection and then closes the connection. Opening and closing a connection invokes the StateChanges event and executes code written on the StateChange event handler.
On the InfoMessageBtn_Click handler, I opened a connection, called the ChangeDatabase method of SqlConnection and then closed the connection. The ChangeDatabase method invokes InfoMessageEvent.
Listing 9-2 Writing code that executes connection events
Private Sub StateChangeEventBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Try
' Create a Connection object
Dim ConnectionString As String = "Integrated Security=SSPI;" & "Initial Catalog=Northwind;" & "Data Source =MAIN-SERVER"
sqlConnection1.ConnectionString = ConnectionString
' Open the connection
If sqlConnection1.State <> ConnectionState.Open Then
sqlConnection1.Open()
End If
' Close the connection
sqlConnection1.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Private Sub InfoMessageEventBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Try
' Create a Connection Object
Dim ConnectionString As String = "Integrated Security=SSPI;" & "Initial Catalog = Northwind;" & "Data Source=localhost;"
sqlConnection1.ConnectionString = ConnectionString
' Open the connection
If sqlConnection1.State <> ConnectionState.Open Then
sqlConnection1.Open()
End If
'Change database
sqlConnection1.ChangeDatabase("Master")
' Close the connection
sqlConnection1.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
The output of the StateChange event's button-click handler looks like figure 9-5 and figure 9-6. The StateChangeEventBtn_Click method fires the StateChange event two times-first when you call the Open method and the connection state changes from closed to open and, second, when you call the Close method and the connection state changes from open to closed.
Figure 9-5 The StateChangeEvent output when you call the open method
Figure 9-6 The StateChangeEvent output when you call the Close method
The output of InfoMessageBtn click looks like figure 9-7, which shows the database changing to master as a result of the ChangeDatabase method.
Figure 9-7 The InfoMessage event output when you call the ChangeDatabase method
Conclusion
Hope this article would have helped you in understanding handling ADO .NET Events & Connection Events. See other articles on the website also for further reference.