Know Dot Net Article

Getting Intimate with your Connection

InfoMessage, StateChange and more...

by William Ryan

By and large, all you need to know about a IDBConnection object is that you need to Open it before you make a request and you should Close it when you are done with it.  That bit of knowledge can get you pretty far, but if you want to really learn about the connection object, there's a few other things you might be interested in.

I had recently written an article describing the ConnectionState property of the SqlClient and OleDbClient objects.  As I mentioned, only two of the enumerated values are supported, namely ConnectionState.Open and ConnectionState.Closed.  However, let's say you wanted to know if something went wrong with the connection in the middle of some action, for instance, someone tripped on the network cable and pulled it out of your machine.  The StateChange event is fired whenever the State of a connection changes, so you could wire an event handler to let you know WHEN a connection has successfully been made and when/if it's been closed.  (This example came from MSDN, but there were some modifications that needed to be made to get it to run):

VB.NET

Protected Shared Sub OnStateChange(ByVal sender As Object, ByVal e As StateChangeEventArgs)
    Console.WriteLine("The current Connection state has changed from {0} to {1}.", _
                  e.OriginalState, e.CurrentState)
End Sub

private void cnMain_StateChange(object sender, System.Data.StateChangeEventArgs e)
{
    Debug.WriteLine("The current Connection state has changed from {0} to {1}.",
       e.OriginalState, e.CurrentState);
}

The first thing to notice is that StateChange is an event of System.Data not SqlClient or OleDbClient. StateChangeEventArgs has two properties outside of those that every object has, CurrentState and OriginalState.  So, if I wire the EventHandler in with this code:

VB.NET

AddHandler cnMain.StateChange, New StateChangeEventHandler(AddressOf OnStateChange)

C#

this.cnMain.StateChange += new System.Data.StateChangeEventHandler(this.cnMain_StateChange);

The code in cnMain_StateChange will execute every time there is a state change.  So, in this example, I'm using an IDataAdapter object to populate a DataSet and I'm never calling Connection.Open or Connection.Close explicitly.  Other than the fact that my dataset gets populated, how do I know that the connection is actually being opened and closed?  On Form_Load, I call the following and set a break point on cnMain_StateChange:

VB.NET

daFacilities.Fill(dsEmployees1, "Facilities")

C#

daFacilities.Fill(dsEmployees1, "Facilities");

My eventhandler will be called twice and the output is shown below:

The current Connection state has changed from Closed to Open.
The current Connection state has changed from Open to Closed.

So, if you could conceivably implement a mechanism with this that would alert you if a connection was opened but not closed in a certain period of time.

Another neat feature is the InfoMessage event.  It's supported under SqlClient, OleDbClient, Oracle and ODBC, although you'll need the 1.1 framework for either or both of the last two.

So, add in the handlers:

VB.NET

AddHandler cnMain.InfoMessage, New OleDbInfoMessageEventHandler(AddressOf OnInfoMessage)

C#

this.cnMain.InfoMessage += new System.Data.SqlClient.SqlInfoMessageEventHandler(this.cnMain_InfoMessage);

Now, if we get an error message back with a severity level less than 10 in SqlServer (I wasn't able to find it out for the other providers) it will be contained in the Errors collection.  As such, you can interrogate it an find out about subtle errors that may have occured but weren't deal breakers as such:

VB.NET

Private Shared Sub OnInfoMessage(ByVal sender As Object, ByVal args As System.Data.SqlClient.SqlInfoMessageEventArgs)
   Dim err As System.Data.SqlClient.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#

private void cnMain_InfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e)
{
   foreach (System.Data.SqlClient.SqlError err in e.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);
   }
}

Of course, you won't get any information if no errors are returned.  But, as you can see, there is some really good information you can retrieve, and in your own stored procedures, you can send errors back that you write, adjusting the severity thereby allowing yourself an effective way to communicate back and forth between server and client.  The OleDb library is a little smaller and I guess that's because it's suited to multiple providers and it's doubtful Access would have the same support for messages as Oracle or Sybase.  Nonetheless, it's something you may find helpful.