How to connect to mysql using c #?

Question

I'm just new to C #. I use the XAMPP server for the MySQL database and Visual C # 2010. Then I created a database called "testdb" in phpMyAdmin and a table called "login". I inserted my username and password into the table. I make a simple login to WinForm, where I made two text fields for username and password and button. I have my codes and there is no compiler error. But I was worried about one line. It says: "Unable to connect to any of the specified MySQL nodes." I have added MySql.Data to my links. I want to get data in a database table when I log in. Then authorize the user or, if he is not mapped, he will give 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 feedback. :)

Answer 1

Perhaps your immediate problem is with the wrong connection string or database server. 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.

In addition, your code has several problems, and you should definitely study them if it is intended to create production code and, possibly, even if it is just a toy project to learn something. The list is in a specific order and cannot be exhaustive.

• Do not print the line connection string. Instead, move it to the configuration file.
• Do not include text passwords in configuration files or source code. There are various solutions, such as Windows authentication, certificates, or password-protected using the Windows Data Protection API .
• Don't just delete instances of IDisposable by calling IDisposable.Dispose() . Instead, use the using statement to release resources even in the event of exceptions.
• Do not create SQL statements using string manipulation methods. Use SqlParameter to prevent SQL injection attacks.
• Do not store text passwords in the database. Instead, at least store salted password hashes and use a slow hash function, not MD5 or a member of the SHA family.
• You can use IDbCommand.ExecuteScalar to get a scalar result and not use 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 simply use if (reader.IsDBNull(0)) . The same is true for if (reader.Read() != false) , which is equivalent to if (reader.Read() == true) and therefore also if (reader.Read()) .
• Using an O / R module such as the Entity Framework is usually preferable to interacting with a database at the SQL command level.

Answer 2

Try changing your ConnectionString 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, which shows how to connect to a MySQL database using C #:

Create Connector / Network String (MYSQL)

Answer 3

Make it simple and sql injection for free, and also don't forget to add MySql.Web in your links as you use 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 }