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

Question

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.

Do you want to change the connection string IN the configuration file? It's easy to dynamically create a new connection with any connection string you construct... if that's what you want to do... But if you want to write to the actual config file, that's a different issue. — Charles Bretana, Dec 11 '08 at 16:50

score 118 · Accepted Answer · answered Dec 18 '08 at 11:29

118

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

answered Dec 18 '08 at 11:29

David Gardiner

16,892
20
80
117

1

If you're using NHibernate, see this http://nhforge.org/wikis/howtonh/dynamically-change-user-info-in-connection-string.aspx – David Gardiner May 04 '09 at 06:16
8

Great solution! If you want to add a new connection string, use the following to enable adding to the ConnectionString collection: `typeof(ConfigurationElementCollection).GetField("bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic).SetValue(ConfigurationManager.ConnectionStrings, false);` – Allon Guralnek Oct 03 '11 at 08:56
I just love it when SO already has my question and even better when the answer's already here as well. +1 – Bernhard Hofmann Nov 17 '11 at 14:17
12

There is something that just seems wrong about using reflection to change a property to writeable, but dang, it works! – Brian May 05 '12 at 18:28
Yeah, it works but seems like there should be a better way to do this. Agree with @rhooligan - seems wrong – Mario Jun 28 '12 at 18:49
Also, this doesn't save to the file but changes what configuration manager presents to you. As soon as your application restarts the connection string (never changed) is re-read – Mario Jun 28 '12 at 20:13
1

@BrianV - it's not wrong if it has 67 upvotes :-D - things were a lot different back in 2005 when this mechanism was introduced – Simon_Weaver Apr 05 '14 at 22:12
2

The fact this stuff is necessary is why I hate .NET. @Simon_Weaver Not wrong in the sense that it doesn't work; like... *morally* wrong. – jpmc26 Aug 13 '16 at 03:06
@jpmc26 Sitting here in 2018 I love this. Sure, it's a nasty hack, but it lets me get legacy code to work while 99% of my codebase uses Net Core config... which does away with all this unpleasantness ;) – NPSF3000 Apr 20 '18 at 21:20
1

@NPSF3000 Strictly speaking, you don't even need to use this unpleasantness at all, even in older versions, as long as you just construct your connection objects yourself and bypass all the automatic construction garbage that makes everything more complicated, more confusing, and much harder to use. But this was deemed the "normal" way of doing things by MS, and the community blindly followed as usual, resulting in third party libraries depending on it and having to support it and the like. – jpmc26 Apr 20 '18 at 22:22

score 9 · Answer 2 · answered Jul 31 '14 at 15:41

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);

This works great, but similar to the answer above by Andrew McClellan, for .Net 5 (and probably everything 2.1+), both need to be _readOnly in GetField. — Brandon Barkley, Jun 29 '21 at 20:12

score 8 · Answer 3 · edited Apr 17 '14 at 12:20

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))
}

FYI, this causes your app domain to reload, so use sparingly — yano, Apr 15 '14 at 01:22

score 7 · Answer 4 · answered Aug 21 '09 at 15:42

7

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.

answered Aug 21 '09 at 15:42

Rebecca

13,914
10
95
136

3

Be careful with changing the web.config file as it causes the workerprocess to restart. I tried to set the Connectionstring to a file based database in the ApplicationStart Event which caused an Application Restart for every visited page... – Thomas Feb 12 '10 at 16:19
Indeed. I used this as a administrative tool function to encrypt and decrypt the web.config on a shared hosting account. As Thomas has pointed out, you wouldn't want to do this as part of a Session or application start. – Rebecca Feb 14 '10 at 20:05
1

+1: This is perfect for my purposes (an integration test). However, because my production code uses `ConfigurationManager` I have to do `ConfigurationManager.RefreshSection("connectionStrings");` once I've updated the section in my test. – Alex Humphrey Jul 20 '11 at 10:46
@AlexHumphrey You shouldn't have to if you use Remove() and Add() instead of indexing and assigning. I've not verified this, but all I know is that I use Clear() and Add() and it works fine for me. – Neo Nov 08 '12 at 18:03
1

This may work under ASP.NET (I'm not really sure), but it absolutely does *not* work from any executable that doesn't auto-reload (e.g., a console app). The configuration is already loaded before this can be run, meaning the connection string is never changed. – jpmc26 Aug 13 '16 at 03:26
By defaul after web app installed in a way where web.config file does not have write permissions. So, it is unclear - how exactly do you want to call Save() method?! Because it is always reporting "access denied" error. Also, opening web app folder for writing - THAT IS VERY BAD IDEA. So, method Save() sounds like a bad solution. – dmitry_bond Mar 03 '20 at 21:05

score 5 · Answer 5 · answered Dec 11 '08 at 16:57

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.

score 2 · Answer 6 · answered Mar 19 '19 at 19:46

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

score 1 · Answer 7 · answered Dec 11 '08 at 16:50

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.

score 1 · Answer 8 · edited Apr 15 '19 at 16:57

1

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.

edited Apr 15 '19 at 16:57

jpaugh

6,634
4
38
90

answered Dec 12 '08 at 10:10

Christian.K

47,778
10
99
143

score -1 · Answer 9 · answered Dec 11 '08 at 16:55

-1

ConfigurationManager is used to read from the config file.

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

answered Dec 11 '08 at 16:55

devio

36,858
7
80
143

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

9 Answers9

Linked