System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 0c26384ec44d9f8b8bdde4ccec8ebc9af5221a54
Title: System.Data.SQLite keeps database file locked after closing all connections
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Resource_Cleanup Resolution: Rejected
Last Modified: 2013-06-07 08:41:17
Version Found In: 1.0.86.0
User Comments:
anonymous added on 2013-06-07 07:51:27:
System.Data.SQLite keeps database file open after closing all connections. This problem started occurring in one of the previous releases of System.Data.SQLite, but it doesn't occur with 1.0.80.0.

I attach a NUnit test (below) which I wrote to check my previous post (http://system.data.sqlite.org/index.html/tktview/e06c4caff3c433c80616ae5c6df63fc830825e59). The test fails on its last line: 
File.Delete(fn); 
All the connections are enclosed within using(...) clauses, so I expect them to be closed after usage. Why cannot I delete database file afterwards?

With regards,
Dariusz Wasacz, InstalSoft

------------------------------------------------------
      [Test]
      public void Insert_Nan_using_ADO_NET()
      {
         var fn = Path.Combine(Path.GetTempPath(), FileNamePrefix + "Insert_Nan_using_ADO_NET.db3");
         if (File.Exists(fn)) File.Delete(fn);
         var connectionString = "Data Source=" + fn + ";Flags=BindAndGetAllAsText";
         // create database
         using (var connection = new SQLiteConnection(connectionString))
         {
            string exception = null;
            try
            {
               // Open the connection.
               connection.Open();

               // Create and execute the DbCommand.
               DbCommand command = connection.CreateCommand();
               command.CommandText =
                   "CREATE TABLE [EntA] (Id NVARCHAR(36) COLLATE NOCASE NOT NULL PRIMARY KEY ,[EntityProperty1] TEXT NOT NULL DEFAULT '',[DoubleProperty] FLOAT NOT NULL DEFAULT 0);";
               int rows = command.ExecuteNonQuery();

            }
            // Handle data errors.
            catch (DbException exDb)
            {
               exception = String.Format("DbException.GetType: {0}\r\n", exDb.GetType()) +
                  String.Format("DbException.Source: {0}\r\n", exDb.Source) +
                  String.Format("DbException.ErrorCode: {0}\r\n", exDb.ErrorCode) +
                  String.Format("DbException.Message: {0}\r\n", exDb.Message);
            }
            // Handle all other exceptions.
            catch (Exception ex)
            {
               exception = String.Format("Exception.Message: {0}", ex.Message);
            }
            Assert.AreEqual(null, exception);
         }

         // Insert double values with INF and NaN
         using (var connection = new SQLiteConnection(connectionString))
         {
            string exception = null;
            string result = null;
            try
            {
               // Open the connection.
               connection.Open();

               // Create and execute the DbCommand.
               DbCommand command = connection.CreateCommand();
               command.CommandText =
                   "INSERT INTO EntA (Id, EntityProperty1, DoubleProperty) VALUES (1, 'NaN', 'NaN'), (2, 'RegularDouble', '1.0'), (3, 'PositiveInfinity', 'Infinity'), (4, 'NegativeInfinity', '-Infinity')";
               int rows = command.ExecuteNonQuery();

               // Display number of rows inserted.
               result = String.Format("Inserted {0} rows.", rows);
            }
            // Handle data errors.
            catch (DbException exDb)
            {
               exception = String.Format("DbException.GetType: {0}\r\n", exDb.GetType()) +
                  String.Format("DbException.Source: {0}\r\n", exDb.Source) +
                  String.Format("DbException.ErrorCode: {0}\r\n", exDb.ErrorCode) +
                  String.Format("DbException.Message: {0}\r\n", exDb.Message);
            }
            // Handle all other exceptions.
            catch (Exception ex)
            {
               exception = String.Format("Exception.Message: {0}", ex.Message);
            }
            Assert.AreEqual(null, exception);
            Assert.AreEqual("Inserted 4 rows.", result);
         }

         // Assert correct values
         using (var connection = new SQLiteConnection(connectionString))
         {
            using (connection)
            {
               string exception = null;
               var result = new List<string>();
               try
               {
                  // Create the command.
                  DbCommand command = connection.CreateCommand();
                  command.CommandText = "SELECT EntityProperty1, DoubleProperty FROM EntA ORDER BY EntA.Id";
                  command.CommandType = CommandType.Text;

                  // Open the connection.
                  connection.Open();

                  // Retrieve the data.
                  DbDataReader reader = command.ExecuteReader();
                  while (reader.Read())
                  {
                     var entityProperty1 = (string)reader[0];
                     double doubleProperty = 0;
                     if (reader[1] != DBNull.Value)
                        doubleProperty = Double.Parse((string)reader[1], NumberStyles.Any, CultureInfo.InvariantCulture);
                     //Double.TryParse((string)reader[1], out doubleProperty);
                     result.Add(String.Format(CultureInfo.InvariantCulture, "EntityProperty1={0}, DoubleProperty={1}", entityProperty1, doubleProperty));
                  }
               }
               // Handle data errors.
               catch (DbException exDb)
               {
                  exception = String.Format("DbException.GetType: {0}\r\n", exDb.GetType()) +
                     String.Format("DbException.Source: {0}\r\n", exDb.Source) +
                     String.Format("DbException.ErrorCode: {0}\r\n", exDb.ErrorCode) +
                     String.Format("DbException.Message: {0}\r\n", exDb.Message);
               }
               // Handle all other exceptions.
               catch (Exception ex)
               {
                  exception = String.Format("Exception.Message: {0}", ex.Message);
               }
               Assert.AreEqual(null, exception);
               CollectionAssert.AreEqual(new[] { "EntityProperty1=NaN, DoubleProperty=NaN", "EntityProperty1=RegularDouble, DoubleProperty=1", "EntityProperty1=PositiveInfinity, DoubleProperty=Infinity", "EntityProperty1=NegativeInfinity, DoubleProperty=-Infinity" }, result);
            }

         }
         File.Delete(fn);
      }

mistachkin added on 2013-06-07 08:41:17:
All SQLiteCommand and SQLiteDataReader objects must be disposed as well prior to
the underlying native database connection being closed.  This behavior prevents
issues with out-of-order disposal of resources by the CLR GC.