Dapper disposable instance with Ninject

Question

Dapper disposable instance with Ninject

c# dapper dispose ninject sqlite

Question

I want to bind the database connection instance using Ninject and use it in scope of a thread in order to skip repeatedly typing the using { var cn = new DbConnectionFactory() } [..] whenever I need to make a query.

I have added a binding:

IOC.Kernel.Bind<IDbConnection>().ToMethod(ctx =>
{
    using (var cn = new DbConnectionFactory())
    {
        return cn.GetConnection();
    }
}).InThreadScope();

db factory class:

public class DbConnectionFactory : IDisposable
{
    private IDbConnection _connection;

    public DbConnectionFactory()
    {
        _connection = new SQLiteConnection("Data Source=mydb.sqlite;Version=3;");
        _connection.Open();
    }

    public IDbConnection GetConnection() => _connection;

    public void Dispose()
    {
        _connection.Close();
    }
}

Now, this is working fine. One db connection is being created per thread, however, connection is being closed but not disposed!

So when I modify my Dispose method to do so:

public void Dispose()
{
    _connection.Close();
    _connection.Dispose();
}

The connection no longer seem to work properly and when accessed, throwing an exception: ObjectDisposedException Object is no longer accessible.

Question is: how can I bind and dispose the connection properly in Ninject?

Please note that I cannot use .InRequestScope() because this is not a web application.

Lucas

View more on Stack Overflow

mjwills · Accepted Answer · 6/8/2018 11:23:14 AM

With Ninject and other IoC containers, it is the container's responsibility to call Dispose on disposable objects. Unfortunately, with your implementation you are calling the Dispose yourself - and doing it incorrectly.

using (var cn = new DbConnectionFactory())
{
    return cn.GetConnection();
}

This code creates the DbConnectionFactory and creates a database connection. Then (due to using) it calls cn.Dispose. Your implementation there then disposes of the new connection you just created.

There are multiple ways of solving this, but the one I would suggest you starting with is to change:

public class DbConnectionFactory
{
    public IDbConnection GetConnection() 
    {
        var connection = new SQLiteConnection("Data Source=mydb.sqlite;Version=3;");
        connection.Open();
        return connection;
    }
}

And then:

IOC.Kernel.Bind<IDbConnection>().ToMethod(ctx =>
{
    var cn = new DbConnectionFactory();
    return cn.GetConnection();
}).InThreadScope();

Then, as per the docs, the database connection will be automatically Disposed when the underlying Thread is garbage collected.

Dapper disposable instance with Ninject

Question

Fastest Dapper Plus Extensions

Accepted Answer

Performance

Expression Evaluator