How important is it for me to pool my connections?

Question

It has been suggested to me that I rearrange my code to "pool" my ADO connections. On each web page I would open one connection and keep using the same open connection. But someone else told me that was important 10 years ago but is not so important now. If I make, let's say, 5 db calls on a web posting, is it problematic to be using 5 separate connections that I open/close?

Answer 1

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.