When should I call CloseAsync()？

[yu-xiaowen]

In every synchronous API example of the Wiki, there is conn.Close() in every method. In the asynchronous API example of the Wiki, there is no call to conn.CloseAsync(). When should I call conn.CloseAsync()? Should I write new SQLiteAsyncConnection("foofoo") and CloseAsync() in each async method?

[csm101]

Because SqliteAsyncConnection is not really a connection: it is a global static dictionary of regular SqliteConnection instances that get shared among all possible threads and tasks in your application.

oversimplifying it is something like this:

  public class SqliteAsyncConnection
  {
     private static Dictionary<string, SQLiteConnection> sharedConnections = new(); // notice this is STATIC
     
     private static SqliteConnection GetConnectionForConnectString(string connectString)
     {
         if (sharedConnections.TryGetValue(connectString, out var result))
              return result;
         result = new SqliteConnection(connectString);
         sharedConnections.Add(connectString,result);
         return result;
     }
  }

so, basically SqliteAsyncConnection is just a cache of already opened and SYNCRONOUS connections, with some locking mechanisms put in place.

its main job is to make impossible for two task running in the same thread to overlap write operations belonging to two totally different transactions: the database engine has no way to understand that the sql commands is receiving from the same process are actually executed by totally unrelated tasks that get executed in an interleaved way in the same process due to the async/await state c# feature.

you can't close SqliteAsyncConnection because it is not a real connection, and if you try to close the underlying synchronous connections (you get access to it in the lambda you provide to RunInTransactionAsync, and you should use it only in the context of such lambda) you would close a connection that is shared by multiple threads/tasks.

[CodingOctocat]

@csm101 So, what does CloseAsync do? Going back to the title, do I need to call CloseAsync? Do I need CloseAsync in IAsyncDisposable? Is it a managed resource or an unmanaged resource?

[csm101]

@csm101 So, what does CloseAsync do? Going back to the title, do I need to call CloseAsync? Do I need CloseAsync in IAsyncDisposable? Is it a managed resource or an unmanaged resource?

(AFAIK ISQLiteAsyncConnection is a recent addition and I suppose it has been added in order to make it possible to use some mocking framework like NSubstitute when writing unit tests, instead of using actual sqlite databases)

It is not disposable for a good reason: there is nothing that gets created for your exclusive use when you instantiate a SQLiteAsyncConnection: you just get a handle to a shared connection and you aren't necessarily the only one using such connection. If you close it just because your task has finished working on the database, you crash all the other tasks that were using the same handle

In other words: if you have some class that acts as a coordinator of all the database activity (for example: you have a class responsible of launching and stopping all the possible processes that do actually work on your database), you could have in that class a method that calls "StopMyDatabaseTasks" and only after having stopped them, calls "CloseAsync". For example you might need this if you want to perform a backup of the database files.

but in other situations I don't think you really need to call CloseAsync()