使用Dapper时关闭连接

问题描述

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

Is it necessary to close connection once query is executed explicitly calling Close method or putting the connection within Using statement? Would leaving connection open lead to connection reuse and improve SQL performance for future queries?

推荐答案

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

I am assuming that you are using latest version of Dapper.

使用Dapper，可以通过两种方式管理连接：

With Dapper, there are two ways to manage connection:

• 这可能并非每次都适用。以下是Marc Gravell在：

  This may not be applicable every time. Following is what Marc Gravell says in one of the comment for this answer: https://stackoverflow.com/a/12629170/5779732

  以下是来自：

  当然，您可以在单个连接上调用多个查询。但是，应该关闭连接（通过调用 Close（）， Dispose（）方法或将其包含在<$中） c $ c> using 块）以避免资源泄漏。关闭连接会将其返回到连接池。参与连接池可以提高性能，而不是增加新的连接成本。

  Ofcourse, you can call multiple queries on single connection. But, connection should be closed (by calling Close(), Dispose() method or by enclosing it in using block) to avoid resource leak. Closing connection returns it to connection pool. Involvement of connection pool improves the performance over new connection cost.

  除了处理连接之外，建议您实施UnitOfWork同时管理交易。在GitHub上参考优秀示例。

  In addition to just handling connection, I suggest you implement UnitOfWork to manage transactions as well. Refer this excellent sample on GitHub.

  源代码可能会帮助您。请注意，这是为我的需要而写的；

  Following source code may help you. Note that this is written for my needs; so it may not work for you as is.

  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。我选择使用构造函数进行依赖注入。

  Now, your repositories should accept this UnitOfWork in some way. I choose Dependency Injection with Constructor.

  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);
  }
  

  这样，您可以控制它而不是直接在调用代码中公开连接

  This way, instead of directly exposing connection in your calling code, you control it at one location.

  可以在。

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

  Please note that UnitOfWork is more than just a transaction. This code handles only transaction though. You may extend this code to cover additional roles.

  这篇关于使用Dapper时关闭连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！