Connecting MySQL Server 8.0 using C#

Question

I'm trying to connect MySQL Server 8.0 running on windows to C#. Before this, I was using PhpMyAdmin but now I've installed MySql 8.0 Command Line Client and I want to connect my program with it. It is running on port 2208 as port 3306 was already in use.

I've tried to search internet for solution but all the solution that appears are connecting MySQL with PhpMyAdmin.

        server = "localhost:2208";
        database = "LoginFormApplication";
        uid = "root";
        password = "";
        connectionString = "SERVER=" + server + ";" + "DATABASE=" +
        database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
        Debug.WriteLine(connectionString);

        connection = new MySqlConnection(connectionString);
        OpenConnection(); //connecting to database

I'm looking to connect to MySQL server running on windows machine to C#. But it throws error even though the server is still running. Below are the errors:

1. Connection must be open and valid
2. Unable to connect to any of the specified MySQL hosts
3. Exception thrown: 'MySql.Data.MySqlClient.MySqlException' in MySql.Data.dll
4. Exception thrown: 'System.InvalidOperationException' in MySql.Data.dll

Open Connection Method:

   connection.Open();

You didnt show us the errors you are getting. Also what does OpenConnection() method do? — apomene, Sep 18 '19 at 12:49
SERVER=localhost:2208;DATABASE=LoginFormApplication;UID=root;PASSWORD="" — Umar Saleem, Sep 18 '19 at 12:53

score 4 · Accepted Answer · answered Sep 18 '19 at 14:54

But there was a syntax error. I need to declare the port number separately in connection String. I was declaring the port number right after the I was declaring server name

Solution:

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

Link:

Unable to connect to any of the specified mysql hosts. C# MySQL

score 0 · Answer 2 · answered Sep 18 '19 at 13:05

0

There are several attempts you can make to debug better: 1. Use a try-catch block to get further information what Exception is being thrown:

try
{
   OpenConnection(); //connecting to database
}
catch(Exception ex)
{
   Console.WriteLine(ex.toString());
}

Try and connect to the server with another program to see if the connection works and the credentials are accepted.
Make sure that the using MySql.Data.MySqlClient; which should be at the top of your class file, is connected correctly by checking the set references in your solution (right-click in solution explorer -> References) and the NuGet package (or however you got the package in)

If none of these enable you to solve it please comment the exact exception being thrown in the try-catch blcok ;)

answered Sep 18 '19 at 13:05

TheBest_Kappa

86
10

If I run wampserver and connect with phpMyAdmin, then everything works perfectly fine. – Umar Saleem Sep 18 '19 at 13:33
Exception: Unable to connect to any of the specified host – Umar Saleem Sep 18 '19 at 13:43
@UmarSaleem Okay, i sadly can't test this right now since I don't have a mySql database set up but could you change your code to call the `connection.Open();` directly in the main function? Another option would be to include a command into your connection before opening it. If it doesn't work like that, then it's a server or refernce problem. I've only tried it with an included command yet. `MySqlCommand command = connection.CreateCommand();` `command.CommandText = "SELECT * FROM tablename";` – TheBest_Kappa Sep 18 '19 at 13:45
Thanks for the answer. But there was a syntax error. I need to declare the port number separately in connection String. I was declaring the port number right after the I was declaring server name – Umar Saleem Sep 18 '19 at 14:52

Connecting MySQL Server 8.0 using C#

2 Answers2