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 : learn.microsoft.com/en-us/dotnet/framework/data/adonet/… — jdweng
– jdweng, Commented Apr 21, 2021 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
– madreflection, Commented Apr 21, 2021 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
– Astupidhippo, Commented Apr 22, 2021 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
– madreflection, Commented Apr 22, 2021 at 15:00

Ondrej Valenta · Accepted Answer · 2024-02-15 22:13:13Z

For ASP.NET in .Net 6 and Npgsql 8, this is my implementation..

Initialization

connection builder helper

public static class ConnectionHelper
{
    public static NpgsqlDataSource CreateDataSource(string connectionString, Action<NpgsqlDataSourceBuilder>? customConfigurationSetter = null)
    {
        var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);

        customConfigurationSetter?.Invoke(dataSourceBuilder);

        var dataSource = dataSourceBuilder.Build();

        return dataSource;
    }
}

register NpgsqlDataSource as Singleton, this is crucial, each instance has own connection pool

builder.Services.AddSingleton<NpgsqlDataSource, NpgsqlDataSource>(provider =>
{
    var connectionString = configuration.GetConnectionString("Database");

    return ConnectionHelper.CreateDataSource(connectionString);
})

in DatabaseProvider, or whatever you call the place where you call database, use Dependency Injection to give you instance of NpgsqlDataSource

private readonly ILogger<DatabaseProvider> logger;
private readonly NpgsqlDataSource dataSource;

public DatabaseProvider(ILogger<DatabaseProvider> logger, NpgsqlDataSource dataSource)
{
    this.logger = logger;
    this.dataSource = dataSource;
}

Use with DBCommand

when you create SqlCommand use NpgsqlDataSource.CreateCommand method like this and use it with await using

await using command = dataSource.CreateCommand("select * from table1");

await using var reader = await command.ExecuteReaderAsync(cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
  // ...
}

NOTE:

There is no need to open the connection. DBCommand does it automatically.

Collectives™ on Stack Overflow

Postgres Npgsql Connections Pools with Code

1 Answer 1

Initialization

Use with DBCommand

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Initialization

Use with DBCommand

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related