SQLiteConnection DisposedObject

Question

class SQLiteDatabase
    {
        String dbConnection;
        static SQLiteConnection cnn;
        static int connections = 0;
        /// <summary>
        ///     Default Constructor for SQLiteDatabase Class.
        /// </summary>
        public SQLiteDatabase()
        {
            dbConnection = "Data Source=SQLiteOphthaMetrics.db;foreign keys=true;";
            if (connections == 0)
            {
                cnn = new SQLiteConnection(dbConnection);
                cnn.Open();
                this.ExecuteNonQuery("CREATE TABLE IF NOT EXISTS patients ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, birthday TEXT, gender TEXT, iriscolor INTEGER);");
                this.ExecuteNonQuery("CREATE TABLE IF NOT EXISTS images ( nameRed TEXT NOT NULL PRIMARY KEY, checksumRed TEXT NOT NULL, "
                    + "nameGreen TEXT NOT NULL, checksumGreen TEXT NOT NULL, state INTEGER, cvalue REAL, sharpness REAL, deviation REAL, area REAL, redExposure REAL, "
                    + "redGain REAL, greenExposure REAL, greenGain REAL, zfocus INTEGER, flipped INTEGER, patientID INTEGER, FOREIGN KEY (patientID) REFERENCES patients(id));");
            }
            connections++;
        }

        ~SQLiteDatabase()
        {
            connections--;
            if (connections == 0)
            {
                cnn.Close();
                cnn.Dispose();
            }
        }
    }

This code throws a DisposedObjectException

System.ObjectDisposedException was unhandled
  Message=Auf das verworfene Objekt kann nicht zugegriffen werden.
Objektname: "SQLiteConnection".
  Source=System.Data.SQLite
  ObjectName=SQLiteConnection
  StackTrace:
       bei System.Data.SQLite.SQLiteConnection.CheckDisposed()
       bei System.Data.SQLite.SQLiteConnection.Close()
       bei EyeScanner.SQLiteDatabase.Finalize()
  InnerException: 

I am calling SQLiteDatabase currently only a single time in the code, thus connections = 1 in the destructor, but I do not understand why it would dispose the object before the class destructor is finished.

Answer 1

I believe you are trying to dispose the connection once the class instance is ready for dispose. It would be better if you use using statement with your SQLiteConnection. SQLiteConnection class implements IDisposable and you will be able to do the following:

using(cnn = new SQLiteConnection(dbConnection))
{
     cnn.Open();
     this.ExecuteNonQuery("...your query");
}

This will act as a try/finally block, and even if an exception occurs, the connection will be closed and disposed after the using block.

With respect to Database connection, the best approach is to open as late as possible and close as early as possible.