Click here to Skip to main content
Click here to Skip to main content

Cinchoo - Using a SQL Server database as a configuration source, Part 2

, 3 Feb 2013
Rate this:
Please Sign up or sign in to vote.
This article shows another approach in using SQL Server database as configuration source.

Introduction

Cinchoo is an application framework for .NET. One of the main functionalities it provides to users is application configuration management. Application configuration is information that an application reads and/or writes at run-time from the source.  

Please visit jump start article [Cinchoo - Simplified Configuration Manager] for more information about Cinchoo configuration manager.

This is the second part of [Cinchoo - Using SQLServer database as configuration source] article. So far we learned about using SQL Server database as configuration source in a polling approach. Cinchoo configuration manager polls for the change in the underlying table for every elapsed interval (configurable). This approach is ideal in a situation where SQL Server query notification service is not available or is turned off by administrators. 

In this section, I'm going to detail you about using SQL Server database as configuration source for your applications. More commonly all the .NET application configuration are kept in files as XML format. It has the limitation, such as maintaining them for each application separately, potential disk corruption by many applications due to access etc. This may not fit in enterprise world.  In a enterprise application development, there may be requirement to centralize these configuration parameters in database to better serve, manage and control them. Cinchoo framework opens the possibility of extending the configuration source to various medium. A SQL Server database is one of the mediums to store the application configurations.

In this approach, we are going to leverage SQL Server query notification service for change notification. It is available in SQL Server 2005+/ADO.NET 2.0. 

You will have to enable SQL Server service broker for the database. You can do so using below sql

ALTER DATABASE Chatter SET ENABLE_BROKER  

If you have any trouble running above sql, please restart the SQL Server database services and rerun the above query. 

There are many ways you can store the configuration in DBMS world. Here I'm going to talk about couple of ways

  1. Row-wise approach - Configuration parameters are maintained in two columns (KEY-VALUE) table. Each configuration parameter corresponds to a row in that table. Lets say, if a configuration object has 5 members, will ends up having corresponding 5 rows in that table.  
  2. Column-wise approach - Configuration parameters are kept in a row within the table. The table is structured in such a way each configuration parameter corresponds to a column in that table. Lets say, if a configuration object has 5 members, we have to create table with 5 columns.

Row-wise Approach 

In this approach, the table must be created with two columns. A sample schema can be seen below: 

CREATE  TABLE APP_SETTINGS (
  [KEY] VARCHAR(100) NOT NULL ,
  [VALUE] VARCHAR(250) NULL ,
  PRIMARY KEY ([KEY]) ); 

And a configuration object is defined as below: 

[ChoSqlServerPushKeyValueConfigurationSection("sqlPushKeyValueSectionHandlerTest/applicationSettings", @"CONNECTION_STRING='Data Source=LOCALHOST\SQLEXPRESS;Initial Catalog=Sample;Integrated Security=True;Pooling=False';TABLE_NAME=PUSH_APP_SETTINGS;")]
public class ApplicationSettings : ChoConfigurableObject
{
    [ChoPropertyInfo("path", DefaultValue = @"C:\")]
    public string Path;
 
    [ChoPropertyInfo("OS", DefaultValue = "Windows")]
    public string OS;
 
    [ChoPropertyInfo("singleInstanceApp", DefaultValue = "False")]
    public bool SingleInstanceApp;
}
  1. In order to be configurable object, it must derive from ChoConfigurableObject.
  2. It must be decorated using ChoSqlServerPushKeyValueConfigurationSectionAttribute, so that this object will use the SQL Server database as the configuration source. There are two key parameters, configuration element path (sqlPushKeyValueSectionHandlerTest/applicationSettings) and configuration storage specific parameters in comma separated key-value formatted text value. Particularly this storage plug-in needs below parameters 
    • CONNECTION_STRING - SQL Server instance connection string in single quotes.
    • TABLE_NAME - SQL Server table where the configuration values are stored.
    • KEY_COLUMN_NAME - Optional. Default value is 'KEY'. 
    • VALUE_COLUMN_NAME - Optional. Default value is 'VALUE'

Once you have class declared as above, it is now ready use SQL Server database table as configuration source as simple as creating object out of it. Any changes made to this object as well as in the corresponding underlying table (PS. Make sure you touch the last update time stamp value in order to pick up the updated values from table) will be exchanged automatically. The sample code as below

static void Main(string[] args)
{
    ApplicationSettings applicationSettings = new ApplicationSettings();
    ChoConsole.PauseLine();
} 

After you run your application, if you take a look at the table, you will see the below rows 

KEY                       VALUE
OS                          Windows 
path                               C:\
singleInstanceApp                           False 

Column-wise Approach 

In this approach, the table must be created with number of columns corresponding to number of configuration object members. Sample schema can be seen as below 

CREATE TABLE [dbo].[APPLICATION_SETTINGS] (
    [Path]                  VARCHAR (255) DEFAULT (NULL) NULL,
    [OS]                    VARCHAR (50)  DEFAULT (NULL) NULL,
    [SingleInstanceApp]     BIT           DEFAULT (NULL) NULL
); 

The corresponding configuration object can be defined as below:

[ChoSqlServerPushDictionaryConfigurationSection("sqlPushDictionarySectionHandlerTest/applicationSettings", 
  @"CONNECTION_STRING='Data Source=LOCALHOST\SQLEXPRESS;Initial Catalog=Sample;Integrated Security=True;Pooling=False'; TABLE_NAME=PUSH_APPLICATION_SETTINGS;")]
public class ApplicationSettings : ChoConfigurableObject
{
    [ChoPropertyInfo("path", DefaultValue = @"C:\")]
    public string Path;
 
    [ChoPropertyInfo("OS", DefaultValue = "Windows")]
    public string OS;
 
    [ChoPropertyInfo("singleInstanceApp", DefaultValue = "False")]
    public bool SingleInstanceApp;
}  
  1. As usual in order to be configurable object, it must derive from ChoConfigurableObject.
  2. It must be decorated using ChoSqlServerPushDictionaryConfigurationSectionAttribute, so that this object will use the SQL Server database as the configuration source. There are two key parameters, configuration element path (sqlPushDictionarySectionHandlerTest/applicationSettings) and configuration storage specific parameters in comma separated key-value formatted text value. This storage plug-in needs below parameters  
    • CONNECTION_STRING - SQL Server instance connection string in single quotes.
    • TABLE_NAME - SQL Server table where the configuration values are stored. 

Once you have class declared as above, it is now ready use SQL Server database table as configuration source as simple as creating object out of it. Any changes made to this object as well as in the corresponding underlying table (PS. Make sure you touch the last update time stamp value in order to pick up the updated values from table) will be exchanged automatically. The sample code as below  

static void Main(string[] args)
{
    ApplicationSettings applicationSettings = new ApplicationSettings();
    ChoConsole.PauseLine();
}  

After you run your application, if you take a look at the table, you will see the below row 

Path   OS    SingleInstanceApp       
C:\             Windows     1        

Configuring Storage Parameters

The parameters specific to the above two configuration storage plug-ins can be configured through application configuration file. It provides flexibility of changing them during deployment.  

Open [appExeName].xml file under the bin/config folder. This file usually created very first time run your application. Look for the XML node named configuration element path (e.g., sqlPushDictionarySectionHandlerTest/applicationSettings).  

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <sectionGroup name="sqlDictionarySectionHandlerTest">
      <section name="applicationSettings" 
        type="Cinchoo.Core.Configuration.ChoDictionarySectionHandler, Cinchoo.Core, 
          Version=1.0.1.5, Culture=neutral, PublicKeyToken=b7dacd80ff3e33de" />
    </sectionGroup>
  </configSections>
  <sqlDictionarySectionHandlerTest>
    <applicationSettings cinchoo:configObjectAdapterType="Cinchoo.Core.Configuration.ChoSqlServerPushDictionaryConfigStorageAdapter, 
      ChoSqlServerPushDictionaryConfigStorage, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" 
      xmlns:cinchoo="http://schemas.cinchoo.com/cinchoo/01/framework">
      <cinchoo:configObjectAdapterParams xmlns:cinchoo="http://schemas.cinchoo.com/cinchoo/01/framework">
          <![CDATA[CONNECTION_STRING='Data Source=LOCALHOST\SQLEXPRESS;Initial Catalog=Sample;Integrated Security=True;Pooling=False';
        TABLE_NAME=PUSH_APPLICATION_SETTINGS;]]></cinchoo:configObjectAdapterParams>
    </applicationSettings>
  </sqlDictionarySectionHandlerTest>
</configuration> 

If you take a deep look at the above xml file for 'applicatonSettings' node, you will find 'configObjectAdapterParams' child element. This is a CDATA section node. All configuration storage parameters are kept in this section. You should take a extra care when you modify this data. Will require restart of your application in order to take the new parameters into effect. 

That's all folks. A sample project is attached. Download and try for yourself.

License

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

About the Author

Cinchoo

United States United States
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 3 Feb 2013
Article Copyright 2013 by Cinchoo
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid