Connection Strings

Referring back to my initial attempt to port the existing VB6 application from MS Access to SQL Server by changing the connection string, one of the aims of Project Sputnik is to try to make it DB independent. Before my recent trip abroad my techie nerd/guru friend and former colleague put me on to a method which I believe will help me achieve this. The application will have a separate configuration file for the connection string (than App.Config) and the DAL will have a class that reads the connection string from this file. By writing ANSI compliant SQL in the DAL concrete classes I hope to achieve the goal to be DB impartial.

The App.Config file in the recent DAL Test Harness looks like this:

<?xml version="1.0" encoding="utf-8" ?>

<configuration>
       <connectionStrings configSource="connectionstrings.config" />
</configuration>

I added a configuration file called ConnectionStrings.Config and set the file property ‘Copy to Output Directory’ to ‘Copy Always’. The content of the file looks like this:

<?xml version="1.0" encoding="utf-8" ?>

<connectionStrings>
       <add name="sql" connectionString="Server=<server>;Database=OfficeManager;User Id=<uid>;Password=<pwd>;" providerName="System.Data.SqlClient"/>
</connectionStrings>

The User ID and password have been hidden of course… The DAL has an additional class called AppSettingsReader.cs which looks like this:

using System;
using System.Configuration;

namespace DAL

{
    sealed class AppSettingsReader
    {
        public static ConnectionStringSettings GetConnectionSettings(String key)
        {
            return ConfigurationManager.ConnectionStrings[key];
        }
    }
}

For this to work there needs to be a reference to System.configuration in the project.