CodeProject: Intergrating MySql with Enterprise Library for .NET Framework 2.0. Free source code and programming help

Report Article

Discuss

Send to a friend

10 votes for this Article.

Popularity: 3.53 Rating: 3.53 out of 5

Note: This is an unedited contribution. If this article is inappropriate, needs attention or copies someone else's work without reference then please Report This Article

Sample Image - mysql.gif

Introduction

IntroductionI 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 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 what you are doing. :-)

Background

Microsoft's Enterprise Library for .NET Framework 2.0 is a framework to base your code around, its flexible and extendable. The Data Access Block proves a database independant implementation layer for you to add into your code, although its database independant, 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 thats 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 dont!! The more observant of you may notice 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 dont think I needed to do this, but the intergration is much tighter.

Good luck!!

Start hacking

1) Create a sub-folder called 'MySql' in the directory contain the Data Access block source, for me its; 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)

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.

4) 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;

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

Its quite clere where to add it, its at the top of the file, just under the namespace declaration (with the rest of the XML)

6) Paste this code

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

under the

public const string DefaultOracleProviderName = "System.Data.OracleClient";

line.
7) 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 statments, 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 dont 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

 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 has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Mark Pryce-Maher

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

Occupation:	Web Developer
Location:	United Kingdom

Other popular Database articles:

A set of ADO classes - version 2.20
Simple database access using an ADO class.
FileHelpers v2.0 - Delimited (CSV) or Fixed Data Import/Export Framework
An easy to use .NET library to read/write strong typed data from files with fixed length or delimited records (CSV). Also has support to import/export data from different data storages (Excel, Acces, SqlServer, MySql)
SQL Server DO's and DONT's
SQL Server database best practices
ADO Connection Strings
A list of ODBC DSN Connection Strings.
Dynamic... but fast: The tale of three monkeys, a wolf and the DynamicMethod and ILGenerator classes
How to use the DynamicMethod and ILGenerator classes to create dynamic code at runtime that out performs reflection

Article Top

You must Sign In to use this message board.

Msgs 1 to 21 of 21 (Total in Forum: 21) (Refresh)

FirstPrevNext

Subject

Author

Date

It has it errors but...

Mario Serrano (makadown)

8:17 8 Jan '08

I got intuitive and modified the parts that i thought were wrong and voilá! It works! You saved my back man! This works!
Sign In·View Thread·PermaLink

build errors

stephan restocity

0:43 17 Nov '07

Hi, I tried to integrate your code but I have many errors when I build project. DbProviderMapping.cs and DatabaseConfigurationView.cs already exist in Enterprise libraries Should I replace the existing sources by yours in zip file ? Thanks for your help. Stephan. .
Sign In·View Thread·PermaLink

Hot to add encode utf-8 or gb2312 to the block?

wishsky

1:31 20 Oct '07

I do as folowing you do,but when i got the mysql data ,The decoding is wrong ,the data contain chinese word(utf-8 or gb2312) . but if i got the data use ODBC , it work better. What can i do to make it work? i am very confused,does any one can help me ?
Sign In·View Thread·PermaLink

what can i do for it to make GB2312 work?

wishsky

1:27 20 Oct '07

I do as folowing you do,but when i got the mysql data , It is decode was wrong ,the data contain chinese word . but if i got the data use ODBC , i work right. What can i do to make i work? i am very confused,any one can help me ?
Sign In·View Thread·PermaLink

Enterprise 3.1

Ricardo Luceac

3:44 16 Oct '07

Does it works with the version 3.1 of enterprise library???
Sign In·View Thread·PermaLink

Re: Enterprise 3.1

Mark Pryce-Maher

4:14 16 Oct '07

Simple answer is 'yes' and 'no'. I did get this running on EntLib 3.1 - I followed my article and found out it wasn't quite right, I missed off a using (somewhere) and something else (I cant remember). I intended to update the article but never got around to it. So Yes, it will work, but not out of the box (so to speak).
Sign In·View Thread·PermaLink

Re: Enterprise 3.1

exmarouane2

2:13 26 Mar '08

EntLib Contrib project Supports MySql and it also works with MEL 3.1. EntLib Contrib project has MySql Support[^]
Sign In·View Thread·PermaLink

Error while running the demo application

RajendranK

2:39 6 Oct '07

hello i am getting the following error while running the sample application "Could not load file or assembly 'MySql.Data, Version=5.1.3.0, Culture=neutral, PublicKeyToken=35d2c96540b8969e' or one of its dependencies. An attempt was made to load a program with an incorrect format." Please help me on this. Raja Rajendran K Software Developer, SHLOK Information Systems,
Sign In·View Thread·PermaLink

Re: Error while running the demo application

Mark Pryce-Maher

23:03 8 Oct '07

I'll have a quick look today at the issue.... It looks like you dont have the MsSql.dll on your machine... (I may have forgotten to include the DLL in the project)
Sign In·View Thread·PermaLink

Good and Bad Points

Ricardo Casquete

0:59 8 May '07

Hi Mark...

First of all, I´d like to say that I like what you have done, cause I have done something similar with the ODP.NET driver for Oracle (Oracle data Access Provider, developed by Oracle).

I am not an expert, but I don't like the idea of anybody modifiying the Enterprise Libary, mostly becuase you lost some scalability and extensibility; Now you have to redistribute this new version of the Enterprise Library and you have to mofify the new versions.

All Database Data Provider has something in Common, they have an insntace of System.Data.Comon.DBProviderFactory, as the MySQL have as you well know; all the Enterprise Libary needs to start is that.

You could have developed the sames classes outside of your project; have a look to the SQLDatabase and OracleDatabase in the Enterprise Library, and then in the Enterprise Library Configuration Application, Load Assembly (and select your database there) With that you will have all your code in "Your" DLL as part of your project (or your project's framework) that would have been much easy to DEBUG and to Mantain.

The best bit from my point of view, is that you are really adding funcionality to the Enterprise Library, you have really extended it more than developing a plugable dll....

Very Good Work Mark!!!!

Ricardo Casquete

Problems hacking...

eduardo_g

1:58 7 May '07

Hi Mark, Sorry to bother you i'v try everithing but i cant get it. First i try with the entlib 3.0 for about 5 hrs and i give up and now i am tring with the same you talk in the article. I a a bit stupid i know but what do you mean to put the source into the MySQL folder? the sorce code files of the articule? and then do i have to add the folder to the project? Thanks a lot.
Sign In·View Thread·PermaLink

Re: Problems hacking...

Mark Pryce-Maher

23:18 7 May '07

Firstly, I've got it working on Entlib 3.0 - I followed my own article and it didnt work!!!
I found out I missed out a few 'using' statements - but other than that it worked.

The mysql folder was a folder I created in the entlib directory structure, it was just a way of keeping the mysql code in a seperate place to all the microsoft source.

The mysql folder should be at the same level as the oracle and sql folders. (it being just another data provider)

I've zipped up the whole 'data' directory for you. Hopefully you just need to drop it over the top of your own data directory in the entlib - then compile.

I'll put it somewhere public later, so you can download it. I'll post back with the URL.

Mark

Re: Problems hacking...

Mark Pryce-Maher

0:05 8 May '07

I've put the files here. Let me know how it goes. Mark
Sign In·View Thread·PermaLink

Re: Problems hacking...

dilipakumara

2:37 9 May '07

please send me that new changed url thanks dilip kumars
Sign In·View Thread·PermaLink

testing demo

lenkry

7:21 25 Oct '06

When i try your demo he's failing .. Some error : Could not load file or assembly 'MySql.Data, Version=5.0.0.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies. How can I solve this problem ?
Sign In·View Thread·PermaLink

Re: testing demo

lenkry

7:37 26 Oct '06

Wrong version of MySql.Data.dll it was but I have a problem with filling a dataset .. Database db = DatabaseFactory.CreateDatabase("MySql"); String sql = "SELECT * FROM table"; tableDataset ds = new tableDataset(); DbCommand cmd = db.GetSqlStringCommand(sql); db.LoadDataSet(cmd, ds, "table"); The last line isn't working, with Sql it works. Why it doesn't work on MySql ?? Help please
Sign In·View Thread·PermaLink	2.00/5 (1 vote)

Link error

theDiver

4:37 13 Sep '06

This link 'Microsoft's Enterprise Library for .NET Framework 2.0' does not work. Does this project allow you to drag and drop tables directly from the server explorer into your application and dataset ?
Sign In·View Thread·PermaLink

Re: Link error

Mark Pryce-Maher

7:30 14 Sep '06

Thanks for spotting the link didnt work. I sort that right away. Nope sorry, it wont let you do that. Thanks Mark
Sign In·View Thread·PermaLink

Re: Link error

Mark Pryce-Maher

5:49 15 Sep '06

This project doesn't do the IDE intergration you want, but this might: MySQL Tools for Visual Studio (Alpha).[^]. I've just downloaded it, but I've not had any time to play with it yet. Good Luck! Mark
Sign In·View Thread·PermaLink

Re: Link error

theDiver

9:23 15 Sep '06

Looks nice, Thanks a lot for the tip I will for sure take a look at it.
Sign In·View Thread·PermaLink	2.00/5 (1 vote)

Re: Link error - drag drop tables

Mark Pryce-Maher

6:58 15 Sep '06

I've just downloaded this; Core Lab MySQL Developer Tools for Visual Studio[^] It does what you said you need, but you have to pay for it. Mark
Sign In·View Thread·PermaLink

Last Visit: Friday, July 4, 2008 7:43 PM Last Update:Friday, July 4, 2008 7:43 PM

General News Question Answer Joke Rant Admin