Close connection to database c#

Question

I am making a login system and get the message of sql Exception when I try to create new account in the database:

MySqlCommand usuaExiste = new MySqlCommand(
    "SELECT * FROM contas WHERE nome = '"+textBox1.Text+"'", bdConn);
bool resultado = usuaExiste.ExecuteReader().HasRows;
if (resultado == true || textBox2.Text == null || textBox2.Text.Length < 4)
{
    MessageBox.Show("Por favor, certifique se sua senha não é muito curta, seu usuário já é existente ou ele é muito curto.");
}
else
{
    try
    {
        MySqlCommand criar = new MySqlCommand("INSERT INTO contas (nome, senha) VALUES ('" + textBox1.Text + "','" + textBox2.Text + "')", bdConn);
        criar.BeginExecuteNonQuery();
        criar.ExecuteNonQuery();
        MessageBox.Show("Conta Criada com sucesso!");
        bdConn.Close();
    }
    catch (MySqlException ex)
    {
        MessageBox.Show("Erro ao criar a conta, informe isto ao desenvolvedor! \r\n "+ ex);
    }
}

The following error occurs:

Answer 1

In this line:

bool resultado = usuaExiste.ExecuteReader().HasRows;

You are creating a Data Reader. A Data Reader is an object that you use to take quick readings of the result of a query. As long as you have a Data Reader open on a connection, you should not run commands on it. You must close the Data Reader before executing the commands, exactly as the error message indicates.

Note that the Data Reader is the return of the method ExecuteReader. You’re not holding that Data Reader in a variable, and that way you will not have how to close it. I suggest doing something more like:

bool resultado;
using (var dataReader = usuaExiste.ExecuteReader()) {
    resultado = dataReader.HasRows();
} // o "using" garantirá o fechamento do Data Reader aqui

Note that there are other ways to know if a particular query has results, which can eliminate the need to use a Data Reader. Apparently you only use the Data Reader to know if a query brings results. The method ExecuteScalar of the command object returns the value of the cell in the first column and first column of the result. If you make a SELECT COUNT or something like, you can use the method ExecuteScalar and dispense with the Data Reader.

Answer 2

I can see some problems with your code.

You should use blocks using for the objects that are IDisposable:

• Mysqlconnection
• Mysqlcommand

Thus:

• using (var bdConn = new MySqlConnection(conexao))

• using (var command = new MySqlCommand("... SQL aqui ...", bdConn))

In addition, the method BeginExecuteNonQuery is the asynchronous version of the method ExecuteNonQuery, so there is no need to call both methods.

Just call the method ExecuteNonQuery since the intention is to wait for the result and execute something shortly afterwards synchronously.

And to improve performance, validate the UI interface before database checks.

At the end, your code should look something like this (I put some comments to indicate what I did)

using (var bdConn = new MySqlConnection(conexao)) // o bloco using garante que o recurso
                                                  // será libarado ao sair do bloco
                                                  // de código
{
    try
    {
        bdConn.Open();
    }
    catch
    {
        MessageBox.Show("Impossível conectar ao banco de dados, ligue o wamp server!");
    }

    if (textBox2.Text != null && textBox2.Text.Length >= 4) // fazer validações de
                                                            // interface (UI), antes das
                                                            // verificações no banco
    {
        bool resultado;
        using (var usuaExiste = new MySqlCommand(
            "SELECT * FROM contas WHERE nome = '" + textBox1.Text + "'",
            bdConn)) // using do primeiro objeto MySqlCommand
                     // o que garante que será chamado o respectivo método Dispose()
        {
            resultado = usuaExiste.ExecuteReader().HasRows;
        }

        if (!resultado)
        {
            try
            {
                using (var criar =
                    new MySqlCommand(
                        "INSERT INTO contas (nome, senha) VALUES ('"
                        + textBox1.Text + "','" + textBox2.Text +
                        "')", bdConn)) // using do segundo objeto MySqlCommand
                                       // garantindo a chamada ao Dispose()
                {
                    criar.ExecuteNonQuery();
                    MessageBox.Show("Conta Criada com sucesso!");

                    bdConn.Close(); // NOTA: o Close não é realmente necessário,
                                    // uma vez que estamos colocando os devidos
                                    // using nas variáveis IDisposable
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(
                    "Erro ao criar a conta, informe isto ao desenvolvedor! \r\n "
                    + ex);
            }
        }
    }
    else
    {
        MessageBox.Show(
            "Por favor, certifique se sua senha não é muito curta, "
            + "seu usuário já é existente ou ele é muito curto.");
    }
}

A valuable recommendation

You shouldn’t be able to match both the control code and the data access code. It would be interesting if you separated the control code, and data code (and also the UI part), because in the future, if you want to change your Mysql database to SQL Server or Oracle for example, you will have problems finding and correcting all the points in your code.