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 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 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.
|
<?xml version="1.0" encoding="utf-8"?> <configuration> <configSections> <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 --> <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=4.4.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" /> </configSections> <entityFramework> <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" /> </entityFramework> <connectionStrings> <add name="DbConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Lexicon.ActionManager.ProjectManagementContext;Integrated Security=True"/> </connectionStrings> </configuration> |
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”.
|
<?xml version="1.0" encoding="utf-8"?> <configuration> <connectionStrings> <add name="DbConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Lexicon.ActionManager.ProjectManagementContext;Integrated Security=True"/> </connectionStrings> </configuration> |
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.
|
ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["DbConnection"]; |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
|
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.Data.SqlClient; namespace Lexicon.ActionManager.Connections { /// <summary> /// Retrieves a connection string from the app.config file or the web.config file. /// </summary> public static class ConnectonStrings { static ConnectonStrings() { } /// <summary> /// Retrieves a connection string from the app.config file or the web.config file. /// </summary> /// <returns>Connection string property of connection string settings class</returns> public static string BuildConnectionString() { // Retrieve the connection string named "DbConnection" from the app.config file or web.config file ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["DbConnection"]; return settings.ConnectionString; } /// <summary> /// Retrieves a partial connection string from the app.config file or the web.config file and build a connection string from parameters /// </summary> /// <param name="dataSource">Server name hosting data base</param> /// <param name="userName">User ID granted access to the database</param> /// <param name="userPassword">Password associated with the User ID</param> /// <returns>Connection string property of SQL Connection String Builder class</returns> public static string BuildConnectionString(string dataSource, string userName, string userPassword) { // Retrieve the connection string named "DbConnection" from the app.config file or web.config file ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["DbConnection"]; SqlConnectionStringBuilder csBuilder; if (settings != null) { csBuilder = new SqlConnectionStringBuilder(settings.ConnectionString); //supply additional values to the connection string csBuilder.DataSource = dataSource; csBuilder.UserID = userName; csBuilder.Password = userPassword; return csBuilder.ConnectionString; } else { return "Invalid connection string"; } } } } |
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
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.
|
using Lexicon.ActionManager.Connections; namespace TestEntityFrameworkModel { public class Program { static void Main(string[] args) { using (var pmContext = new ProjectManagementContext()) { pmContext.Database.Connection.ConnectionString = ConnectonStrings.BuildConnectionString(); |