How to close mysql connections in C#

Question

When executing the code below:

    DataSet bdDataSet;
    MySqlConnection conexao;

    public void inserir()
    {
        bdDataSet = new DataSet();

        conexao = new MySqlConnection("Server=localhost; Database=teste; Uid=; Pwd=");

        try
        {
            conexao.Open();
            Console.WriteLine("conectou");
        }catch{
            Console.WriteLine("Erro ao conectar");
        }

        if(conexao.State == ConnectionState.Open ){
            Console.WriteLine("conexao aberta");
            MySqlCommand commS = new MySqlCommand("INSERT INTO usuario VALUES('teste', 'teste')", conexao);
            commS.BeginExecuteNonQuery();
        }
    }

The record is entered normally, the problem is that if after the excerpt:

MySqlCommand commS = new MySqlCommand("INSERT INTO usuario VALUES('teste', 'teste')", conexao);
commS.BeginExecuteNonQuery();

In case I run conexao.Close() to close the connection it does not execute the command, as if it did not give the commit in the query and the insert is not done, what can be?

Answer 1

Mysqlcommand.Beginexecutenonquery performs a form instruction asynchronous in relation to the database.

This means that the query is not executed as soon as the command is given, but at the most opportune time, outside the current execution flow.

To solve your problem, you can simply use ExecuteNonQuery or call end of asynchronous execution before closing your connection:

IAsyncResult myResult = myCommand.BeginExecuteNonQuery(null, null);
...
rowsAffected = myCommand.EndExecuteNonQuery(myResult);

Answer 2

Run the query as shown below:

DataSet bdDataSet;
MySqlConnection conexao;

public void inserir()
{
    bdDataSet = new DataSet();

    conexao = new MySqlConnection("Server=localhost; Database=teste; Uid=; Pwd=");

    try
    {
        conexao.Open();
        Console.WriteLine("conectou");

        MySqlCommand commS = new MySqlCommand("INSERT INTO usuario VALUES('teste', 'teste')", conexao);
        commS.ExecuteNonQuery();

    }catch{
        Console.WriteLine("Erro ao conectar ou ao executar comando SQL");
    } finally {
        if(conexao != null && conexao.State == ConnectionState.Open ){
            conexao.Close();
        }
    }
}

Note that to execute the query the command ExecuteNonQuery() is used. In addition, the connection is closed in the finally, to ensure that it is closed.

Answer 3

I think you can check whether Mysql is set to autocommit or not.

Take a look at this link Mysqlconnection. There is information and code example of how to work with manual commit.