Click here to Skip to main content
15,868,016 members
Articles / Database Development / MySQL

Intergrating MySql with Enterprise Library for .NET Framework 2.0

Rate me:
Please Sign up or sign in to vote.
3.73/5 (12 votes)
7 May 2007CPOL4 min read 125.4K   2.5K   44   25
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. :-)

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!! :-)

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. C#
    private DbProviderMapping GetDefaultMapping(string name, string dbProviderName)

    add the following code:

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

    It should be obvious where it goes.

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

    So the top of the file reads:

    C#
    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:
    C#
    /// <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:
    C#
    /// <summary>
    /// Default name for the MySQL managed provider.
    /// </summary>
    public const string DefaultMySqlProviderName = "MySql.Data.MySqlClient";

    under the line:

    C#
    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. :-)

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:

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

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

XML
<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)


Written By
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 Pin
UtkanSargIn10-Jun-09 5:20
UtkanSargIn10-Jun-09 5:20 
GeneralRe: Error: Argument type 'MySql.Data.MySqlClient.MySqlDbType' is not CLS-compliant Pin
Junior Mayhe7-Aug-09 13:52
Junior Mayhe7-Aug-09 13:52 
GeneralIt has it errors but... Pin
Mario Serrano (makadown)8-Jan-08 7:17
Mario Serrano (makadown)8-Jan-08 7:17 
GeneralRe: It has it errors but... Pin
dashrath2-Nov-08 22:00
dashrath2-Nov-08 22:00 
Generalbuild errors Pin
stephan restocity16-Nov-07 23:43
stephan restocity16-Nov-07 23:43 
GeneralRe: build errors Pin
dashrath2-Nov-08 21:59
dashrath2-Nov-08 21:59 
QuestionHot to add encode utf-8 or gb2312 to the block? Pin
wishsky20-Oct-07 0:31
wishsky20-Oct-07 0:31 
Questionwhat can i do for it to make GB2312 work? Pin
wishsky20-Oct-07 0:27
wishsky20-Oct-07 0:27 
GeneralEnterprise 3.1 Pin
Ricardo Luceac16-Oct-07 2:44
Ricardo Luceac16-Oct-07 2:44 
GeneralRe: Enterprise 3.1 Pin
Mark Pryce-Maher16-Oct-07 3:14
Mark Pryce-Maher16-Oct-07 3:14 
GeneralRe: Enterprise 3.1 Pin
exmarouane226-Mar-08 1:13
exmarouane226-Mar-08 1:13 
QuestionError while running the demo application Pin
RajendranK6-Oct-07 1:39
RajendranK6-Oct-07 1:39 
AnswerRe: Error while running the demo application Pin
Mark Pryce-Maher8-Oct-07 22:03
Mark Pryce-Maher8-Oct-07 22:03 
GeneralGood and Bad Points Pin
Ricardo Casquete7-May-07 23:59
Ricardo Casquete7-May-07 23:59 
QuestionProblems hacking... Pin
eduardo_g7-May-07 0:58
eduardo_g7-May-07 0:58 
AnswerRe: Problems hacking... Pin
Mark Pryce-Maher7-May-07 22:18
Mark Pryce-Maher7-May-07 22:18 
GeneralRe: Problems hacking... Pin
Mark Pryce-Maher7-May-07 23:05
Mark Pryce-Maher7-May-07 23:05 
GeneralRe: Problems hacking... Pin
dilipakumara9-May-07 1:37
dilipakumara9-May-07 1:37 
Generaltesting demo Pin
lenkry25-Oct-06 6:21
lenkry25-Oct-06 6:21 
GeneralRe: testing demo Pin
lenkry26-Oct-06 6:37
lenkry26-Oct-06 6:37 
GeneralLink error Pin
theDiver13-Sep-06 3:37
theDiver13-Sep-06 3:37 
GeneralRe: Link error Pin
Mark Pryce-Maher14-Sep-06 6:30
Mark Pryce-Maher14-Sep-06 6:30 
AnswerRe: Link error Pin
Mark Pryce-Maher15-Sep-06 4:49
Mark Pryce-Maher15-Sep-06 4:49 
GeneralRe: Link error Pin
theDiver15-Sep-06 8:23
theDiver15-Sep-06 8:23 
AnswerRe: Link error - drag drop tables Pin
Mark Pryce-Maher15-Sep-06 5:58
Mark Pryce-Maher15-Sep-06 5:58 

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

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