Almost every desktop application deals with some kind of external memory to save data for further use. The most part of them, use a database. On the other hand an Application needs connection string to connect to the database, for handling its memory. So, how to use connection string and how to manipulate with it, that’s the question which this post wants to answer.

If we want to change the connection string during the application run-time, it is not a simple task, because the connection string is placed in to an application scope that is read only.

The reason why we need to change the connection string is the different settings and environment between users, as well as different environment between developer and end-user. So when we are planning application deployment we must have that in mind, and  provide the implementation custom modification of the connection string.

If we look files of an Application (for example on the picture above) we can see two exe file, and two corresponding configuration files. This is the .NET rule, that every exe file may have only one configuration file.

The structure of configuration file

The picture above shows content of the configuration file and the connection string data. .NET Application can access this data with the API placed in to System.Configuration namespace, by using specific XML schema developed by Microsoft for that purpose.

To access NorthwindConncetionString from the configuration file we can achieve that with the following code:

//Read connection string from configuration file
var connStringFromConfig = System.Configuration.ConfigurationManager.
ConnectionStrings["ConnStringDemo1.Properties.Settings.NorthwindConnectionString"];

If we want to change connection string, exception will throw with the following message:

So the question is how to change connection string anyway?

To change connection string we need to open configuration file with XML API functions, find the connection string and change the data manually then save changes back to file then restart the application. This is in short how to do that, the following code do exactly what we said before:

private bool ChangeConnectionString(string connStringName, string newValue)
{
 try
 {
 //CreateXDocument and load configuration file
 XDocument doc = XDocument.Load(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);

 //Find all connection strings
 var query1 = from p in doc.Descendants("connectionStrings").Descendants()
 select p;

 //Go throught each connection string elements find atribute specified by argument and replace its value with newVAlue
 foreach (var child in query1)
 {
 foreach (var atr in child.Attributes())
 {
 if (atr.Name.LocalName == "name" && atr.Value==connStringName)
 if (atr.NextAttribute != null && atr.NextAttribute.Name == "connectionString")
 atr.NextAttribute.Value = newValue;
 }
 }

 doc.Save(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);

 return true;
 }
 catch (Exception ex)
 {
 Console.WriteLine(ex.Message);
 return false;
 }
}

Change connection string for Entity Framework at run-time

As you already know the connection string for Entity Framework is little bit different than the connection string used by DataSet and Lin2SQL. In order to modify the Entity framework connection string the previous code must be modified. The following code modified the entity framework connection string.

private bool ChangeEFConnectionString(string connStringName, string newValue)
{
try
{
//CreateXDocument and load configuration file
XDocument doc = XDocument.Load(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);

//Find all connection strings
var query1 = from p in doc.Descendants("connectionStrings").Descendants()
select p;

//Go through each connection string elements find atribute specified by argument and replace its value with newVAlue
foreach (var child in query1)
{
foreach (var atr in child.Attributes())
{
if (atr.Name.LocalName == "name" && atr.Value == connStringName)
if (atr.NextAttribute != null && atr.NextAttribute.Name == "connectionString")
{
// Create the EF connection string from existing
EntityConnectionStringBuilder entityBuilder =
new EntityConnectionStringBuilder(atr.NextAttribute.Value);
//
entityBuilder.ProviderConnectionString= newValue;
//back the modified connection string to the configuration file
atr.NextAttribute.Value = entityBuilder.ToString();
}
}
}

doc.Save(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);

return true;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return false;
}
}

In the previous code we used the EntityConnectionStringBuilder which is very handy in this situation.

The source code for this post you can find on SkyDrive.