How to connect to Mysql using C#?

Question

I'm just a beginner in C#. I'm using XAMPP server for MySQL database and Visual C# 2010. Then I have created a database named "testdb" in phpMyAdmin and a table named "login". I have inserted my username and password in the table. I'm doing a simple WinForm login where I made two text boxes for username and password and a button. I have my codes done and there's no compiler error. But I had troubled in one line. It says "Unable to connect to any of the specified MySQL hosts". I added MySql.Data to my references. I want to fetch the data in the database table when I'm going to log in. Then authorize the user or if not matched, it will prompt an error message.

Here is my code:

using MySql.Data.MySqlClient; 

public bool Login(string username, string password)
{
    MySqlConnection con = new MySqlConnection("host=localhost;username…");
    MySqlCommand cmd = new MySqlCommand("SELECT * FROM login WHERE username='" +
                                      username + "' AND password='" + password + "';");

    cmd.Connection = con;

    con.Open(); // This is the line producing the error.

    MySqlDataReader reader = cmd.ExecuteReader();

    if (reader.Read() != false)
    {    
        if (reader.IsDBNull(0) == true)
        {
            cmd.Connection.Close();
            reader.Dispose();
            cmd.Dispose();
            return false;
        }    
        else
        {
            cmd.Connection.Close();
            reader.Dispose();
            cmd.Dispose();
            return true;
        }    
    }    
    else
    {
        return false;
    }    
}

*I hope for your your feedback. :)

Post the entire connection string (redact the password of course) — Adam Schiavone, Feb 10 '13 at 20:16
Consider ASP.NET Dynamic Data if you are trying to produce a web interface for CRUD access to the database. http://msdn.microsoft.com/en-us/library/ee845452(v=vs.100).aspx — Aron, Feb 10 '13 at 20:21
"I added MySql.Data to my references", saying this I assume you have installed "MySQL Connector/net". Can you add **MySql.DafaFC** instead of **MySql.Data**? The same thing worked for me on VS 2013 express. Also no need to use "MySql for visual studio plugin". — Musa Haidari, Jun 23 '14 at 06:23
since your using **XAMPP** don't forget to add **MySql.Web** to your references — Ramgy Borja, Aug 30 '17 at 00:48

score 17 · Answer 1 · edited May 23 '17 at 12:33

Your immediate problem is probably either an incorrect connection string or the database server is not available. The connection string should be something like this

Server=localhost;Database=testdb;Uid=<username>;Pwd=<password>;

with <username> and <password> replaced with your actual values.

Besides that your code has several issues and you should definitely look into them if this is intended to become production code and probably even if this is just a toy project to learn something. The list is in particular order and may not be comprehensive.

Do not hard code your connection string. Instead move it to a configuration file.
Do not include plain text passwords in configuration files or source code. There are various solutions like windows authentication, certificates or passwords protected by the Windows Data Protection API.
Do not just dispose IDisposable instances by calling IDisposable.Dispose(). Instead use the using statement to release resources even in the case of exceptions.
Do not build SQL statements using string manipulation techniques. Instead use SqlParameter to prevent SQL injection attacks.
Do not store plain text passwords in a database. Instead at least store salted hashes of the passwords and use a slow hash function, not MD5 or a member of the SHA family.
You can use IDbCommand.ExecuteScalar to retrieve a scalar result and avoid using a data reader.
Comparing a boolean value with true or false is redundant and just adds noise to your code. Instead of if (reader.IsDBNull(0) == true) you can just use if (reader.IsDBNull(0)). The same holds for if (reader.Read() != false) what is equivalent to if (reader.Read() == true) and therefore also if (reader.Read()).
Using an O/R mapper like the Entity Framework is usually preferred over interacting with the database on the level of SQL commands.

Mateus Schneiders · Answer 2 · 2013-02-11T01:53:48.850

2

Try modifying your ConnectionString accordingly to the Standard MySQL ConnectionString:

Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Source: MySQL ConnectionStrings

You can also take a look at the following link, that shows how to connect to a MySQL database using C#:

Creating a Connector/Net Connection String (MYSQL)

edited Feb 11 '13 at 01:53

answered Feb 10 '13 at 20:22

Mateus Schneiders

4,853
3
20
40

Ramgy Borja · Answer 3 · 2017-08-30T01:05:57.170

Make it simple and sql injection free, and also don't forget to add MySql.Web in your references since your using XAMPP

        public bool Login(string username, string password)
        {
            DataTable dt = new DataTable();
            string config = "server=....";
            using (var con = new MySqlConnection { ConnectionString = config })
            {
                using (var command = new MySqlCommand { Connection = con })
                {
                    con.Open();
                    command.CommandText = @"SELECT * FROM login WHERE username=@username AND password=@password";
                    command.Parameters.AddWithValue("@username", username);
                    command.Parameters.AddWithValue("@password", password);
                    dt.Load(command.ExecuteReader());
                    if (dt.Rows.Count > 0)
                        return true;
                    else
                        return false;

                } // Close and Dispose command

            } // Close and Dispose connection
        }

How to connect to Mysql using C#?

3 Answers3

Linked