Do I need to use Dependency Injection with a simple IDbConnection

Question

I have been using Blazor for about a year and still learning. The app I am developing was setup before I arrived and the decision was made to use a simple IDbConnection to connect to our database. Server-Side Blazor by the way.

Currently, we are adding an IDbConnection as Transient like this.

services.AddTransient<IDbConnection>(db => new SqlConnection());

We are not using EF. We are using MediatR and Dapper to facilitate our queries and commands (probably irrelevant to this discussion). The queries and commands are using a base class that has gets the IDbConnection from an injected IServiceProvider. We get the connection string from the configuration and provide it to the connection at this time.

So, it appears to me that every query that is run gets a new SqlConnection.

Based on my reading I thought we should be using AddScoped instead, but changeing from Transient to Scoped didn't really seem to make much difference as a new SqlConnection is created regardless.

Now I am thinking we don't need to use DI at all and we can just create a new SqlConnection in the base class.

Here is the SqlHandlerBase

public class SQLHandlerBase<T>
{
    protected readonly IDbConnection _dbConnection;
    private string ConnectionStringId = ConnectionConstants.AzureSql;
    protected readonly ILogger _log;
    protected readonly IConfiguration _configuration;

    public SQLHandlerBase(IServiceProvider serviceProvider, string connectionStringId = null)
    {
        if (connectionStringId != null)
        {
            ConnectionStringId = connectionStringId;
        }

        _configuration = serviceProvider.GetRequiredService<IConfiguration>();
        _dbConnection = serviceProvider.GetRequiredService<IDbConnection>();
        _dbConnection.ConnectionString = _configuration[ConnectionStringId];
        

        _log = serviceProvider.GetRequiredService<ILogger<T>>();
    }
}

Please help me out here

Answer 1

Generally speaking, creating a SQL connection will take a connection from the connection pool, and if that connection is not being used, you want to release it back to the connection pool as quickly as possible. To meet that end, the responsibility of opening the connection and closing / disposing of it properly is the responsibility of the consumer of the connection.

We do not want the IoC container in charge of cleaning up connections. See the last section on Service Lifetimes

Making the correlation to Entity Framework, you have a DbContext class, which is the Scoped service. The DbContext is a unit-of-work pattern, and does not open a connection to the database until a command or query is executed through Query or SaveChanges.

Your goal for the functionality of the base class, nor the reasoning behind why it is a generic wasn't entirely clear, so here's a couple of options / examples.

1. If you went the route of simply providing the connection object Your connection provider, derived from SqlHandlerBase<T> should be a Singleton, and it should not provide access to a connection object, but should not be opening the connection to the database. The consuming class would then be responsible for opening the connection, and disposing of it.

    public class SQLHandlerBase<T>
    {
        //// moved to GetConnection method
        //protected readonly IDbConnection _dbConnection;
        //// moved default to the constructor
        // private string ConnectionStringId = ConnectionConstants.AzureSql; 

        protected readonly ILogger _log;
        protected readonly IConfiguration _configuration;
        private readonly string _connectionString;

        public SQLHandlerBase(IConfiguration configuration, ILogger logger, string connectionStringId = ConnectionConstants.AzureSql)
        {
            ////no longer needed, can't be null.
            /*
            if (connectionStringId != null)
            {
                ConnectionStringId = connectionStringId;
            }
            */

            //// we don't want to hide dependencies: see next section
            // _configuration = serviceProvider.GetRequiredService<IConfiguration>();
            _configuration = configuration;

            ////we don't want the IoC container to be responsible for disposing connections
            //_dbConnection = serviceProvider.GetRequiredService<IDbConnection>();
            
            //// put the connection string as a readonly field instead of assigning it to the connection
            //_dbConnection.ConnectionString = _configuration[ConnectionStringId];
            _connectionString = _configuration[connectionStringId];

            //// we don't want to hide dependencies: see next section
            //// the base class should use an ILogger (ILogger), and derived classes will use a typed logger (ILogger<T>)
            //_log = serviceProvider.GetRequiredService<ILogger<T>>();
            _log = logger;
        }

        public IDbConnection GetConnection() => new Microsoft.Data.SqlClient.SqlConnection(_connectionString);
    }

2. If you want to encapsulate the use of the connection in the base class, I find this pattern works well for me. This would still be registered as a Singleton.

    public class SQLHandlerBase<T>
    {
        protected readonly ILogger _log;
        protected readonly IConfiguration _configuration;
        private readonly string _connectionString;

        public SQLHandlerBase(IConfiguration configuration, ILogger logger, string connectionStringId = ConnectionConstants.AzureSql)
        {
            _configuration = configuration;
            _connectionString = _configuration[connectionStringId];
            _log = logger;
        }

        private IDbConnection GetConnection() => new Microsoft.Data.SqlClient.SqlConnection(_connectionString);
        
        public void UseConnection(Action<IDbConnection> action)
        {
            using var con = GetConnection();
            con.Open();
            action(con);
        }

        public TEntity UseConnection<TEntity>(Func<IDbConnection, TEntity> func)
        {
            using var con = GetConnection();
            con.Open();
            return func(con);
        }
    }

And the consumer would look like this:

public void Delete(string unitNumber, string customerNumber)
{
    UseConnection(con =>
    {
        const string sql =
            @"DELETE [Tenants] WHERE UnitNumber = @UnitNumber AND CustomerNumber = @CustomerNumber";
        var parameters = new
        {
            UnitNumber = unitNumber,
            CustomerNumber = customerNumber
        };
        con.Execute(sql, parameters);
    });
}

Dependency Injection - A note on hiding dependencies

base class that has gets the IDbConnection from an injected IServiceProvider

• The IServiceProvider should not be injected into the SQLHandlerBase<T>.

1. By injecting the IServiceProvider and using it to resolve the dependencies in the constructor you are hiding the class' dependencies.
2. You are using GetRequiredService<IService> which will throw an exception if it is unable to resolve the service. Generally, you should avoid exceptions in the constructor. In this specific case, at a minimum you would need to add a finalizer to clean up. Ex; ILogger<T> fails to resolve, but now you've got a database connection which should be disposed.

Ref: [Andrew Locke](The difference between GetService() and GetRequiredService() in ASP.NET Core (andrewlock.net)).

Service Lifetime (Blazor specific)

So, it appears to me that every query that is run gets a new SqlConnection. Based on my reading I thought we should be using AddScoped instead, but changeing from Transient to Scoped didn't really seem to make much difference as a new SqlConnection is created regardless.

1. Blazor behaves differently than MVC (or Razor Pages) when it comes to service lifetime. WebAssembly behaves differently from Blazor Server side, and Blazor Server side behaves differently if you change the default render-mode. Blazor University - Comparing dependency scopes (blazor-university.com)
2. To tackle the differences in how Blazor handles service scopes, you should understand OwningComponentBase for anything deriving from your base class that will be used in Blazor components. An Introduction to OwningComponentBase | Chris Sainty - Building with Blazor
3. According to this GitHub Issue), if you WERE to register IDbConnection (which implements IDisposable) as a Transient scoped service, the IoC container would hold on to these and not let the garbage collector clean up until the IoC container itself could be garbage collected.

Transient services which implement IDisposable :
A reference to the service is maintained by the DI container so it can handle service disposal. This prevents garbage collection of the service upon component disposal. The service is kept in memory till the DI container itself can be garbage collected.

To summarize this quickly for your case. Singleton registered dependencies will be shared across users / tabs. Scoped dependencies will act just like a Singleton, but they are not shared. Transient dependencies will act as advertised, regardless of being Blazor Wasm, Blazor Server or ASP.NET Core MVC. With the caveat that in Blazor when the transient service implements IDisposable, the GC will not be able to clean up the memory.