After an sqlite connection is closed and reopened, insert or update queries dont work - WP8 C#

Question

I have a DBManager class which implements IDisposable interface. The class has a sql_conn variable. It has an OpenConnection function which creates a new connection. The class also has functions to create tables, update record etc.

public DBManager()
{
       sql_conn = new SQLiteConnection("MyDB.db");
}

public void Dispose()
{
       sql_conn.Dispose();
}

Every time I need to update some value, I create an object of DBMan and open connection

using (DBManager dbMan = new DBManager())
{
       dbMan.OpenConnection();
       dbMan.InsertIntoDB(val1, val2);  
}

Since DBManager has implemented IDisposable, it disposes the sql_conn after the using statement is completed.

The problem I'm now facing is, in one of the classes I'm required to update several values of the same row based on the certain checks.

void SaveValues
{
     save1();
     save2();
     save3();
}

In save1, I open the connection, update record and close connection In save2, I open connection and update record and then close.

public void save1()
{
    using (DBManager dbMan = new DBManager())
    {
           dbMan.OpenConnection();
           if(//check)
           {
           dbMan.InsertIntoDB(val1, val2);// update query is used  
           }
    }
}

public void save2()
{
    using (DBManager dbMan = new DBManager())
    {
           dbMan.OpenConnection();
           if(//check)
           {
           dbMan.InsertIntoDB(val3, val4);  
           }
    }
}

Save1 works and the value is updated in db. But save2 is not. The function does not throw error but it does not update value in DB.

Any idea why this is not working?

Maybe this will help you. You could set `sql_conn.Trace = true;` so it will display SQL queries in output window. — Mateusz, Jun 23 '15 at 10:23
@Mateusz there in not Trace available! :( Im using the SQLitePCL wrapper. — alfah, Jun 23 '15 at 10:34
Try to force garbage collection after `dispose()` of `close()`, i.e `GC.Collect();` — Barnstokkr, Jun 23 '15 at 11:16
@Barnstokkr It worked!! You jus saved my mentally broken soul! :D If you could post it as answer, I will mark it. So what exactly happens behind the scenes? Why doesn't dispose clear the connection variables? — alfah, Jun 23 '15 at 12:20

score 2 · Accepted Answer · edited May 23 '17 at 12:24

In SQLite.Net the implemented the code to do the Dispose the SQLiteConnection looks like this:

public void Dispose ()
{
    Dispose (true);
    GC.SuppressFinalize (this);
}

protected virtual void Dispose (bool disposing)
{
    Close ();
}

public void Close ()
{
    if (_open && Handle != NullHandle) {
        try {
            if (_mappings != null) {
                foreach (var sqlInsertCommand in _mappings.Values) {
                    sqlInsertCommand.Dispose();
                }
            }                   
            var r = SQLite3.Close (Handle);
            if (r != SQLite3.Result.OK) {
                string msg = SQLite3.GetErrmsg (Handle);
                throw SQLiteException.New (r, msg);
            }
        }
        finally {
            Handle = NullHandle;
            _open = false;
        }
    }
}

From MSDN, the SuppressFinalize does the following:

Requests that the common language runtime not call the finalizer for the specified object.

So, I'm not sure if this is reason why the object appears to still be open, thus making your own Dispose function to force a GC collect GC.Collect(); should finalize or close the object.

From a similar question

What happens when you call SQLiteConnection.Close() is that (along with a number of checks and other things) the SQLiteConnectionHandle that points to the SQLite database instance is disposed. This is done through a call to SQLiteConnectionHandle.Dispose(), however this doesn't actually release the pointer until the CLR's Garbage Collector performs some garbage collection. Since SQLiteConnectionHandle overrides the CriticalHandle.ReleaseHandle() function to call sqlite3_close_interop() (through another function) this does not close the database.

Hope this helps.

After an sqlite connection is closed and reopened, insert or update queries dont work - WP8 C#

1 Answers1