Firebird ADO.NET connection does not dispose file properly after disposing the connection

Question

This method has to access a database file and perform a simple query:

private static DataTable QueryStuff(string connectionString, string[] types)
{
        string queryTypes = "('" + string.Join("', '", types) + "')";
        DataTable dtResults = new DataTable();

        try
        {
            using (FbConnection myConnection1 = new FbConnection(connectionString))
            {
                myConnection1.Open();

                FbTransaction fbTransaction = myConnection1.BeginTransaction();

                FbCommand fbCommand = new FbCommand();
                fbCommand = new FbCommand()
                {
                    CommandText = "SELECT  * FROM TABLE WHERE TYPE IN " + queryTypes + ";",
                    CommandType = CommandType.Text,
                    Connection = myConnection1,
                    Transaction = fbTransaction
                };

                FbDataReader dr = fbCommand.ExecuteReader();

                dtResults.Load(dr);

                dr.Close();

                fbTransaction.Commit();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("Erro: " + ex.Message);
        }

        return dtResults;
}

Afterwards, the program has to delete the database file, like this:

if (File.Exists(filePath))
    File.Delete(filePath);

Except, it can't, for the file is in use by 'another process'.

The program itself has created the file, and I am sure that the file is not being used in any other process.

Is there a better way to dispose this connection?

Even if the connections are all closed, the embedded database engine is still running. You'll have to find a way to shut it down. — madreflection, Apr 30 '20 at 03:49

score 1 · Answer 1 · edited Aug 19 '20 at 13:24

This probably has to do with the pooling option:

"Database connection pooling is a method used to keep database connections open so they can be reused by others.

Typically, opening a database connection is an expensive operation, especially if the database is remote. You have to open up network sessions, authenticate, have authorisation checked, and so on. Pooling keeps the connections active so that, when a connection is later requested, one of the active ones is used in preference to having to create another one."

(source: https://stackoverflow.com/a/4041136/14131713)

To disable this feature, you have to edit your connection string, adding "Pooling = false" to it:

$"User={user};" +
$"Password={password};" +
$"Database={path};" +
"DataSource=localhost;" +
**"Pooling=false";**

Firebird ADO.NET connection does not dispose file properly after disposing the connection

1 Answers1