C# 我应该什么时候打开和关闭到 SQL Server 的连接

When should I open and close a connection to SQL Server

提问by Martin

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 的静态成员吗​​？我应该如何以及何时放弃它？最佳做法是什么？

采纳答案by Marc Gravell

No, don't keep a static SqlConnectionunless 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.

这也避免了当你开始有两个线程时发生的问题......现在每个线程都可以在自己的连接上工作；使用静态（假设您不使用[ThreadStatic]），您必须同步，从而引入延迟。更不用说重入（即单个线程试图同时使用同一个连接两次）。是的; 留下代码。现在很好，您所做的几乎任何更改都会使它变得不正常。

回答by Jeremy

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.

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

回答by Al W

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.

您正在执行最佳实践。仅在您要查询之前打开它，并尽快关闭它。这种事情一开始可能看起来很浪费，但从长远来看，它实际上使您的应用程序更具可扩展性。

回答by Jonathan Parker

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 有一个连接池，您实际上并没有打开和关闭到服务器的物理连接。您只是在可用连接池中添加/删除连接。因此，在执行命令后尽可能晚地打开连接并尽早关闭连接是一个很好的最佳实践。

回答by Christian Hagelid

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 块中，它将被自动处理。

回答by Antwan

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.

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