Is there a simple way to use Dependency Injection on my connections?

Question

I'm looking for a way to inject my connections into my repositories. I tried to inject the SqlConnection using the IDBConnection, but I got some problems when NInject tries to deactivate the connection, the event is never called. And I cannot figure out how to inject the connection string into my repositories.

Could someone provide me a suggestion?

Answer 1

I use NInject to perform the Dependency Injection of my projects. I usually end with the configuration below:

Simple Factory Interface

public interface IDbConnectionFactory
{
    IDbConnection CreateConnection();
}

Factory Implementation

public class SqlConnectionFactory : IDbConnectionFactory
{
    private readonly string _connectionString;

    public SqlConnectionFactory(string connectionString)
    {
        _connectionString = connectionString;
    }

    public IDbConnection CreateConnection()
    {
        var conn = new SqlConnection(_connectionString);
        conn.Open();
        return conn;
    }
}

NInject Config:

Bind<IDbConnectionFactory>()
    .To<SqlConnectionFactory>()
    .WithConstructorArgument("connectionString",
ConfigurationManager.ConnectionStrings["SqlConnection"].ConnectionString);

Example of a repository:

public class UserRepository : IUserRepository
{
    private readonly IDbConnectionFactory _dbConnectionFactory;

    public UserRepository(IDbConnectionFactory dbConnectionFactory)
    {
        _dbConnectionFactory = dbConnectionFactory;
    }

    public IEnumerable<User> List()
    {
        using (var dbConnection = _dbConnectionFactory.CreateConnection())
        {
            ....
        }
    }
}

Edit: I always let the ADO.NET take care of connection pooling. So I open and close the connection every single time that I need to perform a database operation.

This approach is working for me and is very simple as you mentioned in your question.