Connection doesn't get closed on the DB

ServiceStack.OrmLite SQL Server
1

Hello,
I’ve got a MVC application that uses OrmLite to access DB, I’ve got a strange behavior when I’m manually opening/closing connection using the OpenDbConnection.

Here’s the detail. please consider this call

  public ActionResult Content()
    {
        var typeId = 0;// UserContext.Instance.Contract?.TypeId;

        var groupCode = Repository.GetContractQueryTemplateGroup(typeId);

        var temp = Db.Select<QueryTemplate>(e => e.GroupCode == groupCode);

            return Json(DateTime.Now);
        }
    }

In my base class I’ve redefined the DbConnection as

   public override IDbConnection Db => DbFactory.OpenDbConnection();

    protected virtual string NamedDbFactory { get; }

    protected virtual IDbConnectionFactory DbFactory =>
        NamedDbFactory == null
        ? TryResolve<IDbConnectionFactory>()
        : TryResolveNamed<IDbConnectionFactory>(NamedDbFactory)
            ?? ServiceStackHost.Instance.Container.TryResolveNamed<IDbConnectionFactory>(NamedDbFactory);

and it’s Dispose()

        protected override void Dispose(bool disposing)
    {
        Db?.Close();
        Db?.Dispose();
      
        base.Dispose(disposing);
    }

If I use this code and refresh the page, I see the number of opened db connection to increase.
While if I use

  public ActionResult Content()
    {
        var groupCode = Repository.GetContractQueryTemplateGroup(typeId);
        using (var db = DbFactory.Open())
        {
            var temp = db.Select<QueryTemplate>(e => e.GroupCode == groupCode);
            return Json(DateTime.Now);
        }
    }

It doesn’t open new connections.

Now since I need to perform that check on the DbFactory, I supposed that the Dispose on the Db connection should be enought, what have I also to take care of?

Thanks in advance

Paolo

2

This doesn’t look right:

public override IDbConnection Db => DbFactory.OpenDbConnection();

Everytime you access Db you’re opening a new connection. We have an example of a repository base class in ServiceStack that shows maintaining an open db connection and disposing if it has been opened:

public abstract class RepositoryBase : IDisposable, IRepository
{
    public virtual IDbConnectionFactory DbFactory { get; set; }

    IDbConnection db;
    public virtual IDbConnection Db => db ?? (db = DbFactory.OpenDbConnection());

    public virtual void Dispose() => db?.Dispose();
}

It doesn’t matter whether you manually open the connection or do it in a base class, but you must know the life cycle of the connection, i.e. for every db connection that’s opened it must be closed and db connections are not ThreadSafe and cannot be shared across multiple threads so each thread requires their own new open DB connection.