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();
            }
        }

 

[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.

 

[Pablo]

Hi, @titojd
What I said before, that the 2 lines:

for (int i = 0; i < 15; i++)
     result._01 = Convert.ToInt32(reader[0]);

just repeat the 2nd line 15 times, which doesn't make sense, because with once is enough. If you wanted to group the 15 lines below the for header, then you should have used braces { and }, but, the same, it's nonsense, you don't need to repeat any of those 15 lines 15 times because the 15 assignments with the 15 indices and the reader you already wrote one by one. So take out the:
for (int i = 0; i < 15; i++)

I understand that I need to empty my array to delete, leave only the txtConcurso.text loaded, and for Edita I need to stop the connection, but I don't know how to do this part, and

No, you don't need to empty anything to delete, neither for edit to stop de connection.
For delete a row from a table, you need an SQL language sentence, like:

DELETE FROM Table WHERE Contest = '2857'

which you will probably have in a Stored Procedure in your database, and you have to pass to the parameter txtConcurso.Text, which is a unique row key.
For update a row, you need a SQL language sentence, like (after changing the ResultText TextBoxes):

UPDATE Table
SET _01 = @_01, _02 = @_02, _03 = @_03 ... up to _15 = @_15
WHERE Contest = @contest

which you'll probably have in another Stored Procedure. For the 15 variables you can pass the:
ResultText[index].Text as value, and for @contest, txtConcurso.Text.
You can have a DataGridview with all the rows, and Select (Search) one row by double clicking it or typing its contest name in txtConcurso, and then fill the ResultText array.
And also, as you were told, use the connection object inside a using.
Please tell us where is your trouble, so we can help you better.
Pablo

 

[Skydiver]

If you look at the GitHub link, that is all you really need to package together when you really need to share an entire project, instead of jsut the relevant code. There is no need to include the bin or obj directories. There is no need to include the packages directory. Everything else can be re-built as needed. (Granted the setup project will only work with VS2015 since that style of setup project has been deprecated in favor for the more mature and modern Wix style setup projects.)

Despite the tips on how to package up files to share a project, we still highly encourage people to just post the minimal amount of code to reproduce the problem. That is a lot of code in your project to wade through, when all I want to do is to reproduce the problem and get to the root cause of what is causing the problem.

 

[Pablo]

Perform an edit apparently


Show us yoir fixed code?

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

 

[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

 

[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

 

[Skydiver]

I only fixed the specific repro case described by @Pablo , and I was trying to minimize the changes in your code to show how to fix the issue with a specific focus on that code path. I did not do a holistic refactoring of all code paths because that would have obscured what the problem was and how to fix it.

What is the specific steps you used to reproduce the problem?

Was this the only code that you changed or did you change something else? (For example, that btnBuscar_Click() not only commented out a whole section of code, but you also change the sense of line 3 and swapped the if[/code] and [icode]else blocks.

What changes did you make to "Logica.cs"? Did you you update all the uses of DbConnection() to dispose properly? Did you make sure that the DataReader was disposed?

In general, you really should open a new question. But given how hard its been to get code from you that shows the minimal repro case, and instead you give us your entire project, it seems to be the lesser of two evils to just keep piling on this thread.

Update after: I just tried the following steps and didn't reproduce the error:
1) Start the program.
2) Click on the "Save Edit Delete" button.
3) Check the "Option: Enter tender number to edit or delete" checkbox.
4) In the dark gray box, enter "1234"
5) Click on the "Search" button.

Results:
No exception thrown.

 

[cjard]

A "uses Dapper" version, but I don't have any test data, so I can't easily test it.. Also some other refactoring that *should* work, but again, can't test

Install a tool like BeyondCompare to see the differences between old and new files

 

[Skydiver]

Where do you close and dispose of all those connections that you create via your call to DbConnection()?

In general, the modern philosophy is to use a connection for as short a time as possible: create a connection, open it, create command(s), do some operations, and then dispose command(s), , close connection, and dispose of the connection -- let the database driver take care of any kind of connection pooling. This is very different from what used to be what was taught back in the 80's and early 90's where you needed to try to keep the connection to the database open for as long as possible, and you had to implement your own connection pooling.

 

[Pablo]

Hello, @titojd
Please, see the image ...
About the 15 iteration loop, 1) It will repeat 15 times the (only) line below; if you wanted to repeat 15 times all the 15 lines below (from reader[0] to reader[14]) you should have grouped those lines with braces { and }. But, why repeat 15 times those lines, if you are already asigning from reader[0] to reader[14]? Why the loop?
Pablo

 

[titojd]

hello @Skydiver, I understand what you're thinking, the problem is that I've already tried to put an end to the connection and still didn't get a result, maybe I'm putting the "sqliteConnection.Close();" in the wrong place, which I really don't understand.


hello @Pablo, this was the way I found to do the search and fill my array, from this loaded array I start to edit or delete. I don't know if this is right, but the search is working.


I understand that I need to empty my array to delete, leave only the txtConcurso.text loaded, and for Edita I need to stop the connection, but I don't know how to do this part, and