Opening Connections and Handling Events

for RuBoard

Yesterday you saw how the IDbConnection interface could be used by a .NET Data Provider to implement a class that is used to communicate with a data store. As such, the connection classes are responsible for all communication between the client and the data store, including the execution of queries and commands and the processing of any messages sent back from the data store.

Note

Because the connection class is responsible for communication, it's also implicitly responsible for providing credentials to the data store in order to be authenticated and authorized to access the data protected by the data store. As you can imagine, the credentials are provided through the ConnectionString property, so handling the connection string with care is an issue you must address. We'll discuss this later today.

Opening Connections

To open the connection to the data store, you must, of course, call the Open method of an instance of the connection class and pass it the connection string. The connection object will then attempt to open the connection and wait as long as specified in the ConnectionTimeout property, whose default for both SqlConnection and OleDbConnection is 15 seconds. This property is read-only and can be set as an attribute of the connection string.

Depending on the results of the Open method, the State property of the connection object will change accordingly to one of the values of the ConnectionState enumeration. Although the ConnectionState enumeration includes six values ( Broken , Closed , Connecting , Executing , Fetching , and Open ), only Closed and Open are implemented in this release of VS .NET. The code in the following code snippet connects to a SQL Server database:

 SqlConnection con = new SqlConnection(_connect); try {   con.Open();   // Success con.State = ConnectionState.Open } catch (SqlException e) {   // Failed con.State = ConnectionState.Closed } 

You'll notice in the previous snippet that the ConnectionString property is populated in the constructor with a String variable called _connect , although it could also have been set directly using the ConnectionString property. If the ConnectionString is not initialized before the Open method is called, an InvalidOperationException is thrown. If the connection is opened successfully, its State will be set to Open . If an exception occurs, it will still be Closed .

Tip

After a connection is open, you can't change the ConnectionString property. To reset the connection, you must call its Close method first, change the ConnectionString , and then re-open the connection using the Open method.

Of course, after the connection is open, it can be used to execute command objects. Even though you can open the connection explicitly, the connection can also be opened and closed implicitly by the data adapter when its Update method is called.

To break the connection, you can call the Close method. Note that if the connection is not open, the Close method simply returns.

Handling Events

As you learned yesterday, both the SqlClient and OleDb providers also implement two connection-based events: StateChange and InfoMessage . The StateChange event is fired any time the State property of the connection object changes, which, in the initial release, is when the connection is opened or closed. The InfoMessage event is fired when the data store generates a message for the client and is useful for retrieving warning messages and other supplementary information. Keep in mind that if a severe error actually occurs, an exception will be thrown rather than informational messages. However, it's up to the provider to determine when that threshold is reached. For example, in SQL Server, if an error is generated with a severity greater than 10, a SqlException will be thrown rather than generating an informational message.

To illustrate how and why you would want to catch these events, consider the code in Listing 9.1 where the three methods execute a simple command against SQL Server and catch the resulting events.

Listing 9.1 Connection events. The three methods in this listing show how to handle the StateChange and InfoMessage events.

 public void PrintEvents() {    string  _connect  =      "server=ssosa;database=computebooks;trusted_connection=yes";    SqlConnection con = new SqlConnection(_connect);     con.StateChange += new StateChangeEventHandler(this.conStateChanged);     con.InfoMessage += new SqlInfoMessageEventHandler(this.conInfoMessage);     try     {         con.Open();         SqlCommand com = new SqlCommand(            "PRINT 'Hello From ' +  @@SERVERNAME",con);         com.ExecuteNonQuery();     }     catch (SqlException e)     {         Console.WriteLine(e.Message);     }     finally     {         con.Close();     } }  // Handle the StateChange event private void conStateChanged(object sender,  StateChangeEventArgs e) {     if (e.CurrentState == ConnectionState.Closed)     {         Trace.WriteLine(((SqlConnection)sender).ConnectionString +           " closed at " + DateTime.Now.ToLongTimeString());      } } // Handle the InfoMessage event private void conInfoMessage(object sender,  SqlInfoMessageEventArgs e) {     Trace.Write(DateTime.Now.ToLongTimeString() + ":");     foreach (SqlError err in e.Errors)     {         Trace.WriteLine("The " + err.Source + " has received a severity " +           err.Class + " state " + err.State + " error number " + err.Number +           " on line " + err.LineNumber + " of procedure " + err.Procedure +           " on server " + err.Server +  "\n" + err.Message);     } } 

As you can see from Listing 9.1, the PrintEvents method simply creates a new SqlConnection object and initializes it with a connection string passed into the constructor. The StateChange and InfoMessage events are then mapped to the conStateChanged and conInfoMessage private methods using the appropriate delegates. The instances of the StateChangeEventHandler and SqlInfoMessageEventHandler are both delegates that simply point to the methods they are passed and are then attached to the events using the += operator.

Note

Delegates in .NET are type-safe function pointers that are used as the basis for events and asynchronous programming in .NET.

In VB, you could dynamically associate the events to their handler using the AddHandler and RemoveHandler statements or statically using the Handles statement in the declaration of the method used to handle the event.

The event handlers themselves take specific types as their second argument; in this case, the StateChangeEventArgs and SqlInfoMessageEventArgs classes. The StateChangeEventArgs class simply exposes the CurrentState and OriginalState properties that map to the ConnectionState enumeration. The SqlInfoMessageEventArgs class exposes a subset of the information as the SqlException class, which includes a collection of SqlError objects. You'll notice in the conInfoMessage method that multiple messages may be returned and can be traversed using a foreach loop. The PrintEvents message simply uses the Transact-SQL PRINT statement to output a string message of severity 0.

Both event handlers write their output to the Trace object through the WriteLine method. This is an interesting use of these events because it allows your application to capture the trace output using a custom listener. Of course, you could always look for specific messages by inspecting the Class , State , or Number properties of the SqlError object. In the same way, the OleDbInfoMessageEventArgs class exposes a subset of the properties of the OleDbException class.

for RuBoard