Delete sqlite db file

Question

I am developing a C# application with its backend as sqlite. In my application I have a button for cleaning the database (deleting the .db file and creating it again with some initial data). Sometimes when I try to delete the db it says it cannot be deleted because it is being used by another process. Before deleting it I am using close connection, dispose and clear pool functions. Even then it throws the same exception. Here is my code:

string targetDataBaseFilePath = Path.Combine(dataDirectoryPath, "local.db");               
ThingzDatabase.Create(targetDataBaseFilePath, "touchdb");

In the create function I define the following code, from where I get the error:

if (File.Exists(DatabaseFileName))
{
    try
    {
        ThingzDatabase.localdb.conn.Close();
        ThingzDatabase.localdb.conn.Dispose();
        SQLiteConnection.ClearAllPools();
    }
    catch (Exception)
    {
    }
    File.Delete(DatabaseFileName); //error throws from here.                       
}

How can I prevent the error from being thrown?

score 5 · Answer 1 · answered Jul 12 '20 at 14:53

5

FileInfo fi = new FileInfo(DatabasePath);
try
{
    if (fi.Exists)
    {
        SQLiteConnection connection = new SQLiteConnection("Data Source=" + DatabasePath + ";");
        connection.Close();
        GC.Collect();
        GC.WaitForPendingFinalizers();
        fi.Delete();
    }
}
catch(Exception ex)
{
    fi.Delete();
}

DatabasePath is your database file path

answered Jul 12 '20 at 14:53

Akshay Mishra

1,535
2
15
14

1

I used "File.Delete(dbPath);" instead of "FileInfo" and it worked thanks! – Tahirhan Jan 13 '21 at 06:32

score 2 · Answer 2 · answered Dec 15 '11 at 23:09

2

ThingzDatabase.localdb.conn.ClearAllPools();

should be the call for that line I believe.

answered Dec 15 '11 at 23:09

Travis J

81,153
41
202
273

This definitely needs more voices. In my case I am doing some tests with Setup/Teardown and relying on GC calls seemed really hacky. Note that the API seems to have change since then, it is now a static method on SQLiteConnection, you now call it like that: `SQLiteConnection.ClearAllPools()` – Luke Marlin Apr 04 '19 at 08:56

score 2 · Answer 3 · edited May 23 '17 at 11:50

2

I ended up just asking the question myself. The solution was to let garbage collection run for a bit.

edited May 23 '17 at 11:50

Community

1
1

answered Dec 16 '11 at 16:15

Tom Cerul

1,773
2
13
27

score 1 · Answer 4 · answered Dec 10 '11 at 13:38

1

I beleive you still haven't disposed something returned from API.

Which sqlite wrapper you use? You are quoting use of your custom functions, what do they do?

answered Dec 10 '11 at 13:38

Basilevs

22,440
15
57
102

I had a reference to a DB connection that wasn't disposed properly. Once I took care of that, this issue was resolved for me. Good call. – Jacob Robbins May 22 '18 at 14:05
I had the same issue, found out I did not use "using" for one SQLiteCommand in ADO.NET. – Petr May 25 '20 at 08:08

score 1 · Answer 5 · answered Jan 03 '12 at 13:38

1

In connection string use this Pooling=False; I had the same problem with pooling on.

answered Jan 03 '12 at 13:38

Tommix

133
1
2
10

score -1 · Answer 6 · answered Sep 25 '17 at 07:37

-1

I see a lot working with processes. But sqlite has a method for it. VACUUM

The VACUUM command rebuilds the database file, repacking it into a minimal amount of disk space.

https://sqlite.org/lang_vacuum.html

answered Sep 25 '17 at 07:37

eL-Prova

1,084
11
27

score -1 · Answer 7 · answered Dec 15 '11 at 23:12

-1

You can use a procmon like this one by Mark Russinovich.
Then you can filter the display on the basis of the filename.
Once this is done, you can see exactly which process is locking this file, and debug accordingly.

answered Dec 15 '11 at 23:12

Lelouch Lamperouge

8,171
8
49
60

Delete sqlite db file

7 Answers7