C# connection pooling

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?

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.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi13y ago
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.