Questions related to Connection Pooling in .NET

WCF
WCF @Image courtesy of ddpavumba/ FreeDigitalPhotos.net

Questions related to Connection Pooling in .NET

Welcome to CodeSpread!

What is Connection Pooling in .NET?

Here, the term connection refers to connecting to a data source and Pooling means grouping together of resources.
So, Connection + Pooling means grouping together of resources like connection.
A more formal definition would be; a Connection Pool is a group of open and reusable connections.

Why it is required?

We know that, to execute any query against our database, we are required to establish a connection with the database server and then execute the query against that database server.
But this whole process requires lot of handshaking, parsing of the connection string, authentication of the credentials and so on. Is this a lot we are doing to open a connection?
The answer is No, But Imagine a situation where the same process is repeated for many requests. At this point, much time consuming and resource eating, similar connections are opened and yes, closed also.
Connection pooling addresses this problem by bypassing the whole process of repetitive cumbersome connection creation.
Connection Pool maintains a set of active connections for each given connection configuration. When there are many repetitive requests, one of the idle and free connection is taken from the pool to serve each of the requests.

Advantages:

Improvement of performance for ex: less instances of opening and closing of connections to the database.
Improvement of scalability for ex: Reusability of connections to cater maximum users.

Disadvantages:

Idle open connections.

How it works?

Note that, Connection Pool maintains a set of active connections for each given connection configuration.
A Connection Pool is created starting from the first request where a connection is created for the first time. Ok, now we have a live connection and we did some DB operations like executing stored procedure or a query.
Once work is over, this connection is closed or disposed and is released back into the pool where it sits idle and wait for next request.
Similarly, successive connection objects are created and added to the pool as needed, and it can maximum reach till Max Pool Size(default: 100).

Note: MinPoolSize is 0 else specified

Try
{
SqlConnection connection = new SqlConnection( "Integrated Security=SSPI;Initial Catalog=Northwind")
{
connection.Open();
}
}
Catch(Exception ex)
{
//Throw Exception
}
Finally
{
Connection.close();
}

How can a connection be deleted from Connection Pool?

We have two methods viz.
ClearAllPools: It clears the connection pools for a given provider, and
ClearPool ; It clears the connection pool that is associated with a specific connection.
Or else “iisreset” clears all the connection pools. Simple!

More :

We would like to know the active connections in the pool. To do this, open the Query Analyzer and execute the query : EXEC SP_WHO.

Author: hershey

A passion for knowledge drives me to do programming, A passion for programming drives me to create something different, A passion for creation drives me to spread the knowledge.

Share This Post On

0 Comments

  1. nice superb explaination

    Post a Reply
  2. thanks for the tutorial
    I am having a problem.
    The feature “Create SQL server database” isn’t available , I can’t select it.
    Can you help, please ?

    Post a Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

More from CodeSpread:

  • Few lines about SQL Use statementFew lines about SQL Use statement We see this statement in Management Studio now and then and it is used quite extensively. For Example, [code] USE MyDatabaseName GO [/code] MyDatabaseName : MyDatabaseName is the n...
  • Transact-SQL : Try-Catch BlockTransact-SQL : Try-Catch BlockSimilar to exception-handling features of .Net, T-SQL code provides a TRY-CATCH block. It is one of the implicit features of T-SQL and a famous error handling technique to catch errors. A TRY-CA...
  • SQL:Target a SubQuerySQL:Target a SubQueryPeople ask me a lot about subquery so I thought let’s answer with a article touching a bit of everything about subquery. Before discussing SubQuery, there are few things which we should know lik...
  • Verify existence of SQL tables using Object_ID()Verify existence of SQL tables using Object_ID()Why Object_ID() ? Let’s create a SQL table “Employee” [code] CREATE TABLE Employee ( [EmployeeID] [int] IDENTITY(1,1) PRIMARY KEY, [EmployeeName] [varchar](50) NULL, [Salary] [int] NUL...
  • 19 Dec: Must Read Codes [1-5]19 Dec: Must Read Codes [1-5]We are sharing few must read codes/concepts which are required now and then. Each of these codereads doesn't require a full length descriptive article so combining them into groups of five. CodeRe...
  • IDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property: Almost all the time, We have a table with a primary key and would like to insert a new value automatically in the primary key field. To achieve this, We have IDENTITY ...
  •  SQL Formatting standards – Capitalization, Indentation, Comments, Parenthesis SQL Formatting standards – Capitalization, Indentation, Comments, Parenthesis A post by guest author Milena Petrovic Nobody likes to read a wall of text, even when it’s just plain text. When it comes to reading code, the problem is even bigger. Code can have different for...
  • SQL:Target a TriggerSQL:Target a TriggerI remember those days when I used to simply fall into the trap set by client requirements which could have been easily resolved by using a trigger. So I can definitely say, trigger is a life saver,...