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. I guess you are looking for UoW-Per-Request pattern. Solution given in link helps you achieve this. — Amit Joshi
– Amit Joshi, Commented Nov 19, 2019 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
– Sunny12, Commented Nov 19, 2019 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
– Sunny12, Commented Nov 19, 2019 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
– Sunny12, Commented Nov 19, 2019 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
– Sunny12, Commented Nov 19, 2019 at 12:45

Sunny12 · Accepted Answer · 2019-11-20 11:17:02Z

12

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, 2019 at 11:17

Sunny12

3831 gold badge3 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

felix Antony Over a year ago

Did you read the comments? ;)

felix Antony Over a year ago

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

Steve Lillis Over a year ago

@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.

Alexander Pavlenko Over a year ago

@SteveLillis If you use ServiceProvider to initialize dependency then according to Ms docs it's your responsibility to dispose resources.

Palle Due · Accepted Answer · 2019-11-19 09:54:02Z

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, 2019 at 9:54

answered Nov 19, 2019 at 9:48

Palle Due

6,3224 gold badges22 silver badges36 bronze badges

7 Comments

Sunny12 Over a year ago

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 Over a year ago

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?

Palle Due Over a year ago

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.

Sunny12 Over a year ago

The connection will get disposed at the end of the request as IDbconnection is injected using Transient service like services.AddTransient<IDbConnection>((sp) => new SqlConnection(dbConnectionString));

Palle Due Over a year ago

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.

|

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related