A common SQLite pitfall

This is something I've seen happen to several of our customers, and it can be quite difficult to track down if you don't know how.

Make sure you dispose commands before closing their connections!

The code I've seen typically goes like this:

protected void ExecuteNonQuery (string cmd)
{
    using (var c = connection.CreateCommand ()) {
        c.CommandText = cmd;
        connection.Open ();
        c.ExecuteNonQuery ();
        connection.Close ();
    }
}

This is broken. Here the connection is closed before the command is disposed. Unfortunately SQLite doesn't tell you anything when this happens, but it will leak file handles (the connection will in fact not be closed), eventually leading to random errors somewhere else when you run out of file handles.

Random errors are of course not fun, but fortunately this is very easy to debug if you just know how: in Xamarin Studio open the Run->Exceptions menu and add SqliteExceptions to the list of exceptions to break on:



Now run your app. You'll break here if your code is broken:



and now it's just a matter of walking up the call stack until you find out where the SQLiteConnection.Close is called:



And you should be able to spot the bug and fix it easily:

protected void ExecuteNonQuery (string cmd)
{
    using (var c = connection.CreateCommand ()) {
        c.CommandText = cmd;
        connection.Open ();
        c.ExecuteNonQuery ();
    }
    connection.Close ();
}

As a final note I'd like to mention that breaking on all exceptions (configure Xamarin Studio to break on System.Exception) can be a valuable debugging tool when weird things are happening - when I first ran into this issue I spent several frustrating hours trying to track it down without success. Out of a hunch I decided to break on all exceptions, and then it took me only a couple of minutes to understand what was going on and fix the code.