Connecting to MYSQL from a c# application

Question

Hi, the underlying database on a project has changed from sql 2005 to MySql 5.1 The code has loads of method similar to below. I'm presuming it is just a case of switching the 'con' variable SqlConnection to a MYSql specific connection. Has anyone had any experience with this? I have never touched a mySql db. Any help much appreciated.

private SqlConnection con;

    public User LogonUser(string pUserName, string pPassword)
    {

        con = new SqlConnection();
        con.ConnectionString = DatabaseConstants.DB_CONN_STRING;


        using (con)
        {
            con.Open();
            var command = new SqlCommand();


            command.Connection = con;
            command.CommandText = "SELECT id FROM Users WHERE userName = @userName AND password = @password";
            command.CommandType = CommandType.Text;

            var userName = new SqlParameter("@userName", pUserName);
            var password = new SqlParameter("@password", pPassword);

            command.Parameters.Add(userName);
            command.Parameters.Add(password);


            User user;
            var dr = command.ExecuteReader();
            if (dr != null)

                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        user = new User();
                        user.id = dr.GetString(0);
                        return user;
                    }
                }
                else
                {
                    throw new Exception("Can not find user, please check your username and password");
                }
        }
        return null;
    }

Answer 1

You got it partially correct, but you will need an instance of the MySQL Provider, not the SqlConnection. Also you will have to change any SQL that isn't compatible with MySQL.

Answer 2

Downloadable SQL Connectors are available for various frameworks and platforms - in this case assemblies to reference into your .NET project under the guise of ADO.NET are available from MySql. Can program against them using any .NET language.

Start in C# by referencing the MySql namespace:

using MySql.Data.MySqlClient;

and change over your ADO.NET class names from SqlConnection to MySqlConnection, etc. Google Code examples show cursory usage (similar to other ADO.NET providers), and of course the MySql docs are the best reference.

Answer 3

No, you have to also change this line

var command = new SqlCommand();

to

var command = new con.CreateCommand();

and of course you have to change any specific T-SQL and MSSQL features to MySQL. Date and time function, stored procedure, and parameter binding(? instead of @) are a few things that you need to closely check.