Working with Connection Events

The Connection object has two events you can use to retrieve informational messages from a data source or to determine if the state of a Connection has changed. The following table lists the events of the Connection object.

Event	Description
InfoMessage	Occurs when an informational message is returned from a data source. Informational messages are messages from a data source that do not result in an exception being thrown.
StateChange	Occurs when the state of the Connection changes.

InfoMessage

You can retrieve warnings and informational messages from a data source using the InfoMessage event of the Connection object. Errors returned from the data source result in an exception being thrown. However, the InfoMessage event can be used to obtain messages from the data source that are not associated with an error. In the case of Microsoft SQL Server, any message with a severity of 10 or less is considered informational and would be captured using the InfoMessage event.

The InfoMessage event receives an InfoMessageEventArgs object containing, in its Errors property, a collection of the messages from the data source. You can query the Error objects in this collection for the error number and message text, as well as the source of the error. The .NET Framework Data Provider for SQL Server also includes detail about the database, stored procedure, and line number that the message came from.

The following code example shows how to add an event handler for the InfoMessage event.

SqlClient

  Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind;")

  AddHandler nwindConn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)


Private Shared Sub OnInfoMessage(sender As Object, args As SqlInfoMessageEventArgs)
  Dim err As SqlError
  For Each err In args.Errors
    Console.WriteLine("The {0} has received a severity {1}, state {2} error number {3}\n" & _
                      "on line {4} of procedure {5} on server {6}:\n{7}", _
                      err.Source, err.Class, err.State, err.Number, err.LineNumber, _
                      err.Procedure, err.Server, err.Message)
  Next
End Sub
[C#]
  SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind;");

  nwindConn.InfoMessage  += new SqlInfoMessageEventHandler(OnInfoMessage);


protected static void OnInfoMessage(object sender, SqlInfoMessageEventArgs args)
{
  foreach (SqlError err in args.Errors)
  {
    Console.WriteLine("The {0} has received a severity {1}, state {2} error number {3}\n" +
                      "on line {4} of procedure {5} on server {6}:\n{7}",
                      err.Source, err.Class, err.State, err.Number, err.LineNumber, 
                      err.Procedure, err.Server, err.Message);
  }
}

OleDb

  Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind;")

  AddHandler nwindConn.InfoMessage, New OleDbInfoMessageEventHandler(AddressOf OnInfoMessage)


Private Shared Sub OnInfoMessage(sender As Object, args As OleDbInfoMessageEventArgs)
  Dim err As OleDbError
  For Each err In args.Errors
    Console.WriteLine("The {0} has received a SQLState {1} error number {2}:\n{3}", _
                      err.Source, err.SQLState, err.NativeError, err.Message)
  Next
End Sub
[C#]
  OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind;");

  nwindConn.InfoMessage  += new OleDbInfoMessageEventHandler(OnInfoMessage);


protected static void OnInfoMessage(object sender, OleDbInfoMessageEventArgs args)
{
  foreach (OleDbError err in args.Errors)
  {
    Console.WriteLine("The {0} has received a SQLState {1} error number {2}:\n{3}",
                      err.Source, err.SQLState, err.NativeError, err.Message);
  }
}

StateChange

The StateChange event occurs when the state of a Connection changes. The StateChange event receives StateChangeEventArgs that enable you to determine the change in state of the Connection using the OriginalState and CurrentState properties. The OriginalState property is a ConnectionState enumeration that indicates the state of the Connection before it changed. CurrentState is a ConnectionState enum that indicates the state of the Connection after it changed.

The following code example uses the StateChange event to write a message to the console when the state of the Connection changes.

  AddHandler nwindConn.StateChange, New StateChangeEventHandler(AddressOf OnStateChange)


Protected Shared Sub OnStateChange(sender As Object, args As StateChangeEventArgs)

  Console.WriteLine("The current Connection state has changed from {0} to {1}.", _
                    args.OriginalState, args.CurrentState)
End Sub
[C#]
  nwindConn.StateChange  += new StateChangeEventHandler(OnStateChange);


protected static void OnStateChange(object sender, StateChangeEventArgs args)
{
  Console.WriteLine("The current Connection state has changed from {0} to {1}.",
                    args.OriginalState, args.CurrentState);
}

See Also

Using .NET Framework Data Providers to Access Data