管理 SQL Server 连接

发布于 2024-07-22 05:39:35 字数 342 浏览 7 评论 0原文

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

目前我正在使用以下内容：

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

我读到这是一种非常有效的 SQL 连接方式。默认情况下，SQL 池是活动的，所以我的理解是，当 using 代码结束时，SqlConnection 对象被关闭并释放，但与数据库的实际连接被放置在SQL连接池中。我这件事有错吗？

原文

What is the the best practice for SQL connections?

Currently I am using the following:

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

I have read that this is a very effective way of doing SQL connections. By default the SQL pooling is active, so how I understand it is that when the using code ends the SqlConnection object is closed and disposed but the actual connection to the DB is put in the SQL connection pool. Am i wrong about this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

壹場煙雨 2024-07-29 05:39:35

这就是大部分。需要考虑的一些其他要点：

从哪里获取连接字符串？您不希望在整个地方都进行硬编码，并且您可能需要保护它。
在真正使用连接之前，您通常还需要创建其他对象（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
}

该示例只能存在于您的数据访问类中。另一种方法是将其标记为内部并将数据层分布到整个程序集上。最主要的是严格执行数据库代码的干净分离。

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

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 事件中设置数据源。由于实际的数据绑定事件要等到稍后才会发生，因此您可以使连接保持打开状态的时间比所需的时间长得多。

That's most of it. Some additional points to consider:

Where do you get your connection string? You don't want that hard-coded all over the place and you may need to secure it.
You often have other objects to create as well before your really use the connection (SqlCommand, SqlParameter, DataSet, SqlDataAdapter), and you want to wait as long as possible to open the connection. The full pattern needs to account for that.
You want to make sure your database access is forced into it's own data layer class or assembly. So a common thing to do is express this as a private function call:

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

And then write your sample like this:

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

    // open the connection
    sqlConn.Open();

   // run the command
}

That sample can only exist in your data access class. An alternative is to mark it internal and spread the data layer over an entire assembly. The main thing is that a clean separation of your database code is strictly enforced.

A real implementation might look like this:

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;
            }
        }
    }
}

Notice that I was also able to "stack" the creation of the cn and cmd objects, and thus reduce nesting and only create one scope block.

Finally, a word of caution about using the yield return code in this specific sample. If you call the method and don't complete your DataBinding or other use right away it could hold the connection open for a long time. An example of this is using it to set a data source in the Load event of an ASP.NET page. Since the actual data binding event won't occur until later you could hold the connection open much longer than needed.

回复收藏 0 原文