what can be done for connection re use?

Question

I am using azure postgres sql where I have one of the common method which gives me connection string,

protected readonly string databaseConnString = "Host={0}.postgres.database.azure.com;Username={1}@{2};Password={3};DB={4};Ssl Mode=Require";
    

 protected virtual async Task<NpgsqlConnection> GetDBConnection()
    {
        var connString = string.Empty;
        NpgsqlConnection conn;

        try
        {
            //certificate check
            conn = new NpgsqlConnection(databaseConnString);

            try
            {
                //open the connection
                await conn.OpenAsync();
            }
            catch (PostgresException ex)
            {
                
            }
            finally
            {
                //close the connection
                await conn.CloseAsync();
            }
        }
        
        //return connection string
        return conn;
    }

and I am consuming above connection string everywhere in the application when again I am using open/close connection every time,

   using (var conn = await GetDBConnection())
        {
          await conn.OpenAsync();
          //do the work
          await conn.CloseAsync();
        }

I am using npgsql .net core library and came to know that PgBouncer will not help here. How can I minimize the connection open/close and can achieve connection pooling?

You can set Connection Pooling Settings in your connection string : [Doc](https://www.npgsql.org/doc/connection-string-parameters.html#pooling) — Eldar, Sep 07 '20 at 06:20
Why did you create this method? Don't outsmart the library's and framework's connection pooling mechanisms. Are you trying to solve a problem with this code? — CodeCaster, Sep 07 '20 at 06:38

score 3 · Accepted Answer · edited Sep 07 '20 at 21:12

3

Pooling is controlled by Pooling=true in the connection string, however it's on by default.

When you call NpgsqlConnectiona.Open the physical connection will be taken from the pool if one is exists, you don't have to do anything.
On disposing NpgsqlConnection it will be returned to the pool to be reused.

By default there is nothing you need to do or worry about.

Pooling Documentation

edited Sep 07 '20 at 21:12

halfer

19,824
17
99
186

answered Sep 07 '20 at 06:20

TheGeneral

79,002
9
103
141

Do I need to add `Pooling=true` in connection string? Is there any article you can share – user584018 Sep 07 '20 at 06:24
@user584018 no you dont, its on by default, you don't have to do anything – TheGeneral Sep 07 '20 at 06:26
one point when I am doing `await conn.CloseAsync();`, then this connection back to pool. Is this mean I never used existing open connection as every time I am doing open connection and close connection – user584018 Sep 07 '20 at 06:34
If you are using a `using` statement, you don't need to call close `explicitly` though can if you like. You always need to call `Open`. In short your are doing everything right as is – TheGeneral Sep 07 '20 at 06:36

what can be done for connection re use?

1 Answers1