Store your App.Config inside SQL Server 2005

I did submit some time ago a little fix for the Enterprise Library SqlConfigurationSource **The links are working again** Sam Gentile did not give up until I resubmitted the sample code Code Gallyery

**. but I did not yet have time to blog about it. Better late than never I want to shed some light (again) on configuration. To get your application settings from your local disk you can use the .NET ConfigurationManager class or the (Entlib) FileConfigurationSource to fetch the settings from another file than your App.Config file.
The Enterprise Library SqlConfigurationManager is the counterpart to the .NET ConfigurationManager class and can be directly used from any application that cares about central configuration. To use it you need a running SQL Server 2005 (Express) server and a database which contains some stored procedures which are responsible for reading and writing ConfigurationSections. The following code sample shows you how to get your settings with only a few lines of code from SQL server.

SettingsDialog.cs (Part of SqlConfigConverter Project)

public partial class SettingsDialog : Form

{

const string GetConfig = "EntLib_GetConfig";

const string SetConfig = "EntLib_SetConfig";

const string RefreshSection = "UpdateSectionDate";

const string RemoveSection = "EntLib_RemoveSection";

SqlConfigurationData SqlConnectData;

const string ClientSettingsSection = "ClientSettings";

CustomConfigurationSection SqlSection; // Loaded ConfigurationSection from SQL Server

public SettingsDialog()

{

// Build our Sql Connection data

SqlConnectData = new SqlConfigurationData(Settings.Default.ConnectionString,

GetConfig, SetConfig, RefreshSection, RemoveSection);

// Retrieve data from SQL Server

SqlSection = SqlConfigurationManager.GetSection(ClientSettingsSection, SqlConnectData) as CustomConfigurationSection;

}

Client Application Sample

SQL Server configured client Application

It really feels the same like App.Config and it nearly is, only better. You can call SqlConfigurationManager.GetSection and get your ConfigurationSectionderived config object back from SQL server. What is really cool about this source is that the returned objects are not immutable like the ones returned by the ConfigurationManager. This relieves you from the burden to create every time a new instance of the configuration object when you want to save a changed configuration object.

Inner Workings of the SqlConfigurationSource
The import mechanism is as simple as elegant: Split the App.Config file into its parts (Sections and Serialized data). This enables you to store the config data in SQL server in a table that contains the Section Name, Type, Serialized object and its last modification time (required for section update) as columns. The table below shows an example what you would expect to see if you browse your database. A very good tool to do so is the Microsoft SQL Server Management Studio Express application which helps you to browse and diagnose your databases and tables.

Section Name	ConfigurationSection Type	Serialized XML Node
ClientSettings	SqlSettingsClient.CustomConfigurationSection, SqlSettingsClient, Version=1.0.0.0,Culture=neutral, PublicKeyToken=null	<ClientSettings globalSettings="Some Global Settings" hostSettings="Host Relevant Settings"/>
....	....	...

When you call SqlConfigurationManager.GetSection the Entlib_GetConfig stored procedure is called which does fetch the type and serialized XML node and returns it to the SqlConfigurationManager. He does deserialize the object and returns it back to you. There is a problem in the de/serialization code with the released SqlConfigurationSource which insisted that the type had to be derived from SerializableConfigurationSection which is an abstract Enterprise Library base class. This restriction did prevent the Data Access application block from working and of course every other existing ConfigurationSection derived config class. My patch does remove this annoying restriction and gives you the flexibility you need to do create more advanced tools.

Make the App.Config import Easier: SQL Server Importer
To make it really easy to import your App.Config settings programatically I did write a small tool that allows you to import any section of an App.Config into SQL server.

The source code for the importer and a sample client application can be found at Code Gallery.
What is noteworthy about this tool is that it does not simply load and save xml but it loads the real ConfigurationSection objects defined by any application. This enables data validation at import time which is a bonus. This explains why the tool asks you for the assembly with the data types it finds in the App.Config file. The type discovery is done by my Assembly Resolve Dialog which I did already submit as patch to the Enterprise Library configuration console.

Enterprise Library IConfigurationSource and Writing Configuration Data
Perhaps I should start with the concept how the application block in the Enterprise Library are configured. The root of all evil is the IConfigurationSource interface which does abstract the application blocks from the storage location. They receive via ObjectBuilder only the Interface but not any concrete class implementation. The Logging Application Block for example can be configured from an arbitrary configuration file with this code:

LogWriter writer = null;

FileConfigurationSource source = new FileConfigurationSource(@"C:\Shared.config");

factory = new LogWriterFactory(source);

writer = factory.Create();

The LogWriterFactory does take only the IConfigurationSource interface and instantiates the LogFormatterCustomFactory which does call GetSection to retrieve the logging settings. This approach does work for all application blocks which are configured via the graphical Enterprise Library Configuration Tool. The config tool is the only one which does ever write the configuration. This is nice and does work very well if you do configuration exclusively via the supplied tool. But if you ever want to change the configuration inside your own application (configuration dialog) you will very soon notice that something is missing. You have the IConfigurationSource interface to read/write your settings. Add/Remove is here so lets start coding. I want to add my own ConfigurationSection derived config object so I have to call Add:

void Add(IConfigurationParameter saveParameter, string sectionName, ConfigurationSection configurationSection);

Hm I have to supply some IConfigurationParameter. No problem, lets have a look at its methods

public interface IConfigurationParameter

{

}

Ok now I have a problem. How am I supposed to write to an abstract repository I should know nothing about it? Answer: You have to know with what source you are dealing to be able to write to it. The IConfigurationSource does abstract you nicely when you read only your config objects but breaks polymorphism when you want to write your configuration. I hope this design flaw will be fixed in the next release of the Enterprise Library.

What I really would like is an interface that allows a transparent way to retrieve and save configuration settings. I might have overlooked some issues but I think that the following interface proposal would make the work for the applications much easier if they want to Save/Enumerate or Save to another Source configuration data without knowing what implementation it currently using.

Proposal for a new IConfigurationSource

using System;

using System.Configuration;

namespace Configuration

{

/// <summary>

/// Abstract interface that does allow a transparent read and write of ConfigurationSections regardless

/// of the source.

/// </summary>

public interface IConfigurationSource : IEnumerable<ConfigurationSection>

{

ConfigurationSection GetSection(string sectionName);

void Remove(string sectionName);

void Add(string sectionName, ConfigurationSection dataObject);

void Update(string sectionName, ConfigurationSection dataObject);

void Save();

void SaveTo(IConfigurationSource otherSource);

// Notification Services

void AddSectionChangeHandler(string sectionName, ConfigurationChangedEventHandler handler);

void RemoveSectionChangeHandler(string sectionName, ConfigurationChangedEventHandler handler);

}

It is amazing how much one can write/think about this "small" topic configuration. But for now I think there is enough information out there to get the work done. The next time I will show you how to improve the speed of your applications by optimizing your string handling/formatting.

posted on Monday, April 17, 2006 7:22 PM

Feedback

# re: Store your App.Config inside SQL Server 2005 4/22/2006 12:56 AM Steven

I am trying your application and running into issues.

When trying to open a config file I'm receiving the following error.

ex = {"An error occurred creating the configuration section handler for ClientSettings: Could not load file or assembly 'file:///G:\\Enterprise Library\\SqlConfigConverter GUI\\SqlConfigConverter\\SqlConfigConverter\\bin\\Debug\\' or one of its dependencies. The...

Could not load file or assembly 'file:///G:\Enterprise Library\SqlConfigConverter GUI\SqlConfigConverter\SqlConfigConverter\bin\Debug\' or one of its dependencies. The system cannot find the file specified.

# re: Store your App.Config inside SQL Server 2005 4/22/2006 10:49 AM Alois Kraus

Hi Steven,

you should see before this error the Assembly Resolve dialog where you can add the directory of the SqlSettingsClient.exe application. This is done to validate the settings of the App.Config by deserializing before the import to check if the content is valid. This implies that all assemblies must be loaded which are referenced in the App.Config sections section. Did you not see the assembly resolve dialog? I did notice that I do not see the dialog when I try to open a file which does not contain XML. Alternatively you can edit the settings via Configuration->Assembly Resolver.

HTH,

Alois Kraus

# re: Store your App.Config inside SQL Server 2005 4/23/2006 8:19 PM Steven Ulbrich

Alois, your advice worked. I updated the Assembly Directory and the application, then presented me with the missing assemblies.

I was expecting the data configuration block to imported to the database. What the correct or should I be doing something else different?

Steven

# re: Store your App.Config inside SQL Server 2005 4/23/2006 8:52 PM Alois Kraus

Hi Steven,

this application has no dependency to the DAAB and does not import its settings into it. When doing an import only the sections that you select during the import are saved inside SQL server. The reference I made with regards to the DAAB is that the graphical configuration (EntlibConfig) tool does not allow storing of these settings inside SQL server because when you activate the SQL Configuration Source inside it. My patched SQLConfiguratonSource allows you to store any (ConfigurationSection derived) settings which are inside App.Config now.

Yours,
Alois Kraus

# re: Store your App.Config inside SQL Server 2005 6/26/2006 6:27 PM David

Microsoft apparently fumbled the ball on configuration. Their framework assumes a file based mechanism, and if you want more, build your own.

You're solution works great, until you want some library to use SQL Server for configuration instead of the file based version MS provides. That library knows nothing about your SQL configuration framework. You have to hope they allow you to provide your own Configuration object. Oh wait, Configuration is concrete, I can't provide my own implementation.

I can't believe they came up with what they did based on what was initially in the Enterprise Library. It's a large leap backwards. I can only hope at some point they revamp it. Which means for me, it's worthless until they do.

# re: Store your App.Config inside SQL Server 2005 7/24/2006 6:54 AM rao

How can i write the wapper on FileConfiguration Source.

# re: Store your App.Config inside SQL Server 2005 7/24/2006 6:56 AM rao

How can we crate the wapper on FileConfiguration Source .Is Configuration File path Dictionary object

# re: Store your App.Config inside SQL Server 2005 9/7/2006 9:39 AM Simon

The SQLConfiguration manager is great and solves many of my problems. However, the implementation is quite strongly tied to the DBSchema. I would like a centralised SQL repository for the management of all my server based apps in a enterprise. This means I need a need methods like ...

IConfigurationSource.GetSection(string ConfigName, string SectionName)
where ConfigName is an open string value I choose to identify my particular collection of configuration sections (eg. MyWebAppV2, or MyWebAppV2OnServerX

This will allow me to have a single DB table within which I can store my configuration for each application in my Enterprise environment. I could also store different configs for different versions of my application (even different configs for my app on different servers).

What do you think of these ideas? How would you suggest I implement such a solution? Write a new interface?

I could try and hack it by writing a wrapper, where SectionName is actually ConfigName, and alll the sctions are stored in the DBField. Then I could expose GetSection(ConfigName,SectionName) from the wrapper, load the XML where tblConfig.SectionName = ConfigName and then parse the XML and pull out the node having for SectionName? Your thoughts???

# re: Store your App.Config inside SQL Server 2005 9/7/2006 10:29 AM Simon

Just had another idea! What if I overide the implementation of SqlConfigurationSystem.GetSection(sectionName) to call a stored procedure procGetSection(@ConfigName,@SectionName) with the values
* SqlConifgurationData.Name and
* sectionName respectively.

That could work. Comments ?

# re: Store your App.Config inside SQL Server 2005 9/7/2006 11:37 AM Simon

Duuuuuuuh. Sorry for the last two posts, I just had my Einstein moment. I could use the existsing SQLConfigurationSource and just use a hierarchical naming convention like ...

server.appname.appversion.sectionname

as the section name

eg. localhost.mywebapp.v2.connectionStrings

Well, I hope my oversight steers others clear of the same.

# re: Store your App.Config inside SQL Server 2005 11/8/2006 12:58 AM Phil

I have been trying your import application and it all works fine but I have run into a snag when trying to use it to import dataConfiguration sections.

The imported section contains the provider mappings but does not contain the connection strings. Looking into the structure of the dataConfiguration section it appears that provider mappings are represented as a child node of dataConfiguration but connectionstrings is not.

This weird and not how this information is represented in EntLibConfig which has connectionstrings as a childnode of of Data Access Application Block.

This is makes the imported section un-usable. Surely having the connection strings as a independant node and not defined as a section is not best pratcice.

Any ideas why this is?

# re: Store your App.Config inside SQL Server 2005 11/8/2006 9:56 PM Alois Kraus

Hi Phil,

yes this is a problem with the DAAB block. There are some sections defined in App.Config which are not represented via the normal <section> type .... mechanism but are hard coded into the App.Config schema. AppSettings is another example which could cause problems like that. I am sorry but I do not have a solution for this issue except that you alter the DAAB block to read the settings from a normal node.

Yours,
Alois Kraus

# re: Store your App.Config inside SQL Server 2005 8/27/2007 2:02 AM MikeB

OK dumb question... but what happened to the SqlConfigurationSource in ENTLIB 3.0?

# Found a nasty bug about AddSectionChangeHandler 8/29/2007 12:54 AM J. Longo

Hi all,

I was trying to watch for changes in SqlConfigurationSource, and found my Change handler
was never fired.

Finally, found the following :

in SqlConfigurationSourceImplementation.cs :

private void OnExternalConfigurationChanged(object sender, ConfigurationChangedEventArgs args)
{
ExternalConfigSourceChanged(args.SectionName);
}

BUT

ExternalConfigurationChanged is waiting for the config source (in fact, DB Connection string).

We can solve the problem by adding the following method :

public void ExternalConfigSectionChanged(string section)
{
string[] sectionsToNotify;

lock (lockMe)
{
ConfigurationSourceWatcher watcher = null;
this.watchedSectionMapping.TryGetValue(section, out watcher);
sectionsToNotify = new string[watcher.WatchedSections.Count];
watcher.WatchedSections.CopyTo(sectionsToNotify, 0);
}

foreach (string sectionName in sectionsToNotify)
{
SqlConfigurationManager.RefreshSection(sectionName, this.data);
}

NotifyUpdatedSections(sectionsToNotify);
}

which is the same than ExternalConfigSourceChanged, but searching in watchedSectionMapping instead of watchedSectionMapping instead of watchedConfigSourceMapping.

Ok, that works, now changes are pooled every 15000 ms. Now we have another problem :

The RefreshSection mecanism calls the stored proc UpdateSectionDate, which updates the lastmoddate field in database.
but in Common/configuration/storage/ConfigurationChangeWatcher.cs,
we have the following method :

private void Poller(object parameter)
{
lastWriteTime = DateTime.MinValue;
DateTime currentLastWriteTime = DateTime.MinValue;
PollingStatus pollingStatus = (PollingStatus)parameter;

while (pollingStatus.Polling)
{
currentLastWriteTime = GetCurrentLastWriteTime();
if (currentLastWriteTime != DateTime.MinValue)
{
// might miss a change if a change occurs before it's ran for the first time.
if (lastWriteTime.Equals(DateTime.MinValue))
{
lastWriteTime = currentLastWriteTime;
}
else
{
if (lastWriteTime.Equals(currentLastWriteTime) == false)
{
lastWriteTime = currentLastWriteTime;
OnConfigurationChanged();
}
}
}
Thread.Sleep(pollDelayInMilliseconds);
}
}

The problem is in :
lastWriteTime = currentLastWriteTime
because in OnConfigurationChanged, the database is updated, and the lastwritetime value is no more correct.
The solution is to simply read back the change from DB.
So we have to change :

lastWriteTime = currentLastWriteTime;
OnConfigurationChanged();

to :

OnConfigurationChanged();
lastWriteTime = GetCurrentLastWriteTime();

Hope it helps...

Best regards,

JLongo

PS : MikeB : It is located in Quickstart samples (for Entlib 3.1)

# Found a nasty bug about AddSectionChangeHandler (errata) 8/29/2007 2:26 AM J. Longo

Oups, forgot : of course, in SqlConfigurationSourceImplementation, replace

private void OnExternalConfigurationChanged(object sender, ConfigurationChangedEventArgs args)
{
ExternalConfigSourceChanged(args.SectionName);
}

with

private void OnExternalConfigurationChanged(object sender, ConfigurationChangedEventArgs args)
{
ExternalConfigSectionChanged(args.SectionName);
}

Best regards,

JLongo

# re: Store your App.Config inside SQL Server 2005 9/17/2007 3:30 AM J. Longo

Sorry, the fix I submitted doesn't seem to work as expected.
In fact, section changes are signaled only if the first fetched section is modified.
Monitoring more sections requires a lot more changes.

Best regards,

J.Longo

# re: Store your App.Config inside SQL Server 2005 1/24/2008 10:00 AM B O'Neil

I want to add a line to your Importer application.
It appears the Groups are hiding my sections from being picked up in the Import.
So I would suggest :

foreach (ConfigurationSectionGroup g in config.SectionGroups)

Right around line 74 of the Importer form.

Thanks,
Brian

# re: Store your App.Config inside SQL Server 2005 5/7/2008 2:06 AM Sam Gentile

The links in this post are dead. They go to gotdotnet which is gone. Do you have the source that I can download? Can you repost on Code Gallery or Code Plex? Many thanks!

# re: Store your App.Config inside SQL Server 2005 6/18/2008 5:50 AM RN

Is this a part of enterprise library or is this your own implementation?

Title:
Name:
Email:
Website:
Comment:
Verification:
	Remember Me?

Alois Kraus

News

Article Categories

Archives

Post Categories

Image Galleries

Programming

Feedback