Stock Control - "Database locked"

Question

I am doing a C# inventory control and the idea is that when the user clicks a button, the amount of a record in Datagridview is incremented.

The first time I press the button, it updates perfectly. But the second time, the application hangs and takes a long time to respond. Then when responding, a window written "database locked" appears. And then updates the item all right. But I don’t know how to fix it. The code of the button in question:

private void btnPlus_Click(object sender, EventArgs e)
        {
            IDRegistro = 0;
            IDRegistro = Convert.ToInt32(dgvItems.CurrentRow.Cells[0].Value); //pega o id do item selecionado no dataGridView
    
            conn = new SQLiteConnection(conexao);
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }
    
            cmd = new SQLiteCommand("SELECT QTD_ATUAL FROM ESTOQUE WHERE ID = " + IDRegistro, conn);
    
            SQLiteDataReader dr = cmd.ExecuteReader();
            dr.Read();
            int qtdAtual = Convert.ToInt32(dr["QTD_ATUAL"]);
            qtdAtual++;

            cmd = new SQLiteCommand("UPDATE ESTOQUE SET QTD_ATUAL = @QTD_ATUAL WHERE ID = @ID", conn);
            cmd.Parameters.AddWithValue("ID", IDRegistro);
            cmd.Parameters.AddWithValue("QTD_ATUAL", qtdAtual);
            cmd.ExecuteNonQuery();
    
            MessageBox.Show("Registro atualizado");
            Carregar(); //atualiaza o DataGridView
        }

Answer 1

I believe the problem is that the read and executenonquery commands are coming into conflict due to lack of Ispose, follows an alternative that can give a way out to this problem.

    private void btnPlus_Click(object sender, EventArgs e)
    {
        IDRegistro = 0;
        IDRegistro = Convert.ToInt32(dgvItems.CurrentRow.Cells[0].Value); //pega o id do item selecionado no dataGridView

        //utilize o using para fazer o dispose da conexão
        using (SQLiteConnection conn = new SQLiteConnection(conexao))
        {
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }

            string query = "SELECT QTD_ATUAL FROM ESTOQUE WHERE ID = " + IDRegistro;
            int qtdAtual = 0; //talvez seja necessário passar 1 para no caso de dados não disponiveis

            //utilize o using para fazer o dispose no final do comando
            using (SQLiteCommand cmd = new SQLiteCommand(query, conn))
            {
                conn.Open();

                using (SQLiteDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        qtdAtual = Convert.ToInt32(dr["QTD_ATUAL"]);
                    }
                    qtdAtual++;
                }
            }

            string update = "UPDATE ESTOQUE SET QTD_ATUAL = @QTD_ATUAL WHERE ID = @ID";

            //utilize o using para fazer o dispose no final do comando
            using (SQLiteCommand cmd2 = new SQLiteCommand(update, conn))
            {
                cmd2.Parameters.AddWithValue("ID", IDRegistro);
                cmd2.Parameters.AddWithValue("QTD_ATUAL", qtdAtual);
                cmd2.ExecuteNonQuery();
            }


        }
        MessageBox.Show("Registro atualizado");
        Carregar(); //atualiaza o DataGridView
    }