Custom Configuration SQL Connection String Section

• Download project solution - 19.4 Kb
• Download sample XML, XML scheme and XSLT sheets - 8.14 Kb

Introduction

Issue

It seems that many .NET applications require at least one SQL database connection. Developers usually store the connection string in web.config or app.config file. However, in larger teams, applications are moved from Development to QA to Staging and finally to Production, there is often a requirement to automate the management of these connections because of the errors configuring applications during the promotion process.

Configuration issues often cause tremendous confusion. I make the following assumptions using configuration data at run-time:

1. The required configuration data is missing. (No SQL connection string).
2. The configuration data supplied is formatted incorrectly (an SQL connection string with invalid key=value; pairs).
3. The correctly formatted configuration data has invalid values (an SQL connection string with an invalid server name).

Requirements

• Automated build products have the capability to manage XML content. Therefore the SQL connection strings should be stored in an XML format.
• The XML should be validated using an XML schema.
• The SQL connection strings should be stored in IDictionary collections with unique keys enforced within the XML schema;
• If an SQL connection string is flagged as required, an exception must be thrown if it does not properly connect to a SQL server.
• All SQL connection strings created will be retrieved from the XML configuration elements.

Analysis

.NET web and Windows applications have CONFIG files that have the ability to define a custom XML configuration section which can enforce the requirements. The IConfigurationSectionHandler raises an event that can be used to load, validate and parse the SQL connection strings stored in XML format. If you search using the term configSection you can find other submissions on this website that discuss using this interface.

The challenge will be defining an XML format that can be validated using an XML schema that defines all possible combinations and permutations for a valid SqlConnection object in .NET. For example, if a connection is trusted, then the user ID and password combination is not required.

Background

Microsoft .NET Help has a Remarks section defined in the SqlConnection.ConnectionString property, which has a table that defines the available keys for use in a SqlConnection object. This solution will only use the keys defined in that table.

XML

Validation

Validating an XML file is very easy in .NET using the XmlValidatingReader. Essentially, when the XmlValidatingReader executes a read process using the XML file, it raises an event when an invalid XML is found. In this design, a custom exception is thrown if any of the XML fails validation.

There are a few ways to trigger the read process. In this case, the XmlValidatingReader is used to create a XPathDocument which is used to parse the XML. When the XPathDocument reads the XML using the XmlValidatingReader the XML is validated using the XML schema.

The XML schema can be located in a variety of locations. In this case, it is copied using the build event $(TargetDir) macro, first, to delete a previous copy, then copy the newest version using the $(ProjectDir) macro to be included with the build.

Parsing

At first I considered iterating through the XPathDocument XML nodes directly to either build the SQL connection string on the fly or define an object that was capable of representing all of the different versions. However, it seemed easier to use an XSLT process to parse the configuration XML into an even simpler XML document which would not be suitable for XML schema validation, yet will be easier to convert into SqlConnection.ConnectionString objects.

The XSLT file can also be located in a variety of locations. In this case, it is copied using the build event $(TargetDir) macro, first, to delete a previous copy, then copy the newest version using the $(ProjectDir) macro to be included with the build.

Using the code

This sample validates a simple trusted connection to an (local) instance of SQL server to connect to the Northwind database. I've included a SqlConnectionStringCollectionExample.Xml file that I used for testing that has a variety of sample SQL connections defined.

For example, the following custom configuration section is defined in the app.config file:

<SqlConnectionConfigurationSection>
  <SqlConnectionStringCollection 
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
      xsi:noNamespaceSchemaLocation="SqlConnectionStringCollection.xsd">
    <SqlConnectionString ID="App.Web" Required="true">
      <Authentication><Trusted/></Authentication>
      <Database><Name>Northwind</Name></Database>
    </SqlConnectionString>
  </SqlConnectionStringCollection>
</SqlConnectionConfigurationSection>

which then becomes the following XML after validating the schema and executing the XSLT transformation:

<SqlConnectionStrings>
  <SqlConnectionString ID="App.Web" Required="true">
      Integrated Security=True; Database=Northwind; Server=(local);
  </SqlConnectionString>
</SqlConnectionStrings>

The valid SqlConnectionString ID's are defined in the SqlConnectionString.Xsd schema in the ID attribute:

<xs:attribute name="ID" use="required">
  <xs:simpleType>
    <xs:restriction base="xs:string">
      <xs:minLength value="1" />
      <xs:enumeration value="App.Admin" />
      <xs:enumeration value="App.Manager" />
      <xs:enumeration value="App.Staff" />
      <xs:enumeration value="App.Trusted" />
      <xs:enumeration value="App.Web" />
      <xs:enumeration value="Log" />
      <xs:enumeration value="Backup" />
      <xs:enumeration value="Session" />
    </xs:restriction>
  </xs:simpleType>
</xs:attribute>

The enumeration keys are the only choices available in the code as keys to access the SqlConnectionString objects in the collection. I've included the samples that I've used in my applications. This sample defines the "App.Web" SQL connection string.

In the Main function defined in the MainClass.cs an SqlConnectionConfigurationStrings object is created using the SqlConnectionConfigurationSectionHandler.Create() method. In this case, an optional appSetting value is stored to determine if the SqlConnectionConfigurationStrings object is mandatory.

Within the Main function, there is an example code to show how to retrieve a specific SqlConnectionString object or iterate through all that are stored in the SqlConnectionConfigurationStrings object.

The SqlConnectionString objects are stored in the SqlConnectionConfigurationStrings using a protected Hashtable. The IDs retrieved from the XML SqlConnectionStringCollection section in the .Config file are restricted by an xs:enumeration list for the ID attribute of the SqlConnectionString tag. This strategy was chosen to ensure that the IDs used as keys in the SqlConnectionConfigurationStrings Hashtable are consistent.

Points of interest

• The reason an SqlConnectionString object is defined is so that other functions can require this object to ensure that the correct SQL connection strings are used!
• The SqlTools.IsValidSqlConnectionString() requires the SqlConnectionString object and validates the settings by attempting to use a fake stored procedure. If the SQL connection parameters are correct, an exception stating that a fake stored procedure was used is returned and then ignored!
• XMLSpy v2006 was used to create and test the XML, XSD, and XSLT files. It is an excellent product that I highly recommend.

Future revisions

Regular expressions could be added to further refine the validation of the SQL connection values. A numeric address is permitted for the server name, however, this XML schema does not validate the syntax.

History

• 13^th January, 2006 - First version.
• 22^nd January, 2006 - Updated the documentation and changed the default App.Config in the sample.