Missing information on the connection string page in Microsoft.Data.Sqlite when SqliteConnectionStringBuilder.Pooling=true

[submaie]

Type of issue

Missing information

Description

Background：
After deleting data from an SQLite database, the file size doesn't change.
This is a feature of the SQLite engine,I used a silly but useful method, deleting the database file when the program exits and copying it from an empty database backup file.
An IOException is thrown when using File.Delete(path) to delete a database file.

IOException
The process cannot access the file 'path\sxdata.db' because it is being used by another process.
   at System.IO.FileSystem.DeleteFile(String fullPath)
   at SanxingReport.App.App_ShutdownRequested(Object sender, ShutdownRequestedEventArgs e) in E:\SanxingReport\ReportGenerator\App.axaml.cs:line 58

I used the built-in 'Resource Monitor' in Windows 11 to search for references to the db file, but found nothing.

Problem Description:

The database connection string being used is as follows：

SqliteConnectionStringBuilder sqliteString = new()
{
    Cache = SqliteCacheMode.Default,
    DataSource = Path.Combine(Environment.CurrentDirectory, "sxdata.db"),
    ForeignKeys = false,
    Mode = SqliteOpenMode.ReadWrite,
    Pooling = true
};

In the experimental project, the static SqliteConnection class was used.
As long as the SQL code executes operations, even if used SqliteConnection.Close() and SqliteConnection.Dispose() ,the app is still referencing the sxdata.db file.

Solution:

When SqliteConnectionStringBuilder.Pooling=true; to enable connection pooling, and after using SqliteConnection.Open();, you must call SqliteConnection.ClearAllPools() before operating on the .db database file.

Influence:
Referenced an issue in EfCore (https://github.com/dotnet/efcore/issues)
This issue has been brought up multiple times.
For example：
Upgrade to 6.0: closing connection leaves SqlLite db file open
also
26605
26669
etc.

Solution：
In connection-string page
In the connection string, it explains in detail that when SqliteConnectionStringBuilder.Pooling=true;, must use SqliteConnection.ClearAllPools(); to disconnect from the database.

Page URL

https://learn.microsoft.com/zh-cn/dotnet/standard/data/sqlite/connection-strings

Content source URL

https://github.com/dotnet/docs/blob/main/docs/standard/data/sqlite/connection-strings.md

Document Version Independent Id

8eec3e85-0630-15db-ff40-c89920b89526

Platform Id

d4b28167-c555-9511-87a2-6cb7a86dd454

Article author

@ajcvickers

Metadata

• ID: 054ee24a-82e4-7bb5-fff5-bb1ab5713472
• PlatformId: d4b28167-c555-9511-87a2-6cb7a86dd454
• Service: dotnet-data-access

Related Issues