Manage connection objects

Now that we’ve revised how to create a connection string, how to connect to data sources both in a provider-agnostic and provider-specific way, and how to catch the exceptions thrown by our code, it’s time to dig ourselves deeper into the Connection objects. We’ll take a look at the following topics:

• Managing connection state
• Managing connection pool
• Implementing persistent data connections
• MARS (Multiple Active Result Sets)
• Encrypting and decrypting data

As you can guess, there’s an interface called IDbConnection, implemented by the generic DbConnection class, which is the base class of all provider-specific connection objects, such as SqlConnection or OleDbConnection. This is the family tree of connection classes.

Connection objects have several properties and methods. One that comes quite handy is the State property, which provides information about the (shocking!) state of the connection. This can be one of the values of the System.Data.ConnectionState enumeration. The values are: Open, Closed, Connecting, Fetching, Executing, Broken. I think that these are rather self-describing names, without the immediate need to discuss them. It is a good habit to use the State property, because certain connection-related tasks tend to throw exceptions in given circumstances. For example, opening an opened connection will surely kill your app. This is how would you do it:

Handle and diagnose database connection exceptions

When working with databases, you should be a bit pessimistic. Databases are sometimes down, under maintenance, etc. so they aren’t tend to be available in all cases. Because of this, there’s much need for error-handling logic in your data access code.

This task doesn’t require anything else than you probably know already: use try/catch/finally blocks. You should put anything that might cause an exception into a try block. Then write your error-handling code in catch blocks, and use multiple ones, if you see fit. The last thing to do is to implement a finally block, where you should close your connection and perform cleanup. Because you probably want to use your object which caused an error in a finally block, don’t declare it within a try block, because it won’t be accessible (its scope will be the try block). After all these advices, let’s see a code example:

Manage Connection Strings

Before we can retrieve, modify or even see any data stored in a database, regardless of the engine used, we should set up a connection string. With it, we can instantiate a Connection object, and use it to open the database connection.

So connection strings are such a fundamental part of communicating with databases, but what are they? Basically, they are string literals. But you should treat and see them as name-value collections. ADO.NET has classes which help you build connection strings in this manner. They have the suffix ConnectionStringBuilder, following the prefix of the database engine (for example, SQL). But that’s a further topic which I’ll talk about a bit later in this post.

Let’s see a valid connection string:

Data Source=.\SQLEXPRESS; Integrated Security=SSPI; Initial Catalog=Northwind;

The previous string uses the SQLEXPRESS SQL server on the local machine (the . always means the local machine), and uses Windows Authentication, which should be used in most cases. The most important properties of the connection strings: