Connect and insert data from my computer to server's MySQL DB table with the help of C#.NET

Question

I want to connect with a server's MySql DB(cpanel) . Though there are no errors every time I'm getting a message for Messegebox : unable to connect to any of the specified any of the MySql hosts.

using MySql.Data.MySqlClient;
        connString = "SERVER = ********;PORT=3306;DATABASE=********;UID=**********;PASSWORD=*********";
        try
        {
            conn = new MySqlConnection();
            conn.ConnectionString = connString;
            conn.Open();
            MessageBox.Show("Server is online");

        }
        catch (MySql.Data.MySqlClient.MySqlException ex)
        { MessageBox.Show(ex.Message);}

atoms · Answer 1 · 2019-03-22T17:06:19.360

0

I would look into using ConnectionStringBuilder. Also note the use of 'using'. This will ensure resources are disposed once finished with.

private MySqlConnectionStringBuilder sConnString = new MySqlConnectionStringBuilder
{
    Server = "",
    UserID = "",
    Password = "",
    Database = ""
};

private void Test(){

    // open connection to db
    using (MySqlConnection conn = new MySqlConnection(sConnString.ToString()))
    {
        using (MySqlCommand cmd = conn.CreateCommand())
        {
            try
            {
                conn.Open();
                cmd.CommandText = "SELECT * FROM foo WHERE OrderID = @OrderID";

                // Add any params
                cmd.Parameters.AddWithValue("@OrderID", "1111");
                cmd.Prepare();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
                return;
            }
        }
    }
}

edited Mar 22 '19 at 17:06

answered Mar 22 '19 at 16:15

atoms

2,993
2
22
43

using (MySqlConnection conn = new MySqlConnection(sConnString)) can not convert into string – Md. Ariful Islam Mar 22 '19 at 17:01
can not convert from MySql.Data.MySqlClient.MySqlConnectionStringBuilder to String – Md. Ariful Islam Mar 22 '19 at 17:05
I’ve update my answer. Notice tostring method sConnstring – atoms Mar 22 '19 at 17:06
Thank you for sharing you code . But I don't understand why catch (Exception e) { MessageBox.Show(e.ToString()); return; } works every time. server is not connecting . I think there is something wrong with the server . – Md. Ariful Islam Mar 22 '19 at 18:35

score 0 · Answer 2 · answered Mar 22 '19 at 16:18

0

If you try to connect to your MySQL server externally, external connections need to be enabled.

Be aware that it is a security hole if you provide your app to others which contain the DB information. To go around that, you need to create a Web-API.

answered Mar 22 '19 at 16:18

Stejin

64
4

Ow . Thanks for your suggestion . Can you suggest a related tutorial for me ? – Md. Ariful Islam Mar 22 '19 at 18:37

score 0 · Answer 3 · answered Mar 22 '19 at 16:48

0

The first step to connect to your app to a remote server MySql Server is verify if it allows external connections, for default the root user is locked, to allow local you can try to connect with the root user typing the following command in MySql:

 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
 FLUSH PRIVILEGES;
'root': You can change it with your user.
'%': allows all connections, you can limit it typing an IP.

Second step is verify your MySql .net connector

Cheers!

answered Mar 22 '19 at 16:48

user3403192

1
1

FLUSH PRIVILEGES; What is this ? – Md. Ariful Islam Mar 22 '19 at 17:31
Root. I will replace this with my user name . should I replace pass too ? – Md. Ariful Islam Mar 22 '19 at 17:33
#1045 - Access denied for user 'dragonco'@'localhost' (using password: YES) – Md. Ariful Islam Mar 22 '19 at 17:35
Flush: when you grant some privileges for a user, running the command flush privileges will reloads the grant tables in the mysql database enabling the changes to take effect without reloading or restarting mysql service. – user3403192 Mar 22 '19 at 17:46
Try this: GRANT ALL ON fooDatabase.* TO fooUser@'1.2.3.4' IDENTIFIED BY 'my_password'; Example: https://support.rackspace.com/how-to/mysql-connect-to-your-database-remotely/ – user3403192 Mar 22 '19 at 17:49

Connect and insert data from my computer to server's MySQL DB table with the help of C#.NET

3 Answers3