这是正确的方式来处置的SQLConnection [英] Is this the right way to dispose the SQLConnection

问题描述

我在想，如果我的下面实施处置的SqlConnection在这种情况下，最有效的方式。

I was wondering if my below implementation is the most efficient way to dispose the SQLconnection in this case.

我知道，通常如果我使用SqlConnection直接我就可以换一个采用块内的连接自动配置它关闭，但在这种情况下，我想保持连接打开，并提供在SQLRespository类中的所有方法

I know normally if i'm using the SqlConnection directly I can just wrap the connection inside a using block to dispose it off automatically, but in this case i wanted to keep the connection open and available to All methods in the SQLRespository class.

public class SqlRepository : IRepository
{
    private readonly string connectionString;
    private SqlConnection connection;

    public SqlRepository(string connectionString)
    {
        this.connectionString = connectionString;
        connection = new SqlConnection(connectionString);
        connection.Open();
    }

    public void Method_A()
    {
      // uses the SqlConnection to fetch data
    }     

    public void Method_B()
    {
      // uses the SqlConnection to fetch data
    }     

    public void Dispose()
    {            
        connection.Dispose();
    }
}

用法：

using (IRepository repository = new SqlRepository(connectionString))
{
   var item = repository.items;     
}

更新 IRepository并实现IDisposable

Update IRepository does implement IDisposable

推荐答案

不要保持连接打开跨越的电话。你击败连接池。

Don't keep the connection open spanning calls. You're defeating connection pooling.

如果您正在使用多数民众赞成合并（比如SQLSERVER）的连接，它就会集中和重用。只需打开和关闭在方法A和湾

If you're working with a connection that's pooled (like sqlserver), it will pool and reuse. Just open and close within method a & b.

您可能会说，如果来电者你做一个方法，用什么叫还行吧。但是，如果你使用{}与SqlConnection的每一个辅助方法（1）内的code将更加简单和（2）你保证了池不会被打败了（意思是你持有的物品出池时，其他请求可能使用它）。

You could argue that if the caller does what you did with using with one method call it's fine. But if you do using {} with sqlconnection inside each worker method (1) the code will be simpler and (2) you're ensured the pooling wont be defeated (meaning your holding items out of the pooling when other requests could use it).

编辑：

增加伪基础上的意见。

模式是有问题的，因为呼叫者可以做到的。

The pattern is problematic because a caller can do.

//pseudo code
using (SqlRepository r)
{
    r.MethodA();

    // other code here that takes some time.  your holding a connection
    // out of the pool and being selfish.  other threads could have
    // used your connection before you get a chance to use it again.

    r.MethodB();
}  // freed for others here.

这会杀了服务器的可扩展性 - 相信我。我已经看到了非常大的系统通过这个得到哽咽 - 通常是因为他们跨越船舷交易

That will kill the scalability of the server - trust me. I've seen very large systems get choked by this - usually because they're spanning AT side transactions.

有一个更好的方式：

class Repository
{
    void MethodA()
    {
        using (Sqlconnection)
        {
             // db call
        }
    }

    void MethodB()
    {
        using (Sqlconnection)
        {
            // you can even have multiple calls here (roundtrips)
            // and start transactions.  although that can be problematic
            // for other reasons.  
        }
    }

现在，游泳池是最有效的。我意识到这个问题是在一次性模式 - 是的，你可以做到这一点。但是......

Now, the pool is most effective. I realize the question was on the disposable pattern - and yes you can do it. But ...

我不会让连接跨越储存库的寿命。的

I would not let the connection span the lifetime of the repository.

这篇关于这是正确的方式来处置的SQLConnection的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！