0

I have written a c# multithread function that reads data from sqlserver and for each row of data starts a separate thread.In each thread I have codes that need to work with db. this is what I do in each thread:

  • Create a new sql connection
  • Connect to it and open it
  • Do work
  • Close connection (while it is not necessary)

I should note that I use Using() command and I have read that this statement manages sql connection and closes it automatically after command is executed.

this function should be called periodically (say every 1 minute) and this is the problem i get into:

after first call number of active sql connections increases to number of rows (Threads that created per each row).in the second call this number increases again. for example after 5 calls with 100 rows we have 500 active sql connections! but I have read that connection pooling manages connections and uses created connections again. what should i do to solve this problem? did I miss any statement or is there something to do that I forgot?

sh.nemati
  • 11
  • 3
  • Please provide some context to your question. What are you trying to achive? Is this a load testing application? – Alex Jun 20 '16 at 09:34
  • its a monitoring service and i insert devices that should be monitored to db and start a new thread for monitoring each device – sh.nemati Jun 20 '16 at 09:38
  • Have a look if this has any relevance to your case: http://stackoverflow.com/questions/268982/net-sqlconnection-not-being-closed-even-when-within-a-using – Alex Jun 20 '16 at 10:06

1 Answers1

0

Using just is a try - finally, nothing more, it calls Dispose and that's all.

in a "using" block is a SqlConnection closed on return or exception?

Community
  • 1
  • 1
Dexion
  • 1,101
  • 8
  • 14
  • ok if it calls dispose why connections stay open and do not close after dispose command? – sh.nemati Jun 20 '16 at 09:59
  • It might remain opened because of the connection pool - https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx . You can limit the max number of the connections in the pool in the connection string: Max Pool Size=xxx . Without a code snippet, I cannot help more. – Dexion Jun 20 '16 at 10:27
  • Yes Its Because of the Connection Pool,How Can I limit The Time Connection Remains? Or is it a bad way to disable pooling? – sh.nemati Jun 20 '16 at 11:00
  • In the connections string: Max Pool Size=xxx – Dexion Jun 20 '16 at 11:59
  • what if I totally Disable It? – sh.nemati Jun 21 '16 at 04:34
  • performance will go down, as you practically turn off the possibility of parallel access to the SQL server. – Dexion Jun 21 '16 at 13:06