Connect Mysql C#

Question

I have a MYSQL bank in a shared UOLHOST hosting, I can connect to the bank by Visual Studio Server Explorer normally:

But when trying to connect through a simple test console application I get access denied:

The clear difference I noticed was that apparently mysql adds an IP to the user, in the connection via server explorer the user is

thus:[email protected]

and in the connection via console the user is like this:

'Barc'@'191.17.28.80'

The mistake I get is this:

Authentication to host 'Barcelona-app.mysql.uhserver.com' for user 'Barc' using method 'mysql_native_password' failed with message: Access denied for user 'Barc'@'191.17.28.80' (using password: YES

Someone can help me?

Answer 1

Hello.

1. First you need to download the Connector/Net (since you are using ADO)
2. Import using MySql.Data.MySqlClient;
3. Soon you can do something like this

See the Code below:

//define o dataset
mDataSet = new DataSet();

//define string de conexao e cria a conexao
mConn = new MySqlConnection("Persist Security Info=False;server=localhost;database=Cadastro;uid=root;server=localhost;database=Cadastro;uid=root;pwd=xxxx");

  try{
    //abre a conexao
     mConn.Open();
   }
   catch(System.Exception e)
   {
    MessageBox.Show(e.Message.ToString());
   }

   //verificva se a conexão esta aberta
  if (mConn.State == ConnectionState.Open)
  {
    //cria um adapter usando a instrução SQL para acessar a tabela Clientes
    mAdapter = new  MySqlDataAdapter("SELECT * FROM Clientes", mConn);
    //preenche o dataset via adapter
    mAdapter.Fill(mDataSet, "Clientes");
    //atribui a resultado a propriedade DataSource do DataGrid
    mDataGrid.DataSource = mDataSet;
    mDataGrid.DataMember = "Clientes";
}

See all the step by step here

Answer 2

I use the following form to make the connection.

class Conexao{

    private const string _strCon = @"server=meuProvedor.com.br;" +
                "user id=Meu_Login;" +
                "password=Minha_Senha;" + 
                "database=Meu_banco;" + 
                "persistsecurityinfo=False";
    private string vsql = "";
    public MySqlConnection objCon = null;

    #region "Métodos de conexão como o banco"

    public bool conectar(){
        objCon = new MySqlConnection(_strCon);
        try{
            objCon.Open();
            return true;
        }
        catch{
            return false;
        }
    }

    public bool desconectar(){
        if (objCon.State != ConnectionState.Closed){
            objCon.Close();
            objCon.Dispose();
            return true;
        }
        else{
            objCon.Dispose();
            return false;
        }
    }

For consultations:

public DataTable ListaGrid(){
    // Esta função lê a tabela Cliente e devolve para um DataGridView.
        vsql = "SELECT * FROM CLIENTE ORDER BY NOME";
        MySqlCommand objcmd = null;

        if (this.conectar()){
            try{
                objcmd = new MySqlCommand(vsql, objCon);
                MySqlDataAdapter adp = new MySqlDataAdapter(objcmd);
                DataTable dt = new DataTable();
                adp.Fill(dt);
                return dt;
            }
            catch (MySqlException sqlerr){
                throw sqlerr;
            }
            finally{
                this.desconectar();
            }
        }
        else{
            return null;
        }
    }

   public string Max(){
    // Esta função busca o maior código de cliente e devolve numa string.
        vsql = "SELECT MAX(CODCLIENTE) FROM CLIENTE";
        MySqlCommand objcmd = null;
        if (this.conectar()){
            try{
                objcmd = new MySqlCommand(vsql, objCon);                    
                return Convert.ToString(objcmd.ExecuteScalar());
            }
            catch (MySqlException sqlerr){ throw sqlerr; }
            finally{ this.desconectar(); }
        }
        else{ return "0"; }
    }
    public bool Update(ArrayList reg){
    // Esta função faz um update na tabela Cliente e recebe parâmetros num ArrayList
        vsql = "UPDATE CLIENTE SET NOME = @NOME, ENDERECO = @ENDERECO" +
               " WHERE CODCLIENTE = @CODCLIENTE";
        MySqlCommand objcmd = null;

        if (this.conectar()){            
            try{                
                objcmd = new MySqlCommand(vsql, objCon); // Cria comando do MySql
                // Adiciona o arrayList nos parâmetros
                objcmd.Parameters.Add(new MySqlParameter("@CODCLIENTE", reg[0]));
                objcmd.Parameters.Add(new MySqlParameter("@NOME", reg[1]));
                objcmd.Parameters.Add(new MySqlParameter("@ENDERECO", reg[2]));                    

                objcmd.ExecuteNonQuery(); // Executa a consulta
                return true;
            }
            catch (MySqlException sqlerr){                
                MessageBox.Show(sqlerr, ":: Mensagem", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
                throw sqlerr;
            }
            finally{                
                this.desconectar();
            }
        }
        else return false;
    }

I hope I can help. I use this same method for Sql Server and Access with the appropriate changes.

Answer 3

Greetings !

Friend, I believe there are several ways to make that connection. Here’s one I used on my college project last semester.

        string Source = "aonde o banco esta";
        string Banco = "qual banco";
        string User = "nome do usuario";
        string Senha = "senha do usuario";

        SqlConnection NewConexao = new SqlConnection("Data Source="+ Source 
            +"; Initial Catalog="+ Banco 
            +"; User Id="+User
            +"; Password="+Senha
            +"; Integrated Security=false");

        NewConexao.Open();

I used this method to "open" my connection to sqlServer <-> C#, I believe I can serve for you with mysql as well. I hope it helps.