3.3 Opening and Closing Connections

You've now seen all the ingredients you need to create and use a connection. You simply create the Connection object required for your data source, apply the appropriate connection string settings, and open the connection. In Example 3-1, a connection is created to a SQL Server database on the local computer using integrated authentication. The code opens the connection, tests its state, and closes it.

Example 3-1. Opening and testing a connection

// ConnectionTest.cs - Opens and verifies a connection

using System;
using System.Data.SqlClient;

public class ConnectionTest
{
    public static void Main() 
    {
        SqlConnection con = new SqlConnection("Data Source=localhost;" +
               "Initial Catalog=Northwind;Integrated Security=SSPI");

        con.Open();
        Console.WriteLine("Connection is " + con.State.ToString());
    
        con.Close();
        Console.WriteLine("Connection is " + con.State.ToString());
    }
}

The output clearly indicates whether the connection test is successful:

Connection is Open
Connection is Closed

3.3.1 Connection Events

Connection objects expose only two events. The InfoMessage event can retrieve warnings and other messages from a data source. Generally, you use this event if you wish to receive specific information messages that don't correspond to errors (in SQL Server, these are messages with a severity of 10 or less). You can't use this event to be informed about errors, which simply causes the Connection object to throw an exception.

The message information is wrapped into a provider-specific EventArgs object, such as OleDbInfoMessageEventArgs or SqlInfoMessageEventArgs.

You can examine this object for the error number and message text, as well as the additional provider-specific information. For example, SQL Server provides information about the database, stored procedure, and line number where the message originated.

Here's an example event handler for the InfoMessage event:

private 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} on line {4} of procedure {5} " +
         "on server {6}", err.Source, err.Class, err.State, err.Number,
         err.LineNumber, err.Procedure, err.Server);
    }
}

You can connect the event handler any time after creating the Connection object:

con.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);

Connection objects also provide a StateChange event that fires when the connection is opened or closed. This event provides a StateChangeEventArgs object with information about the current and previous state as a value from the System.Data.ConnectionState enumeration. Currently, the StateChange event fires only when the connection is opened or closed (not to inform you about other ConnectionState values), which reduces its usefulness dramatically.

3.3.2 Connections and Exception Handling

Exception handling is critical when accessing an external resource such as a database, as you can't guarantee the success of your operations. Problems such as a heavy user load, a misbehaving network connection, or invalid connection string parameters can derail your attempts to use or open a connection. However, because database connections are a finite resource, you also need to make sure you close a connection after a problem occurs. For example, consider this poorly written ADO.NET code:

try
{
    con.Open();
    // (Execute an ADO.NET command here.)
    con.Close();
}
catch (Exception err)
{
    Console.WriteLine(err.ToString());
}

If an error occurs after opening the connection but before closing it, the connection is left open. This potentially locks out other users until the garbage collector finds the Connection object and disposes it (at which point any outstanding transactions are rolled back, and the Dispose( ) method is invoked automatically). In a large, heavily used distributed application, this mistake can have a serious detrimental effect on user concurrency.

The correct approach is to create your ADO.NET objects outside the exception handler, open the connection inside the exception handler, and use the finally block to close the connection. This ensures that the Connection will always be closed, even if an error occurs.

try
{
    con.Open();
    // (Execute an ADO.NET command here.)
}
catch (err)
{
    Console.WriteLine(err.ToString());
}
finally
{
    con.Close();
}

If an exception such as SqlException, OleDbException, or OdbcException is thrown by a Connection or Command object, it's provider-specific. This exception can represent invalid query syntax, invalid user credentials, the inability to find the specified data source, or any one of a host of other problems. Provider-specific exceptions don't derive from a common base class that identifies them as ADO.NET exceptions, which can make it difficult to write generic code that works with different providers.

3.3.3 Disposing Connections

Instead of using an exception handling block, you can use the C# using statement with the Connection object. This ensures that Dispose( ) will be called on the Connection when code inside the using block ends. The Dispose( ) method is always called, regardless of whether the statements conclude successfully or an unhandled exception is thrown.

using (con)
{
    con.Open();
    // (Execute an ADO.NET command here.)
}
// (con.Dispose() is called automatically.)

This approach is similar to the previous example because the Dispose( ) method also closes the connection. However, it's not equivalent because the Dispose( ) method also releases all the unmanaged resources associated with the Connection object and removes it from the connection pool. This distinction means that this approach isn't suitable for most large-scale applications because it removes the key benefits of connection pooling (discussed in the next section).