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: