System.Data.SQLite

Hi there,

I am attempting to use SQLite for a repository layer in an application I am writing as a prototype for something bigger. I have hit a stumbling block on a cryptic, yet very unpleasant warning. The error only appears when I close my connection. If I only allow one thread to open a connection to the database the behaviour is the same, all threads make their own connection string and my code reads as shown below.

using System.Data.SQLite;

public int GetTotalRecords(string strDescription)
{
SQLiteConnection sqldbConnection = new SQLiteConnection(ConfigurationManager.ConnectionStrings["MyConnection"].ToString());

SQLiteCommand cmd;
SQLiteDataReader reader;

if (strDescription.Count() != 0)
{
//This query is the one in use when I am misusing the library.
cmd = new SQLiteCommand("SELECT COUNT([ProductId]) FROM [Product] productsReal, [Virtual_Product_FTS] productsVirtual WHERE Virtual_Product_FTS MATCH '%" + strDescription + "%' AND productsReal.ProductId = productsVirtual.docid", sqldbConnection);
}

else

{
//This one is harmless and I get no misuse issues when closing up.
cmd = new SQLiteCommand("SELECT COUNT([ProductId]) FROM [Product]", sqldbConnection);
}

cmd.CommandType = CommandType.Text;

sqldbConnection.Open();

reader = cmd.ExecuteReader();

int temp = 0;

while (reader.Read())
{
    temp = reader.GetInt32(0);
}

sqldbConnection.Close(); //Errors output after this line is run.

return temp;
}

The exact output I get when this is run on its own is this:
"SQLite error (21): API called with finalized prepared statement"
"SQLite error (21): misuse at line 62706 of [ebd01a8def]"
"SQLite error (21): API called with finalized prepared statement"
"SQLite error (21): misuse at line 62706 of [ebd01a8def]"

Both errors appear at the same time as the text search query is closed.

I am using your "sqlite-netFx40-setup-bundle-x86-2010-1.0.79.0" version, I have no idea how to check the flags for threading support I just assume the defaults are on for the time being and that full text search is enabled and fine for use. If it was not I assume the operation would fail rather than throw a none show stopping error.

Am I doing anything wrong? Can you see the cause of this error? It looks to me as if there is a simple step missing in my clean up but I have tried several things and adding reader.Close() does not help.

Thank you for reading this.

<hr /><i>mistachkin added on 2012-02-24 16:24:06 UTC:</i><br />
The code you provided is simply letting the connection, command, and data reader objects fall out of scope and be finalized by the CLR garbage collector.  The recommended way would be something like this:

<verbatim>
using System.Data.SQLite;

public int GetTotalRecords(string strDescription)
{
  int temp = 0;

  using (SQLiteConnection sqldbConnection = new SQLiteConnection(
      ConfigurationManager.ConnectionStrings["MyConnection"].ToString()))
  {
    sqldbConnection.Open();

    string query;

    if (strDescription != null)
    {
      //This query is the one in use when I am misusing the library.
      query = "SELECT COUNT([ProductId]) FROM [Product] productsReal, " +
              "[Virtual_Product_FTS] productsVirtual WHERE " +
              "Virtual_Product_FTS MATCH '%" + strDescription +
              "%' AND productsReal.ProductId = productsVirtual.docid";
    }
    else
    {
      //This one is harmless and I get no misuse issues when closing up.
      query = "SELECT COUNT([ProductId]) FROM [Product]";
    }

    using (SQLiteCommand cmd = new SQLiteCommand(query, sqldbConnection))
    {
      cmd.CommandType = CommandType.Text;

      using (SQLiteDataReader reader = cmd.ExecuteReader())
      {
        while (reader.Read())
        {
            temp = reader.GetInt32(0);
        }
      }
    }

    // NOTE: Not needed within a using block.
    // sqldbConnection.Close(); //Errors output after this line is run.
  }

  return temp;
}
</verbatim>

The 'using' blocks above guarantee that the resources will be properly disposed via their IDisposable interface.