Making Connections - Beginning C# 5.0 Databases - page 192

Now you have a connection, but you still need to establish a session with the database by calling the
Open method on the connection. If the attempt to open a session fails, an exception will be thrown, so
you use a try statement to enable exception handling. You display a message after calling Open , but this
line will be executed only if the connection was successfully opened.
try
{
// Open connection
conn.Open();
Console.WriteLine("Connection opened.");
}
At this stage in the code, you'd normally issue a query or perform some other database operation
over the open connection. However, we'll save that for later chapters and concentrate here on just
connecting.
Next comes an exception handler in case the Open() fails, as shown in Figure 12-2 earlier in the
chapter.
catch (SqlException ex)
{
// Display error
Console.WriteLine("Error: " + ex.Message + ex.StackTrace);
}
Each data provider has a specific exception class for its error handling; SqlException is the class for
the SQL Server data provider. Specific information about database errors is available from the exception,
but here you're just displaying its raw contents.
When you're finished with the database, you call Close() to terminate the session and then print a
message to show that Close() was called.
finally
{
// Close connection
conn.Close();
Console.WriteLine("Connection closed.");
}
You call Close() within the finally block to ensure it always gets called.
Console applications have a tendency to load the command window with output for a short time
and close by itself. To hold the window on the screen so you can read and understand the output, invoke
the ReadLine() method of the Console class. This will let the window stay until you press the Enter key.
Console.ReadLine();
 Note Establishing connections (database sessions) is relatively expensive. They use resources on both the
client and the server. Although connections may eventually get closed through garbage collection or by timing out,
leaving one open when it's no longer needed is a bad practice. Too many open connections can slow a server
down or prevent new connections from being made.