c# connection to database

I was wondering if it is a good idea to maintain a database connection ( System.Data.S开发者_如何学PythonqlClient.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 ?

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.

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.

Open the connection if you needed, don't waste resources ;-)