Configuring KiGG to use MySQL Database through Entity Framework

Introduction

This article is concerned with configuring KiGG to use MySQL database through Entity Framework Repository Provider. It is mandatory that your read How to configure KiGG to use Entity Framework Repository first and apply the settings mentioned in that article.

Creating and preparing KiGG MySQL Database

Along with KiGG source code -so far no deployment package or offecial release for KiGG 2.5- you'll find a folder named Database. Within this folder all SQL Scripts required to create MySQL database are available.
KiGG currently supports 2 providers of MySQL .Net Data Providers Devart dotConnect Provider for MySQL supported Database Schema:
  1. MySQL.Devart.KiGG.Schema.Administrator.Tool.Restore.sql: To be use with MySQL Administrator Restore tool
  2. MySQL.Devart.KiGG.Schema.Raw.Script.sql: Normal script, Will create database with name Kigg and then create the database schema.

MySQL Connector/net supported Database Schema:
  1. MySQL.Connector.KiGG.Schema.Administrator.Tool.Restore.sql: To be use with MySQL Administrator Restore tool
  2. MySQL.Connector.KiGG.Schema.Raw.Script.sql: Normal script, Will create database with name Kigg and then create the database schema.

Difference between both schemas:
Only one major difference between the above 2 schemas. Current Devart provider support GUID as varchar or char field.
Connector\net provider support GUID as binary(16) field. So I had to make 2 different schemas to support both providers.

Choose your preffered way to create your database, but remember its name and credentials as they will be used in ConnectionString, also database name will be used as value of Schema attribute in SSDL file.

IMPORTANT NOTE:
MySQL Connector/net 6.0.3 is the version we used to support MySQL. While I was writing this article version 6.0.4 was out. KiGG doesn't support both versions. KiGG support the assemblies provided along with its package only. The assemblies of connector/net provided with KiGG include bug fixes related to Entity Framework that we faced during development.

Configuring MySQL .Net Data Provider in Web.config

You'll need to configure the .Net Data Provider that you are going to use in web.config. If you are going to use MySQL connector/net -which is free- you'll need to add the following configuration in web.config:
<configuration>
 ......
 <system.data>
  <DbProviderFactories>
   <add name="MySQL Data Provider" 
      invariant="MySql.Data.MySqlClient" 
      description=".Net Framework Data Provider for MySQL" 
      type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.0.3.0, Culture=neutral, PublicKeyToken=92c3c527cdfe7566" />
  </DbProviderFactories>
 </system.data>
 ......
</configuration>
Note:
Assemblies exist in bin directory of the web application.
If you are going to use Devart .Net Data Provider for MySQL you'll need to add the following configuration in web.config:
<configuration>
 ......
 <system.data>
  <DbProviderFactories>
   <add name="dotConnect for MySQL" 
       invariant="Devart.Data.MySql" 
       description="Devart dotConnect for MySQL" 
       type="Devart.Data.MySql.MySqlProviderFactory, Devart.Data.MySql, Version=5.20.33.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
  </DbProviderFactories>
 </system.data>
 ......
</configuration>
Note:
Because Devart dotConnect for MySQL is commercial we didn't include it with the source code. So after you obtain your license you'll need to add the assemblies to the bin folder.

IMPORTANT NOTE:
Above settings for both providers is made because I don't assume that these providers are installed in GAC or configured in machine.config. So it is idle for shared hosting environment. For local development if you used Devart installer the assemblies will be added into the GAC and configuration is added to machine.config.

Add Connection String for KiGG MySQL Database

You'll need to add a connection string to ConnectionStrings section in web.config. So you'll need database name and credentials. In this documentation database name is Kigg, user id is root and password is Pa$$w0rd
<configuration>
 ......
  <connectionStrings>
    <clear/>
    .....
    <!--MySQL Connection String using Connector/net .Net Data Provider-->
    <add name="KiGGMySqlDatabase"
         connectionString="server=localhost;user id=root;password=Pa$$w0rd;persist security info=True;database=kigg"
         providerName="MySql.Data.MySqlClient"/>
    <!--MySQL Connection String using Devart dotConnect for MySql .Net Data Provider-->
    <add name="KiGGDevartMySqlDatabase"
         connectionString="server=localhost;user id=root;password=Pa$$w0rd;persist security info=True;database=kigg"
         providerName="Devart.Data.MySql"/>
  </connectionStrings>
 ......
</configuration>
Note:
Above code include 2 samples of connection MySQL using the 2 .Net Data Providers for MySQL we mentioned before. Later in configuration you'll specify the ConnectionString name that will be used.

Configuring IConnectionString depedency mapping -IEFConnectionString-

This section require that you read and applied How to configure KiGG to use Entity Framework Repository. Specifically configuration of IConnectionString interface dependency mapping under unity.
Find mapping for IConnectionString -Which is IEFConnectionString in typeAliases defined above- interface and change it
<unity>
    <typeAliases>
	....
    </typeAliases>
    <containers>
      <container>
        <types>
	  ....
	 <type type="IEFConnectionString" mapTo="EFConnectionString">
            <lifetime type="Singleton"/>
            <typeConfig 
            extensionType="Microsoft.Practices.Unity.Configuration.TypeInjectionElement, Microsoft.Practices.Unity.Configuration">
              <constructor>
                <param name="configuration" parameterType="IConfigurationManager">
                  <dependency/>
                </param>
                <param name="name" parameterType="System.String">
                  <value type="System.String" value="KiGGMySqlDatabase"/>
                </param>
                <param name="edmFilesPath" parameterType="System.String">
                  <value type="System.String" value="|DataDirectory|"/>
                </param>
                <param name="ssdlFileName" parameterType="System.String">
                  <value type="System.String" value="DomainObjects.MySql"/>
                </param>
              </constructor>
            </typeConfig>
          </type>
	  ....
	</types>		
     </container>
    </containers>
</unity>
In the above configuration for IConnectionString -Which is IEFConnectionString in typeAliases defined above- that maps to EFConnectionString Entity Framework repository type I wish to highlight the injected constructor parameters.
  1. Parameter configuration is as it is, it maps to a configured IConfigurationManager that already maps to a concrete class that is independent from Entity Framework.
  2. Parameter name, the value of this parameter is your database connection string name that you'll configure under connectionStrings element.
  3. Paremeter edmFilesPath, this is the physical path of csdl,ssdl and msl files path. By default all these files are under App_Data directory so the default value is "|Directory|"
  4. Parameter ssdlFileName, this is the name of the SSDL file without extension. Because SSDL might be specific per .Net Data Provider then we had to define this as configurable parameter. The default value maps to SqlServer specific SSDL. Here we specify MySQL SSDL related file DomainObjects.MySql.
Note: Above setttings specify KiGGMySqlDatabase as connectionString name.

Modifying SSDL file

This step is optional or mandatory depending on your database name. Open the SSDL file, for each EntitySet and Function elements make sure that Schema attribute value is the same as your database name. E.g. following the configuration mention in this article Schema attribute value should be kigg

Additional Reading

Read How to configure KiGG to use Entity Framework Repository

Note:
Sample configuration provided with source code.

Last edited Jul 1, 2009 at 6:38 PM by mosessaur, version 2

Comments

No comments yet.