CODEWITHSUNDEEP
asp.net-coredappernpgsqlasp.net-core-3.0
Sunny12
Sunny12

Reputation: 383

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

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.

Upvotes: 11

Views: 10879

Answers (2)

Sunny12
Sunny12

Reputation: 383

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?

Upvotes: 12

Palle Due
Palle Due

Reputation: 6292

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.

Upvotes: 2

Related Questions