Click here to Skip to main content
11,433,901 members (65,314 online)
Click here to Skip to main content

Cinchoo - Using SQLServer Database as Configuration Source

, 3 Feb 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
This tip shows how to use a SQL Server database as configuration source using the Cinchoo Configuration Manager.

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.

In this section, I'm going to tell you the details about using SQLServer 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 the 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 mediums. A SQL Server database is one of the mediums to store the application configurations.

There are many ways in which you can store the configuration in the DBMS world. Here I'm going to talk about a 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. Let's say, if a configuration object has 5 members, will end up having corresponding 5 rows in that table + 1 additional row for last modified timestamp (used to track the data changes).
  2. Column-wise approach - Configuration parameters are kept in a row within the table. The table is structured in such a way that each configuration parameter corresponds to a column in that table. Let's say, if a configuration object has 5 members, we have to create table with 5 columns + 1 additional column for LastModifiedTimeStamp (used to track the data changes).

Prerequisites

  • Download latest Cinchoo binary here.
  • Open VS.NET 2010 or higher.
  • Create a sample VS.NET (.NET Framework 4) Console Application project.
  • Add reference to Cinchoo.Core.dll.
  • Use the Cinchoo.Core.Configuration namespace.

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:

[ChoSqlServerKeyValueConfigurationSection
("sqlKeyValueSectionHandlerTest/applicationSettings",
    @"CONNECTION_STRING='Data Source=LOCALHOST\SQLEXPRESS;
    Initial Catalog=Sample;Integrated Security=True;Pooling=False';
    TABLE_NAME=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 a configurable object, it must derive from ChoConfigurableObject.
  2. It must be decorated using ChoSqlServerKeyValueConfigurationSectionAttribute, so that this object will use the SQL Server database as the configuration source. There are two key parameters, configuration element path (sqlKeyValueSectionHandlerTest/applicationSettings) and configuration storage specific parameters in comma separated key-value formatted text value. Particularly, this storage plug-in needs the following 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'
    • LAST_UPDATE_DATETIME_KEY_NAME - Optional. Default value is 'LAST_UPDATE_TIMESTAMP'. This key holds the time stamp information about the change state of the configuration object. If any data change made in the table is to be communicated back to your application, this key-value needs to be touched.

Once you have class declared as above, it is now ready to use SqlServer database table as configuration source as simple as creating an object out of it. Any changes made to this object as well as in the corresponding underlying table (P.S.: 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 is as shown 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
LAST_UPDATE_TIMESTAMP 1/24/2013 10:57:16 PM
OS Windows123
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,
    [LAST_UPDATE_TIMESTAMP] DATETIME      NULL
);  

The corresponding configuration object can be defined as below:

[ChoSqlServerDictionaryConfigurationSection
("sqlDictionarySectionHandlerTest/applicationSettings", 
  @"CONNECTION_STRING='Data Source=LOCALHOST\SQLEXPRESS;
  Initial Catalog=Sample;Integrated Security=True;Pooling=False'; 
  TABLE_NAME=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 a configurable object, it must derive from ChoConfigurableObject.
  2. It must be decorated using ChoSqlServerDictionaryConfigurationSectionAttribute, so that this object will use the SQL Server database as the configuration source. There are two key parameters, configuration element path (sqlDictionarySectionHandlerTest/applicationSettings) and configuration storage specific parameters in comma separated key-value formatted text value. This storage plug-in needs below parameters:
    • CONNECTION_STRING - SqlServer instance connection string in single quotes.
    • TABLE_NAME - SQL Server table where the configuration values are stored.
    • LAST_UPDATE_DATETIME_COLUMN_NAME - Optional. Default value is 'LAST_UPDATE_TIMESTAMP'. This column holds the time stamp information about the change state of the configuration object. If any data change made in the table is to be communicated back to your application, this column value needs to be touched.

Once you have the class declared as above, it is now ready to 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 (P.S.: 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 is 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 LAST_UPDATE_TIMESTAMP
C:\ Windows 1 1/19/2013 2:04:20 PM

Configuring Storage Parameters

The parameters specific to the above 2 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 is usually created very first time run your application. Look for xml node named configuration element path (e.g., sqlDictionarySectionHandlerTest/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.ChoSqlServerDictionaryConfigStorageAdapter, 
      ChoSqlServerDictionaryConfigStorage, 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=APPLICATION_SETTINGS;]]></cinchoo:configObjectAdapterParams>
    </applicationSettings>
  </sqlDictionarySectionHandlerTest>
</configuration> 

If you take a deeper 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 extra care when you modify this data. It 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.

UPDATE

All the above configuration storages use polling mechanism to detect the underlying table changes. This approach is simple and no special privileges are required for monitoring the changes.

The next tip explains about push mechanism in detecting the table data changes. It uses the SQLServer broker services to detect the changes. Please visit [Cinchoo - Using a SQL Server database as a configuration source, Part 2] tip for more information. Thanks for checking.

License

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

Share

About the Author

Cinchoo

United States United States
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150428.2 | Last Updated 4 Feb 2013
Article Copyright 2013 by Cinchoo
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid