sql - 使用 Dapper 时关闭连接

Question

一旦显式调用 Close 方法或将连接放在 Using 语句中执行查询，是否有必要关闭连接？保持连接打开会导致连接重用并提高 future 查询的 SQL 性能吗？

Answer 1

我假设您使用的是最新版本的 Dapper。

使用 Dapper，有两种方法来管理连接:

全面管理自己:
在这里，您全权负责打开和关闭连接。这就像您在使用 ADO.NET 时处理连接的方式一样。

允许 Dapper 管理它:
Dapper 会自动为您打开连接(如果它没有打开)并关闭它(如果它是由 Dapper 打开的)。这类似于 DataAdapter.Fill()方法。我个人不推荐这种方式。这可能并非每次都适用。以下是 Marc Gravell 在 comment 之一中所说的。对于这个答案:https://stackoverflow.com/a/12629170/5779732

well, technically open/closed is different to disposed. If you are only going to be opening/closing around the individual calls, you might as well let dapper do it. If you are opening/closing at a wider granularity (per request, for example), it would be better for your code to do it and pass an open connection to dapper.

以下是来自 here 的报价:

Dapper will close the connection if it needed to open it. So if you're just doing 1 quick query - let Dapper handle it. If you're doing many, you should open (once) and close at the end, with all the queries in the middle...just from an efficiency standpoint.

当然，您可以在单个连接上调用多个查询。但是，应该关闭连接(通过调用 Close() 、 Dispose() 方法或将其包含在 using block 中)以避免资源泄漏。关闭连接将其返回到连接池。连接池的参与提高了新连接成本的性能。

除了处理连接之外，我建议您也实现 UnitOfWork 来管理事务。引用 this GitHub 上的优秀示例。

以下源代码可能会对您有所帮助。请注意，这是为我的需要而编写的；所以它可能不适合你。

public sealed class DalSession : IDisposable
{
    public DalSession()
    {
        _connection = new OleDbConnection(DalCommon.ConnectionString);
        _connection.Open();
        _unitOfWork = new UnitOfWork(_connection);
    }

    IDbConnection _connection = null;
    UnitOfWork _unitOfWork = null;

    public UnitOfWork UnitOfWork
    {
        get { return _unitOfWork; }
    }

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

public sealed class UnitOfWork : IUnitOfWork
{
    internal UnitOfWork(IDbConnection connection)
    {
        _id = Guid.NewGuid();
        _connection = connection;
    }

    IDbConnection _connection = null;
    IDbTransaction _transaction = null;
    Guid _id = Guid.Empty;

    IDbConnection IUnitOfWork.Connection
    {
        get { return _connection; }
    }
    IDbTransaction IUnitOfWork.Transaction
    {
        get { return _transaction; }
    }
    Guid IUnitOfWork.Id
    {
        get { return _id; }
    }

    public void Begin()
    {
        _transaction = _connection.BeginTransaction();
    }

    public void Commit()
    {
        _transaction.Commit();
        Dispose();
    }

    public void Rollback()
    {
        _transaction.Rollback();
        Dispose();
    }

    public void Dispose()
    {
        if(_transaction != null)
            _transaction.Dispose();
        _transaction = null;
    }
}

interface IUnitOfWork : IDisposable
{
    Guid Id { get; }
    IDbConnection Connection { get; }
    IDbTransaction Transaction { get; }
    void Begin();
    void Commit();
    void Rollback();
}

现在，您的存储库应该以某种方式接受这个 UnitOfWork。我选择使用构造函数的依赖注入(inject)。

public sealed class MyRepository
{
    public MyRepository(IUnitOfWork unitOfWork) 
    {
        this.unitOfWork = unitOfWork;
    }

    IUnitOfWork unitOfWork = null;

    //You also need to handle other parameters like 'sql', 'param' ect. This is out of scope of this answer.
    public MyPoco Get()
    {
        return unitOfWork.Connection.Query(sql, param, unitOfWork.Transaction, .......);
    }

    public void Insert(MyPoco poco)
    {
        return unitOfWork.Connection.Execute(sql, param, unitOfWork.Transaction, .........);
    }
}

然后你这样称呼它:

有交易:

using(DalSession dalSession = new DalSession())
{
    UnitOfWork unitOfWork = dalSession.UnitOfWork;
    unitOfWork.Begin();
    try
    {
        //Your database code here
        MyRepository myRepository = new MyRepository(unitOfWork);
        myRepository.Insert(myPoco);
        //You may create other repositories in similar way in same scope of UoW.

        unitOfWork.Commit();
    }
    catch
    {
        unitOfWork.Rollback();
        throw;
    }
}

无交易:

using(DalSession dalSession = new DalSession())
{
    //Your database code here
    MyRepository myRepository = new MyRepository(dalSession.UnitOfWork);//UoW have no effect here as Begin() is not called.
    myRepository.Insert(myPoco);
}

这样，您无需在调用代码中直接公开连接，而是在一个位置对其进行控制。

上面代码中关于 Repository 的更多细节可以找到 here .

请注意 UnitOfWork是 more而不仅仅是一笔交易。这段代码只处理事务。您可以扩展此代码以涵盖其他角色。