Potty Little Details

Just another WordPress.com weblog

Working with Connection Events [C#]

leave a comment »

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 SQL Server .NET Data Provider 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.

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);

}

}

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.

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);

}

Advertisements

Written by oneil

September 11, 2008 at 2:59 am

Posted in ADO dot net

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: