How to handle postgresql db connections with dapper using dependency injection in .net core?

Question

I am using Dapper ORM for database operations in my asp.net core web API project. Right now I am opening the new database connection every time and using it inside using block so they will get disposed when scope ends. But I was looking to handle all those connections without using using block and also want to dispose of them automatically. I was searching for a way to achieve this using dependency injection as they dispose of objects that implements IDisposable, automatically.

Here is how I am handling all the DB connections:

Created a GetConnection property in my base repository:

private IDbConnection _connection;

public IDbConnection GetConnection
{
    get
    {
        _connection = new NpgsqlConnection("Connection String");
        return _connection;
    }
}

Accessing the property inside using block:

public async Task<IEnumerable<T>> GetAllAsync()
{
    IEnumerable<T> records = null;

    using (IDbConnection connection = GetConnection)
    {
        //db operations
    }

    return records;
}

So how can I achieve the same using dependency injection that will initialize the IDbconnection when required and disposes of at the end of the request without the need to encapsulate IDbconnection inside the using block?

In short, I want to avoid using GetConnection property every time to create a database object and eliminate using using blocks to dispose of the same.

Why you want to remove `using` blocks? Suggesting best practices is actually opinion based but you may have a look at [this](https://stackoverflow.com/a/40827671/5779732). I guess you are looking for UoW-Per-Request pattern. Solution given in link helps you achieve this. — Amit Joshi, Nov 19 '19 at 10:08
Because I want to dispose of each database connection using dependency injection and want to avoid using blocks in the application. — Sunny12, Nov 19 '19 at 10:13
like described here: stackoverflow.com/a/47403685/11748401 .. I just wanted to know what will be best practices, whether it will be good to handle database operations like described in this link or not? — Sunny12, Nov 19 '19 at 10:34
I do not want to include using blocks everywhere in my application where I am doing DB operations. They should get disposed of automatically at the end of the request. — Sunny12, Nov 19 '19 at 11:51
In short, just wanted to ask if the answer provided here: stackoverflow.com/a/47403685/11748401 is recommended for handling DB connections in dapper or not. Also, I got your point to inject UoW in startup.cs file so as to avoid using block. But If we inject IDbconnection in startup.cs file it will also dispose of the DB object as IDbconnection also implements IDisposable and we do not need to write dispose() in UoW neither calling dispose() will be required as all that will be handled by IDbconnection automatically. Whatever the operation will it be, it will get disposed automatically, — Sunny12, Nov 19 '19 at 12:45

score 8 · Accepted Answer · answered Nov 20 '19 at 11:17

8

I did it like this:

Adding Transient service in startup.cs file

services.AddTransient<IDbConnection>((sp) => new NpgsqlConnection("connectionString"));

Initializing the IDbconnection object in the base repository constructor like:

class  RepositoryBase
{
    protected IDbConnection _connection;

    protected RepositoryBase(IDbConnection dbConnection)
    {
         _connection = dbConnection;
    }
}

and performing the DB operation in my Repositories like

class XyzRepository : RepositoryBase
{
    public async Task<IEnumerable<T>> GetAllAsync()
    {
        IEnumerable<T> records = null;

        await _connection.ExecuteScalarAsync<object>("sqlQuery");

        return records;
    }

}

This will automatically dispose of IDbconnection object at the end of the request without using using blocks.

Reference from answer: How do I handle Database Connections with Dapper in .NET?

answered Nov 20 '19 at 11:17

Sunny12

303
3
11

Did you read the comments? ;) – felix Antony Feb 15 '22 at 18:24
No need to use a using statement. Dapper will automatically open, close, and dispose of the connection for you." That's not correct. Dapper will automatically open closed connections, and it will automatically close connections that it auto-opened, but it will not automatically dispose of connections. Marc Gravell and Eric Lippert both advocate using using with Dapper here. – MarredCheese Jul 9 2021 at 20:07 – felix Antony Feb 15 '22 at 18:25
@felixAntony You're right that Dapper won't dispose of the connection. However, dotnet's dependency injection container will dispose it once the connection object reaches the end of it's lifecycle (transient, in this case). This is true of any container-created object that implements `IDisposable`. – Steve Lillis Oct 17 '22 at 08:21

Palle Due · Answer 2 · 2019-11-19T09:54:02.370

2

You don't want to use only one database connection for your database access. When a database connection is disposed (at the end of the using block) it is given back to the connection pool, which is far more efficient and safe than any scheme you can come up with yourself.

If you want to remove the dependency on NpgsqlConnection you should make a connection factory that creates an IDbConnection and insert the factory into your classes. The using construct is good and best practice and not something you would want to get rid off.

There's more about connection pooling here.

Edit: Looking at your code I see that you already have abstracted the connection creation away. Your code is actually fine as it is.

edited Nov 19 '19 at 09:54

answered Nov 19 '19 at 09:48

Palle Due

5,929
4
17
32

I agree that using block disposes of the object and gives back to the connection pool and this is what the dependency injection does in .net core. If I inject the IDbConnection as Transient service it will automatically dispose of the database object without me writing the using block for any DB operation. – Sunny12 Nov 19 '19 at 10:11
like described here: https://stackoverflow.com/a/47403685/11748401 .. I just wanted to know what will be best practices, whether it will be good to handle database operations like described in this link or not? – Sunny12 Nov 19 '19 at 10:32
I don't see connections being disposed and recycled in the answer you are linking to. The connection is being kept for the life time of the repository. – Palle Due Nov 19 '19 at 11:21
1

The connection will get disposed at the end of the request as IDbconnection is injected using Transient service like services.AddTransient((sp) => new SqlConnection(dbConnectionString)); – Sunny12 Nov 19 '19 at 11:53
OK, thanks for forcing me to read up on `AddTransient`. I still think it's bad practice to keep the connection alive for the whole request. Connections should be disposed of as soon as possible. – Palle Due Nov 19 '19 at 12:34
I guess it could be helpful also as all the DB operations will be performed using that object only. Just sharing my thoughts don't sure whether it will be beneficial in all conditions or not. – Sunny12 Nov 19 '19 at 12:49
This article explains about different methods of disposing of an object: https://andrewlock.net/four-ways-to-dispose-idisposables-in-asp-net-core/ . – Sunny12 Nov 19 '19 at 13:14

How to handle postgresql db connections with dapper using dependency injection in .net core?

2 Answers2