汇集人脉对我来说有多重要？

发布于 2024-08-25 05:15:52 字数 142 浏览 5 评论 0原文

有人建议我重新安排我的代码以“池化”我的 ADO 连接。在每个网页上，我都会打开一个连接并继续使用相同的打开连接。但其他人告诉我，这在 10 年前很重要，但现在不那么重要了。如果我在网络发布上进行 5 个 db 调用，那么使用我打开/关闭的 5 个单独的连接是否有问题？

原文

分享到QQ

分享到微博

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

发布评论

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

Saygoodbye 2024-09-01 05:15:52

与 SQL Server 的连接自动汇集在 ASP.NET 应用程序中：每个不同的连接字符串对应一个池。如果您遵循最佳实践并将数据库代码隐藏在连接字符串在整个应用程序中保持不变的 DAL 中，那么您将始终使用单个连接对象池。

那么这对于您的数据库方法意味着什么？嗯，一方面，这意味着“关闭连接”实际上转化为“将连接返回到池”，而不是真正关闭应用程序与 SQL Server 的链接。因此，关闭和重新开放并不是什么大事。然而，话虽如此，这里还是有一些最佳实践可供遵循。

首先，即使您的应用程序急剧扩展，您也不希望耗尽池中的连接。也就是说，永远不要轮流考虑“此页面上的用户” - 考虑“使用此页面的上千人”。

其次，尽管关闭和重新打开连接并不那么费力，但您通常会希望尽可能晚打开连接，使用它直到完成，然后将其关闭<尽可能早。唯一的例外是，如果您有一个耗时的过程，该过程必须在检索某些数据之后以及保存或检索其他数据之前进行。

第三，我强烈建议不要在页面生命周期的早期打开连接并在生命周期的后期以不同的方法关闭它。为什么？因为您能做的最糟糕的事情就是让连接保持打开状态，因为您忘记添加关闭它的逻辑。是的，当 GC 启动时，它们最终会被关闭，但是，再次，如果您正在考虑“成千上万的人使用此页面*，那么真正遇到麻烦的机会就变得显而易见了。

现在，如果你说你确定你关闭了连接，因为你总是在一些关键的、合乎逻辑的地方这样做（例如Page_Unload方法），那么只要你可以自信地说，这就很好。你永远不会抛出跳出页面生命周期的错误。所以，不要在页面生命周期的一种方法中打开并在另一种方法中关闭

。建议实现管理数据库连接并提供处理数据的工具的 DAL，构建一个业务逻辑层 (BLL)，使用这些工具向 UI 提供类型安全对象（例如“模型”对象）。如果您使用 IDisposable 接口实现 BLL 对象，那么您始终可以使用作用域确保连接安全。它还可以让您在非常时间内保持数据库连接打开：只需打开BLL 对象，将数据拉入本地对象或列表，然后关闭 BLL 对象（超出范围）。然后，您可以在连接关闭后使用 BLL 返回的数据。

那么...这看起来像什么。那么，在您的页面（UI）上，您将使用如下所示的业务逻辑类：

using (BusinessLogicSubClass bLogic = new BusinessLogicSubClass())
{
   // Retrieve and display data or pull from your UI and update
   // using methods built into the bLogic object
    .
    .
    .
}  // <-- Going out of scope will automatically call the dispose method and close the database connection.

您的 BusinessLogicSubClass 将从实现 IDisposable 的 BusinessLogic 对象派生。它将实例化您的 DAL 类并打开一个连接，如下所示：

public class BusinessLogic : IDisposable
{
    protected DBManagementClass qry;
    public BusinessLogic()
    {
        qry = new DBManagementClass();
    }

    public void Dispose()
    {
        qry.Dispose();  <-- qry does the connection management as described below.
    }

    ... other methods that work with the qry class to 
    ... retrieve, manipulate, update, etc. the data 
    ... Example: returning a List<ModelClass> to the UI ...

 }

当 BusinessLogic 类超出范围时，将自动调用 Dispose 方法，因为它实现了 IDisposable 接口。

您的 DAL 类将具有匹配的方法：

public class DBManagementClass : IDisposable
{
    public static string ConnectionString { get; set; }  // ConnectionString is initialized when the App starts up.

    public DBManagementClass()
    {
        conn = new SqlConnection(ConnectionString);  
        conn.Open();
    }

    public void Dispose()
    {
        conn.Close();
    }

    ... other methods
}

根据我到目前为止所描述的内容，DAL 不具有 IDisposable 属性。但是，当我测试新的 BLL 方法时，我在测试代码中广泛使用 DAL 类，因此我将 DAL 构造为 IDisposable，以便我可以在测试期间使用“using”构造。

遵循这种方法，本质上，您将永远不必再考虑连接池。

Connections to SQL Server are pooled in an ASP.NET application automatically: one pool for each distinct connection string. If you follow best practices and hide your database code away in a DAL whose connection string is constant throughout the application, then you'll always be working with a single pool of connection objects.

So what does this mean for your approach to the database? Well, for one it means that "closing a connection" really translates into "returning a connection to the pool" rather than truly closing the application's link to SQL Server. Thus, closing and reopening is not that big of a deal. However, with that being said, there are a few best practices to follow here.

First, you don't want to run out of connections in your pool even if your app scales up dramatically. That is, never think in turns of "the user on this page" - think in terms "the thousand people using this page".

Second, even though it isn't that taxing to close and reopen a connection, you'll generally want to open a connection as late as possible, use it until it is done and then close it as early as possible. The only exception is if you have a time-consuming process that must come after retrieving some data and before saving or retrieving other data.

Third, I would strongly advise against opening a connection early in the page lifecycle and closing it late in the lifecycle in a different method. Why? Because the worst thing you can do is to leave a connection open because you forget to add the logic to close it. Yes, they'll eventually be closed when the GC kicks in, but, again, if you are thinking about "the thousand people using this page* the chance for real trouble becomes obvious.

Now, what if you say that you are sure you close the connection because you always do so in some key, logical spot (e.g. the Page_Unload method). Well, this is great as long as you can confidently say you'll never throw an error that hops out of the page lifecycle. Which you can't. So...don't open in one method of the page lifecycle and close in another.

Finally, I would strongly recommend implementing a DAL that manages the database connections and provides tools for working with data. On top of that, build a Business Logic Layer (BLL) that uses these tools to supply type-safe objects to your UI (e.g. "Model" objects). If you implement the BLL objects with an IDisposable interface, then you can always ensure Connection safety using scope. It will also let you keep the database connection open for a very short period of time: just open the BLL object, pull data into a local object or list, and then close the BLL object (go out of scope). You can then work with the data returned by the BLL after the connection has been closed.

So...what does this look like. Well, on your Pages (the UI) you'll use Business Logic classes like this:

using (BusinessLogicSubClass bLogic = new BusinessLogicSubClass())
{
   // Retrieve and display data or pull from your UI and update
   // using methods built into the bLogic object
    .
    .
    .
}  // <-- Going out of scope will automatically call the dispose method and close the database connection.

Your BusinessLogicSubClass will be derived from a BusinessLogic object that implements IDisposable. It will instantiate your DAL class and open a connection like so:

public class BusinessLogic : IDisposable
{
    protected DBManagementClass qry;
    public BusinessLogic()
    {
        qry = new DBManagementClass();
    }

    public void Dispose()
    {
        qry.Dispose();  <-- qry does the connection management as described below.
    }

    ... other methods that work with the qry class to 
    ... retrieve, manipulate, update, etc. the data 
    ... Example: returning a List<ModelClass> to the UI ...

 }

when the BusinessLogic class goes out of scope, the Dispose method will automatically be called because it implements the IDisposable interface.

Your DAL class will have matching methods:

public class DBManagementClass : IDisposable
{
    public static string ConnectionString { get; set; }  // ConnectionString is initialized when the App starts up.

    public DBManagementClass()
    {
        conn = new SqlConnection(ConnectionString);  
        conn.Open();
    }

    public void Dispose()
    {
        conn.Close();
    }

    ... other methods
}

Given what I've described so far, the DAL doesn't have to be IDisposable. However, I use my DAL class extensively in my testing code when I'm testing new BLL methods so I constructed the DAL as an IDisposable so I could use the "using" construct during testing.

Follow this approach and, in essence, you'll never have to think about connection pooling again.

回复收藏 0 原文