How do I set a connection string config programmatically in .net?

ripper234_11236 0
Viewed 62497

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.

connection-string configuration .net
9 Answers

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";
user_257020

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.

rubyyot_20357220

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";
user_71082290

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.

Out_Into_Space_75650

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.

Christian_215670

ConfigurationManager is used to read from the config file.

Your solution is to simply set conn.ConnectionString to the conn string you need.

devio_213360
Related