XtremeData
LANGUAGES: C#
ASP.NET VERSIONS: 1.x
Stay Connected
Simplify Database Connection String Management
By Dan Wahlin
Managing database connection strings can be a challenge for any development team working in an environment where multiple connection strings are shared across a variety of applications. Many different options have been employed to handle this issue, including using multiple copies of web.config each with their own set of connection strings, simple renaming of connection strings during deployment, and even custom solutions that employ external configuration files.
Although each of these options gets the job done, they re prone to error and can cause unexpected issues as human error can come into play when moving connection strings between different environments. After all, if a connection string pointing to a development or test database isn t modified before deployment to production, the application doesn t function properly. If you have multiple web.config files scattered across many applications, servers, and environments, you know how difficult it can be to ensure all connection strings are pointing to the proper database.
In this article I ll demonstrate a custom solution I ve been using with great success that simplifies the process of managing database connection strings. The solution relies on XML configuration files along with the XmlSerializer class, and requires no connection string modifications when deploying applications from development to test to production. The solution allows all connection strings used on a Web server to be stored in a central location, which greatly simplifies maintenance and has the added benefit of allowing updates to occur without affecting web.config. The solution also handles storing configuration data for various server types, including Web servers, SMTP servers, and Proxy servers.
Associating Database Connection Strings with Web Domains
Web server domains are typically associated with a specific environment (development, test, production, etc.). For example, a domain such as hrdev.site.com used for developing HR applications will connect to a development database. Any other servers used for development will also connect to development database servers. Test domains will likely access test databases. Production domains such as hr.site.com or www.site.com will of course hit production databases.
I m stating the obvious here, but the point is that this pattern can be leveraged to allow development, test, and production connection strings to be stored in the same file and dynamically loaded based on the environment the application is running within. Figure 1 shows an example of creating an XML configuration file named ServerConfig.config that stores Web server and database configuration data. Test Web server configuration data normally used at the quality assurance stage of deployment has been omitted for the sake of brevity.
Environment="Development">
Environment="Production">
server=ProdServerName;Integrated
Security=SSPI;database=Northwind
server=SecProdServerName;Integrated
Security=SSPI;database=Northwind
server=TestServerName;Integrated
Security=SSPI;database=Northwind
server=localhost;Integrated
Security=SSPI;database=Northwind
server=ProdServerName;Integrated
Security=SSPI;database=Pubs
server=SecProdServerName;Integrated
Security=SSPI;database=Pubs
server=TestServerName;Integrated
Security=SSPI;database=Pubs
server=localhost;Integrated
Security=SSPI;database=Pubs
Figure 1: Storing server and database connection string information in ServerConfig.config.
Looking at Figure 1 you can see that it contains
configuration data for two Web servers, as well as two databases. Each
Notice that Web servers aren t directly associated with the database connection strings in Figure 1. This is by design; it allows for more flexibility when multiple servers may leverage the same connection string. The association between server and database connection string is made based on the domain and the environment in which the application is running. For example, if an application requires the Northwind database and is accessed using a URL of http://localhost/app1, the connection string can be obtained by first locating the server that supports the localhost domain (DevServerName in Figure 1). Once this server is found the environment it supports (Development, Test, or Production) can be obtained and used to grab the appropriate connection string for the Northwind database.
Doing this server to database association manually wouldn t do much good, so what can be done to automate the process? Several techniques can be used, ranging from forward-only parsing of the XML configuration document to employing XPath queries (to name only two). However, I chose to rely on XML serialization so that developers could leverage an object model rather than having to know anything about XML.
Using the XmlSerializer Class
The XmlSerializer class (located in the System.Xml.Serialization namespace) provides an excellent mechanism for working with XML without having to know XML specifics. With a bit of code it can automatically serialize objects to XML and deserialize XML back into objects. This is done using its Serialize and Deserialize methods. The database connection string management application discussed here relies on a class named ServerConfigManager that wraps XmlSerializer functionality. ServerConfigManager handles deserializing XML into objects and provides several methods, such as GetDatabase and GetServer, plus others that allow quick and easy access to the required configuration data. I ll provide more details on this class a little later in the article.
To simplify the process of converting the configuration file shown in Figure 1 to objects, I used a command line tool named xsd.exe that ships with the .NET SDK. This tool is best known for its ability to generate strongly typed DataSets, but it can also be used to generate standard C# or VB.NET classes. To generate the object model used by the ServerConfigManager class, I created an XSD schema that defined the structure and data types in the XML configuration file (you ll find this schema in the accompanying downloadable code; see end of article for details). Figure 2 shows a diagram of the schema.
Figure 2: ServerConfig.xsd schema
viewed in Visual Studio.NET 2003.
Converting the ServerConfig.xsd file shown in Figure 2 to C# classes and enumerations is accomplished by using the following command line syntax (to generate VB.NET classes you can add the /language:vb switch):
xsd.exe /classes /namespace:XmlForAsp.Configuration
ServerConfig.xsd
The file generated after running this syntax is named ServerConfig.cs.
The ServerConfigManager class mentioned earlier is responsible for deserializing the XML configuration data into objects defined in the ServerConfig.cs file. ServerConfigManager has a method named GetServerConfig that creates a new XmlSerializer instance, calls its Deserialize method, and returns a new ServerConfig object loaded with configuration data. Figure 3 shows how the XmlSerializer is used to deserialize the configuration file into objects within the GetServerConfig method.
public static ServerConfig GetServerConfig() {
HttpContext context = HttpContext.Current;
XmlTextReader reader = null;
try {
string configPath =
ConfigurationSettings.AppSettings["ServerConfigPath"];
if (configPath != null) {
string fullConfigPath = null;
if (configPath.IndexOf(":\\") == -1 &&
configPath.IndexOf(":/") == -1 &&
!configPath.StartsWith("\\")) {
fullConfigPath =
HttpContext.Current.Server.MapPath(configPath);
} else { //Physical path specified
fullConfigPath = configPath;
}
//Handle cache
ServerConfig sc =
(ServerConfig)context.Cache.Get(fullConfigPath);
if (sc == null) {
reader = new XmlTextReader(fullConfigPath);
XmlSerializer serializer =
new XmlSerializer(typeof(ServerConfig));
sc = (ServerConfig)serializer.Deserialize(reader);
if (sc != null) {
CacheDependency dep =
new CacheDependency(new string[]{fullConfigPath});
context.Cache.Insert(fullConfigPath,sc,dep);
}
}
return sc;
} else {
return null;
}
}
catch (Exception exp) {
//Log exception here if desired
context.Trace.Warn("Error reading XML: " + exp.Message);
}
finally {
if (reader != null) reader.Close();
}
return null;
}
Figure 3: Using the XmlSerializer class in ServerConfigManager to deserialize XML into objects.
The code shown in Figure 3 starts by loading the path to the ServerConfig.config file from the application s web.config file (or machine.config in cases where you d like to store the ServerConfigPath key in one central place on a server). Once the path is found, it is used to check the ASP.NET cache to see if the configuration data has already been loaded and stored in memory. If a cache entry isn t found, an XmlTextReader object is created to parse the XML configuration data and an XmlSerializer object is created to handle deserializing the XML data to objects.
Figure 4 shows how data within the ServerConfig container object can be accessed using a ServerConfigManager method named GetDatabase, which returns database-specific connection string information.
public static Database GetDatabase(string dbName) {
ServerConfig config = GetServerConfig();
if (config != null) {
//See Listing 5 for the GetServerEnvironment() method
ServerEnvironment env = GetServerEnvironment(config);
if (config.Databases != null) {
foreach (Database db in config.Databases) {
if (db.Name.ToLower() == dbName.ToLower()) {
switch (env) {
case ServerEnvironment.Development:
Database devDB = new Database();
devDB.Name = dbName;
devDB.ConnectionStrings = new ConnectionStrings();
devDB.ConnectionStrings.Primary =
db.ConnectionStrings.Development;
devDB.ConnectionStrings.Secondary =
db.ConnectionStrings.Development;
devDB.ConnectionStrings.Test =
db.ConnectionStrings.Development;
devDB.ConnectionStrings.Development =
db.ConnectionStrings.Development;
return devDB;
case ServerEnvironment.Test:
Database testDB = new Database();
testDB.Name = dbName;
testDB.ConnectionStrings = new ConnectionStrings();
testDB.ConnectionStrings.Primary =
db.ConnectionStrings.Test;
testDB.ConnectionStrings.Secondary =
db.ConnectionStrings.Test;
testDB.ConnectionStrings.Test =
db.ConnectionStrings.Test;
testDB.ConnectionStrings.Development =
db.ConnectionStrings.Test;
return testDB;
case ServerEnvironment.Production:
return db;
}
}
}
}
}
return new Database();
}
Figure 4: Accessing database connection strings stored in the ServerConfig object.
GetDatabase starts by calling the GetServerConfig method (shown earlier in Figure 3). It then calls GetServerEnvironment to dynamically discover if the application is running in the context of a development, test, or production environment (see Figure 5). The environment is determined by searching for the value returned by Request.Url.Host in the collection of Web domains each server defines in the XML configuration file that is deserialized into memory. If the target domain is found to be supported by a server, the environment defined for the server is returned (Development, Test, or Production). If the Web domain isn t found in the configuration data, GetServerEnvironment returns ServerEnvironment.Development.
public static ServerEnvironment GetServerEnvironment(
ServerConfig config) {
ServerConfig config = (
config==null)?GetServerConfig():config;
string hostName =
System.Web.HttpContext.Current.Request.Url.Host;
if (config != null) {
if (config.Servers != null) {
foreach (Server s in config.Servers) {
if (s.Domains != null) {
foreach (string domain in s.Domains) {
if (domain.ToLower() == hostName.ToLower()) {
return s.Environment;
}
}
}
}
}
}
//Default to development
return ServerEnvironment.Development;
}
Figure 5: The GetServerEnvironment method determines if an application is running in a development, test, or production environment.
Once the server environment is known, each Database object within ServerConfig is iterated through in order to locate the desired database. Once this database is found, the connection strings it defines are wrapped within a Database object and returned from GetDatabase. All of this may seem like a lot of work, but keep in mind the configuration data is cached as objects in memory and accessed directly, which results in very good performance.
Using the ServerConfigManager Class in an ASP.NET Application
Putting the ServerConfigManager class to work in ASP.NET applications is extremely straightforward. To access a specific database s connection string information you simply need to call the GetDatabase method and pass in the name of the database. This method will return a Database object (as shown in Figure 4) loaded with the connection strings appropriate for the environment where the ASP.NET application is running.
Figure 6 shows how to call the Northwind database and access the Primary connection string. Note that when running in a development environment, the Primary connection string will contain the development connection string. However, when moved to production, Primary will automatically contain the primary production connection string which means no code or configuration data has to be changed when deploying applications between environments.
public static SqlDataReader GetCustomers() {
Database db = ServerConfigManager.GetDatabase(
"Northwind");
SqlConnection conn = null;
//Recommend using stored procedures here
//instead of inline SQL
string sql = "SELECT * FROM Customers";
try { //Hit primary database
conn = new SqlConnection(db.ConnectionStrings.Primary);
SqlCommand cmd = new SqlCommand(sql,conn);
conn.Open();
return cmd.ExecuteReader(
CommandBehavior.CloseConnection);
}
catch (Exception exp) {
HttpContext.Current.Trace.Write(exp.Message);
//Log error if desired
}
return null;
}
Figure 6: Using the ServerConfigManager class to access database connection strings.
Conclusion
Although the web.config file provides a simple way to store database connection strings, as the number of applications and servers supported by a company grows, this technique can lead to potential issues as applications are deployed from development to test and production environments. These issues are typically caused by human error.
In this article you ve seen one potential solution that allows connection strings to be stored in a central location that can be accessed by multiple applications. The solution relies on the XmlSerializer class, which handles deserializing XML configuration data to .NET objects and dynamically loads development, test, and production connection strings based on what environment an application is running in. All the code needed to use the ServerConfigManager class (and supporting classes) is included with this article s downloadable code, so you can certainly enhance the framework to perform other features, such as encryption and decryption of connection strings.
The sample code accompanying this article is available for download.
Dan Wahlin (Microsoft Most Valuable Professional for ASP.NET and XML Web Services) is the president of Wahlin Consulting and founded the XML for ASP.NET Developers Web site (http://www.XMLforASP.NET), which focuses on using XML, ADO.NET, and Web services in Microsoft s .NET platform. He s also a corporate trainer and speaker, and teaches XML and .NET training courses around the US. Dan coauthored Professional Windows DNA (Wrox), ASP.NET: Tips, Tutorials, and Code (Sams), ASP.NET 1.1 Insider Solutions, and authored XML for ASP.NET Developers (Sams).
