Friday, May 22, 2015

How to change codes from MSSQL to MySQL

I have already developed my codes to work with MSSQL (Microsoft SQL Server) database. As I am changing my database to MySQL, I knew there are codes that I need to change. I was quite surprise that it's actually quite easy to make the change, and changes are really minimal.

1. using System.Data.SqlClient ---> using MySql.Data.MySqlClient

2. All classes to change from Sql to MySql.
For example:
SqlConnection ---> MySqlConnection
SqlDataAdapter ---> MySqlDataAdapter
SqlDataReader ---> MySqlDataReader
SqlParameter ---> MySqlParameter
SqlTransaction ---> MySqlTransaction

3. I used to use brackets to enclose my table name, just to be save. But, it seems like MySQL doesn't recognize the brackets. So, I have to remove all the brackets.
E.g.
SELECT Name FROM [Users] ---> SELECT Name FROM Users

4. To get current  UTC time:
GetUTCDate() ---> UTC_DATE()

5. To select the last inserted Id:
SELECT @@Identity ---> SELECT LAST_INSERT_ID();

NOTE:
I read that as we call from stored procedure, MySQL recognizes the parameter to prefix with ? instead of @. But, perhaps MySQL has updated this, as it works pretty fine when I am using all @ for the parameters.


No comments:

Post a Comment