Dynamically determine session connection string by environment

Question

I'm comfortable with determining connection strings by environmental variable in general, but the connection string that's inherent in the session state section of the web.config file is throwing us for a loop.

We want to dynamically determine the connection string that points to the SQL State Server based on an environmental variable. We've tried changing it in Application_Start: no dice. We have tried using a custom config section and pointing to that: no dice (maybe that's a PEBCAK issue? Not sure). And it won't switch the value at runtime; just shows the old value.

Anyone know a way around this that doesn't involve multiple web.config files? We've been trying to avoid that, but that's the fallback position if nothing else works.

Thanks!

Answer 1

If you can, place the connection string in the machine.config and have it change in the different environments. That way you don't have to worry about messing with the Web.configs.

Answer 2

Use RefreshSection to refresh the connectionStrings section each time before you load the value in your Session_start. (Note I said Session_Start, not Application_Start, this way you won't have to restart the application when you make a change to the connection string).