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
- 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. - Add a reference to the MySQL.Data DLL in references.
- 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.
- 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;
- In DbProviderMappings.cs, add the following XML/code:
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).
- Paste this code:
public const string DefaultMySqlProviderName = "MySql.Data.MySqlClient";
under the line:
public const string DefaultOracleProviderName = "System.Data.OracleClient";
- 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:
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
