Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » Databases » Downloads
 
Add your own
alternative version
Go to top

Custom Configuration SQL Connection String Section

, 23 Jan 2006
This solution demonstrates enforcing valid SQL connection strings in the App.Config file.
/*
Fair License

Copyright (c) 2005 Theodore William Bouskill

Usage of the works is permitted provided that this
instrument is retained with the works, so that any entity
that uses the works is notified of this instrument.

DISCLAIMER: THE WORKS ARE WITHOUT WARRANTY.
*/
using System;

using System.Collections;
using System.Configuration;
using System.Diagnostics;
using System.Xml;
using System.Xml.Schema;
using System.Xml.XPath;
using System.Xml.Xsl;

namespace CodeProject
{
	public class SqlConnectionConfigurationException : Exception
	{
		public SqlConnectionConfigurationException(String inMessage) : base(inMessage) {}
		public SqlConnectionConfigurationException(String inMessage, Exception inException) : base(inMessage, inException) {}
	}

	/// <summary>
	/// Custom SqlConnection Configuration Section Handler
	/// </summary>
	public class SqlConnectionConfigurationSectionHandler : IConfigurationSectionHandler
	{
		public static String SectionName = "SqlConnectionConfigurationSection/SqlConnectionStringCollection";

		public SqlConnectionConfigurationSectionHandler() {}

		#region IConfigurationSectionHandler Members
		public object Create(object parent, object configContext, XmlNode section)
		{
			// Prepare the object to return
			SqlConnectionConfigurationStrings sqlConnCfngStrs = new SqlConnectionConfigurationStrings();

			// If the section is empty we have nothing to do
			if (section.HasChildNodes)
			{
				// First prepare a text reader to be used by the validating reader
				XmlTextReader xtr = new XmlTextReader(section.OuterXml, XmlNodeType.Element, null);
				XmlValidatingReader xvr = new XmlValidatingReader(xtr);

				// Prepare the validation properties
				xvr.ValidationType = ValidationType.Schema;
				xvr.ValidationEventHandler += new ValidationEventHandler (SqlConnectionConfigurationValidationHandler);

				// Prepare a XSLT transformation object which will use the XmlValidatingReader
				XslTransform xslt = new XslTransform();
				xslt.Load("SqlConnectionStringCollection2Xml.xslt");

				// Use the XmlValidatingReader to process, thereby transforming and validating in one step!
				XPathDocument xpd = new XPathDocument(xvr);

				// Execute the transformation and return the simple XML result set for creating the SqlConnectionStrings
				XmlReader xr = xslt.Transform(xpd, null, new XmlUrlResolver());

				// Pull the transformed XML into a XmlDocument to simplify parsing
				XmlDocument xd = new XmlDocument();
				xd.Load(xr);

				// The XML Schema defines that there must be at least one child
				XmlNode xn = xd.FirstChild.FirstChild;
				do
				{
					Debug.Write(xn.Attributes["ID"].Value+",");
					Debug.Write(xn.Attributes["Required"].Value+",");
					Debug.Write(xn.InnerXml+"\n");

					bool required = Convert.ToBoolean(xn.Attributes["Required"].Value);
					if (required)
					{
						if (!SqlTools.IsValidSqlConnectionString(new SqlConnectionString(xn.InnerXml)))
							throw new SqlConnectionConfigurationException(String.Format("Invalid required SqlConnectionString; ID[{0}] {1}", xn.Attributes["ID"].Value, xn.InnerXml));
					}

					// TODO: Add logic to remove all invalid characters!
					sqlConnCfngStrs.AddSqlConnectionString(xn.Attributes["ID"].Value, xn.InnerXml);

					xn = xn.NextSibling;

				} while (xn != null);

			} // if (section.HasChildNodes)

			return sqlConnCfngStrs;
		}
		#endregion

		/// <summary>
		/// Method used to validate the XML using the XML Schema
		/// </summary>
		/// <param name="sender">Unused</param>
		/// <param name="args">Relayed to caller</param>
		/// <exception cref="SqlConnectionConfigurationException">Wrapper for the Exception returned by the XML Validation</exception>
		public static void SqlConnectionConfigurationValidationHandler(object sender, ValidationEventArgs args)
		{
			throw new SqlConnectionConfigurationException(String.Format("SqlConnectionConfiguration XML Validation Error; Severity: {0}, Message: {1}", args.Severity, args.Message), args.Exception);
		}
	}

	/// <summary>
	/// Type definition for a SqlConnectionString
	/// </summary>
	/// <remarks>
	/// By providing a Type definition other programmers can enforce business rules
	/// such as having all SqlConnection strings retrieved from the App.Config file
	/// </remarks>
	public class SqlConnectionString
	{
		private string _SqlConnectionString;
		
		internal SqlConnectionString(string inSqlConnectionString)
		{
			_SqlConnectionString = inSqlConnectionString;
		}

		public override string ToString()
		{
			return _SqlConnectionString;
		}
	}

	public class SqlConnectionConfigurationStrings : IDisposable
	{
		/// <summary>
		/// Hashtable with SqlConnectionStrings is private to prevent direct manipulation of contents
		/// </summary>
		private Hashtable _SqlConnectionStrings;

		/// <summary>
		/// The SqlConnectionConfigurationSectionHandler.Create() method should be used to create this object
		/// </summary>
		internal SqlConnectionConfigurationStrings()
		{
			_SqlConnectionStrings = new Hashtable();
		}

		/// <summary>
		/// The SqlConnectionConfigurationSectionHandler.Create() method will populate this object
		/// </summary>
		internal void AddSqlConnectionString(string inID, string inSqlConnectionString)
		{
			_SqlConnectionStrings.Add(inID, new SqlConnectionString(inSqlConnectionString));
		}

		/// <summary>
		/// Retrieves the SqlConnectionString tag ID attributes values as a ArrayList from the XML
		/// </summary>
		/// <remarks>
		/// This property is only provided as a way to iterate through the IDs for diagnostics.
		/// A Software Development team should have a defined list documented for the application(s).
		/// </remarks>
		public ArrayList IDs
		{
			get { return new ArrayList(_SqlConnectionStrings.Keys); }
		}

		/// <summary>
		/// Wrapper for SqlConnectionString Hashtable Contains() function
		/// </summary>
		/// <param name="inID"></param>
		/// <returns>true if the ID is valid</returns>
		public bool Contains(String inID)
		{
			return _SqlConnectionStrings.Contains(inID);
		}

		/// <summary>
		/// Retrieves the specific SqlConnectionString using the string index
		/// </summary>
		/// <exception cref="SqlConnectionConfigurationException">If the ID is invalid</exception>
		/// <remarks>
		/// This could be converted to use Debug.Assert
		/// </remarks>
		public SqlConnectionString this [string inID]
		{
			get
			{
				if (!_SqlConnectionStrings.Contains(inID))
					throw new SqlConnectionConfigurationException(String.Format("Invalid SqlConnectionString ID[{0}]", inID));

				return (SqlConnectionString)_SqlConnectionStrings[inID];
			}
		}

		#region IDisposable Members
		public void Dispose()
		{
			_SqlConnectionStrings.Clear();
		}
		#endregion
	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ted Bouskill
Software Developer (Senior)
Canada Canada
Very experienced Senior Software Developer/Manager with natural leadership and proven project management skills.
 
Started in Civil Engineering leading the integration of PC's into the Engineering process as a Design/Drafting Technician as well as an in-house Software Developer then transitioned into a full time Software Development career in the early 90's.
 
Ability to change and adapt has led to diverse experience with a wide array of technology and roles from graphics or web development to designing line of business enterprise applications.
 
Knowledge Base: Sharepoint, C#, SQL, ASP.NET, C++, CSS, HTML, JavaScript, XML, XSLT

| Advertise | Privacy | Mobile
Web01 | 2.8.140922.1 | Last Updated 24 Jan 2006
Article Copyright 2006 by Ted Bouskill
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid