Migrating C# ADO.NET Applications from SQL Server to MySQL

ADO.NET allows you to access different databases from C# applications using a similar set of classes. But unlike ODBC where you can use functions having exactly the same names, different ADO.NET Data Providers use different namespaces and class names.

For example, you have to use System.Data.SqlClient namespace for SQL Server and MySql.Data.MySqlClient for MySQL. Classes are also different, for example, SqlConnection, SqlCommand etc. in SQL Server, and MySqlConnection, MySqlCommand etc. in MySQL.

So migration of C# ADO.NET applications from SQL Server to MySQL involves mapping of ADO.NET provider classes and methods (for some classes) as well as conversion of embedded SQL statements (dynamic SQL).

Microsoft provides ADO.NET Data Provider for SQL Server (System.Data.SqlClient namespace) to access SQL Server. To access MySQL, you can use MySQL Connector/NET, a native ADO.NET Data Provider for MySQL (MySql.Data.MySqlClient namespace).

Another way to access MySQL from C# applications is to use ADO.NET Data Provider for ODBC (System.Data.Odbc namespace), a provider that wraps MySQL ODBC Driver (that wraps MySQL C API on its turn).

C# application migration consists of relatively easy and quite challenging tasks.

Relatively Easy Conversion Tasks:

• Changing Connection String
• Mapping Basic ADO.NET Classes (Connection, Command, DataReader etc)
• Simple SQL language elements and built-in functions that have different syntax in SQL Server and MySQL (for example, ISNULL() to IFNULL() conversion)

Migration of your C# applications from SQL Server can be very simple or very complex, and it depends not only on the total number of lines of C# code containing ADO.NET and dynamic SQL, but also on specific SQL issues and how your database access code is organized.

For C# application migration from SQL Server to MySQL migration, the issues that affects the complexity are as follows:

• Total number of lines of C# code containing ADO.NET and dynamic SQL
• Use of old-style join syntax and functions that do not have direct equivalent in MySQL
• Use of Transact-SQL blocks in C# application (sending a batch with flow-of-control statements, variable declarations rather than a single statement)
• Use of SqlBulkCopy class

As shown above, migration greatly depends on the application, so performing a migration assessment is crucial.

The goal of the assessment is to identity what conversion issues actually exist in the application and require attention. As a result, the assessment allows you to estimate the cost and effort of the migration.

For C# migration from SQL Server to MySQL, the assessment includes:

• Identifying C# source files containing database access statements (ADO.NET references and dynamic SQL)
  
  In a typical database-driven application, you may have up to 10-20% of files containing database related code (except batch processing utilities that may contain SQL code in every file). So it is worth separating them from other source files.
  
• Identifying lines of code with database related statements
• Identifying SQL statements that are easy to convert
• Identifying challenging SQL statements

• MySQL ADO.NET Provider - MySQL Connector/NET (Download page)
• MySQL 5.5 Connector/NET Documentation

We also offer services to convert your C# applications from SQL Server to MySQL at highly competitive and reasonable fixed price. For more information, see Services