How do I set a connection string config programmatically in .net?
.NetConfigurationConnection String.Net Problem Overview
I'd like to set a connection string programmatically, with absolutely no change to any config files / registry keys.
I have this piece of code, but unfortunately it throws an exception with "the configuration is read only".
ConfigurationManager.ConnectionStrings.Clear();
string connectionString = "Server=myserver;Port=8080;Database=my_db;...";
ConnectionStringSettings connectionStringSettings =
new ConnectionStringSettings("MyConnectionStringKey", connectionString);
ConfigurationManager.ConnectionStrings.Add(connectionStringSettings);
Edit: The problem is that I have existing code that reads the connection string from the configuration. So setting the config string manually, or through a resource, don't seem like valid options. What I really need is a way to modify the configuration programmatically.
.Net Solutions
Solution 1 - .Net
I've written about this in a post on my blog. The trick is to use reflection to poke values in as a way to get access to the non-public fields (and methods).
eg.
var settings = ConfigurationManager.ConnectionStrings[ 0 ];
var fi = typeof( ConfigurationElement ).GetField( "_bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic );
fi.SetValue(settings, false);
settings.ConnectionString = "Data Source=Something";
Solution 2 - .Net
Another way to approach this would be to operate on the collection directly:
var settings = ConfigurationManager.ConnectionStrings;
var element = typeof(ConfigurationElement).GetField("_bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic);
var collection = typeof(ConfigurationElementCollection).GetField("bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic);
element.SetValue(settings, false);
collection.SetValue(settings, false);
settings.Add(new ConnectionStringSettings("ConnectionStringName", connectionString));
// Repeat above line as necessary
collection.SetValue(settings, true);
element.SetValue(settings, true);
Solution 3 - .Net
I was looking for the answer to the same qustion about allowing the user to amend the connection string in a click once application by selecting a local SQL Server.
The code below displays a user form which contacts all the locally available SQL Servers and allows them to select one. It then constructs a connection string for that sever and returns it from a variable on the form. The code then amends the config files AND SAVES IT.
string NewConnection = "";
// get the user to supply connection details
frmSetSQLConnection frm = new frmSetSQLConnection();
frm.ShowDialog();
if (frm.DialogResult == DialogResult.OK)
{
// here we set the users connection string for the database
// Get the application configuration file.
System.Configuration.Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
// Get the connection strings section.
ConnectionStringsSection csSection = config.ConnectionStrings;
foreach (ConnectionStringSettings connection3 in csSection.ConnectionStrings)
{
// Here we check for the preset string - this could be done by item no as well
if (connection3.ConnectionString == "Data Source=SQL204\\SQL2008;Initial Catalog=Transition;Integrated Security=True")
{
// amend the details and save
connection3.ConnectionString = frm.Connection;
NewConnection = frm.Connection;
break;
}
}
config.Save(ConfigurationSaveMode.Modified);
// reload the config file so the new values are available
ConfigurationManager.RefreshSection(csSection.SectionInformation.Name);
return clsDBMaintenance.UpdateDatabase(NewConnection))
}
Solution 4 - .Net
I find that this works for me:
Configuration config = WebConfigurationManager.OpenWebConfiguration("~");
ConnectionStringsSection section = config.GetSection("connectionStrings") as ConnectionStringsSection;
if (section != null)
{
section.ConnectionStrings["MyConnectionString"].ConnectionString = connectionString;
config.Save();
}
This overwrites an existing connection string.
Solution 5 - .Net
I'm currently using dependency injection to handle different connection strings in dev/prod vs. test environments. I still have to manually change the webconfig if I want to move to between dev and prod, but for testing I have an IConnectionStringFactory interface with a default implementation that looks at the web config and an alternate testing configuration that returns static values. That way when I'm testing I simply set the factory to the testing implementation and it will return the testing connection string for the key I ask for. Otherwise it will look in the webconfig.
I could extend this to another implementation for dev vs. prod but I'm more comfortable having a single implementation of IConnectionStringFactory in my production assembly and the testing implementation in my testing assembly.
Solution 6 - .Net
You could put it in a resources file instead. It won't have the built-in features of the ConfigurationManager class, but it will work.
Assuming Resources.resx:
Resources.Default.ConnectionString = "Server=myserver;" // etc
Then in your code:
conn.ConnectionString = Resources.Default.ConnectionString
It's a hack, I know.
Solution 7 - .Net
In addition to the other answers given, and assuming the connection string is not simply another configuration variable or constant as a whole, you might consider using SqlConnectionStringBuilder class instead of directly concatenating the string together.
EDIT: Ups, sorry just saw that you basically want to read your connection string (complete I guess) from another source.
Solution 8 - .Net
Looks like the naming was changed as of .net Core 2.1 Modifying David Gardiner's answer This way should work for referencing new and old versions:
var settings = ConfigurationManager.ConnectionStrings[ 0 ];
var fi = typeof( ConfigurationElement ).GetField( "_bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic );
if(fi == null)
{
fi = typeof(System.Configuration.ConfigurationElementCollection).GetField("_readOnly", BindingFlags.Instance | BindingFlags.NonPublic);
}
fi.SetValue(settings, false);
settings.ConnectionString = "Data Source=Something";
Solution 9 - .Net
ConfigurationManager is used to read from the config file.
Your solution is to simply set conn.ConnectionString to the conn string you need.