Delete from command not working in sqlite?

Question

I have a sqlite db file. I am using DB Browser for Sqlite as the client. I went in and ran delete from command on most of my tables. Thereafter I tried to export using the option Database to SQL file I notice all my data is appearing in it. What I wondering is that why the data have not been deleted? I know the sqlite file size will not shrink.

Below is snippet of my codes.

string str = @"Data Source=" + userFilePath + "\\mysqlite.sqlite3";
                    using (SQLiteConnection con = new SQLiteConnection(str))
                    {
                        con.Open();
                        SQLiteTransaction trans = con.BeginTransaction();
                        try
                        {


                            String cmdSelect1 = "Select * from table1 where companyID='" + companyID + "' And month='" + month + "' And year='" + year + "'";
                            int fiscalPeriod = Convert.ToInt32(monthNumber);
                            int financialYear = Convert.ToInt32(itemvalueyear);
                            using (SQLiteCommand cmd1 = new SQLiteCommand(cmdSelect1, con, trans))
                            {
                                SQLiteDataReader dr1 = cmd1.ExecuteReader();

                                if (dr1.Read())
                                {

                                    MessageBoxResult messageBoxResult = System.Windows.MessageBox.Show("Records Already Exist ? Are you confirm replace it?", "Delete Confirmation", System.Windows.MessageBoxButton.YesNo);
                                    if (messageBoxResult == MessageBoxResult.Yes)
                                    {

                                        String deleteTable = "Delete from table1 where companyID='" + companyID + "' And month='" + month + "' And year='" + year + "'";
                                        using (SQLiteCommand cmdDeleteTb1 = new SQLiteCommand(deleteTable, con, trans))
                                        {
                                            cmdDeleteTb1.ExecuteNonQuery();
                                            cmdDeleteTb1.Dispose();
                                        }
                                        foreach (object line in linesC)
                                        {
                                            if (line.GetType() == typeof(TypeC))
                                            {

                                                String cmdText2 = "INSERT INTO table1(tbID,companyID,month,year) VALUES(@tbID,@companyID,@month,@year)";
                                                using (SQLiteCommand cmd = new SQLiteCommand(cmdText2, con, trans))
                                                {

                                                    cmd.Parameters.AddWithValue("@tbID", tbID);
                                                    cmd.Parameters.AddWithValue("@companyID", companyID);
                                                    cmd.Parameters.AddWithValue("@month", month);
                                                    cmd.Parameters.AddWithValue("@year", year);
                                                    cmd.ExecuteNonQuery();
                                                    cmd.Parameters.Clear();
                                                    cmd.Dispose();
                                                }
                                            }
                                        }

                                    }
                                }


                                dr1.Close();
                                cmd1.Dispose();
                            }
                            trans.Commit();

                            MessageBox.Show("Successfully Inserted Into Database");
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("Rollback " + ex.ToString());
                            trans.Rollback();
                        }

                        con.Close();
                        con.Dispose();



                        GC.Collect();

Answer 1

Ok:

It appears you are beginning two transactions. You begin your loop inserts after you begin your delete.

Commit your Delete transaction and then later commit your inserts.

This is than committing after beginning both transactions.