Handling Connection Events

The SqlConnection object exposes a few events that can be useful to trap "back-channel" communications from SQL Server. There are no new events implemented by ADO.NET 2.0, so I'll have to make do with those pulled forward from ADO.NET 1.1. Handling the Connection events is simple for Visual Basic .NET developerssimply declare the Connection object using the WithEvents operand. In C#, you'll have to set up a delegate to the SqlInfoMessageEventHandler.

The Connection events are the:

• Disposed event: This event fires when the object is disposed. There is little information about what should or should not happen here.

• InfoMessage event: This event is used to return an "informational" message back to your application. For example, if the T-SQL being executed includes a PRINT statement, the text message is returned via the InfoMessage event handler. RAISERROR also uses this mechanism to return messages if the severity is 10 or less. Note that in ADO.NET 2.0, a new Connection property, FireInfoMessageEventOnUserErrors, has been added. When you set this property to true, errors that previously were treated as exceptions are handled as InfoMessage events. All events fire immediately and are handled by the event handler. If is FireInfoMessageEventOnUserErrors is set to false, InfoMessage events are handled at the end of the procedure.

• StateChange event: This event is fired when the Connection object's State property changes. No, even though there is a Broken state, this is not implemented in ADO.NET 2.0it's simply a placeholder.

The example shown in Figure 9.34 shows how to set up the Connection event handlers in Visual Basic .NET and implement code to deal with the returned values. Because the FireInfoMessageEventOnUserErrors property has not been set to true, the RAISERROR messages generated by the T-SQL are not passed to the InfoMessage event handler.

Figure 9.34. Using WithEvents to set up Connection Exception handlers.

Sometimes, your application design requires that you leave the SqlConnection open for the life of the applicationor at least for extended periods of time. In other designs (especially ASP.NET and Web Services), you'll be prevented from holding SqlConnection objects open. In this case, you need to avoid the most fundamental of mistakesleaving the SqlConnection in an open state when it falls from scope. I've talked about this a number of times, so I won't pound on this further. However, I do need to briefly touch on an old saw about using Connection.Dispose instead of or in addition to Connection.Close. OO purists will say, "If the class implements a Dispose method, you should call it when you're done with the object." I say to them in response: "Who's going to pay for all that extra (and unneeded code)? Are you aware that the TextBox, Label, and every other control has a Dispose method? How many programs do you see that call Dispose on every single one of these controls. Let's not go there."