Tuesday, 23 September 2008

Programmatically setting a connectionString property

The application we are developing will be installed at different locations and consequently will need to connect to a local SQL Server instance.

Rather than build separate configurations and installers for every site, we’re planning to store this information centrally. The application then uses a web service to retrieve the appropriate configuration information for its site.

As we are using NHibernate/ActiveRecord with the database configuration being loaded though the Castle ActiveRecord Integration Facility, the database name is indicated through naming a connection string (by specifying the config key ‘hibernate.connection.connection_string_name’). The difficulty is that this appears to be loaded the first time Windsor loads the config file. What we need is a way of replacing the connection string that was read from the app.config file with one that we’ve retrieved from the web service.

The main problem that needed to be overcome is that .NET doesn’t allow you to change the connection strings after they are read from the app.config file. If you try to do it, it will throw a ConfigurationErrorsException with the message “The configuration is read only”. The following code illustrates this:

var settings = ConfigurationManager.ConnectionStrings[0];
settings.ConnectionString = "blah";

Steve Michelotti describes how you can override ConnectionStringsSection’s IsReadOnly method, which would be fine for custom configuration sections but doesn’t work in this case as the class is sealed.

Dmitry suggests another approach, though at the time it was just an untested idea. ConnectionStringSection inherits from the abstract class ConfigurationElement. Using Reflector you can see that the default implementation of IsReadOnly just returns the value of a private field _bReadOnly, which is set to true.

We use reflection to locate the private field _bReadOnly and then force it to be false. 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";

This is a bit of a hack and obviously would fail should Microsoft choose to change the name of this private field in the future, but as long as we do this before the container loads the configuration, it means that in the example above, any reference to the first connection string will return our new value.

An alternate approach might be to implement a custom NHibernate configuration provider, but I’ll leave that exercise to the reader :-)


Anonymous said...

Just the type of solution I was looking for :-) I've had a hard time finding this... lots of posts about changing connection strings in the web.config file but few about changing them without updating the web.config.

Thanks for writing it up.


psyafter said...

Fine solution.

I try do the same with whole ConnectionStringCollection but this private field not changed to false.

var fi = typeof(ConfigurationElement).GetField("_bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic);

//var fi = typeof(ConfigurationElementCollection).GetField("_bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic);

fi.SetValue(System.Configuration.ConfigurationManager.ConnectionStrings, false);

What can be a problem?


Brian Rogers said...

Here's what I did. This post inspired me.

Public Shared Sub AddConnectionStringFromRegistry()
Dim csSettings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("MyDBConnectionString")
If csSettings IsNot Nothing Then Return

csSettings = New ConnectionStringSettings("MyDBConnectionString", GetMyDBConnectionString(), "System.Data.SqlClient")

Dim cssc As ConnectionStringSettingsCollection = WebConfigurationManager.ConnectionStrings
Dim t As Type = cssc.GetType().BaseType ' System.Configuration.ConfigurationElementCollection
Dim fi As FieldInfo = t.GetField("bReadOnly", BindingFlags.Instance Or BindingFlags.NonPublic)
fi.SetValue(cssc, False)

End Sub

Will McKee said...

David please email me I have a question re: this post but it's too long to post here as a comment.

elector said...

Great work! Thanks a lot for this!

Anonymous said...

hi David Gardiner ,

Thanks! It does work like a charm! Has been looking for a solution on this a long time from forum to forum!

I hate when a hosting provider staff can see easily the credential of connection string in web.config

I dont implement RSA encryption because hosting provider staff can still see the consString credential since we get them install the RSA key in their server...

I love this code > fi.SetValue(cssc, False)

Once again, Thanks!

Team Serrins Springfield said...

First, thanks so much for this solution. I have been using it and it works great. Or it has until the last week or so.

For some reason, in our test environment, it seems as if the app can no longer access the settings in the web.config file. It still works fine in my development environment and in the production environment but not in the test environment.

Do you have any thoughts on a setting which might have gotten changed or a permission I need to grant?

Thanks so much.

David Gardiner said...

Only thing I can think of is whether there might be an internal change in .NET 4.5 and if you had installed 4.5 on your test environment.

Otherwise I don't know why this wouldn't work any more.

Zendev said...

the Microsoft Practice Enterprise Library 5.0 does not use anymore the "after-startup" modified configuration elements, so this solution does not fit anymore when using DAAB Microsoft Practice Enterprise Library layer.

Newbie said...

I'm a newbie, so I confused in which part does this changes can be placed?

* Is the datasource part in connectionstrings in web.config not filled? or filled with wrong username and password?

I have tried to put the code in Application_start in Global.asax, but still, seems the connection to the database takes place before that part is invoked.
For your info: In my application I force everybody that want to access the application to login first.

Thanks in advance for the help

Anonymous said...

Thanks, this worked like a charm!

Rikin Patel said...

Wow what a awesome solution.....

Thanks a lot....

Peter R said...

This works fine, but when I restart the application, changes are lost. Is there any way to save them?

David Engelkemier said...

I've been looking all over the place for this. Really helpful!!!

Unknown said...

No need to hack = )


Unknown said...

About my previos post...
After 5 hours i can't make it works, but your code is work perfectly, so...
Much thanks to you! = )
p.s. god damit magic world of coding = (