我应该何时打开和关闭与SQL Server的连接

I have a simple static class with a few methods in it. Each of those methods open a SqlConnection, query the database and close the connection. This way, I am sure that I always close the connection to the database, but on the other hand, I don't like to always open and close connection. Below is an example of what my methods look like.

我有一个简单的静态类，其中包含一些方法。每个方法都打开一个SqlConnection，查询数据库并关闭连接。这样，我确信我总是关闭与数据库的连接，但另一方面，我不喜欢总是打开和关闭连接。下面是我的方法的示例。

public static void AddSomething(string something)
{
    using (SqlConnection connection = new SqlConnection("..."))
    {
        connection.Open();
        // ...
        connection.Close();
    }
}

Considering that the methods are inside a static class, should I have a static member containing a single SqlConnection? How and when should I drop it? What are the best practices?

考虑到方法在静态类中，我是否应该有一个包含单个SqlConnection的静态成员？我该如何以及何时放弃它？什么是最佳做法？

6 个解决方案

#1

No, don't keep a static SqlConnection unless you have to. Threading would be one concern, but more importantly - usually you simply don't need to. With your code as presented, the internal connection pooling means that most of the time you will get the same underlying connection on successive calls anyway (as long as you use the same connection string). Let the pooler do its job; leave the code alone.

不，除非必须，否则不要保留静态SqlConnection。线程是一个问题，但更重要的是 - 通常你根本不需要。使用您显示的代码，内部连接池意味着大多数时候您将在连续的调用中获得相同的底层连接（只要您使用相同的连接字符串）。让小便者做好自己的工作;保持代码单独。

This also avoids the issues of what happens when you start having two threads... now each can do work on their own connection; with static (assuming you don't use [ThreadStatic]) you'd have to synchronize, introducing delays. Not to mention re-entrancy (i.e. a single thread trying to use the same connection twice at the same time). Yup; leave the code alone. It is fine now, and almost any change you make would make it not fine.

这也避免了当你开始有两个线程时会发生什么问题...现在每个线程都可以在自己的连接上工作;使用static（假设你不使用[ThreadStatic]）你必须同步，引入延迟。更不用说重入（即单个线程试图同时使用两次相同的连接）。对;保持代码单独。它现在很好，几乎你所做的任何改变都会让它变得不好。

#2

Because the SqlConnection has a connection pool when you call Open() and Close() you aren't actually opening and closing the physical connection to the server. You are just adding / removing the connection from a pool of available connections. For this reason it is a good and best practice to open the connection as late as possible and close the connection as early as possible after executing your command.

因为当您调用Open（）和Close（）时，SqlConnection具有连接池，您实际上并不打开和关闭与服务器的物理连接。您只是从可用连接池中添加/删除连接。因此，尽可能晚地打开连接并在执行命令后尽早关闭连接是一种良好的最佳做法。

#3

In your code sample there is no need to call the close() method on the connection object as it will be handled automatically due to the code residing inside a using block.

在您的代码示例中，不需要在连接对象上调用close（）方法，因为它将自动处理，因为代码驻留在using块中。

#4

Most programmers believe in open late and close early. This is only a problem if the latency for opening and closing the connection each time causes the entire application to slow down.

大多数程序员都相信开放时间较早且较早关闭。如果每次打开和关闭连接的延迟导致整个应用程序变慢，则这只是一个问题。

In your case with a static class it is probably best to open and close the connection each time.

在您使用静态类的情况下，最好每次都打开和关闭连接。

#5

You are doing the best practices. Only open it right before you are going to query it, and close it as soon as you can. This kind of thing may seem wasteful at first, but it actually makes your application more scalable in the long run.

你正在做最好的做法。只在您查询之前打开它，并尽快关闭它。这种事情起初可能看起来很浪费，但它实际上使您的应用程序从长远来看更具可扩展性。

#6

-1

Don't ever rely on the connection to close itself. If it's not explicitly closed, it will lead to performance issues. It happened to us on our project. Yes, I'm aware that connections are managed by a connection pool, but they still have to be closed and returned to the pool.

不要依赖连接来关闭自己。如果没有明确关闭，则会导致性能问题。它发生在我们的项目上。是的，我知道连接是由连接池管理的，但它们仍然必须关闭并返回池中。

#1

#2

#3