Friday, July 18, 2008

.Net (2.0) Connection String Manager: Find and Replace Connection Strings Across an Entire System

I am working on project composed of a couple WinForms applications, a series of console applications, two Windows services, and a web site.  Each system component talks to the same application database plus the web site talks to an ASPState database.  As I am wrapping this project up, I need to setup the system to work against different configurations (essentially a development server, a staging server, and a production environment). 

I needed a simple way to sweep across the entire system and modify connection string information, plus change the ASPState database to point to the correct server, depending on configuration.  I haven't written any code for a while because I am working primarily on documentation these days... I got a bit of a coding itch this morning.   So, I spent a few hours writing a tool that does a smart find and replace of connection string information across the entire system.

image

Here is the code that handles setting the connection strings in a Windows executable (in some cases, there are up to TEN connection strings because I am overriding connection string information in other assemblies--see an early post about that problem).

   1: private void RepaceInAppConfig(string configFile)
   2: {
   3:     Configuration configuration = ConfigurationManager.OpenExeConfiguration(configFile);
   4:  
   5:     ConnectionStringsSection configSection = 
   6:                             (ConnectionStringsSection) configuration.GetSection("connectionStrings");
   7:  
   8:     for (int i = configSection.ConnectionStrings.Count - 1; i >= 0; i--)
   9:     {
  10:         ConnectionStringSettings connection = new ConnectionStringSettings(configSection.ConnectionStrings[i].Name, connectionStringTextBox.Text)
  11:                                               {
  12:                                                   ProviderName = configSection.ConnectionStrings[i].ProviderName
  13:                                               };
  14:  
  15:         configSection.ConnectionStrings.RemoveAt(i);
  16:  
  17:         configSection.ConnectionStrings.Add(connection);
  18:     }
  19:  
  20:     configuration.Save(ConfigurationSaveMode.Minimal, true);
  21: }
  22: v

Here is the code that handles replacing the connection strings in a web.config.  Keep in mind that I cannot use the ConfigurationManager stuff here because I am not opening the web.config from the site--the OpenWebConfiguration requires a virtual path...

   1: private void ReplaceInWebConfig(string configFile)
   2: {
   3:     XmlDocument xmlDocument = new XmlDocument();
   4:  
   5:     xmlDocument.Load(configFile);
   6:  
   7:     XmlNamespaceManager xmlNamespaceManager = new XmlNamespaceManager(xmlDocument.NameTable);
   8:     xmlNamespaceManager.AddNamespace("ns", "http://schemas.microsoft.com/.NetConfiguration/v2.0");
   9:  
  10:     if (defaultStringsCheckBox.Checked)
  11:     {
  12:         ReplaceWebConfigConnectionStrings(xmlDocument, xmlNamespaceManager);
  13:     }
  14:  
  15:     if (stateStringsCheckBox.Checked)
  16:     {
  17:         ReplaceWebConfigStateConnectionInfo(xmlDocument, xmlNamespaceManager);
  18:     }
  19:  
  20:     xmlDocument.Save(configFile);
  21: }
  22:  
  23: private void ReplaceWebConfigStateConnectionInfo(XmlDocument xmlDocument,
  24:                                                  XmlNamespaceManager xmlNamespaceManager)
  25: {
  26:     XmlNode sessionStateNode = xmlDocument.SelectSingleNode("//ns:system.web/ns:sessionState", xmlNamespaceManager);
  27:  
  28:     XmlAttribute sessionStateConnectionAttribute = sessionStateNode.Attributes["sqlConnectionString"];
  29:  
  30:     if (sessionStateConnectionAttribute != null)
  31:     {
  32:         if (stateDatabaseTextBox.Text.Equals("ASPState", StringComparison.InvariantCultureIgnoreCase))
  33:         {
  34:             sessionStateConnectionAttribute.Value = string.Format("data source={0};trusted_connection=true", stateServerTextBox.Text);
  35:  
  36:             if (sessionStateNode.Attributes["allowCustomSqlDatabase"] != null)
  37:             {
  38:                 sessionStateNode.Attributes.Remove(sessionStateNode.Attributes["allowCustomSqlDatabase"]);
  39:             }
  40:         }
  41:         else
  42:         {
  43:             if (sessionStateNode.Attributes["allowCustomSqlDatabase"] == null)
  44:             {
  45:                 XmlAttribute customSqlDbAttribute = xmlDocument.CreateAttribute("allowCustomSqlDatabase");
  46:  
  47:                 customSqlDbAttribute.Value = "true";
  48:  
  49:                 sessionStateNode.Attributes.Append(customSqlDbAttribute);
  50:             }
  51:  
  52:             sessionStateConnectionAttribute.Value = string.Format("data source={0};initial catalog={1};trusted_connection=true", stateServerTextBox.Text, stateDatabaseTextBox.Text);
  53:         }
  54:     }
  55: }
  56:  
  57: private void ReplaceWebConfigConnectionStrings(XmlDocument xmlDocument,
  58:                                                XmlNamespaceManager xmlNamespaceManager)
  59: {
  60:     XmlNodeList connectionStrings = xmlDocument.SelectNodes("//ns:connectionStrings/ns:add", xmlNamespaceManager);
  61:  
  62:     if (connectionStrings != null && connectionStrings.Count > 0)
  63:     {
  64:         foreach (XmlNode node in connectionStrings)
  65:         {
  66:             if (node.Name.Equals("add", StringComparison.InvariantCultureIgnoreCase) && node.Attributes["connectionString"] != null)
  67:             {
  68:                 node.Attributes["connectionString"].Value = connectionStringTextBox.Text;
  69:             }
  70:         }
  71:     }
  72: }v

As you can see from the number of checkboxes in the options groupbox, there are plenty of ways to configure the search criteria to just what I needed.

So, this thing works and now I can switch to another configuration without rebuilding the system (there are ways to automate setting connection strings in the build process through Nant or MSBuild).

Have a great weekend

No comments :

Disclaimer

Content on this site is provided "AS IS" with no warranties and confers no rights. Additionally, all content on this site is my own personal opinion and does not represent my employer's view in any way.