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

 

[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

 

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

 

[Skydiver]

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

I got the same. This is why we tell people to post the code to this forum instead of linking to an external location. The long term value of the thread is at serious risk.

 

[cjard]

The file was 58MB mind.. how you get that from a few hundred kilobytes app to read and write lottery numbers in a text file is anyone's guess

 

[Skydiver]

how you get that from a few hundred kilobytes app to read and write lottery numbers in a text file is anyone's guess

I know lots of beginners who focus on the UI and put background images and music in with their WinForms programs. That media takes up a lot of room. Also a lot of beginners zip up the entire solution project including the binaries instead of just the minimum source files needed to build the app.

 

[Pablo]

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.

Hi, @Skydiver
There are not concurrent writes in @titojd 's program. And, the same, there is that error 'database is locked'. If it was a web application which may have concurrent writes, it could be what you say, but being a simple desktop application that access the database, it can't, al least it's my vision. Also I don't have a network and got the error.
Anyway, if someone had the solution for that issue, I think titojd would be very thankful. I had no problem downloading the project with the first link he gave us. It would be very nice to find a solution for this thread, which is taking several weeks and at the beggining it already had a solution by handling a text file in memory (as a string, that's what it is) and only rewritting the file when a button was clicked, but it was said that code was not good and titojd was told to use a database that, by now, doesn't work. Regardless it is (for me at least) too much to use a database for so simple operations, reality is that it (SQLite) doesn't work, ... by now. If someone resolved the issue, I will be glad, too.
Regards
Pablo

 

[Skydiver]

Your text file solution reads and writes everything in one shot. Change your text file solution to not use ReadAllLines() and WriteAllLines(). Switch to opening streams, AND not closing or disposing the steams. Making this change will give you an apples to apples comparison with his code where he was opening the database multiple times and not closing and disposing his connections.

A WinForms program can still be re-entrant (albeit single threaded), if the code using Application.DoEvents() or used ShowDialog() and is not careful with how events are handled.

If you have the original database code he shared, can you share it out? I can take a look as time allows. I was not jumping on it originally because I expected the file share to be persistent at least for the duration when the problem was solved. But alas, he retracted the share.