C# sqlite delete rows not work when there is no rows in that table

Question

I am tring to delete one table's all record using C#.

SQLiteConnection sql_con = new SQLiteConnection("Data Source=test.db3;Version=3;New=false;Compress=true;");
        SQLiteCommand sql_cmd;
        sql_con.Open();
        sql_cmd = sql_con.CreateCommand();
        sql_cmd.CommandText = "delete from table1";
        sql_cmd.ExecuteNonQuery();
        sql_con.Close();

It works when there are rows existing in table1. However if there is no rows in table1, the code will break at sql_cmd.ExecuteNonQuery(); What do I miss?

What do you mean "break"? Does it throw an error? What error? — Ron Beyer, Oct 18 '18 at 16:02
@RonBeyer it throws an error after few mins saying that database is locked. BTW there is no truncate command in SQLite. — CZA, Oct 18 '18 at 16:05
I realized that after posting it, I was thinking SQL... It isn't possible that this is being called more than once on multiple threads? Have you tried running that command in an SQLite console or management utility? — Ron Beyer, Oct 18 '18 at 16:07
@RonBeyer thats the point. I ran it in a SQLite management utility(DB brower for SQLite). It works perfectly, When I move the command to C#, It won't work. What I can do is that check if there has rows or not before I delete it, and If it does, I ll go delete it. — CZA, Oct 18 '18 at 16:14
You may have a connection that is left open, see [this question](https://stackoverflow.com/questions/17592671/sqlite-database-locked-exception) which isn't really the same issue, but it is good practice to use `using` if you get exceptions elsewhere that are leaving connections open. — Ron Beyer, Oct 18 '18 at 16:18

score 0 · Answer 1 · answered Oct 18 '18 at 17:14

It is critically important to wrap all your disposable objects in using blocks. SQLite will NOT fully close the connection until the objects are disposed. Try this code:

using (SQLiteConnection sql_con = new SQLiteConnection("Data Source=test.db3;Version=3;New=false;Compress=true;"))
{
    sql_con.Open();
    using (SQLiteCommand sql_cmd = sql_con.CreateCommand())
    {
        sql_cmd.CommandText = "delete from table1";
        sql_cmd.ExecuteNonQuery();
    }
}

C# sqlite delete rows not work when there is no rows in that table

1 Answers1