Connect to MySQL from Microsoft Data Application Block

Question

lI am using the Data Application block for a majority of my data access, specifically using the SqlHelper class to call the ExecuteReader, ExecuteNonQuery, and like methods. Passing the connection string with each database call.

How can I modify this to enable connection to a MySQL database as well.

Answer 1

If you've got the Enterprise Library installed and already know how to connect to SQL Server databases, connecting to MySQL databases is not any harder.

One way to do it is to use ODBC. This is what I did:

1. Go to MySQL.com and download the latest MySQL ODBC connector. As I write this it's 5.1.5. I used the 64-bit version, as I have 64-bit Vista.
2. Install the ODBC Connector. I chose to use the no-installer version. I just unzipped it and ran Install.bat at an administrator's command prompt. The MSI version probably works fine, but I did it this way back when I installed the 3.51 connector.
3. Verify the installation by opening your ODBC control panel and checking the Drivers tab. You should see the MySQL ODBC 5.1 Driver listed there. It seems to even co-exist peacefully with the older 3.51 version if you already have that. Additionally it coexists peacefully with the .NET connector if that is installed too.
4. At this point you will be doing what you've done to connect to a SQL Server database. All you need to know is what to use for a connection string.
5. Here's what mine looks like:
6. Of course you can set "name" to whatever you want.
7. If this is your only database, you can set it up as the defaultDatabase like this:
8. Access your data in your code like you always do! Here's a plain text sql example:

    public List<Contact> Contact_SelectAll()
    {
        List<Contact> contactList = new List<Contact>();
        Database db = DatabaseFactory.CreateDatabase("MySqlDatabaseTest");
        DbCommand dbCommand = db.GetSqlStringCommand("select * from Contact");
        using (IDataReader dataReader = db.ExecuteReader(dbCommand))
        {
         while (dataReader.Read())
         {
          Contact contact = new Contact();
          contact.ID = (int) dataReader["ContactID"];
          client.FirstName = dataReader["ContactFName"].ToString();
          client.LastName = dataReader["ContactLName"].ToString();
          clientList.Add(client);
         }
        }

        return clientList;
    }

Another way to do it is to build and use a MySql provider. This guy did that. I learned how to do this by adapting these instructions for connecting to Access. Oh, and here are some more MySql Connection String samples.