Do we need to close SQLite connection when exiting WP app?

Question

In the App.xaml.cs of my universal app I have opened an SQLConnection. I'm not closing the connection. So far it hasn't caused me trouble. But is it the right way, can we leave it like that?

If we are to close the connection (Dispose() because I don’t see a close function) where do we close? In the App_Closing?

EDIT

This is how I'm opening the connection in App.xaml.cs

public static SQLiteConnection conn;
public void LoadDatabase()
    {
        conn = new SQLiteConnection( "JusWriteDB.db");

        string sql = @"CREATE TABLE IF NOT EXISTS Folder (FolderCompletedStatus INTEGER, FolderPriority INTEGER, PenColor INTEGER,                                                         FolderText TEXT, FolderUUID TEXT PRIMARY KEY NOT NULL );";
        using (var statement = conn.Prepare(sql))
        {
            try
            {
                statement.Step();
            }
            catch(Exception)
            {

            }
        }
    }

And where ever(in some other file) I need a to access this connection, to update table or insert. I access it through the public variable defined in App.xaml.cs

 var db = App.conn;
 string sql = "SELECT * from Folder Where FolderSyncStatus = 'Del'; ";
 try
    {
      using (var statement = db.Prepare(sql))
      {
         while (statement.Step() == SQLiteResult.ROW)
         { //  code 
         }
      }
    }
  catch{}

I don't know much about WP but pretty sure you should close it. — artm, May 26 '15 at 12:31
I would wrap the connection in a Using, then atleast you would be sure it closes when exiting program. — Henrik Bøgelund Lavstsen, May 26 '15 at 12:33

score 5 · Answer 1 · answered May 26 '15 at 12:40

The general rule is to always invoke Dispose() method on IDisposalbe objects. So yes, you should close it. The next question is where. There are two ways of doing it. First - use using directive for a temporary local connection, e.g.:

using(SqlConnection connection = new SqlConnection("ConnectionString"))
{
    // some code here
}

But is seems that you store a reference to the connection in a field. In this case, the class that has this field should implement IDisposable and invoke connection.Dispose() in its own Dispose method:

class Repository : IDisposable
{
    SqlConnection connection = new SqlConnection("ConnectionString");

    void Dispose()
    {
        connection.Dispose();
    }
}

But anyway you will need using here, the only difference - in using you will create Repository instead of SqlConnection

To open connection I am not using the 'using' statement. But all other places when I use the using directive to update, insert etc. I have added more code — alfah, May 27 '15 at 06:23
Probably you should rewrite the code to open a connection each time you need to execute some SQL. — Alex Sikilinda, May 29 '15 at 09:44

score 2 · Answer 2 · edited May 23 '17 at 11:58

Do we need to close SQLite connection when exiting WP app?

The general rule is: If an object implements IDisposable, you should Dispose it or use a using.

using(SQLiteConnection conn = new SQLiteConnection("<ConnectionString>"))
{
...
}

There are only a few exceptions where it depends (like DataSets and DataTables).

If we are to close the connection (Dispose() cuz I dont see a close function) where do we close?

You don't have to ask this question if you use a using, because it will be Disposed automatically.

If you don't use using, then in most cases storing a connection and reusing it is not a good idea (except if you like errors), so the answer would be "as soon as possible".

EDIT (following OP)

This is how I'm opening the connection in App.xaml.cs

You don't want to have anything related to DB connections in the view.
With the samples you provided, I don't see any advantage of doing so. You could simply open the connection every time.
If you worry about opening X connections, then take a look at the following MSDN article. It is about SqlConnections but the concept is the same for SQLiteConnections.

Sevrin. I've added snippets to the question to give you more clarity. I use the using directive for updating, inserting etc. But the connection which I opened in app.xaml.cs is left like that. :( — alfah, May 27 '15 at 06:25

Do we need to close SQLite connection when exiting WP app?

2 Answers2