MySQL runs fine - SQLite throws ObjectDisposedException

Question

I'm developing a program for a Windows Mobile Handheld 6.5.3 device (attention: compact framework!). The app is primary built for MySQL. As I want to be able to use different databases, I thought a factory-scheme would be the best way.

So I made a db factory out of a tutorial (http://www.c-sharpcorner.com/UploadFile/db2972/factory-design-pattern/) and rewrote most of the database parts, so that it uses the correct interface methods.

Executing a query looks like this (simplified):

using (conn)
  using (cmd)
  {
    conn.Open();
    // Prepare query and stuff
    //..
    // Fetch results
    conn.Close();
    // check result
    return result;
  }
}

There is a chain of database queries depending on the result of the first query. Using my app with MySQL is no problem. But switching to SQLite throws an ObjectDisposedException at conn.Open() of the query that gets executed AFTER the FIRST one.

Any site/tutorial that writes about handling databases in c# refer to close the connection as soon as the query is done and - if you want to execute a new query - just open the connection again.

Why is SQLite having problems with my code? How can I get any tips from the debugger except for ObjectDisposed (as in: how to handle it with ensuring that mysql runs fine nonetheless)?

Some infos about the IDE, assemblys and the corresponding connectionstrings:

• Visual Studio 2008 Prof.
• MySQL Connector / .NET - 6.9.6.0
  • SERVER={0};DATABASE={1};UID={2};PASSWORD={3}
• System.Data.SQLite (https://system.data.sqlite.org) - 1.0.97.0 PocketPC/ARM
  • tried Data Source=mydb.sqlite;Version=3;Pooling=True;Max Pool Size=100;FailIfMissing=True;
  • and Data Source=mydb.sqlite;Version=3;FailIfMissing=True;
• same structure and values of databases

Edit: looking at conn while debugging shows that when running mysql conn.Close(), it seems that conn is still available (but obviously closed). While looking at conn with sqlite, it seems that conn gets destroyed after conn.Close()... Shouldn't it have the same behaviour because of the IDbConnection interface? Any hint what I may do now?

Both docs say: public void Close() implements IDbConnection.Close

Edit 2: conn.Close() doesn't seem to be the problem - but the end of the using-block is. I set up a debugger-watch on the conn-object and tested it with mysql and then with sqlite. After exiting the using-block, the conn-object gets "destroyed" with sqlite, but not with mysql.

Answer 1

The using construct calls Dispose when you exit the block. It is designed to clean up unmanaged resources no matter what. Therefore it makes sense that you would get an ObjectDisposedException if you try to access the object again. One simple way to deal with this is to not try to reuse the disposed connection but instead connect each time. Alternatively, move your dispose logic to a higher level such that all you DB access is done before disposing.