Recursive .Net, IT & Tech Blog

If you tried to add connection string in code by attempting

ConfigurationManager.ConnectionStrings.Add(...)

you get an exception telling you ConnectionStrings collection is read only.

Here is a way to get past that restriction using reflection:

const string DbConnectionName = "MyConnection";			

if (null == ConfigurationManager.ConnectionStrings[DbConnectionName])
{
	lock (ConfigurationManager.ConnectionStrings)
	{
		if (null == ConfigurationManager.ConnectionStrings[DbConnectionName])
		{
			//Force ConnectionStrings collection to be writeable
			var field = typeof(ConfigurationElementCollection).GetField("bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic);
			field.SetValue(ConfigurationManager.ConnectionStrings, false);

			//Create new connection string instance
			var connection = new ConnectionStringSettings();
			connection.Name = DbConnectionName;
			connection.ConnectionString = "Server=myServer;Database=myDb;Uid=myUser;Pwd=myPass;charset=utf8;";
			connection.ProviderName = "MySql.Data.MySqlClient";

			//Add
			ConfigurationManager.ConnectionStrings.Add(connection);
		}
	}
}

In the above example DbConnectionName is a default connection that is expected to be in the ConnectionStrings, if its not there, then we create a new connection string and add it to the ConnectionStrings collection. Lock ensures that the operation is thread-safe. Example shows a MySQL connection string with MySQL provider. Change it to your specific provider and database server.

As for the question, why would anyone want to add connection string in code, when you can do it very easily in app/web.config which is standard best practice (not to mention doing it in code adds hard coded values for db)? Well, it depends to specific need. I have used the above approach in test cases when a database connection in needed while testing compatibility with different types of databases for EntityFramework (for that matter any ORM). I am sure there are many other uses as well.