Set Connection String Dynamically

Introduction

The application we are developing will be installed at different locations and consequently will need to connect to a local SQL Server instance.

Rather than build separate configurations and installers for every site, we’re planning to store this information centrally. The application then uses a web service to retrieve the appropriate configuration information for its site.

The main problem that needed to be overcome is that .NET doesn’t allow you to change the connection strings after they are read from the app.config file. If you try to do it, it will throw a ConfigurationErrorsException with the message “The configuration is read only”. The following code illustrates this:

var DBCS = ConfigurationManager.ConnectionStrings[0];

DBCS.ConnectionString = "data source=srv-suchit; database=student; user id=sa; password=12345";

Code

Here is the code for change connection string Dynamically.

 

//Fetch Connection String from Web.config
// ConnectionStrings[0] == Fetch Connection String Format
var DBCS = ConfigurationManager.ConnectionStrings[0];

//Convert Readonly to Writable (In Connection String Provider is readonly so we need to change it as Writable)
//Dont forgot to Declare BelowNameSpace
//using System.Configuration;
//using System.Reflection;
var writable = typeof(ConfigurationElement).GetField("_bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic);
writable.SetValue(DBCS, false);

//Replace Connecion string
DBCS.ConnectionString = "data source=srv-suchit; database=student; user id=sa; password=12345";