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

Cinchoo - Using MySql Database as Configuration Source

, 27 Jan 2013
Rate this:
Please Sign up or sign in to vote.
This tip shows how to use a MySql 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.

This tips details about using MySql database as configuration source, which is very similar to Cinchoo - Using SQLServer database as configuration source tip.

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 a 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
  • Download MySql and client connectivity library from here, if you don't have them

Row-wise Approach

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

CREATE  TABLE `test`.`APP_SETTINGS` (
  `KEY` VARCHAR(100) NOT NULL ,
  `VALUE` VARCHAR(250) NULL ,
  PRIMARY KEY (`KEY`) 
  ); 

And a configuration object is defined as below:

[ChoMySqlKeyValueConfigurationSection
("mySqlKeyValueSectionHandlerTest/applicationSettings",
    @"CONNECTION_STRING='server=localhost;User Id=root;
    password=admin;Persist Security Info=True;database=test'; 
    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 configurable object, it must derive from ChoConfigurableObject.
  2. It must be decorated using ChoMySqlKeyValueConfigurationSectionAttribute, so that this object will use the MySql database as the configuration source. There are two key parameters, configuration element path (mySqlKeyValueSectionHandlerTest/applicationSettings) and configuration storage specific parameters in comma separated key-value formatted text value. Particularly, this storage plug-in needs the below parameters:
    • CONNECTION_STRING - MySql database connection string in single quotes
    • TABLE_NAME - MySql 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 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 MySql 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 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 `test`.`application_settings` (
  `Path` VARCHAR(255) NULL ,
  `OS` VARCHAR(50) NULL ,
  `SingleInstanceApp` BIT NULL ,
  `LAST_UPDATE_TIMESTAMP` TIMESTAMP NULL ); 

The corresponding configuration object can be defined as below:

[ChoMySqlDictionaryConfigurationSection
("mySqlDictionarySectionHandlerTest/applicationSettings", 
@"CONNECTION_STRING='server=localhost;User Id=root;password=admin;
Persist Security Info=True;database=test'; 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", SourceType=typeof(UInt16))]
    public bool SingleInstanceApp;
}
  1. As usual in order to be configurable object, it must derive from ChoConfigurableObject.
  2. It must be decorated using ChoMySqlDictionaryConfigurationSectionAttribute, so that this object will use the MySql database as the configuration source. There are two key parameters, configuration element path (mySqlDictionarySectionHandlerTest/applicationSettings) and configuration storage specific parameters in comma separated key-value formatted text value. This storage plug-in needs the below parameters:
    • CONNECTION_STRING - MySql database connection string in single quotes
    • TABLE_NAME - MySql 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 to be communicated back to your application, this column value needs to be touched.

Once you have class declared as above, it is now ready use MySql 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 the very first time you 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="mySqlKeyValueSectionHandlerTest">
      <section name="applicationSettings" 
      type="Cinchoo.Core.Configuration.ChoDictionarySectionHandler, 
      Cinchoo.Core, Version=1.0.1.3, Culture=neutral, PublicKeyToken=b7dacd80ff3e33de" />
    </sectionGroup>
  </configSections>
  <mySqlKeyValueSectionHandlerTest>
    <applicationSettings cinchoo:
    configObjectAdapterType="Cinchoo.Core.Configuration.
    ChoMySqlKeyValueConfigStorageAdapter, ChoMySqlKeyValueConfigStorage, 
    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='server=localhost;
      User Id=root;password=admin;Persist Security Info=True;database=test'; 
      TABLE_NAME=APP_SETTINGS;]]></cinchoo:configObjectAdapterParams>
    </applicationSettings>
  </mySqlKeyValueSectionHandlerTest>
</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.

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
Web02 | 2.8.140721.1 | Last Updated 28 Jan 2013
Article Copyright 2013 by Cinchoo
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid