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.

Answer 1

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?