Wednesday, November 30, 2022
Code Search
Code Bank
.NET Applications
XSD Schema Generator
.NET/XML Training


Manage Development and Production Database Connection Strings with XML Serialization

Database connection strings are an important part of any data-enabled application. Because developers typically have development, staging, and production connection strings to manage (depending upon the environment they are working in), many different strategies have been created to handle switching from development to production. One popular way to manage this task is to have multiple web.config files and simply name them differently. For example, while in development the production web.config file may be named When code has been tested in development and needs to be deployed to staging/test, the staging web.config file can be renamed to "web.config" and then moved over to the production Web Server(s). While this works well it involves an unnecessary step that should be more automated to avoid human error and involves keeping data in-sync between two or more web.config files. Using VS.NET build events is another popular way to handle this problem.

When large numbers of developers are involved in creating different applications that have separate web.config files for each application that may hit the same databases, a management issue comes into play. If the database connection strings change at all (for instance moving from passing username/password credentials to integrated security) then all web.config files with the appropriate connection strings in them must be updated. A more centralized way to store connection strings needs to be available.

This example code demonstrates how to avoid renaming web.config files when applications are moved to production while providing a centralized storage location for database connection strings as well as other server information such as proxy servers and mail servers. The code relies upon XML serialization to load data from an XML file named ServerConfig.config into an object-model, detect the environment automatically based upon data in the ServerConfig.config file, and return the appropriate connection string for the environment (development, staging, production, etc.). It also allows primary and secondary connection strings to be accessed in cases where a secondary production database server is available to use when the primary is too busy or down. An example of the Server.Config file used in the downloadable sample code is shown below:

<ServerConfig> <Servers> <Server Name="DevelopmentServerName" Type="Web" Environment="Development" UserName="" Password="" Domain="" IP=""> <Domains> <Domain>localhost</Domain> </Domains> </Server> <Server Name="StagingServerName" Type="Web" Environment="Staging" UserName="" Password="" Domain="" IP=""> <Domains> <Domain>staging.server.domain</Domain> </Domains> </Server> <Server Name="ProductionServerName" Type="Web" Environment="Production" UserName="" Password="" Domain="" IP=""> <Domains> <Domain></Domain> <Domain></Domain> </Domains> </Server> <Server Name="ProxyServerName" Type="Proxy" Environment="Production" UserName="" Password="" Domain="" IP=""> <Domains> <Domain>your.proxy.server</Domain> </Domains> </Server> <Server Name="SMTPServerName" Type="Mail" Environment="Production" UserName="" Password="" Domain="" IP=""> <Domains> <Domain>your.smtp.server</Domain> </Domains> </Server> </Servers> <ConnectionStrings> <ConnectionString Database="Northwind"> <Primary>server=prodServerName;Integrated Security=SSPI; database=Northwind </Primary> <Secondary>server=secondaryProdServerName; Integrated Security=SSPI;database=Northwind </Secondary> <Development>server=localhost;Integrated Security=SSPI; database=Northwind </Development> <Staging>server=stageServer;Integrated Security=SSPI; database=Northwind </Staging> </ConnectionString> <ConnectionString Database="Pubs"> <Primary>server=prodServerName;Integrated Security=SSPI; database=Pubs </Primary> <Secondary>server=secondaryProdServerName; Integrated Security=SSPI;database=Pubs </Secondary> <Development>server=localhost;Integrated Security=SSPI; database=Pubs </Development> <Staging>server=stageServer;Integrated Security=SSPI; database=Pubs </Staging> </ConnectionString> </ConnectionStrings> </ServerConfig>

A sample of accessing the Northwind database connection string from the data layer is shown below:

public static SqlDataReader GetCustomers() { ConnectionString cs = ServerConfigManager.GetConnectionString("Northwind"); //Use sproc if possible (and avoid SELECT * of course...just a demo) string sql = "SELECT * FROM Customers"; try { //Hit primary database SqlConnection conn = new SqlConnection(cs.Primary); SqlCommand cmd = new SqlCommand(sql,conn); conn.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception exp) { //Log error try { //Hit secondary database if needed SqlConnection conn2 = new SqlConnection(cs.Secondary); SqlCommand cmd = new SqlCommand(sql,conn2); conn2.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch { //Log error } } return null; }

This code works in development, staging and production since the appropriate connection string to load is dynamically discovered. No code changes or file changes need to be made when the code is moved to production. All of the ServerConfig.config functionality is accessible via an object named ServerConfigManager which has several static methods such as GetConnectionString(string database), GetServerEnvironment(), GetServer() and GetServerConfig(). While the code does not currently encrypt/decrypt the connection strings, that functionality could certainly be added.

Teaches XmlSerializer
Requirements .NET 2.0
Download Code: Click Here


.NET, SharePoint and Silverlight Training Solutions
    Online, Onsite and Video Training on .NET and SharePoint technologies available through The Wahlin Group.

© 2007 Wahlin Consulting LLC
XML for ASP.NET Developers in bookstores