Conversion from C#/SQL Server 2005 to C#/MySQL?

Question

I want to convert a Job Search Site developed in C# for SQL Server 2005 to MySQL.Could someone give me suggestions what steps do i have to take.
There is no support of stored procedures in MySQL and data transactions are carried out through stored procedures in the existing code.

The code for the Job Search Site can be downloaded from this site:
http://asp.net/downloads/starterkits/default.aspx?tabid=62


Thankyou very much, regards
MaqsoodurRehman

Answer 1

first off, I've been exactly where you are. Second, MySQL 5 and later does indeed support stored procedures.

Your hardest part will be making sure to substitute all of the proper types during conversion(Watch nvarchars for when they need to be varchar(255) or Text). My solution was to translate each table first, then I used Toad for MSSQL to export everything as a tab delimited file. I then imported using the import tool of Toad for MySQL into a 5.0.27 MaxDB MySQL installation. Watch your indexes!

Next download the asp ado.net adapter from MySQL. Change your connection manager and your configuration file. Convert your stored procedures but be very careful here. Postgres SQL (the language underneath MySQL) does not support the Transact SQL method of selecting tables into anything. To make your temporary tables you'll have to get a little more creative and creat all of your tables first, and then truncate from them. That said, the overhead you'll save by not creating and deleting tables every time you run a stored proc will probably be tremendous.

After the stored procs and the data has been subbed, check formats like datetime, money and other conversions you've had to make to make sure you don't have to write from scratch any conversion methods. test your stored procs in a sample db and then let fly with the debug.

Good luck, it took me about two full days and then two days of debugging to convert about 100 tables and a 4 million plus rows, but in the end things worked out well.

Answer 2

use Mysql migration Toolkit for database migration. u have to figure out a alternative way for stored procedures.