Handling ADO .NET Events & Connection Events

This article has been excerpted from book "A Programmer's Guide to ADO.NET in C#".

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 void EventHandler(object sender, System.EventArgs e);

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 void OleDbInfoMessageEventHandler(object sender, OleDbInfoMessageEventArgs e);

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 void sqlConnection1_InfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e)
        {
            int i;

            for (i = 0; i < e.Errors.Count; i++)
            {
                MessageBox.Show(e.Errors[i].Message);
            }
        }

        private void sqlConnection1_StateChange(object sender, System.Data.StateChangeEventArgs e)
        {
            MessageBox.Show("Original State:" + e.OriginalState.ToString()
            + ", New state = " + e.CurrentState.ToString());
        }

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 void StateChangeEventBtn_Click(object sender, System.EventArgs e)
        {
            try
            {
                // Create a Connection object
                string ConnectionString = "Integrated Security=SSPI;" +
                "Initial Catalog=Northwind;" +
                "Data Source =MAIN-SERVER";

                sqlConnection1.ConnectionString = ConnectionString;

                // Open the connection
                if (sqlConnection1.State != ConnectionState.Open)
                    sqlConnection1.Open();

                // Close the connection
                sqlConnection1.Close();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void InfoMessageEventBtn_Click(object sender, System.EventArgs e)
        {
            try
            {
                // Create a Connection Object
                string ConnectionString = "Integrated Security=SSPI;" +
                "Initial Catalog = Northwind;" +
                "Data Source=localhost;";
                sqlConnection1.ConnectionString = ConnectionString;

                // Open the connection
                if (sqlConnection1.State != ConnectionState.Open)
                    sqlConnection1.Open();

                //Change database
                sqlConnection1.ChangeDatabase("Master");

                // Close the connection
                sqlConnection1.Close();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

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.

This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET.