I was wondering if it is a good idea to maintain a database connection ( System.Data.SqlClient.SqlConnection() ) open or is it recommended to close the connection after using it and open it again when needed ? ( Note : My app will run continuously for days/months ) . I am kind of pushed towards leaving it open. Which solution is better ?
Add a comment
|
3 Answers
In general, dispose of it when you're done with it, and don't worry about it.
ADO.NET implements connection pooling by default, so the connection is kept open behind the scenes so as to spare you the performance penalty of opening new connections all of the time.
Another reason to close the connections in your code -- if you lose connectivity to your database server when you're not using the connection, you won't encounter an error, which could happen if you keep the connection open.
Comments
You absolutely should open your connections as late as possible, and close them as soon as possible.
Not only should you close them when you are done, though: you should close them even between closely-related commands, if there is any other code running in between at all. In fact, let me put it like this: Close your ADO.NET Connection objects as quickly and frequently as you practically can. (that is, don't do obviously stupid things to close connections that obviously should not be closed)
By default, the ADO.NET provider for SQL Server (as well as most of the other prominent providers) provide connection pooling. This will actually manage the creation and destruction of these connections for you - but only if you close them when you are done.
If you wrap the creation and use of your connection objects in using blocks, this is easy to do...
using(SqlConnection conn = new SqlConnection(...))
{
///Open and use the connection
} //the 'using' causes it to automatically be closed.
2 Comments
stakx - no longer contributing
I find the last code example with the
using block misleading: using won't .Open(), nor .Close() a connection, these operations still have to be done manually.
Andrew Barber
The
using statement causes the connection to automatically be closed. There is no need to explicitly call Close() when you call Dispose(), which is what the using construct does.Open the connection if you needed, don't waste resources ;-)
