Migrating Code From SQL Server To MySql

After having worked exclusively with Microsoft’s SQL Server for some years now I was working on a project in which the customer wanted the data to be stored in their database of choice, MySql. My first thought was to utilize the ODBC client code in the .Net framework instead of my familiar SQL Client components. That should mean a drop in placement with just a few name changes.

My fears were raised a bit when I saw that the MySql folks had their own .Net libraries (referred to as connectors). Worse, when I tried to download just those libraries I was coaxed into creating an Oracle login account and download either a full suite of MySql products or all of the client libraries and tools. Not wanting another server on my laptop I went with the smaller, client only, option.

In a word my experience with the MySql client code has been wonderful. Here are the steps needed to take a simple program that’s working against SQL Server and get it up and running with a MySql database.

Add a reference to the MySql.Data.dll library in your project.
Add a using statement referring to “MySql.Data.MySqlClient” in your source file.
Replace the SQL Server data commands with their MySql equivalents.

Here’s a snippet of code that was written for SQL Server:

using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(sqlCommand, conn))
{
cmd.Parameters.AddWithValue("@param1", Param1);
using (SqlDataReader reader = cmd.ExecuteReader())
{
...

And now in MySql it looks like:

using (MySqlConnection conn = new MySqlConnection(ConnectionString))
{
conn.Open();
using (MySqlCommand cmd = new MySqlCommand(sqlCommand, conn))
{
cmd.Parameters.AddWithValue("@param1", Param1);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
...

That’s it! The code flow is exactly the same. Since the MySql libraries inherit from the same base .Net framework classes and interfaces it’s an effective drop in replacement for SQL Server.

Of course, SQL Server and MySql do not share the same quirks when it comes to SQL standards. Anything other than the simplest queries will have to be modified to function properly.

Amongst the tools installed with the libraries was MySql Workbench. I figured that my web provider’s web based tools for database creation and management would suffice. And they probably would have for my limited requirements. However, once I started up Workbench I quickly set aside my intention of doing the database management via the simpler web tools.

MySql Workbench is a very functional MySql-based replacement for everything that I’ve used SQL Server Management Studio to do with SQL Server. Though the feel and flow of operations is a bit different it’s not too terribly hard to get used to. Plus all of the functionality that you want from such as tool from schema development to adhoc query tuning is there.

Leave a Reply Cancel reply

Categories

Meta