System.Data.SQLite

SQLiteConnection has a `internal SQLiteBase _sql` field which holds a Sqlite3 object.
Sqlite3 is IDisposable but SQLiteConnection but not appear to dispose of the _sql field when it is being disposed.

When many SQLiteConnections are being created, it appears that this can cause a performance issue (maybe a memory leak?). I profiled my application with dotMemory and noticed that there are many Sqlite3 objects in the finalizer queue.

The stacktrace in dotMemory looks like this.
```
System.Data.SQLite.SQLiteConnection.SetupSQLiteBase(SortedList<TKey, TValue>)
System.Data.SQLite.SQLiteConnection.Open()```

My usage is like this:
```
using (var conn = new SQLiteConnection("Data Source=" + fullPathName + ";Version=3;" + (sharedMode ? "cache=shared" : string.Empty)))
{
    conn.Open();
    conn.Insert(row);
}
```

Could you check if the `_sql` field should indeed be disposed and if so, dispose it? Otherwise, would you know why I see many instances of `Sqlite3` objects in the finalizer queue from dotMemory?

btw, Looking at the source code of 1.0.116 (latest version), I see the the Dispose method on SQLiteConnection doesn't dispose of the _sql field too, so I expect it to have the same issue

Connection objects should be disposed.  It's a bit more complex than the
typical IDisposable pattern due to things like connection pooling.  This
behavior should be tested pretty thoroughly by the test suite; however,
just to be completely sure, I will double check the expected behavior
manually.

I just attached an example program that reproduces what I saw, which is a very high number of finalized instances of Sqlite3 objects.

I'm expecting that they should be disposed and not appear in finalizable queue. If the GC isn't done in time, their memory consumption can grow significantly.

I looked at your example project and I strongly suspect is that Dapper is
somehow keeping the connection open past the end of the using block, e.g.
via not disposing of one or more SQLiteCommand and/or SQLiteDataReader
objects.

Using the following code of yours, slightly modified, appears to show the
connections being properly closed.  Aside: I have another variant of this
without Dapper which also shows the connections being properly closed. 

<verbatim>
  using System.Data.SQLite;
  using System.Diagnostics;
  using System.Runtime.CompilerServices;
  using Dapper.Contrib.Extensions;

  string fullPathName = Path.Combine(
      AppDomain.CurrentDomain.BaseDirectory, "mydb.sqlite");

  const string tableScript = @"CREATE TABLE IF NOT EXISTS MyTable (
      [Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
      Value INTEGER
      );";

  void CreateDB()
  {
      var fileInfo = new FileInfo(fullPathName);
      if (fileInfo.Exists)
      {
          fileInfo.Delete();
      }
      SQLiteConnection.CreateFile(fullPathName);
      using (var conn = new SQLiteConnection(
          "Data Source=" + fullPathName + ";Version=3;"))
      {
          conn.Open();
          var command = new SQLiteCommand(tableScript)
          {
              Connection = conn
          };
          using (command)
          {
              command.ExecuteNonQuery();
          }
      }
  }

  void SQLiteConnection_Changed(object sender, ConnectionEventArgs e)
  {
      if ((e.EventType == SQLiteConnectionEventType.Opened) ||
          (e.EventType == SQLiteConnectionEventType.Closed))
      {
          Console.WriteLine(String.Format("{0} {1}",
              RuntimeHelpers.GetHashCode(sender), e.EventType));
      }
  }

  void Main()
  {
      SQLiteConnection.Changed += new SQLiteConnectionEventHandler(
          SQLiteConnection_Changed);

      CreateDB();

      var watch = Stopwatch.StartNew();
      for(var i=0; i<10000; i++)
      {
          using (var conn = new SQLiteConnection(
              "Data Source=" + fullPathName + ";Version=3;"))
          {
              conn.Open();
              conn.Insert(new MyTable(){Value = 1});
          }
      }
      watch.Stop();
      Console.WriteLine($"took {watch.ElapsedMilliseconds/1000.0}s");
  }

  Main();
</verbatim>

It's not about the connection closing though.
It's about the `Sqlite3`/ `SQLiteBase` object in the `_sql` field in `SQLiteConnection` not being cleaned immediately.

I changed the inner loop to this (so 100k runs and dapper inserts is commented)
```
for(var i=0; i<100000; i++)
{
    using (var conn = new SQLiteConnection(
               "Data Source=" + fullPathName + ";Version=3;"))
    {
        conn.Open();
        //conn.Insert(new MyTable(){Value = 1});
    }
}
```

I still see a large number of `Sqlite3` objects in the finalizer queue, which I think should not be there. They should be disposed of when `SQLiteConnection` is disposed (via the using statement). If `Sqlite3` object is disposed, then there should be a `GC.SuppressFinalize` call that prevent it from being put into the finalizer queue.

In this example, the program can run fast enough to clear the finalize queue quickly, but in a large program will many other allocations, this will cause a performance issue.

I think the solution should be as simple as adding a `_sql.Dispose()` call when `SQLiteConnection` is disposed.

Thanks for the additional information.

The underlying (native) resources associated with a SQLiteConnection do
get disposed when the connection is closed unless connection pooling is
enabled.

It sounds like your primary concern is the finalizer queue.  I think your
idea of using the GC.SuppressFinalize method is correct.  The code should
be doing that when it gets done cleaning up the underlying CriticalHandle
and its associated native resources.

I will work on adding that and the necessary tests.

Thanks again for the report.

The fix for this is now on trunk, complete with tests.

Thanks! It's good to know that!