管理SQL Server连接

Question

什么是SQL连接的最佳实践？

目前我正在使用以下方法：

using (SqlConnection sqlConn = new SqlConnection(CONNECTIONSTRING))
{
    sqlConn.Open();
    // DB CODE GOES HERE
}

我了解到这是一种非常有效的进行SQL连接的方式。默认情况下，SQL连接池是激活的，因此我的理解是当using代码结束时，SqlConnection对象被关闭和处理，但实际连接到数据库的连接被放置在SQL连接池中。请问我的理解是否正确？

- Neale

Answer 1

这就是大部分内容。 还有一些其他要考虑的点：

• 你从哪里获取连接字符串？ 你不希望在许多地方硬编码连接字符串，而且你可能需要保护它。
• 在实际使用连接之前，通常还需要创建其他对象（SqlCommand、SqlParameter、DataSet、SqlDataAdapter），并且尽可能等到最后才打开连接。整个模式需要考虑到这一点。
• 你希望确保数据库访问被强制进入其自己的数据层类或程序集中。 所以一个常见的做法是将其表达为一个私有函数调用：

.

private static string connectionString = "load from encrypted config file";
private SqlConnection getConnection()
{
    return new SqlConnection(connectionString);
}

然后按照以下方式编写您的示例：

using (SqlConnection sqlConn = getConnection())
{
    // create command and add parameters

    // open the connection
    sqlConn.Open();

   // run the command
}

那个示例只能存在于您的数据访问类中。另一种选择是将其标记为internal，并将数据层分布在整个程序集中。最重要的是，必须严格执行数据库代码的清晰分离。

真正的实现可能如下所示：

public IEnumerable<IDataRecord> GetSomeData(string filter)
{
    string sql = "SELECT * FROM [SomeTable] WHERE [SomeColumn] LIKE @Filter + '%'";

    using (SqlConnection cn = getConnection())
    using (SqlCommand cmd = new SqlCommand(sql, cn))
    {
        cmd.Parameters.Add("@Filter", SqlDbType.NVarChar, 255).Value = filter;
        cn.Open();

        using (IDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                yield return (IDataRecord)rdr;
            }
        }
    }
}

注意，我还能够“堆叠”创建cn和cmd对象，从而减少嵌套并只创建一个作用域块。最后，对于在此特定示例中使用yield return代码的注意事项。如果您调用该方法并没有立即完成DataBinding或其他用途，则可能会长时间保持连接处于打开状态。一个例子是在ASP.NET页面的Load事件中将其用于设置数据源。由于实际的数据绑定事件要晚些发生，因此您可能会比需要的时间长时间保持连接处于打开状态。

- Joel Coehoorn

---

点一：我将我的连接字符串存储在应用程序/网站配置文件的<connectionstring>部分中。 点二：这是我应该担心的事情吗？我通常会使用SqlConnection，然后打开它，创建我的SqlCommand，然后从那里继续进行。 点三：我们所有的DB访问都来自一个类库。我不喜欢从核心应用程序进行DB访问。值得我创建getConnection方法吗？ - Neale

由于您使用类库进行所有数据库访问，因此将连接字符串作为类库的.config文件的一部分而不是整个应用程序全局的.config文件是一件简单的事情。这应该足以强制执行干净的数据层分离，但同时也没有必要使方法公开。这应该解决了第1和第3点。对于第2点，多出一两行可能不会有什么影响，但也不会有帮助。我正在编辑另一个您可能希望以这种方式执行的原因。 - Joel Coehoorn

你的 yield return 示例有点误导人。围绕 yield return 构建的函数直到在 IEnumerator 上第一次调用 MoveNext() 才开始执行。因此，即使你调用了该函数，连接也不会打开，直到你开始迭代结果。 - Scott Chamberlain

@ScottChamberlain 这是一项功能，而不是一个错误（请参见第二个要点）。 - Joel Coehoorn

抱歉我表达不清，我的意思是你的yield return示例描述有些误导性。它谈到了一旦调用方法就会保持连接打开，但实际上并没有这样做。我同意一旦枚举开始，它会保持连接打开，并且您应该尽快完成绑定以关闭它。我认为短语“如果您调用该方法…”是让我停顿并撰写评论的原因，我只是不知道有什么更好的描述可以替代它。 - Scott Chamberlain

Answer 2

微软的模式和实践库是处理数据库连接的极佳方法。这些库封装了大部分打开连接所涉及的机制，从而使您的工作更轻松。

- Jagd

Answer 3

您对于使用的理解是正确的，而那种方式也是建议的做法。您还可以在代码中调用close方法。

- Mitchel Sellers

---

谢谢。我的理解是，当“using”结束并调用SqlConnection.Dispose()时，最终会调用SqlConnection.Close()。 - Neale

@Neale - 是的，SqlConnection.Dispose()会在必要时调用Close。如果连接已经关闭，它只是释放句柄回到ado.net提供程序，然后可以自由地将其放入池中或实际关闭它。 - Jason Short

Answer 4

另外：营业到晚上很晚，早上关门早。

在调用数据库之前，不要打开连接，直到没有其他步骤。完成后尽快关闭连接。

- Çağdaş Tekin