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:

SqlConnection theConnection = new SqlConnection(“Connection string here”);
//A lot lines later
if(theConnection.State != ConnectionState.Open)

Or maybe another use:

if(theConnection.State == ConnectionState.Open)

Database connections are expensive to create, maintain and destroy. For this reason, you should enable connection pooling. If you didn’t disable connection pooling in your connection string, and, even better, you use a connection string from an external .config file (this will be made clearer in a moment), you have a good chance that your connections will be pooled. This means that after you close a connection with the database, it gets into the pool, and waits to be reused. So the next time you need to connect to the exact same data source with the exact same connection string, the previously closed connections are reused. In this way, authentication, handshaking, securing, etc. won’t take place every time you need to work with a database (imagine the effect in an ASP.NET application, where data binding takes place after every postback!). You can control connection pooling in the connection string, with the following properties:

  • Connection Lifetime:  you specify seconds in an integer. If the creation time of the connection just returned to the pool compared to the current time exceeds the given value, the connection will be destroyed.
  • Connection Reset: determines whether the connection is reset when being drawn of the pool.
  • Load Balance Timeout: the minimal time for which the connection should be kept in the connection pool before destroyed, in seconds.
  • Max/Min Pool Size: the maximum and minimum number of connections to be stored in the pool. Default is 100/0.
  • Pooling: turn connection pooling on or off.

One case when you should disable connection pooling is when you want to deal with persistent connections. Persistent connections are remaining available after they’ve handled every task, than closed explicitly. I have nothing more to tell about them.

Multiple Active Result Sets have a cool name, by coincidence, I’m quite sure about that. Their main benefit is what their name suggests, they provide you with the power of handling multiple active result sets in the same time. Consider the following example:

SqlConnection theConnection = new SqlConnection(“my connection string”);
SqlCommand theCommand1 = new SqlCommand(“SELECT * FROM Data”, theConnection);
SqlCommand theCommand2 = new SqlCommand=”SELECT * FROM OtherData”, theConnection);
SqlDataReader = theCommand1.ExecuteReader();
SqlDataReader = theCommand2.ExecuteReader();

If you haven’t included “MultipleActive ResultSets=true;” in your connection string, this code will fail. If you have, no exceptions will be thrown. Using MARS, however, you must pay a price. For example, when you are inserting data, and in the same time, try to select, you’ll have to keep waiting until the insertion finishes, and the selection will be executed after it.

There is really not much to say about encryption and decryption of data. As a general rule of thumb, you shouldn’t encrypt data stored in databases (with the exception of passwords, for example), because it won’t make your database more secure, it just take more time to get the information needed by a potential attacker. Instead, you should focus on securing the channel in which data transmits. Use SSL when connecting to data sources. This can be done by specifying the Encrypt=true value in the connection string. However, if no certificate exists, the connection will fail.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s