Dynamic SQL Connection String : string.Format and Multiple Connectionstrings in Web.config

Common Issue:

There may be a need of dynamic connection string for a webapplication. There are two solutions I have successfully implemanted. One is String Formatting macro replacement and another one is keeping multiple connection strins in web.config file.

Tips and Tricks:

Following are two example solutions:

1. A macro replacement:

< add connectionstring="Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}" name="ClientConnection" >

Use string.Format to transform the connection string:

string connectionString = string.Format(ConfigurationManager.ConnectionStrings["ClientConnection"].ConnectionString, serverName, databaseName, userId, password);

2. Multiple connection strings, based on database name:

<add connectionstring="Data Source=DBServer1;Initial Catalog=Pepsi;Persist Security Info=True;Trusted_Connection=yes" name="Database1">

<add connectionstring="Data Source=DBServer2;Initial Catalog=Coke;Persist Security Info=True;Trusted_Connection=yes" name="Database2">

string connectionString = ConfigurationManager.ConnectionStrings[DatabaseName].ConnectionString;

Best Practce:

Always use common connection string in one place, either it in Web.config or anyother suitable place, but practice to avoid duplication.