我应该什么时候打开和关闭到 SQL Server 的连接 [英] When should I open and close a connection to SQL Server

问题描述

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

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.

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

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

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.线程将是一个问题，但更重要的是 - 通常您根本不需要.使用您提供的代码，内部连接池意味着在大多数情况下，您无论如何都会在连续调用中获得相同的底层连接(只要您使用相同的连接字符串).让池工做它的工作；留下代码.

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.

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

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.

这篇关于我应该什么时候打开和关闭到 SQL Server 的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！