Daily Archives: September 9, 2013

Managing Database Connections

By Tim Daniels

Before an application can access information from a database, a proper connection to the server hosting the database, and the database itself, must be established. Managing database connections to optimize application performance, and application maintainability, is relatively easy to accomplish on the Microsoft .NET platform. To demonstrate this, I’ve developed a C#.NET class, which utilizes three key components for managing database connections;

Configuration File

First, lets discuss the configuration file. A configuration file in .NET, is simply an XML file with parent/child nodes. Each node can contain a set of both system defined, and programmer defined, key/value pairs. These key/value pairs contain application settings to control specific application and program level functions. The configuration file can be accessed by program code throughout an application. Many of the project templates in Visual Studio will automatically generate a configuration file for the project. However, a configuration file can be manually added to a project simply by right clicking on the project name in the Solution Explorer, taking the add new item menu option, and then selecting Application Configuration File, as illustrated here;

Adding an application config file

Adding an application configuration file to a project in Visual Studio 2012

This will add an XML file called App.config to the Visual Studio project. There is one subtle difference when adding a configuration file to a website project. The configuration file is called Web Configuration File, and adds an XML file called Web.config to the project, as illustrated here;

Adding a web configuration file

Adding a web configuration file to a website project in Visual Studio 2012

The beauty of the configuration file, is that it’s deployed along with the application. Configuration files can be changed as needed. Developers can use configuration files to change settings without recompiling the application. Administrators can use configuration files to set policies that affect how applications run on their computers.

Here is the application configuration file I created for an application I’m currently developing.

As you can see there are several XML nodes dealing with Entity Framework, which is another component I’m using in this application. For this discussion we are only concerned with the “configuration” node and its child node “connectionStrings”.

Now lets look at an example of using the configuration file to manage database connections in an application. Below is a C# class I wrote called “ConnectionsStrings”. Here is how the class is implemented in an application that access a SQL Server database.

Database Connection Management Solution
The objectives for developing a database connection management solution are as follows;

  • Develop a reusable class with flexibility to retrieve a connection string from the App.config file, or the Web.config file, and return the string to the consumer
  • Implementation of the ConfigurationManager class, to take advantage of having the database connection string coded in one place, the App.config file. Thus, eliminating the need to have it hard-coded throughout the application. This improves overall maintainability
  • Implementation of the SqlConnectionStringBuilder class, to supply additional values to the connection string, such as user name and password

Configuration Manager
The ConnectionStrings class below, has a method called BuildConnectionString. You will notice that the method is also overloaded with a version that will accept the following parameters;

  • dataSource
  • userName
  • userPassword

The BuildConnectionString method implements two classes from the System.Configuration name space.

The ConfigurationManager class has a property called ConnectionStrings. This property gets the connection strings section data from the current application’s default configuration file. It returns a connection string settings collection object. This collection object holds the contents of the connection strings section of the configuration file. Refer back to the configuration file example above to see the contents of the connection strings section. Remember how I described the contents of the configuration file as key/value pairs. In this case, the key is name=”DbConnection”. The value associated with this key is connectionString=”Data Source=.\SQLEXPRESS;Initial Catalog=Lexicon.ActionManager.ProjectManagementContext;Integrated Security=True”.

The following line of code retrieves the value associated with “DbConnection” and assigns it to variable called settings which is a type of ConnectionStringSettings.

Don’t forget to add a reference to the System.Configuration name space in your project and put the using System.Configuration statement at the top of the class.

Connection String Builder
Now lets take a look a the SqlConnectionStringBuilder class. This class comes in handy when additional information needs to be added to the connection string in the configuration file. In cases where it may not be practical to store the connection string in a configuration file, it can be used to build the entire connection string from a set of parameters. The SQLConnectionStringBuilder class has numerous properties for configuring all kinds of settings related to database connectivity. In my ConnectionStings class, I overloaded the BuildConnectionString method to accept data source, user name and password as parameters, that supply additional information for the connection string retrieved from the configuration file.

Final Implementation
In order to make the ConnectionStrings class as reusable as possible, I’m hosting it within a separate class library called “Connections”. This class library is referenced by a console application project I developed for unit testing purposes. This is illustrated in the solution explorer of Visual Studio 2012.

Solution Explorer with Connections class library

Solution Explorer with Connections class library

Finally, here is one implementation of the ConnectionStrings class, where it is used to populate the connection string property of a database instance of a database context class.