Introduction

Now a days, more and more people are using the internet. People use websites for knowing all the details from various service sectors like railway, telecommunication, air travel, tourism, courier, cargo, etc..., oh God, I cannot say it will keep on going. So if we create a website for some service, definitely we look forward to having more customers. So if more customers log into the database, web servers perform slowly because of more multiple connections getting opened. To avoid this problem, we have to use the best feature named "connection pooling" in our website or Windows application in the connection string.

ConnectionString

I think you know about connectionstring, still I will tell you what it is. It is something like an address for connecting an application to the database wherever the DB is located. It looks like:

Connection Pooling

Connection polling is a very simple thing for implementing, but it makes a huge difference in the performance when more users are logged in. By default, a connection string is enabled with connection pooling.

By default, the maximum number of pools is 100, minimum is 0.

Working of Connection Pooling

Let's see how it works?

We said that by default we have 100 pools. When the first user enters our webpage and tries to open one connection, then the first connection pool is created. Now 99 pools are remaining. At the same time, one more user tries to open a connection with the same string then our pooler checks whether any opened pools with same connection string are free or available. If the first user closes the connection, that pool will be free and this user starts using the same pool. So this second user also uses the same pool and we can get more performance and resources. Suppose if the pool is not freed and the first user is still using it, what the pooler will do from the remaining 99 pools is that it will create one more pool and use it. Like this, when n number of users try to establish a connection if the pools are free it will reuse the same pool or if 100 pools are engaged, it will wait for a pool to be free. If the pool is getting free in a particular time, it will use it otherwise the waiting connection will expire. So for huge customer websites, 100 pools will not be enough. So we have to allow more pools.

Remember

If the connection string is different, it will create a new pool.

Using the Code

Below we can see the connection string created, here I am using a separate class for connection establishment and connection opening. If our website is huge, we don't want to write the connection string everywhere. Just create a connection in a class.

Here the class name is:

publicclass ContextFactoryDAO

We are establishing the connection string in the constructor of the same class. So by the time we create the object for the class, the connection string is ready. We are using a string builder for creating the connection string, so if there is any change we can alter it later very easily.

Comments and Discussions

This may just be a semantics question, but isn't the 'pool' a pool of connections and not of pools? When you occupy one item from the connection pool aren't you lowering the number of connections available in the pool and not a 'pool' per se?