Maintain a single connection or open only when i need to change something?

Question

I have an SQL server on a server at home (which is an Atom processor with 1GB ram), and i'm using it to store data for one or two of my applications. I was wondering if i should create a DataContext object when the program starts then hold onto it for the entire life of the app, or only create the connection when necessary. What happens if the application dies suddenly? does the connection get cleaned up?

Answer 1

Bring up the data context when you need it and get rid of it when you're done.
Having one global datacontext means you have to pass it around to everything that needs it. (Every method you write that needs database access has an extra parameter in its signature).

Having a single global datacontext is also just going to become a pain if you ever decide to multi-thread one of your apps.

Answer 2

Have you looked at SQL connection pooling in .NET? Have a look at the article at http://msdn.microsoft.com/en-us/library/8xx3tyca%28VS.80%29.aspx. Connection pooling takes care of all the dirty work for you and automatically cleans up after itself if an error occurs in your program. It's pretty efficient at re-using connections so there is very little overhead in re-using the connections (creating the first connection when your program starts still has the same cost). It might be overkill for small applications but it's probably a good habit to get into for larger projects.

Answer 3

Unless you're handing DataContext object an already open SqlConnection, DataContext will automatically close the database connection after the completion of a database operation anyway. So it won't keep the connection open. You can see this by looking at DataContext class in Reflector or you can read ASP.NET MVC Tip #34: Dispose of your DataContext(or Don't) blog post. So even if your DataContext object continues to live, there should not be any open database connection.

If you're handling the database connection outside of DataContext and keeping it open, then you really should not do this. Generally, you should create and use resources when and where you need them including DataContext object. There is no need to keep a database connection open without any demand for it, close it so that it gets released back into the pool to serve another database connection request. As I said, if you're letting DataContext handle the database connection, you don't need to do anything special as far as the database connection is concerned.

If your application crashes suddenly and terminates, you should be ok since everyhing will die including open database connections and the underlying connection pool associated with your application domain.