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]

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

 

[cjard]

Oh, if people would just use something like entity framework, all this goes away

I agree with SD; you're opening connections willy nilly and not reliably closing them; trouble will result. You should at least be keeping your connections in a variable so you can still gain access to close them again

using var conn = GetConnection();
using var cmd = conn.CreateCommand();

//do stuff with command

//finish method, conn is disposed
}

etc


But the more code you keep adding to that My Class, the more you're just writing a poor man's EF; bite the bullet and upgrade already..

 

[Skydiver]

As people know on this forum, I am not a fan of Entity Framework, but something is better than nothing in this case. There are lots of examples of how to use EF around the Internet.

 

[titojd]

in fact, as soon as you open the application it edits the first one and to edit the second one comes the error,
the problem is that I just started with SqLite, I'm more lost than blind in a firefight, I'll read more about...
A hug to all and many thanks.

 

[cjard]

try { Saida = "Concurso editado com sucesso!"; } catch (Exception exc) { Saida = "Ocorreu um erro inesperado: " + exc.Message; }

Setting a string variable to a string value does not need to be try'd

 

[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

 

[titojd]

hello @Pablo, Thanks for the explanation, I have my project here, if you want to take a look, it's quite messy, but I intend to improve with comments and organization.
projeto

 

[titojd]

Project with some comments in English

 

[Pablo]

Hi, @titojd

I saw your project and changed the issue of the connection open more than once (see the .txt attachment), but I can't fix the runtime error 'database is locked', I think it's a bug from SQLite. Could anyone solve this? @Skydiver , @cjard ? If I were you I would use SQL Server (with which I have never had an issue). Or better, the code I gave you some weeks ago, which handles the txt file as a string for the Edit and Delete operations and only rewrite all the file when you click the Save button (see the code, it isn't too hard to understand; with SQLite you are dealing since some weeks ago with several hundreds lines of code, and my example is only 75 lines, you can just read it and test it and then decide if you find it useful or not).

If you still want to use a DB, I recommend SQL Server, because it seems SQLite is buggy. There is a forum, maybe there you can ask about this error 'database is locked' when it shouldn't be.

For testing my program (see the image - the 15 TextBoxes for numbers are created dinamically), you have to, first, open the txt file that contains the raffle lines by typing its name and clicking the Open Button (the text of that file will appear in the txtFileContents Multiline TextBox), then write the number of contest in the first Textbox, then click Search Button, then the 15 numbers will appear in the TextBox array 'numbers', then you can delete that line by clicking the Exclude Button, or you can edit the 15 TextBoxes and then click the Edit Button; only when you click the Save Button the changes are done to the txt file, which is rewritten.

I know that the other members told you not to use my code, but you are complicating yourself since several weeks ago with SQLite and you didn't get anywhere. Just have a look at it and give it a test (and ask me what you don't understand), and if it doesn't suit your needs, then go ahead with the Database and its many hundreds lines of code, but in that case, unless you or another person manages to solve that runtime error, I advise you to switch to SQL Server.

The code, again (it assumes commas as delimiters, change it if you use spaces or other character):

namespace WinFormsAppEditTextFile
{
    public partial class Form1 : Form
    {
        private TextBox[] numbers;
        private string[] lines;
        private string fileText;
        private int lineIndex;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            int X = txtContest.Location.X + txtContest.Size.Width + 8;
            int Y = txtContest.Location.Y;
            numbers = new TextBox[15];
            for (int i = 0; i < numbers.Length; i++)
            {
                numbers[i] = new TextBox();
                numbers[i].Size = new Size(23, 23);
                numbers[i].Location = new Point(X + i * (23 + 8), Y);
            }
            Controls.AddRange(numbers);
        }

        private void btnOpen_Click(object sender, EventArgs e)
        {
            string contents = File.ReadAllText(txtFileName.Text);
            fileText = contents;
            txtFileContents.Text = contents;
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            int index = 0;
            lines = File.ReadAllLines(txtFileName.Text);
            for (int i = 0; i < lines.Length; i++)
            {
                if (lines[i].Substring(0, 4) == txtContest.Text)
                {
                    for (int j = 0; j < 15; j++)
                    {
                        index = lines[i].IndexOf(',', index) + 1;
                        numbers[j].Text = lines[i].Substring(index, 2);
                    }
                    lineIndex = i;
                    break;
                }
            }
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            File.WriteAllText(txtFileName.Text, fileText);
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            string editedLine = txtContest.Text;
            for (int i = 0; i < 15; i++)
                editedLine += "," + numbers[i].Text;
            fileText = fileText.Replace(lines[lineIndex], editedLine);
            txtFileContents.Text = fileText;
        }

        private void btnExclude_Click(object sender, EventArgs e)
        {
            fileText = fileText.Replace(lines[lineIndex] + "\r\n", "");
            txtFileContents.Text = fileText;
        }
    }
}

I hope you find the way that takes you to success.
Regards
Pablo

 

[cjard]

Cannot download file. It says "No preview available. File is in owner's bin".

As I mentioned earlier, I think youre going about creating and opening conenctions the wrong way. My vote is this would work:

        public static string _sqliteConnectionString = "";


        public static int Update(Resultado result)
        {
            using var conn = new SqliteConnection(_sqliteConnectionString);
            using var cmd = conn.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"));
            conn.Open();
            return cmd.ExecuteNonQuery();    //database is locked
        }


        public static int Delete(int concurso)
        {
            using var conn = new SqliteConnection(_sqliteConnectionString);
            using var cmd = conn.CreateCommand();
            cmd.CommandText = "DELETE FROM TBResultado Where Concurso=@Concurso";
            cmd.Parameters.AddWithValue("@Concurso", concurso);
            conn.Open();
            return cmd.ExecuteNonQuery();
        }

        public static Resultado SearchResult(int contest)
        {
            var result = new Resultado();
            using var conn = new SqliteConnection(_sqliteConnectionString);
            using var cmd = conn.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);
            conn.Open();
            using (var reader = cmd.ExecuteReader())
            {
                if (reader.Read())
                {
                    result.Concurso = contest;
                    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]);
                }
            }

            return result;
        }

I don't think you should store data as strings and convert it back and forth all the time. I think you should store ints, formatting if necessary


If you used Dapper, life gets simpler:

        public static string _sqliteConnectionString = "";


        public static int Update(Resultado result)
        {
            using var conn = new SqliteConnection(_sqliteConnectionString);
            return conn.Execute("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", result);
        }


        public static int Delete(int concurso)
        {
            using var conn = new SqliteConnection(_sqliteConnectionString);
            return conn.Execute("DELETE FROM TBResultado Where Concurso=@concurso", new { concurso });
        }

        public static Resultado SearchResult(int concurso)
        {
            using var conn = new SqliteConnection(_sqliteConnectionString);
            return conn.QueryFirst<Resultado>("SELECT * from TBResultado WHERE Concurso = @concurso", new { concurso });
        }

 

[cjard]

I recommend SQL Server, because it seems SQLite is buggy

Bit of a reach

 

[Skydiver]

I've only seen SQLite "bugs" when people don't follow the documentation, specifically when people insist on putting the SQLite database on a file/SMB network share, and then are surprised that their database gets corrupted when there are concurrent writes.