[ACCEPTED]-When should I open and close a connection to SQL Server-sqlconnection
No, don't keep a static SqlConnection unless you have 18 to. Threading would be one concern, but 17 more importantly - usually you simply don't 16 need to. With your code as presented, the 15 internal connection pooling means that most 14 of the time you will get the same underlying 13 connection on successive calls anyway (as 12 long as you use the same connection string). Let 11 the pooler do its job; leave the code alone.
This 10 also avoids the issues of what happens when 9 you start having two threads... now each 8 can do work on their own connection; with 7 static (assuming you don't use [ThreadStatic]) you'd have 6 to synchronize, introducing delays. Not 5 to mention re-entrancy (i.e. a single thread 4 trying to use the same connection twice 3 at the same time). Yup; leave the code alone. It 2 is fine now, and almost any change you make 1 would make it not fine.
Because the SqlConnection has a connection 9 pool when you call Open() and Close() you 8 aren't actually opening and closing the 7 physical connection to the server. You are 6 just adding / removing the connection from 5 a pool of available connections. For this 4 reason it is a good and best practice to 3 open the connection as late as possible 2 and close the connection as early as possible 1 after executing your command.
In your code sample there is no need to 4 call the close() method on the connection 3 object as it will be handled automatically 2 due to the code residing inside a using 1 block.
Most programmers believe in open late and 6 close early. This is only a problem if the 5 latency for opening and closing the connection 4 each time causes the entire application 3 to slow down.
In your case with a static 2 class it is probably best to open and close 1 the connection each time.
You are doing the best practices. Only 5 open it right before you are going to query 4 it, and close it as soon as you can. This 3 kind of thing may seem wasteful at first, but 2 it actually makes your application more 1 scalable in the long run.
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.