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:
I have another post about this topic, you can find it under the title of Create a Vendor-Independent Data Access Class. But let’s be clearer: the System.Data.Common namespace provides a set of interfaces and classes to build your provider-agnostic data access class. Using these classes, you can even build new data providers.
The .NET Framework comes with four providers installed and configured in Machine.config. These are OdbcProvider, OledbProvider, SqlClientProvider and OracleClientProvider. When you want to connect to a data source, you must at least know its type name, and have a valid connection string. Without these, you’re stuck. The good news is that you can store both of these in the same place: yes, in a .config file, in the connectionStrings section. An example:
<add name=”myString” connectionString=”Data Source=.\SQLEXPRESS; Initial Catalog=Northwind; Integrated Security=true;” provider=”System.Data.SqlClient” />
Then you should instantiate a DbProviderFactory object, generate a DbConnection with it, and open the connection through that. The code is the following:
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:
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: