SQLite Database Locked exception

Question

Somewhere along the way a connection is getting left open. Get rid of OpenConnection and CloseConnection and change ExecuteNonQuery to this:

using (SQLiteConnection c = new SQLiteConnection(ConnectionString))
{
    c.Open();
    using (SQLiteCommand cmd = new SQLiteCommand(sql, c))
    {
        cmd.ExecuteNonQuery();
    }
}

Further, change the way you read data to this:

using (SQLiteConnection c = new SQLiteConnection(ConnectionString))
{
    c.Open();
    using (SQLiteCommand cmd = new SQLiteCommand(sql, c))
    {
        using (SQLiteDataReader rdr = cmd.ExecuteReader())
        {
            ...
        }
    }
}

Do not attempt, to manage connection pooling on your own like you are here. First, it's much more complex than what you have coded, but second, it's handled already inside the SQLiteConnection object. Finally, if you're not leveraging using, you're not disposing these objects properly and you end up with issues like what you're seeing now.

You can use 'using' statement as below, that will make sure connection & command disposed correctly even in exception

private static void ExecuteNonQuery(string queryString)
{
    using (var connection = new SQLiteConnection(
               ConnectionString))
    {
        using (var command = new SQLiteCommand(queryString, connection))
        {
            command.Connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

You should close your DataReader before attempting to write any data to the database. Use:

dr.Close();

after you finish using the DataReader.

In my case it was very stupid of me, I was making changes in SQLite browser and did not click on write changes, which locked the DB to be modified by the services. After I clicked the Write changes button, all the post request worked as expected.