Dynamic Connecton String Based on Session Variable

Question

Hey all,

I have an application that does projection of data for my client over a length of time. In order to allow the client to 'play' w/ other scenarios w/o messing up production data, they are able to choose alternative databases (basically What-If scenarios) at any point, which are a copy of the production database as of a given time. Currently, I have bit of code in place that basically does a string replacement on the connection string, which will hot-swap the server name and database name, based on the user's selection stored in the session state.

This approach works well for what I am doing, although it does require me to pass around those selections throughout every layer. Here is my question: I would like to be able to create a custom connection string that would handle this 'switching' in the background and allow me to use a connection string just like I do in a standard Asp.Net application.

In other words, many asp.net tools accept just a connection string name as part of their constructor. I would like to be able to utilize those tools, but cannot do so b/c of the way I currently build my connection string. Is there a way to support this behavior on a user session level?

Answer 1

It seems as if you could just name your connection strings significantly, then pass around that significant name: Production, Test, Etc.

Your web.config could have all the connection strings in it, then you just retrieve the connection string based on the name.

<add name="Production" connectionString="Database=Widgets;Server=SQL-PROD;Trusted_Connection=yes" />
<add name="Test" connectionString="Database=AltWidgets;Server=SQL-TEST;Trusted_Connection=yes" />

When you make your connection, just pass it:

SqlConnection conn = new conn(ConfigurationManager.ConnectionStrings[Session["SelectedDB"]].ToString());

Answer 2

You could create a custom object, override the ToString method, and pass myObject.ToString() in as the parameter for those objects, eg.

class CustomObject
{
    override public string ToString()
    {
         // In here would go whatever logic you use to decide 
         // which conn string should be returned
         return "Data Source=server; User ID=user; Password=password;" + 
                   "Initial Catalog=" + Session["DBName"];
    }
}

Then to use it:

CustomObject co = new CustomObject();
SqlConnection conn = new SqlConnection(co.ToString());

Answer 3

I think System.Data.SqlClient.SqlConnectionStringBuilder will be perfect for your needs.

Answer 4

There's a number of different ways of dealing with this.

Some people have complete separate copies of their web.config files (where the connection string(s) should be kept) and using post-build events, they can copy a relevant web.config file based upon the "release configuration" you're using in Visual Studio.

This method has been blogged about by Scott Gu, and also others.
Also, see this StackOverflow question:
How do you deal with connection strings when deploying an ASP.NET site?

Now, the above is are great methods when initially deploying your ASP.NET application, however, if you want to do this when the application is already deployed, you can use this handy mechanism: ConnectionString Trick

It does sound like you're doing something very similar to this already, and if you want to be able to dynamically switch databases at runtime, I think the best you can do is to just "switch" connection strings within your code. Of course, the trick here is to simplify this so that only one place in your application is responsible for providing a database connection string to the rest of your application.

Probably the best way to handle this is to ensure that all values you wish to retrieve from the web.config are retrieved using a class that derives from the System.Configuration.ConfigurationSection class. This will allow you to encapsulate any logic (such as the "connection string-based-on-machine-name" trick above) required to "switch" the actual value returned in one place, and allow you to use your custom class's "connectionstring" property in any other component's constructor that requires a connectionstring as a parameter, without having to change this when the actual connection string value changes.

Some good resources for custom Configuration Sections are:

How to: Create Custom Configuration Sections Using ConfigurationSection
Creating Custom Configuration Sections in Web.config Using .NET 2.0's Configuration API
Custom Configuration Sections in 3 Easy Steps