Database Reference
In-Depth Information
Note that you can call
Close()
on a closed connection, and no exception will be thrown. So, your
message would have been displayed if the connection had been closed earlier or even if it had never
been opened. See Figure 12-2, where the connection failed but the close message is still displayed.
In one typical case, multiple calls to both
Open()
and
Close()
make sense. ADO.NET supports
disconnected processing of data, even when the connection to the data provider has been closed. The
pattern looks like this:
try
{
// open connection
conn.Open();
// online processing (e.g., queries) here
//
conn.Close(); // close connection
//
// offline processing here
//
conn.Open(); // reopen connection
//
// online processing(e.g., INSERT/UPDATE/DELETE) here
//
conn.Close(); // reclose connection
}
catch(SqlException ex)
{
// error handling code here
}
finally
{
// close connection
conn.Close();
}
The
finally
block still calls
Close()
, calling it unnecessarily if no exceptions are encountered, but
this isn't a problem or expensive, and it ensures the connection will be closed. Although many
programmers hold connections open until program termination, this is usually wasteful in terms of
server resources. With
connection pooling,
opening and closing a connection as needed is actually more
efficient than opening it once and for all.
That's it! You're finished with the first connection example. However, since you saw a possible error,
let's look at typical causes of connection errors.
Debugging Connections to SQL Server
Writing the C# code to use a connection is usually the easy part of getting a connection to work.
Problems often lie not in the code but rather in a mismatch in the connection parameters between the
client (your C# program) and the database server. All appropriate connection parameters must be used
and must have correct values. Even experienced database professionals often have problems getting a
connection to work the first time.
