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

Intergrating MySql with Enterprise Library for .NET Framework 2.0

, 7 May 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
An article on adding a MySql connector into Enterprise Library for .NET Framework 2.0
Sample Image - mysql.gif

Introduction

I have been looking at Microsoft's Enterprise Library for .NET Framework 2.0[^] and I wondered how difficult it would be to extend the Data Access Application Block to include MySQL as one of the databases.

Standing on the Shoulders...

I found Benjamin Mayrargue's article: MySQL connector for Microsoft Enterprise Library (January 2005 version). I followed the steps he took, just to see if I could get it to work.
It didn't because it was written for a previous version of the Enterprise Library, but his article gave me all the information I needed to have a stab at doing it myself.

I also looked at the way the Oracle and SQL Server blocks were built, copied how they were built.

Assumptions

I have made a few assumptions;

  • Have Visual Studio 2005 installed. (I have the full version)
  • Install the Enterprise Blocks for .NET 2[^] (check whether they compile before you start messing with them!!)
  • Install the MySQL Connector/NET 5.0[^] (I used the Alpha release, I don't know if it will work with the 1.0 connector)
  • Finally... have a rough idea about what you are doing. Smile | :)

Background

Microsoft's Enterprise Library for .NET Framework 2.0 is a framework to base your code around, it's flexible and extendable. The Data Access Block proves a database independent implementation layer for you to add into your code. Although it's database independent, there are only two existing providers supplied with the data access block, SQL Server and Oracle. This article shows you how to add MySql to this list, infact using the ideas from the article it should be possible to implement any .NET database connector as a Data Access Block (well, that's the idea... But actually doing it might be quite tricky).

Before We Start...

Just a quick warning before we start, this code works for me and my uses; connecting to MySql and running simple stored procedures (passing in parameters). I've not done a huge amount of work testing all the features of the MySql Data block work.... To be honest, they don't!! The more observant of you may notice that there are some references to the MSSQL objects in the MySQL class - I'll get around to fixing it sometime or someone else might do it for me!! Smile | :)

Getting Down and Dirty...

We have to go through a number of steps. They involve adding a couple of new classes (MySql Data Blocks) and some modifications to the Data Blocks themselves (which I have supplied in the source zip file). From looking through the code, I don't think I needed to do this, but the integration is much tighter.

Good luck!!

Start Hacking

  1. Create a sub-folder called 'MySql' in the directory containing the Data Access block source, for me it is C:\Program Files\Microsoft Enterprise Library January 2006\Src\Data\
    Download the source, and copy them into the 'mysql' folder.
  2. Add a reference to the MySQL.Data DLL in references.
  3. Time to hack the Data Access block.
    In the method (in the file DatabaseConfigurationView.cs):
  4. private DbProviderMapping GetDefaultMapping(string name, string dbProviderName)

    add the following code:

    if (DbProviderMapping.DefaultMySqlProviderName.Equals(dbProviderName))    
                return defaultMySqlMapping;

    It should be obvious where it goes.

  5. In the file DatabaseConfigurationView.cs add the following line:
    private static readonly DbProviderMapping defaultMySqlMapping = 
     new DbProviderMapping(DbProviderMapping.DefaultMySqlProviderName, 
     typeof(MySqlDatabase));
    private IConfigurationSource configurationSource;     

    So the top of the file reads:

    public class DatabaseConfigurationView
    {
        private static readonly DbProviderMapping defaultSqlMapping = 
        	new DbProviderMapping(DbProviderMapping.DefaultSqlProviderName, 
    	typeof(SqlDatabase));
        private static readonly DbProviderMapping defaultOracleMapping = 
    	new DbProviderMapping(DbProviderMapping.DefaultOracleProviderName, 
    	typeof(OracleDatabase));
        private static readonly DbProviderMapping defaultGenericMapping = 
    	new DbProviderMapping(DbProviderMapping.DefaultGenericProviderName, 
    	typeof(GenericDatabase));
        private static readonly DbProviderMapping defaultMySqlMapping = 
    	new DbProviderMapping(DbProviderMapping.DefaultMySqlProviderName, 
    	typeof(MySqlDatabase));
        private IConfigurationSource configurationSource;
  6. In DbProviderMappings.cs, add the following XML/code:
    /// <item>For provider name "System.Data.SqlClient", 
    /// or for a provider of type <see cref="System.Data.SqlClient"/>, the
    /// <see cref="Microsoft.Practices.EnterpriseLibrary.Data.MySql.MySqlDatabase"/> 
    /// will be used.</item>    

    It's quite clear where to add it, it's at the top of the file, just under the namespace declaration (with the rest of the XML).

  7. Paste this code:
    /// <summary>
    /// Default name for the MySQL managed provider.
    /// </summary>
    public const string DefaultMySqlProviderName = "MySql.Data.MySqlClient";

    under the line:

    public const string DefaultOracleProviderName = "System.Data.OracleClient";
  8. You will have to add a using MySql.Data.MySqlClient; to DatabaseConfigurationView.cs and DbProviderMappings.cs to get it to compile.

Bob's Your Uncle

Well almost, I've tested normal SQL statements, they work, and stored procs work too. Well, in the limited way, I've tested them. Now you give it a go...

I've create a simple demo project to go with the code. Have fun and let me know how you get on! I've not supplied any SQL code (hopefully you should be able to get it working with your own!)

Points of Interest

As well as the MySql classes, I've included the two from the Data Access Block that I changed, so you don't have to do the typing yourselves. Smile | :)

I found using the Application Blocks very easy, much easier than I expected, so go on, give it a try.

I've been doing some playing since I wrote this, and I've had some issues with MySql .NET provider having not being (fully) CLS-compliant [MySqlDbType]. Klaus Frederiksen has already answered this on the MySql Forums.

The Test App

In the test app, the first line which sets up the database connection is:

 Database db = DatabaseFactory.CreateDatabase("MySql");

The MySql bit refers to name in the following line in the app.config:

<add name="MySql" connectionString="Data Source=YourServerName;
	Database=YourDataBase;User ID=YourUserID;Password=YourPassword;"
      providerName="MySql.Data.MySqlClient" />

History

  • V1.0 First release

License

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

Share

About the Author

Mark Pryce-Maher
Web Developer
United Kingdom United Kingdom
I've been a programmer for longer than I care to remember. It all started with those BBC (model B's) at school, it all went down hill from there.....
 
This year, I am mostly coding in.. C# (WPF & WCF)
 
Blog Address http://markpm.blogspot.com
 

Comments and Discussions

 
GeneralError: Argument type 'MySql.Data.MySqlClient.MySqlDbType' is not CLS-compliant PinmemberUtkanSargIn10-Jun-09 5:20 
GeneralRe: Error: Argument type 'MySql.Data.MySqlClient.MySqlDbType' is not CLS-compliant PinmemberJunior Mayhe7-Aug-09 13:52 
GeneralIt has it errors but... PinmemberMario Serrano (makadown)8-Jan-08 7:17 
GeneralRe: It has it errors but... Pinmemberdashrath2-Nov-08 22:00 
Generalbuild errors Pinmemberstephan restocity16-Nov-07 23:43 
GeneralRe: build errors Pinmemberdashrath2-Nov-08 21:59 
QuestionHot to add encode utf-8 or gb2312 to the block? Pinmemberwishsky20-Oct-07 0:31 
Questionwhat can i do for it to make GB2312 work? Pinmemberwishsky20-Oct-07 0:27 
GeneralEnterprise 3.1 PinmemberRicardo Luceac16-Oct-07 2:44 
GeneralRe: Enterprise 3.1 PinmemberMark Pryce-Maher16-Oct-07 3:14 
GeneralRe: Enterprise 3.1 Pinmemberexmarouane226-Mar-08 1:13 
QuestionError while running the demo application PinmemberRajendranK6-Oct-07 1:39 
AnswerRe: Error while running the demo application PinmemberMark Pryce-Maher8-Oct-07 22:03 
GeneralGood and Bad Points PinmemberRicardo Casquete7-May-07 23:59 
QuestionProblems hacking... Pinmembereduardo_g7-May-07 0:58 
AnswerRe: Problems hacking... PinmemberMark Pryce-Maher7-May-07 22:18 
GeneralRe: Problems hacking... PinmemberMark Pryce-Maher7-May-07 23:05 
GeneralRe: Problems hacking... Pinmemberdilipakumara9-May-07 1:37 
Generaltesting demo Pinmemberlenkry25-Oct-06 6:21 
GeneralRe: testing demo Pinmemberlenkry26-Oct-06 6:37 
GeneralLink error PinmembertheDiver13-Sep-06 3:37 
GeneralRe: Link error PinmemberMark Pryce-Maher14-Sep-06 6:30 
AnswerRe: Link error PinmemberMark Pryce-Maher15-Sep-06 4:49 
GeneralRe: Link error PinmembertheDiver15-Sep-06 8:23 
AnswerRe: Link error - drag drop tables PinmemberMark Pryce-Maher15-Sep-06 5:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.141022.2 | Last Updated 8 May 2007
Article Copyright 2006 by Mark Pryce-Maher
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid