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