Resolved Error when Editing and Deleting in SqLite

[titojd]

hello guys, I'm having a problem deleting and editing a Contest in SqLite, sometimes it works and sometimes it doesn't.
Catch: Enter the Try Catch exceptions
on Edit: I get the message database is locked
I have the following code;

My Form

 private void editar()
        {
            if (!string.IsNullOrWhiteSpace(txtConcurso.Text))
            {
                if (txtConcurso.TextLength != 4)
                {
                    MessageBox.Show("Insira um concurso válido para salvar no Banco!", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                outro
                {
                    if (ResultText[0].Text != "" && ResultText[1].Text != "" && ResultText[2].Text != "" && ResultText[3].Text != "" && ResultText[4] .Texto! = "" &&
                        ResultText[5].Text != "" && ResultText[6].Text != "" && ResultText[7].Text != "" && ResultText[8].Text != "" && ResultText[9].Text != "" &&
                        ResultText[10].Text != "" && ResultText[11].Text != "" && ResultText[12].Text != "" && ResultText[13].Text != "" && ResultText[14].Text != "")
                    {
                        string Saida = Logica.Update(PreencherInformacoes());
                        MessageBox.Show(Saida, "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    outro
                    {
                        MessageBox.Show("Insira um resultado válido para editar!", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }       
            }
            outro
            {
                MessageBox.Show("Insira um Concurso!", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        private string Excluir(string Concurso)
        { 
            string Saida = string.Format("O Concuso {0} foi excluído com sucesso!", Concurso);
            tentar
            {
                Lógica.Delete(txtConcurso.Text);
            }
            catch (Exceção exc)
            {
                Saida = string.Format("Ocorreu um erro ao excluir o Concurso {0}", Concurso, exc.Message); //no momento de excluir esta entrada nesta parte
            }
            retornar Saida;
        }

 private void btnEditar_Click(objeto remetente, EventArgs e)
        {
            if (!string.IsNullOrWhiteSpace(txtConcurso.Text))
            {
                editar();
            }
            outro
            {
                MessageBox.Show("Insira um concurso para editar!", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

 private void btnExcluir_Click(objeto remetente, EventArgs e)
        {
            if (!string.IsNullOrEmpty(txtConcurso.Text))
            {
                if (MessageBox.Show("Você deseja mesmo excluir o cliente?", "Pergunta", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    string saida = Excluir(txtConcurso.Text);
                    MessageBox.Show(saida, "Saida", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }     
            }
            outro
            {
                MessageBox.Show("Insira um valor", "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

My Class

 private static SQLiteConnection sqliteConnection;

        private static SQLiteConnection DbConnection()
        {
            sqliteConnection = new SQLiteConnection("Data Source=" + path);
            sqliteConnection.Open();
            return sqliteConnection;
        }

public static string Update(Resultado result)
        {
            string Saida = "";
            SQLiteCommand cmd = new SQLiteCommand();
            using (cmd = DbConnection().CreateCommand())
            {
                cmd.CommandText = "UPDATE TBResultado SET _01=@_01,_02=@_02,_03=@_03,_04=@_04,_05=@_05,_06=@_06," +
                    " _07=@_07,_08=@_08,_09=@_09,_10=@_10,_11=@_11,_12=@_12,_13=@_13,_14=@_14,_15=@_15 WHERE Concurso=@Concurso";
                cmd.Parameters.AddWithValue("@Concurso", result.Concurso);
                cmd.Parameters.AddWithValue("@_01", result._01.ToString("D2"));
                cmd.Parameters.AddWithValue("@_02", result._02.ToString("D2"));
                cmd.Parameters.AddWithValue("@_03", result._03.ToString("D2"));
                cmd.Parameters.AddWithValue("@_04", result._04.ToString("D2"));
                cmd.Parameters.AddWithValue("@_05", result._05.ToString("D2"));
                cmd.Parameters.AddWithValue("@_06", result._06.ToString("D2"));
                cmd.Parameters.AddWithValue("@_07", result._07.ToString("D2"));
                cmd.Parameters.AddWithValue("@_08", result._08.ToString("D2"));
                cmd.Parameters.AddWithValue("@_09", result._09.ToString("D2"));
                cmd.Parameters.AddWithValue("@_10", result._10.ToString("D2"));
                cmd.Parameters.AddWithValue("@_11", result._11.ToString("D2"));
                cmd.Parameters.AddWithValue("@_12", result._12.ToString("D2"));
                cmd.Parameters.AddWithValue("@_13", result._13.ToString("D2"));
                cmd.Parameters.AddWithValue("@_14", result._14.ToString("D2"));
                cmd.Parameters.AddWithValue("@_15", result._15.ToString("D2"));
                cmd.ExecuteNonQuery();    //database is locked
            }
            try
            {
                Saida = "Concurso editado com sucesso!";
            }
            catch (Exception exc)
            {
                Saida = "Ocorreu um erro inesperado: " + exc.Message;
            }

            return Saida;
        }


        public static string Delete(string Concurso)
        {
            string Saida = "";
            SQLiteCommand cmd = new SQLiteCommand();
            using (cmd = DbConnection().CreateCommand()) ;
            {
                cmd.CommandText = "DELETE FROM TBResultado Where Concurso=@Concurso";
                cmd.Parameters.AddWithValue("@Concurso", Concurso);             
                cmd.ExecuteNonQuery();
            
            } 
                                
            return Saida;
        }

Searching for the Contest to delete My Class

public static Resultado SearchResult(int contest)
        {
            var result = new Resultado();
            using (var cmd = DbConnection().CreateCommand())
            {
                cmd.CommandText = "SELECT _01,_02,_03,_04,_05,_06,_07,_08,_09,_10,_11,_12,_13,_14,_15 from TBResultado WHERE Concurso = @concurso";

                cmd.Parameters.AddWithValue("@concurso", contest);

                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        result.Concurso = contest;
                        for (int i = 0; i < 15; i++)
                            result._01 = Convert.ToInt32(reader[0]);
                        result._02 = Convert.ToInt32(reader[1]);
                        result._03 = Convert.ToInt32(reader[2]);
                        result._04 = Convert.ToInt32(reader[3]);
                        result._05 = Convert.ToInt32(reader[4]);
                        result._06 = Convert.ToInt32(reader[5]);
                        result._07 = Convert.ToInt32(reader[6]);
                        result._08 = Convert.ToInt32(reader[7]);
                        result._09 = Convert.ToInt32(reader[8]);
                        result._10 = Convert.ToInt32(reader[9]);
                        result._11 = Convert.ToInt32(reader[10]);
                        result._12 = Convert.ToInt32(reader[11]);
                        result._13 = Convert.ToInt32(reader[12]);
                        result._14 = Convert.ToInt32(reader[13]);
                        result._15 = Convert.ToInt32(reader[14]);                       
                    }
                }               
            }
            //sqliteConnection.Close();
            return result;
        }

Searching for the Contest to delete in the Form

 private void SearchResult()
        {
            var contest = txtConcurso.Text;
            var result = new Resultado();
            if (!string.IsNullOrWhiteSpace(result.ToString()))
            {
                result = Logica.SearchResult(Convert.ToInt32(contest));

                ResultText[0].Text = result._01.ToString("D2");
                ResultText[1].Text = result._02.ToString("D2");
                ResultText[2].Text = result._03.ToString("D2");
                ResultText[3].Text = result._04.ToString("D2");
                ResultText[4].Text = result._05.ToString("D2");
                ResultText[5].Text = result._06.ToString("D2");
                ResultText[6].Text = result._07.ToString("D2");
                ResultText[7].Text = result._08.ToString("D2");
                ResultText[8].Text = result._09.ToString("D2");
                ResultText[9].Text = result._10.ToString("D2");
                ResultText[10].Text = result._11.ToString("D2");
                ResultText[11].Text = result._12.ToString("D2");
                ResultText[12].Text = result._13.ToString("D2");
                ResultText[13].Text = result._14.ToString("D2");
                ResultText[14].Text = result._15.ToString("D2");
            }
            else
            {
                result = null;
            }
        }

  private void btnBuscar_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrWhiteSpace(txtConcurso.Text))
            {
                if (Logica.VerificarExistencia(Convert.ToInt32(txtConcurso.Text)) == false)
                {
                    MessageBox.Show("Numero de Concurso não existe");
                    LimpaTexto();
                }
                else
                {
                    SearchResult();
                }
            }
            else
            {
                MessageBox.Show("Insira um Concurso para buscar!", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information);
                LimpaTexto();
            }
        }

 

[Pablo]

Perform an edit apparently


Show us yoir fixed code?

Hello, @cjard
Look at the attachment, it's the Logica.cs file fixed.

 

[Skydiver]

@Pablo : thanks for the repro steps. No home Internet right now. I'll try them out tomorrow when the phone company fixes their cable.

 

[Skydiver]

Internet is still down, but connecting briefly with my hotspot to show how to fix that specific repro case. The delta's can be seen in github with this link:
Deltas to fix repro case

Simply disposing the connections for that code path was not enough. There was also a DataReader that needed to be disposed. I narrowed down on that due to this blog post regarding the "database locked" errors happening during long reads. Interestingly doing a search through the rest of the code, all other uses of ExecuteReader() was within using statements. It was only that specific case inside VerificarExistencia() which didn't dispose the DataReader.

As a quick note, as tempting as it was to do a full refactoring of the code, my goal was just to show where the key impact areas were for that specific repro case. Personally, I would have refactored fully and switched over to using Dapper.

 

[cjard]

Sooo.. it was a case of "not `using` the connection?

 

[Skydiver]

That and also use using on the data reader. I tried just fixing the connection not being closed, but that wasn't enough. Apparently, the SQLite data reader implementation locks the file to provide the Consistency part of ACID.

 

[Pablo]

Internet is still down, but connecting briefly with my hotspot to show how to fix that specific repro case. The delta's can be seen in github with this link:
Deltas to fix repro case

Simply disposing the connections for that code path was not enough. There was also a DataReader that needed to be disposed. I narrowed down on that due to this blog post regarding the "database locked" errors happening during long reads. Interestingly doing a search through the rest of the code, all other uses of ExecuteReader() was within using statements. It was only that specific case inside VerificarExistencia() which didn't dispose the DataReader.

As a quick note, as tempting as it was to do a full refactoring of the code, my goal was just to show where the key impact areas were for that specific repro case. Personally, I would have refactored fully and switched over to using Dapper.

Hi, @Skydiver
You are a genious! I (with my 10+ years programming) didn't catch that issue of the DataReader. I just looked at the writting stuff (edit, delete, etc.), not at the reading stuff. So thank you very very much. As I told @cjard not so long ago, it is nice to know you are there for helping us!
Pablo

 

[titojd]

Hi everyone, I'm very grateful to everyone, I learned a lot here in this post, I realized that I have to study a lot, and that's what I've been trying to do, but there are so many things that make my brain rack,
I tried every way.
I took the tip from @Pablo and it kept giving the error, the tips from @cjard and @Skydiver did nothing to solve my error, I spent nights researching and didn't solve it, so the best thing to do is change.
I'm thinking about going back to Sql Server, which never gave me any problems,
unfortunately it's a price I'll have to pay,
I thank each and every one of you who helped me this far.
A big hug to everyone,

 

[cjard]

the tip from @cjard ... did nothing to solve my error

It's no surprise, given that you didn't do what I recommended!

 

[Skydiver]

the tip from @skydiver did nothing to solve my error

If you were the one who wrote:

using (var cmd = DbConnection().CreateCommand())
{
    :
    // do work here
    :

    DbConnection().Close();
}

then you didn't implement my tip about closing the connection. That's because you are not closing the connections you were creating.

Notice how you wrote:

private static SQLiteConnection DbConnection()
{
    sqliteConnection = new SQLiteConnection("Data Source=" + path);
    sqliteConnection.Open();
    return sqliteConnection;
}

Basically this is what is happening:

var conn1 = DbConnection();
---> // this creates a brand connection and forgets the reference to the last connection it had created
---> sqliteConnection = new SQLiteConnection( ... );
---> // opens the brand new connection
---> sqliteConnection.Open() that connection
---> // return that new connection and a reference to it is stored in conn1
using (var cmd = conn1.CreateCommand())
{
    :
    // do work here
    :
    var conn2 = DbConnection();
    ---> // this creates a brand connection and forgets the reference to the last connection it had created (e.g. conn1 above)
    ---> sqliteConnection = new SQLiteConnection( ... );
    ---> // opens the brand new connection
    ---> sqliteConnection.Open() that connection
    ---> // return that new connection and a reference to it is stored in conn2

    // Close the conn2 connection
    conn2.Close();
}

The correct thing to do implement my tip was to do:

using (var connection = DbConnection())
{
    using (var cmd = connection.CreateCmd())
    {
        :
        // do work here
        :
    }
}    // when the end of this using scope is hit, the connection is closed and disposed

That would have gotten you 90% of the way to a solution.

But it wouldn't have completely fixed your problem. There was that other problem with your DataReader not being closed and disposed in that specific method. The irony is that for all the other code where you used a DataReader, you closed and disposed it.

The reason why you were getting success with SQL Server and not SQLite is because SQL Server has a different way of guaranteeing Consistency and Isolation in its ACID operations. When you do a cursored read operation using a DataReader on SQL Server, it keeps a "snapshot" of what the data looks like in server memory*** at the time you started your query. So as you call ReadNext() you are walking through the in-memory snapshot, not the actual data on disk. SQL Server can afford to do this because it assumes that it is the only thing running on a machine and can use up as much memory as it wants. (If you've ever run and managed a SQL server and look at its memory consumption, you'll notice that at start up it tries to hog as much memory as it can.) When anything tries to update the database while a read is happening, it can be written right away.

SQLite is different. It was designed to run desktops and mobile devices. It tries to be really conservative about how it uses resources. So when you a cursored read operation with a DataReader, it locks the entire file to prevent anything else from updating the data within the database. This is how it guarantees Consistency and Isolation in its ACID operations. When something tries to update the database while a read is happening it gives back an error. This approach works fine because originally SQLite was written for C code that was originally conceived to be single threaded, and also assumed that programmers would be disciplined C/C++ programmers who would always release resources that they grabbed when they are not using them: e.g. release memory, close files, etc. SQLite was not conceived with the garbage collection mindset that modern C# and Java programmers have. (Old school C/C++ programmers who made the transition to C# and Java are still paranoid about releasing resources and not depending on the garbage collector.)

*** The snapshot in memory is a gross simplification. It is actually more sophisticated than that and less memory hungry.

 

[Pablo]

Hello, @titojd
Please read carefuly the last messages of @Skydiver , there you have the solution for your SQLite issue.
Besides the issue of the multiple connections opening (that I think you already understood that), there is a DataReader you open/read (with the Search button, I think) and you don't close/dispose. See well that, and fix it, and your app with SQLite will work fine. Do not give up now that you already have the solution. Also I saw a few posts back that Skydiver shared a Github link to the fixed code. Come on! Go ahead and succeed! Here you find the help you need.
Pablo

 

[Skydiver]

Here's a link to the fixed code for that specific repro case, it should give you a hint on how to fix the other code paths that still haven't been fixed:

GitHub - azureskydiver/GeradorLotofacil at minimal-fix-for-repro-case

Contribute to azureskydiver/GeradorLotofacil development by creating an account on GitHub.

github.com

The changes were in Logica.cs:

GeradorLotofacil/Controle/Logica.cs at minimal-fix-for-repro-case · azureskydiver/GeradorLotofacil

Contribute to azureskydiver/GeradorLotofacil development by creating an account on GitHub.

github.com

 

[titojd]

I'm going to dedicate myself to this post, I'm going to redo the project based on these tips, because my code is all messed up, as soon as I'm finished I'll come back here to inform everyone, thank you all.
a hug.

 

[titojd]

It's no surprise, given that you didn't do what I recommended!

I'm sorry, but that's not exactly what I meant, I'm using Google Translate, my English is weak.

 

[Skydiver]

I'm going to dedicate myself to this post, I'm going to redo the project based on these tips, because my code is all messed up, as soon as I'm finished

That's a tenacious developer. Keep at it!

 

[cjard]

Do it with Dapper; it will manage the opening and closing the connection for you, and cut down on all that tedious GetXXX code when using a reader