这是处理 SQLConnection 的正确方法吗 [英] Is this the right way to dispose the SQLConnection

问题描述

我想知道我的以下实现是否是在这种情况下处理 SQL 连接的最有效方法.

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

我通常知道如果我直接使用 SqlConnection，我可以将连接包装在 using 块中以自动将其关闭，但在这种情况下，我想保持连接打开并可供 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 & 中打开和关闭b.

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)代码会更简单并且(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.

这将扼杀服务器的可扩展性 - 相信我.我见过非常大的系统因此而窒息 - 通常是因为它们跨越 AT 侧事务.

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 ...

我不会让连接跨越存储库的生命周期.

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