Connect a C# Windows Form App to a MySQL Database on a Linux based server

Question

I am working on an C# application which would use the remote MySQL database located in my website hosted on a Linux server with PHP & MySQL support.

I tried to connect directly to the MySQL database using MySql.Data.MySQLClient Reference, as a result, my program is throwing following exception :

"Unable to connect to any of the specified MySQL hosts."

I searched many tutorials but got no solutions... getting the same error.

Can anybody please tell me how to do this.

Please suggest any online links or tutorials or your own idea.

Please help me.

here's my code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace mysq
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {              
                string conn = "Server = myserver;database = db ;uid = username ;password = pwd ;";

                MySqlConnection con = new MySqlConnection(conn);
                con.Open();
                if (con.State == ConnectionState.Open)
                {
                    MySqlCommand cmd = new MySqlCommand("Select * from table", con);
                    DataTable dt = new DataTable();
                    MySqlDataAdapter ad = new MySqlDataAdapter(cmd);
                    ad.Fill(dt);
                    dataGridView1.DataSource = dt;
                }

           }
            catch (Exception)
            {
                throw;
            }


        }
    }
}

Thanks in advance..

C4d · Accepted Answer · 2015-11-20 14:52:11Z

I've written an own class with functions for my mysql-connection.

First of all declare the server connection:

string ServerConnection = "Server=localhost;Port=1234;Database=YourDb;Uid=user;password=pass;"

This is how I select data:

public static void DB_Select(string s, params List<string>[] lists)
        {
            try
            {
                using(var conn = new MySqlConnection(ServerConnection))
                {
                    conn.Open();
                    MySqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                    string command = s;
                    cmd.CommandText = command;
                    using(var sqlreader = cmd.ExecuteReader())
                    while (sqlreader.Read())
                    {
                        if (sqlreader[0].ToString().Length > 0)
                        {
                            for (int i = 0; i < lists.Count(); i++)
                            {
                                lists[i].Add(sqlreader[i].ToString());
                            }
                        }
                        else
                        {
                            foreach (List<string> save in lists)
                            {
                                save.Add("/");
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error while selecting data from database!\nDetails: " + ex);
            }
        }

The call:

List<string> allRows = new List<string>();

DB_Select("SELECT field1 FROM table1 WHERE 1", allRows);

Notice: For every field you are selecting, you have to pass an own list which will be filled with the output.

For updating your database it would be quite the same function. You just wouldnt have to grad the output. Could look like this:

public static void DB_Update(string s)
        {
            try
            {
                using (var conn = new MySqlConnection(ServerConnection))
                {
                    conn.Open();
                    MySqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                    string command = s;
                    cmd.CommandText = command;
                    int numRowsUpdated = cmd.ExecuteNonQuery();

                    if(numRowsUpdated < 0)
                    {
                        MessageBox.Show("Warning DB-Contact: Affected_rows < 0!");
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(String.Format("Updating database failed!\n\nSQL: {0}\n\nERROR: {1}",s,ex.Message));
            }
        }

Steephen · Accepted Answer · 2015-08-08 13:38:20Z

0

You have to provide proper connection string in your program, i am suspecious you are passing proper IP address or hostname of the server where you hosted the database. As a first step try to ping the db server to make sure it is reachable from your machine. If it is reachable try as follows:

MySql.Data.MySqlClient.MySqlConnection conn;
string myConnectionString;
string conn = "Server =<provide proper ip address >;database = db ;uid = username ;password = pwd ;";

conn = new MySql.Data.MySqlClient.MySqlConnection();
conn.ConnectionString = myConnectionString;
conn.Open();

More details here : http://dev.mysql.com/doc/connector-net/en/connector-net-programming-connecting-open.html

edited Aug 8, 2015 at 13:38

answered Aug 8, 2015 at 13:02

Steephen

16k7 gold badges46 silver badges47 bronze badges

5 Comments

Ali Hasan Over a year ago

I've copied and pasted the hostname and other details provided by web host , getting same error

Steephen Over a year ago

Can you able to ping the host name successfully?

Ali Hasan Over a year ago

No cannot ping Request Timed Out

Steephen Over a year ago

Then how you can connect? Find an accessible ip address or hostname

Ali Hasan Over a year ago

I'm using a free domain and unfortunately they do not allow access to my sql remotely, I think this is the reason.. Thanks for your support :)

Collectives™ on Stack Overflow

Connect a C# Windows Form App to a MySQL Database on a Linux based server

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related