Database Connection

Hello and welcome to Wednesday, February 12. Today we're going to talk about connection pooling, specifically connection pooling with SQL server data provider. The SQL data provider has built in support for connection pooling which basically means that it will keep connections to the database around even if your application seem sto be through with them just in case it requests another connection in the future. In which case it won't have to recreate the connection which is expensive in terms of processing power and time. If for example two or three connections are required to be open by the application it will keep those two or three connections open in case it needs them in the future.

Once a sufficient time has elapsed and it realizes that it doesn't need to the connection open anymore it will conserve connections on SQL server by shutting all the connections pools down. To kind of illustrate how it works lets start by actually building a little application that basically has three connections: con, con2, and con3. And we'll do various things. I'll even show how disposing of a connection really doesn't change anything. You can't circumvent the connection pooling. So let's go ahead and run our application. And before we actually start clicking buttons I have the SQL profiler open. And now let's start recording what happens. This will tell us every time that a new connection is created within our application.

So let's go ahead, pull this down so we can see and click connection open for example. And you can see barely it says "audit login, network protocol, application dot.net, SQL client, NT username Bob, login name, desktop" and so on. So now that we have one connection open let's go ahead and close that connection. And now let's open up second conenction to the database. And when I click it notice that nothing really happened. Why? Because our connection to the database is pooled, meaning that although we closed the connection from our codes perspective, from the SQL data providers perspective it knew that we might need another connection again. So it just left it open. So now what happens if I have the second connection open and I go to open yet a third connection. Notice that it does open up a new connection so we have another audit login. So now if we close both of these and then click "open," "open." Now what will happen if I click the third "open?" It will create another one. Why? Because we have three connections open and up to this point it only two connections pooled.

So now we can hit "close," "close," "close," "dispose." Notice that nothing happens whenever I shutdown my application. Notice that it logged out all three of those connections. However there are some caveats when working with connection pooling. Connection pooling depends on the connection strength being exactly the same for all of the connections that you hope to pool. So if even one character is off between this connection object's connection strength and this connection strength then they will be treated as two separate connection pools. Meaning that our application will open two physically different connections to the database. So the first tip is that you want to make sure that your connections strings are exactly the same and one way that you can accomplish that is actually through the use of the dynamic properties and mapping the connections strength for example to a configuration file. That's one way to make sure that all your connections are the same for you to take advantage of connection pooling.

The other way that you take advantage of connection pooling is to only keep connections open for the brief period of time that you actually need them. As you saw from our example we had one connection open but before we closed it we clicked on a second connection open to the same exact same data source. And then as a result what happened? It actually opened up a second connection to the database. It couldn't use the existing one that was open, it needed to open a new one. So if in our code we had immediately closed the connection after we opened it and then tried to open a second one then it would not require a new connection being opened to the database.

Now one last thing that I'd like to point out to you is that there is a great topic in the help about this: connection pooling for SQL server.net data provider. Please look this tip up because near the middle to bottom it gives you a set of connection pooling options that you can set in the connections string that allow you to have a little more control over how connection pooling works. For connection lifetime, you can set exact number of seconds that the connection should be open before it closes it automatically in the connection pool. And things like connection reset and the max pool size, how many connections should be allowed in the pool before the pool throws an exception. And so on. And you can even set whether you want to allow pooling in the first place. So again I'd highly recommend you take a look at this help topic if this is of interest to you.