Dependency Injection IDbConnection SqlConnection

Question

I have this exception raised sometimes:

System.InvalidOperationException: 'The ConnectionString property has not been initialized.'

I use the built-in dependency injection:

public void ConfigureServices(IServiceCollection services)
{
    services.AddTransient<IDbConnection>(db => new SqlConnection(
                    Configuration.GetConnectionString("AppConnectionString")));

    services.AddScoped<IAppConfigurationRepository, AppConfigurationRepository>();
    services.AddScoped<IHomeworkingRequestRepository, HomeworkingRequestRepository>();
    services.AddScoped<IEmployeeRepository, EmployeeRepository>();
    services.AddScoped<IEmployeeService, EmployeeService>();
    services.AddScoped<IHomeworkingRequestService, HomeworkingRequestService>();
    services.AddMvc();
}

Before, I had already this error. The code services.AddScoped<IDbConnection> I changed to services.AddTransient<IDbConnection> and solved the issue. But now I have the error again.

EDIT

Please find the code when the exception occurs :

public class EmployeeRepository : IEmployeeRepository
{
    private readonly IDbConnection _connection;

    public EmployeeRepository(IDbConnection connection)
    {
        _connection = connection;
    }

    public IEnumerable<Employee> GetAllActiveEmployees()
    {
        string query = @"
           SELECT
               FirstName
              ,LastName
              ,BusinessUnit
          FROM Employees";

        using (var db = _connection)
        {
            _connection.Open(); // <-- The exception is thrown here
            return db.Query<Employee>(query);
        }
    }
 }

Please also find the full stacktrace :

at System.Data.SqlClient.SqlConnection.PermissionDemand()
   at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Homeworking.Dal.EmployeeRepository.GetAllActiveEmployees() in C:\Users\florian.nouri\source\repos\Homeworking\Homeworking.Repository\EmployeeRepository.cs:line 42
   at Homeworking.Service.EmployeeService.GetAllEmployees() in C:\Users\florian.nouri\source\repos\Homeworking\Homeworking.Service\EmployeeService.cs:line 22
   at Homeworking.Service.HomeworkingRequestService.GetAllEmployees() in C:\Users\florian.nouri\source\repos\Homeworking\Homeworking.Service\HomeworkingRequestService.cs:line 23
   at Homeworking.Web.Controllers.AppController.Index() in C:\Users\florian.nouri\source\repos\Homeworking\Homeworking.Web\Controllers\AppController.cs:line 22
   at lambda_method(Closure , Object , Object[] )
   at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)
   at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeActionMethodAsync()

Answer 1

using (var db = _connection)

This is bad. You create a new variable for the same connection, so after the using block is done, your connection will be disposed. You should not do this. If you use a container to create stuff, the container should dispose those instances it does not need any more.

Most likely, what is happening is that your _connection variable got disposed and any class that gets a connection from the container (in case of Scoped) or any class that has this variable already and uses it a second time because it's an instance field, will use the already disposed connection.