Taking variables out of the web.config to assign dynamically

Question

I'm using sql dependent caching in my ASP.NET application, and to achieve this I have the following entry in the web.config:

<connectionStrings>
  <add name="DatabaseName" connectionString="Data Source=.\Dev;Initial Catalog=DatabaseName;Integrated Security=True" />
</connectionStrings>

<system.web>
  <caching>
    <sqlCacheDependency enabled="true">
      <databases>
        <add name="DatabaseName" connectionStringName="DatabaseName"/>
      </databases>
    </sqlCacheDependency>
  </caching>
</system.web>

In the application, a SqlCacheDependency is created using the name of the database that is specified in the web.config, like this:

var tableDependency = new SqlCacheDependency("DatabaseName", "TableName");

What I want to achieve is removing the need for the connection string in the web.config, as this application's connection string is likely to change when placed onto different servers.

There is already a connection string class in the application that returns the correct string for what server it is currently on, so without using that, I will have to manually change the connection string in the web.config on each server.

Thanks in advance,

Greg.

Answer 1

As far as I can tell, the "code behind" of sqlCacheDependency is taking the connection string directly from the config file, so it must be there.

Leave the connectionstrings section empty in the web.config then use the code from this question to programmatically add the proper connection string.

You need to put it in a place that is called before any other code, maybe even Handler or Module, otherwise the sqlCacheDependency might not "catch" the proper value. Maybe the Application_Start of global.asax will be enough.

Answer 2

I believe you can set up cache dependencies programmatically using the SqlCacheDependencyAdmin class so you don't have to rely on SqlDependency to read the settings from the web.config file.