C# connection pooling

Question

At my same company we are using sql server 2008 r2 and C# 2010 and 2008 web desktop and console applications that are accessing the same tables. Alot of time the tables are being accessed at the same time. Thus my question is when do you recommend setting up connection pooling to the database. In other words limiting the number of connections that can be setup to the database? I want to prevent too many deadlocks from occuring. Thus can you tell me how the following: 1. when do you recommend connection pooling be setup? 2. Does it make a difference if this connection pooling is a web desktop console or some other C# application? 3. Can you point me to a reference and or some me some code how to setup the connection pooling?

Jignesh Trivedi · Answer

hi During application execution many identical connections will be repeatedly opened and closed. To minimize the cost of opening connections ADO.NET uses an optimization technique called connection pooling. Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Only connections with the same configuration can be pooled. ADO.NET keeps several pools at the same time one for each configuration. Connections are separated into pools by connection string and by Windows identity when integrated security is used. Please refer http://msdn.microsoft.com/en-us/library/8xx3tyca%28v=vs.71%29.aspx To prevent deadlocks you may use nolock keyword with query. select * from EmployeeDetails (nolock) E join EmployeeMasters (nolock)EM on E.EmployeeID = EM.EmployeeId hope this will help you.