ASP.NET applications traditionally store database connection details in the web.config file. Once configured, the entire web application can easily utilize these settings to connect to the database.
However, Sitefinity 4 does not adhere to this traditional convention. By default, Sitefinity stores database connection details in ~/App_Data/Sitefinity/Configurations/DataConfig.config. This was done to support medium-trust environments. In these environments, Sitefinity would not have access to modify the web.config file. However, circumventing the web.config makes it more difficult to manage different environments.
Development, Staging, Production…oh my!!!
During development it’s common to have different environments for various phases of the project. Development might be done on a developer’s personal machine, testing on a staging server and then deployment onto a production server. Each of these environments might utilize different application settings and different databases.
Visual Studio 2010 comes with a handy web.config transformation feature that makes it relatively painless to switch between these different configuration settings:
However, this feature is not applicable to the DataConfig.config that Sitefinity uses by default. Consequently, customers who are juggling different development & database environments with Sitefinity need to manually edit the DataConfig.config –OR- write a custom post build script.
Make Sitefinity use the web.config for the database connection string
Thankfully Sitefinity’s default behavior can be modified and traditional web.config database configurations utilized within Sitefinity. To force Sitefinity to use the web.config’s database connectionstring, remove the connection string from the DataConfig.config and relocate it to the web.config.
1. Remove the connectionString from the DataConfig.config
2. And relocate it to the <configuration> section in the web.config
That’s it! Sitefinity will now use the web.config file for the database connectionString and Visual Studio 2010’s web.config transformation technique can now be used with Sitefinity.
More insights from Sitefinity’s Team…
The procedure outlined above is the easiest to follow & document. However, while discussing this topic with the Sitefinity team, several other details and tactics began to emerge.
How does Sitefinity resolve conflicts between the web.config and the DataConfig.config?
“The connection strings from Web.config and DataConfig.config are merged and they all can be used by data providers. If there are connection strings with identical names in both configuration files the one form DataConfig.config is used.”
Unrecognized attribute 'dbType'. Note that attribute names are case-sensitive.
This is an error I received while following the instructions above. To fix this error, I simply removed the dbType attribute from the web.config. Here is what the team said about this error and my fix:
“In this case, the information which is specific for Sitefinity will be lost. For example, the dbType attribute specifies the Backend configuration, which will be used for OpenAccess mapping. By default it is MSSQL, so this approach could work correctly only for MS SQL. Since we support different databases now, we should probably improve this implementation. For the time being in the blog post we can mention that this solution only works for MSSQL. However, in the future, if the connection string is in the web.config, we could try to get the information about the dbType from the connectionString attribute.”
Applying transformations to the DataConfig.config
Visual Studio’s transformation feature can also be applied to other files (not just the web.config). This means that multiple DataConfig.config files could be maintained for different environments. However, this feature requires that you hack your project. A blog post, detailing this technique, can be found here. A few of our Sitefinity projects, internally, are using this technique.
That’s it! Hopefully this blog post will make it easier for anyone who is managing multiple environments for their Sitefinity project. For more information about Web.config transformations, check out this great tutorial.