Close connection to database C #

9

I am making a login system and I get the exception sql message when I try to create a 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:

    
asked by anonymous 21.02.2014 / 20:45

2 answers

12

In this line:

bool resultado = usuaExiste.ExecuteReader().HasRows;

You are creating a Data Reader . A Data Reader is an object that you use to make quick reads 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 should close Data Reader before executing the commands exactly as the error message indicates.

Note that the Data Reader is the return of the ExecuteReader method. You are not holding this Data Reader in a variable, and you will not be able 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 Data Reader . Apparently you only use Data Reader to know if a given query yields results. The ExecuteScalar method 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, you can use the ExecuteScalar method and dispense Data Reader .

    
21.02.2014 / 21:06
4

I can see some problems with your code.

You should use using blocks for objects that are IDisposable :

  • MySqlConnection
  • MySqlCommand

So:

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

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

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

Call only the ExecuteNonQuery method since the intention is to wait for the result and execute something immediately afterwards in a synchronous way.

And to improve performance, validate the UI before scanning the database.

In the end, your code should look like this (I've 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 should not couple both the control code and the data access code. It would be interesting if you separate the control code, and data (and also the part of UI), 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 points in your code.

    
23.02.2014 / 19:59