Why does this `using` block not release the database connection?

Question

Edit: @Jeroen Mostert's comment worked well: adding Pooling=false to the connection string. As he/she noted, I have to read some more about this additions impact on overall performance.

Lang: C#
DBMS: Postgres

In the code below with two consecutive using blocks (much has been deleted to make it clear);

In the first using block, before dropping the database camsdb, I connect to the database for the last time and do a final query.
After the query, the code exits the first using block and is supposed to close the database connection.
Unfortunately, the second using block (which is supposed to drop the database that was just connected to in the first using block) fails to drop the database. The PostgresException says "There is 1 other session using the database."

However, when I comment out the first using block, the second using block —fulfilling its duty— drops the database without any problem. Isn't that using block supposed to close the connection when the code reaches the related curly brace? Is there a way to force the Garbage Collector to clean the connection immediately? (Any other solutions are welcome.)

private M_SqlConn m_sqlConn = null; // predefined
...
...

// *** using block no: 1 ***
using (m_sqlConn = new M_SqlConn("127.0.0.1", 5432, "camsdb", "my_user_name", "my_password")) {
    // perform a last sql query
}

// *** using block no: 2 ***
using (m_sqlConn = new M_SqlConn("127.0.0.1", 5432, "postgres", "postgres", "admin_password")) {
    if (this.DbDrop("camsdb")) {
        FormWarn_ShowDialog(FormWarn.FormType.Info, "db dropped succesfully ...")
    }
    else {
        FormWarn_ShowDialog(FormWarn.FormType.Error, "can not drop db !!!")
    }
}

The M_SqlConn class as a whole is below (didn't want to mess up the page, that's why I didn't paste it at first):

using Npgsql;
using System;
using System.Text;

internal class M_SqlConn : IDisposable {
    public Boolean connectionIsOpen = false;
    private FormWarn formWarn = null;
    private NpgsqlConnection sqlConnection = null;
    private String hostAddress = "";
    private String portNumber = "";
    private String dbName = "";
    private String roleName = "";
    private String password = "";

    internal M_SqlConn(String hostAddress, String portNumber, String dbName, String roleName, String password) {
        this.hostAddress = hostAddress;
        this.portNumber = portNumber;
        this.dbName = dbName;
        this.roleName = roleName;
        this.password = password;
        this.ConnectionOpen();
        return;
    }

    private void ConnectionOpen() {
        StringBuilder exceptionString = new StringBuilder(String.Empty);
        NpgsqlConnectionStringBuilder connectionStringBuilder = new NpgsqlConnectionStringBuilder {
            Host = this.hostAddress,
            Port = Convert.ToInt32(this.portNumber),
            Database = this.dbName,
            Username = this.roleName
        };

        this.sqlConnection = new NpgsqlConnection(connectionStringBuilder.ToString() + $";Password={this.password}");

        try {
            this.sqlConnection.Open();

            if (this.sqlConnection.State == System.Data.ConnectionState.Open) {
                this.connectionIsOpen = true;
            }
        }
        catch (PostgresException e) {
            exceptionString.AppendLine(
                $"{Environment.NewLine}" +
                $"Postgres Exception{Environment.NewLine}" +
                $"{e.ToString()}{Environment.NewLine}");
        }
        catch (NpgsqlException e) {
            exceptionString.AppendLine(
                $"{Environment.NewLine}" +
                $"Npgsql Exception{Environment.NewLine}" +
                $"{e.ToString()}{Environment.NewLine}");
        }
        catch (Exception e) {
            exceptionString.AppendLine(
                $"{Environment.NewLine}" +
                $"General Exception{Environment.NewLine}" +
                $"{e.ToString()}{Environment.NewLine}");
        }

        if (!this.connectionIsOpen) {
            this.FormWarn_ShowDialog(FormWarn.FormType.Error, exceptionString.ToString());
        }

        return;
    }

    private void ConnectionClose() {
        StringBuilder exceptionString = new StringBuilder(String.Empty);

        if (this.sqlConnection != null) {
            try {
                this.sqlConnection.Close();
                this.connectionIsOpen = false;
            }
            catch (PostgresException e) {
                exceptionString.AppendLine(
                    $"{Environment.NewLine}" +
                    $"Postgres Exception{Environment.NewLine}" +
                    $"{e.ToString()}{Environment.NewLine}");
            }
            catch (NpgsqlException e) {
                exceptionString.AppendLine(
                    $"{Environment.NewLine}" +
                    $"Npgsql Exception{Environment.NewLine}" +
                    $"{e.ToString()}{Environment.NewLine}");
            }
            catch (Exception e) {
                exceptionString.AppendLine(
                    $"{Environment.NewLine}" +
                    $"General Exception{Environment.NewLine}" +
                    $"{e.ToString()}{Environment.NewLine}");
            }

            if (this.connectionIsOpen) {
                this.FormWarn_ShowDialog(FormWarn.FormType.Error, exceptionString.ToString());
            }
            else {
                this.sqlConnection.Dispose();
                this.sqlConnection = null;
            }
        }

        return;
    }

    public Boolean SqlCommandExecuteNonQuery(String sqlString) {
        Boolean commandStatus = false;
        StringBuilder exceptionString = new StringBuilder(String.Empty);

        using (NpgsqlCommand sqlCommand = new NpgsqlCommand(sqlString, this.sqlConnection)) {
            try {
                sqlCommand.ExecuteNonQuery();
                commandStatus = true;
            }
            catch (PostgresException e) {
                exceptionString.AppendLine(
                    $"{Environment.NewLine}" +
                    $"Postgres Exception{Environment.NewLine}" +
                    $"{e.ToString()}{Environment.NewLine}");
            }
            catch (NpgsqlException e) {
                exceptionString.AppendLine(
                    $"{Environment.NewLine}" +
                    $"Npgsql Exception{Environment.NewLine}" +
                    $"{e.ToString()}{Environment.NewLine}");
            }
            catch (Exception e) {
                exceptionString.AppendLine(
                    $"{Environment.NewLine}" +
                    $"General Exception{Environment.NewLine}" +
                    $"{e.ToString()}{Environment.NewLine}");
            }
        };

        if (!commandStatus) { 
            exceptionString.AppendLine(
                $"{Environment.NewLine}" +
                $"Sql string is:{Environment.NewLine}" +
                $"{sqlString}{Environment.NewLine}");
            this.FormWarn_ShowDialog(FormWarn.FormType.Error, exceptionString.ToString());
        }

        return (commandStatus);
    }

    public Int32 SqlCommandExecuteScalar(String sqlString) {
        Int32 count = -1;
        StringBuilder exceptionString = new StringBuilder(String.Empty);

        using (NpgsqlCommand sqlCommand = new NpgsqlCommand(sqlString, this.sqlConnection)) {
            try {
                Int32 countTmp = Convert.ToInt32(sqlCommand.ExecuteScalar());
                count = countTmp;
            }
            catch (PostgresException e) {
                exceptionString.AppendLine(
                    $"{Environment.NewLine}" +
                    $"Postgres Exception{Environment.NewLine}" +
                    $"{e.ToString()}{Environment.NewLine}");
            }
            catch (NpgsqlException e) {
                exceptionString.AppendLine(
                    $"{Environment.NewLine}" +
                    $"Npgsql Exception{Environment.NewLine}" +
                    $"{e.ToString()}{Environment.NewLine}");
            }
            catch (Exception e) {
                exceptionString.AppendLine(
                    $"{Environment.NewLine}" +
                    $"General Exception{Environment.NewLine}" +
                    $"{e.ToString()}{Environment.NewLine}");
            }
        };

        if (count == -1) {
            exceptionString.AppendLine(
                $"{Environment.NewLine}" +
                $"Sql string is:{Environment.NewLine}" +
                $"{sqlString}{Environment.NewLine}");
            this.FormWarn_ShowDialog(FormWarn.FormType.Error, exceptionString.ToString());
        }

        return (count);
    }

    public NpgsqlDataReader SqlCommandExecuteQuery(String sqlString) {
        NpgsqlDataReader dataReader = null;
        StringBuilder exceptionString = new StringBuilder(String.Empty);

        using (NpgsqlCommand sqlCommand = new NpgsqlCommand(sqlString, this.sqlConnection)) {
            try {
                dataReader = sqlCommand.ExecuteReader();
            }
            catch (PostgresException e) {
                exceptionString.AppendLine(
                    $"{Environment.NewLine}" +
                    $"Postgres Exception{Environment.NewLine}" +
                    $"{e.ToString()}{Environment.NewLine}");
            }
            catch (NpgsqlException e) {
                exceptionString.AppendLine(
                    $"{Environment.NewLine}" +
                    $"Npgsql Exception{Environment.NewLine}" +
                    $"{e.ToString()}{Environment.NewLine}");
            }
            catch (Exception e) {
                exceptionString.AppendLine(
                    $"{Environment.NewLine}" +
                    $"General Exception{Environment.NewLine}" +
                    $"{e.ToString()}{Environment.NewLine}");
            }
        };

        if (exceptionString.Length > 0) {
            exceptionString.AppendLine(
                $"{Environment.NewLine}" +
                $"Sql string is:{Environment.NewLine}" +
                $"{sqlString}{Environment.NewLine}");
            this.FormWarn_ShowDialog(FormWarn.FormType.Error, exceptionString.ToString());
        }

        return (dataReader);
    }

    private void FormWarn_ShowDialog(FormWarn.FormType formType, String msg) {
        using (this.formWarn = new FormWarn(formType, msg)) {
            this.formWarn.ShowDialog();
        }

        this.formWarn.Dispose();
        this.formWarn = null;
        return;
    }

    public void Dispose() {
        if (this.formWarn != null) {
            this.formWarn.Dispose();
            this.formWarn = null;
        }

        if (this.connectionIsOpen) {
            this.ConnectionClose();
        }

        this.hostAddress = "";
        this.portNumber = "";
        this.dbName = "";
        this.roleName = "";
        this.password = "";
        return;
    }
}

I've tried @Panagiotis's trick but it didn't work. Changed the if block to the below but this time the program halted giving "System.NullReferenceException: 'Object reference not set to an instance of an object.'"

if (this.sqlConnection.State == System.Data.ConnectionState.Open) {
    this.sqlConnection.Dispose(); // exception here
    this.sqlConnection = null;
}

Could you show this class? Are you write code to close connection in Dispose method? — TemaTre
– TemaTre, Commented Dec 20, 2019 at 12:11
The garbage collector is not the problem, incidentally. It's either your own Dispose logic, or else the Postgres database provider keeps the (physical) connection pooled. There are typically ways to turn that off for a specific connection, but that depends on the connection library used. (Clearing the connection pool is another approach, but that's a global solution to a local problem.) — Jeroen Mostert
– Jeroen Mostert, Commented Dec 20, 2019 at 12:15
Now that you posted the code, you don't call sqlConnection.Close at all. You should call sqlConnection.Dispose() whether it's open or not. It's the connection's job to check and take care of its status. All tutorials and examples show using a connection inside a using block, so not only can you use Dispose() on it, it's the only thing guaranteed to always work — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 20, 2019 at 12:20
NpgSqlConnection supports connection pooling; if the "open session" persists despite disposing everything, create a non-pooled connection by passing Pooling = false to the connection string builder for that connection only. Take care not to make this a global default, as that would negatively impact performance for most applications. — Jeroen Mostert
– Jeroen Mostert, Commented Dec 20, 2019 at 13:29

jean · Accepted Answer · 2019-12-20 13:14:20Z

You, for sure, already know it but let's settle some concepts.

What using means in C#? It means if the class implements IDisposable, the Dispose() method will be called at the end fo the Object instance life-cycle.

Who opens and close the connection? The library does it. You merely call some method of an instance of some class exposed by the library and hopes it does the job for you.

Why are connections kept? Most times you do want to reuse the same connection with your app, and most times that's great since each connection consumes resources. That's why we have connections pools.

What can you do to mitigate your problem? You can try another library as suggested in the comments. Or see TemaTre answer she/he looks like to have a good insight into that specific library.

But also note you are doing something unusual. You do have two connections (and two connection pools?). One for a "normal" user to query the database. A second conn for an admin to drop the database.

I can suggest you to first put the database in single-user mode and to be sure there are no other connections to it, even connections external to your application. You can achieve it by using simple SQL commands before dropping the DB.

TemaTre · Accepted Answer · 2019-12-20 14:03:45Z

2

You are not calling dispose for your sqlConnection. please uncomment this.

this.sqlConnection.Dispose();

And remove the calling of your method CloseConnection();

As you can see here https://github.com/npgsql/npgsql/blob/01f3f97bf7500229f896fda1f56279da78894d12/src/Npgsql/NpgsqlConnection.cs

NpgsqlConnection is IDisposable. And when you call Dispose for it, it should run all logic and close the connection. It is not correct to write a Wrapper that will call Close for it. When you do this it can have a random behavior.

One question, if NpgsqlConnection is IDisposable from the box, are you really needing your OWN class that is a Wrapper (or Proxy) in nature?

edited Dec 20, 2019 at 14:03

answered Dec 20, 2019 at 12:10

TemaTre

1,4442 gold badges13 silver badges21 bronze badges

9 Comments

ssd Over a year ago

I've pasted the ConnectionClose method.

TemaTre Over a year ago

Update my answer

Panagiotis Kanavos Over a year ago

@ssd just don't use that method. Use Dispose() instead. There's no reason to perform the same checks that Dispose() does.

ssd Over a year ago

@PanagiotisKanavos & TemaTre: Let me try that way. I'll let you know.

ssd Over a year ago

Nope! Same problem persists though I've commented the whole if block and left the Close connection duty solely to Npgsql.

|

Daniel W. · Accepted Answer · 2024-11-21 14:44:13Z

0

What you need to do this while pooling is active is to call NpgsqlConnection.ClearAllPools(); or NpgsqlConnection.ClearPool(conn);

I had the same issue and find out that call ClearAllPools();in Dispose of my wrapper fixed my problem.

answered Nov 21, 2024 at 14:44

Daniel W.

1,18813 silver badges27 bronze badges

Collectives™ on Stack Overflow

Why does this `using` block not release the database connection?

3 Answers 3

Comments

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related