Checking if Table Exists Keeping Connection Open in SQLite

Question

So I have a method which is supposed to check if a table exists in a database which is defined as follows:

internal override bool TableExists(string tableName)
{
    bool tableExists = false;

    // Check the Tables schema and see if the table exists
    using (SQLiteConnection conn = (SQLiteConnection) CreateConnection())
    {
        conn.Open();
        DataRow[] rows = conn.GetSchema("Tables").Select(string.Format("Table_Name = '{0}'", tableName));
        tableExists = (rows.Length > 0);
    }

    // Actually called elsewhere in the code, just here for testing.
    File.Delete(DatabaseEnvironmentInfo.GetPrimaryDataFile(DatabaseName));

    return tableExists;
}

CreateConnection() just creates a new connection with a connection string so I don't think the issue is there. If I have remove the line conn.GetSchema("Tables")... and I am able to delete the database file but if I add that line back in I get the following exception when I try to delete after the using:

System.IO.IOException: The process cannot access the file 'C:\db.sqlite' because it is being used by another process..

Do DataRowobjects keep a connection to the database or does anyone know what the issue could be? If there is a better way to check if a table exists in SQLite I am open to that as well.

Thanks!

score 2 · Accepted Answer · answered May 13 '11 at 20:48

Ok so I've figured out the issue so I'll post it here in case anyone comes across the same problem. Basically I had connection pooling enabled so the connections were maintaining an open connection with the database and that was why i was seeing the exception. Just add the following after the using:

SQLiteConnection.ClearAllPools();

score 0 · Answer 2 · edited May 23 '17 at 12:18

0

I've had a similar problem without using connection pooling. I've found that the problem was caused by the missing disposal of SQLiteCommands in the TableAdapters.

You can find additional details here .

edited May 23 '17 at 12:18

Community

1
1

answered Oct 01 '12 at 19:48

edymtt

1,778
1
21
37

score 0 · Answer 3 · answered May 13 '11 at 15:26

0

If you add conn.Close() at the end of your using, can you delete your database ?

answered May 13 '11 at 15:26

Martin

5,954
5
30
46

I have already tried that and it doesn't help. Plus I'm pretty sure the the using statement will explicitly close the connection when we exit scope. – Ian Dallas May 13 '11 at 15:27
The using statement makes sure that dispose will be called and the object is registered for garbage collection when the block ends. I though that maybe the dispose had not been called yet when you try to delete the file – Martin May 13 '11 at 15:30
Yeah I suppose that's possible. But in the real code I try to delete the database much further along in the code. This is just where I narrowed it down to. – Ian Dallas May 13 '11 at 15:32

Checking if Table Exists Keeping Connection Open in SQLite

3 Answers3

Linked