System.Data.SQLite

I'm getting the error message "The database file is locked" error message in a DataAdapter under certain situations such as the select and update commands sharing the same connection.

For example:

    // This version does not work ... same connection for select and update.
    private static void TestOnSameConnectionNoTransaction()
    {
      SQLiteDataAdapter dataAdapter = BuildDataAdapter();
      DataSet dataSet = new DataSet();

      using (SQLiteConnection conn = new SQLiteConnection(connString))
      {
        dataAdapter.SelectCommand.Connection = conn;
        dataAdapter.UpdateCommand.Connection = conn;

        dataAdapter.Fill(dataSet);

        // change some data
        DataRow contactRow = dataSet.Tables[0].Rows[2];  // find Lisa Simpson row.
        contactRow["FirstName"] += "X";
        contactRow["Stamp"] = DateTime.Now.ToString("s");

        // save the changed data back to the database
        dataAdapter.Update(dataSet.Tables[0]); // EXCEPTION THROWN HERE.
        // Exception reads: "The database file is locked"
        // "Unable to close due to unfinalised statements"
        // NOTE: the data IS actually saved. Seems to error after finishing the update SQL.
      }
    }

 but:

    // This version works ... different connections for select and update.
    private static void TestOnDifferentConnections()
    {
      DataSet dataSet = new DataSet();     // the DataSet being used
      SQLiteDataAdapter dataAdapter = BuildDataAdapter();

      using (SQLiteConnection conn = new SQLiteConnection(connString))
      {
        dataAdapter.SelectCommand.Connection = conn;
        dataAdapter.Fill(dataSet);
      }

      using (SQLiteConnection conn = new SQLiteConnection(connString))
      {
        dataAdapter.UpdateCommand.Connection = conn;

        // change some data
        DataRow contactRow = dataSet.Tables[0].Rows[2];  // find Lisa Simpson row.
        contactRow["FirstName"] += "X";
        contactRow["Stamp"] = DateTime.Now.ToString("s");

        // save the changed data back to the database
        dataAdapter.Update(dataSet.Tables[0]); // No exception here.
      }
    }

I think the first function should work properly shouldn't it?

I'm thinking this might have something to do with internal transactions not closing properly because properly bounding the first version with an explicit transaction allows it to complete successfully.

I've created a small console application that shows the three scenarios discussed with more comments to explain what's going on if it's of any help.
The zip file containing the project (about 300k) is located (for a time) here: http://www.CryLikeLincoln.com/HelpMe/DataAdapterLock.zip

--rgesswein

This bug is fixed in 1.0.33.0 which I will be posting this weekend.  Thanks for the report!

Robert

 

I have the same problem with v1.0.46 , but it worked fine with version 1.0.43