Which one of the following two methods has better performance ?
using( var DB_Connection_s = new DBConnection() )
{
//todo: interact with database connection
}
or just :
DB_Connection_s.Close();
at the end.
Does the first method make the pooling concept useless? Because if I dispose the connection with each use, then I have to open a new connection every time (and there won’t be any connections in the pool).
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
The using pattern is better, since the Dispose call closes the connection anyway, but as a bonus the connection is closed even if something inside the using goes wrong. For example an exception or just a return that forces the program execution to go out of the using scope. With a using, you don’t need to explicitly close the connection, which makes the code more readable.
As an another pattern, the connection must be closed as soon as possible. There is no performance drawback in closing/opening the connection too frequently, because the connection pool will optimize the connection re-using for you.
Method 2
Connections are released back into the pool when you call Close or Dispose on the Connection…
source = SQL Server Connection Pooling (ADO.NET)
So, remove any worry about performance loss caused by missed pooled connections.
From the code standpoint the difference should be so minimal that the using statement should always be used
Method 3
Use Dispose. Internally within Dispose it will close the connection so you don’t need to worry, this can be checked easily enough with Reflector or similar if in doubt.
As for the performance I would still go with the Using. Windows has various caches enabled (certainly in ODBC) to ensure that re-use can occur for repeated requests to the same connection and therefore you shouldn’t really need to worry about the performance.
Method 4
Unless you going to call .Open() again sometime soon,
use the using(){} block.
if you are going to use the same connection somewhere else soon,
call .close(); then .open() and so on…
keep your class implement IDisposable and dispose of the connection there!
it is still taking time to create the Connection object
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0