Click here to Skip to main content
15,559,491 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
Hi all,

My project is multi-Thread service using SqlConnection (sql server + C#), stability and performance are very importance, and there are not too much connection at same time.

In fact, I does has not much experiences of working with TSQL, now I am considering 2 solution of working with SqlConnection:

- Solution1: Using (SqlConnection con = new SqlConnection(someConnectionString)){...} => so dispose() the SqlConnection each time we use.

- Solution2: Using SortedList<ThreadName, SqlConnection> to manage SqlConnection, each Thread using its own 1 SqlConnection, it was opened 1 time and wasn't dispose(), so still keep Number of Connection opening at same time is small enough, because we keep Number of Thread at same time is smaller than a threshold.

I have no idea which solution is better (good stability and performance).

Could you give me some guide.

Thank in Advance,
Updated 22-Dec-11 20:41pm

1 solution

Based on the info you provided, I'd first try to keep things as simple as possible. So creating the connection and closing it when it's not needed anymore would be my choice. One reason is that the connection pooling helps you with the performance regarding to open the connection.

More info, see:
- SQL Server Connection Pooling (ADO.NET)[^]
- SQL Server: Understanding and Controlling Connection-Pooling Fragmentation[^]
Share this answer
TuanNGUYEN 9-Jan-12 10:06am    
I decided to chose your solution.
Wendelius 9-Jan-12 13:46pm    
You're welcome :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900