We have an ASP.NET web site that uses SQL Server session state. The state is configured in Web.config like:
<sessionState mode="SQLServer" sqlConnectionString="data source=TheServer;
User ID=TheUser;password=ThePassword;" cookieless="false" timeout="480"/>
But there are three environments (development / staging / production). All the other connection strings are configured like:
<configuration>
<connectionStrings>
<add name="Development_Db1" connectionString="..."/>
<add name="Production_Db1" connectionString="..."/>
</connectionStrings>
</configuration>
At runtime, we pick one to connect to the database based on hostname. Unfortunately, the Session State connection string appears to be hard coded in web.config.
Is there a way to configure SQL Server session state at runtime, or make it refer to a connection string from the connectionStrings section?
As it turned out, there was a fairly easy way of doing this. Session State provides a feature called Partitioning, where you can spread your state over multiple SQL Servers. You can provide a function to select the SQL Server based on the session id (SID). The trick is that you can use this feature with ONE server, just to choose the server dynamically.
The web.config configuration looks like:
<sessionState mode="SQLServer"
partitionResolverType="YourNamespace.PartitionResolver"
cookieless="false"
timeout="60" />
The function that chooses the SQL Server looks like:
public class PartitionResolver : IPartitionResolver
{
public void Initialize() {}
// The key is a SID (session identifier)
public String ResolvePartition(Object key)
{
return <grab your config here>;
}
}
This approach allowed us to continue using one web.config for both production and development.
As mentioned above, I think you should not have both dev and prod connections strings in the web.config. You can use a Web Deployment Project so solve that issue. You can use a web deployment project to replace your config settings based upon the build. For instance, you could have an two external config files called connectionStrings.dev.config and connectionStrings.prod.config. If you build in Debug, it would use the dev.config, but if you build in Release, it would use prod.config.
It's a little different from VS 08 and 10. Here are some references:
VS 2008 - http://johnnycoder.com/blog/2010/01/07/deploy-aspnet-web-applications-with-web-deployment-projects/
VS 2010 - http://www.hanselman.com/blog/WebDeploymentMadeAwesomeIfYoureUsingXCopyYoureDoingItWrong.aspx
According to this article, you can customize the session state provider:
http://www.exforsys.com/tutorials/asp.net-2.0/asp.net-2.0-customizing-the-session-state-mechanism.html
The information here could be used to design an environment-aware session state provider that could select the connection string based on a configuration in the .config file, or some other environmental key.
