SQLite: 'Unable to open the database file' exception with transaction with multiple commands

I have the following (simplified) code:

IDbConnection connection = new SQLiteConnection(GetConnectionString());
if (connection.State == ConnectionState.Closed)
  connection.Open();
using (IDbTransaction transaction = connection.BeginTransaction())
{
  using (IDbCommand cmd = transaction.Connection.CreateCommand())
  {
    cmd.CommandText = "DELETE FROM Records2 WHERE ClientIndex = @ClientIndex";
    AddParameter(cmd, "@ClientIndex", DbType.Int32, clientIndex);
    cmd.ExecuteNonQuery();
  }
  using (IDbCommand cmd = transaction.Connection.CreateCommand())
  {
    cmd.CommandText = "INSERT INTO Records2(ClientIndex, CandidateIndex, Name)";
    cmd.CommandText += " VALUES(@ClientIndex, @CandidateIndex, @Name)";
    AddParameter(cmd, "@ClientIndex", DbType.Int32, clientIndex);
    IDbDataParameter pIndex = AddParameter(cmd, "@CandidateIndex", DbType.Int32, null);
    IDbDataParameter pName = AddParameter(cmd, "@Name", DbType.AnsiString, null);
    int index = 0;
    foreach (Record record in records)
    {
      pIndex.Value = index++;
      pName.Value = record.Name;
      cmd.ExecuteNonQuery();
    }
  }
  using (IDbCommand cmd = transaction.Connection.CreateCommand())
  {
    cmd.CommandText = "UPDATE Records SET Status = @Status, UpdateDate = @UpdateDate WHERE ClientIndex = @ClientIndex"开发者_运维知识库;
    AddParameter(cmd, "@ClientIndex", DbType.Int32, clientIndex);
    AddParameter(cmd, "@Status", DbType.Byte, status);
    AddParameter(cmd, "@UpdateDate", DbType.DateTime, DateTime.Now);
    cmd.ExecuteNonQuery();
  }
  transaction.Commit();
}
connection.Close();

with:

private IDbDataParameter AddParameter(IDbCommand command, string paramName, DbType type, object value)
{
  IDbDataParameter parameter = command.CreateParameter();
  parameter.ParameterName = paramName;
  parameter.DbType = type;
  if (value != null)
    parameter.Value = value;
  else
    parameter.Value = DBNull.Value;
  command.Parameters.Add(parameter);
  return parameter;
}

All the ExecuteNonQueries work without problems, except for the last one. On my machine (running xp) I get the following exception, but on other machines (running windows 7) it works without problems, with the same code and the same database file.

SQLiteException (0x80004005): Unable to open the database file
  System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt) +375
  System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt) +199
  System.Data.SQLite.SQLiteDataReader.NextResult() +226
  System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) +87
  System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) +38
  System.Data.SQLite.SQLiteCommand.ExecuteNonQuery() +39

I tried using JournalMode = Perist in the connection string and i excluded the directory containing the database file from my anti-virus, but nothing helps.

I'm using the 3.5 Framework and my version of System.Data.SQLite is 1.0.66.0.

Any Ideas ?

Thanks in advance,

Marc

I was having the same issue with 1.0.87 and 1.0.89, and eventually (with the help of procmon) figured out the reason. It's actually a misleading error message, because its not the database file itself to which access is denied, but the SQLite statement journal.

Although the SQLite database file and journal are being created in the correct location, and the permissions on that folder allow access to Everyone, SQLite is still using %TEMP% for its statement journal. %TEMP% maps to a temp folder under the user profile of the identity running the service. So when a non-privileged client identity hits the service (and is impersonated inside and behind the service), that non-privileged identity can't create the etilqs_XXXXX temp folder used for the statement journal.

My fix was to set a PRAGMA on SQLiteConnections:

PRAGMA temp_store_directory='<same_path_as_the_database_file>'

I upgraded my System.Data.SQLite.dll from version 1.0.66.0 written by Robert Simpson (http://sqlite.phxsoftware.com) to version 1.0.74.0 written by the SQLite development team (http://system.data.sqlite.org) and that seems to solve the problem.

I know this questions is old but I just had the same issue yesterday and was searching for answers. In my case my development environment was working and production was failing. The issue was the following line in my production web.config:

<connectionStrings>
    <add name="SalesForceConnection"
        connectionString="metadata=res://SalesForceDataModel.csdl|res://SalesForceDataModel.ssdl|res://SalesForceDataModel.msl;
        provider=Devart.Data.Salesforce;
        provider connection string=&quot;
        User Id=myUserId;
        Password=myPassword;
        Security Token=myToken;
        Persist Security Info=True;
        Host=login.salesforce.com;
        Data Cache=c:\projects\salesforce\1.db;
        Metadata Cache=c:\projects\salesforce\1_Metadata.db&quot;"
        providerName="System.Data.EntityClient" />
</connectionStrings>

The data cache and metadata cache both point to directories in my development environment. Since the connection string is really long I missed these 2 settings. I changed the directories to ones on the production server and it all worked.