Postgres Npgsql Connections Pools with Code

Question

I am aware of this post: Postgres Npgsql Connection Pooling But it hasn't completely solved my issue.

I conceptually understand how connection pools work but I am struggling to understand how to apply them in my .NET app using Npgsql.

Here is what I am currently doing:

NpgsqlConnectionStringBuilder cb ...    // build command and set properties
cb.Pool = True                         // set pool setting to True and set max pool

NpgsqlConnection conn =  new NpgsqlConnection(cb.ConnectionString)

public void thread1()
    NpgsqlCommand cmd = new NpgsqlCommand(conn)
    cmd.Connection.Open()
    // code
    cmd.ExecuteNonQuery
    
    cmd.Connection.Close()

public void thread2() {
    //... same thing
}

From what I understand whenever I make the new command I am actually just taking the same connection from the connection pool. I don't understand how to make use of the rest of the connection pool by only making one NpgsqlConnection object.

What happens when I do NpgsqlConnection.Open() (aka conn.Open()) vs cmd.Connection.Open()?

I feel there is something basic I am not understanding.

See : https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling — jdweng, Apr 21 '21 at 23:46
*"whenever I make the new command"* - No, whenever you make a new *connection*, it might take one from the pool if one's available. It's connection pooling, not command pooling. You've misunderstood connection pooling. You can create local variable `NpgsqlConnection` objects all you want, and you should, as long as you're also disposing them correctly (read: `using` blocks). You shouldn't touch `cmd.Connection` directly. The connection object and the underlying connection (socket, named pipe, whatever) are not the same thing, and it's the underlying connection that's pooled. — madreflection, Apr 21 '21 at 23:47
@madreflection Thanks for your explanation. So every time I create the new connection object, it sees the same connection string properties and associates it with the initial pool. Is that correct? — Astupidhippo, Apr 22 '21 at 13:01
Close. Not *"initial pool"* but rather the pool for those properties (it might not be the first/initial pool). Actually, depending on the implementation (we're talking about connection pooling in general, but I believe each ADO.NET provider implements connection pooling for itself), it may just compare the string, character by character, not the set of property values. So descriptions usually just say "same connection string". But otherwise, yes, that is correct. — madreflection, Apr 22 '21 at 15:00

Postgres Npgsql Connections Pools with Code

0 Answers0