Maintain one connection or only open when I need to change something?
I have a SQL server on my home server (which is an Atom processor with 1GB of RAM) and I use 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 and then hold it for the entire life of the application, or create a connection only when needed. What happens if the app suddenly disappears? is the connection clearing?
source to share
If you do not pass the DataContext object to an already open SqlConnection, the DataContext will automatically close the database connection after the database operation completes. Therefore, it will not keep the connection open. You can see this by looking at the DataContext class in Reflector or you can read ASP.NET MVC Tip # 34: Dispose of your DataContext (or Do not) blog post. So even if your DataContext continues to live, there shouldn't be any open database connection.
If you handle the database connection outside of the DataContext and keep it open, then you really shouldn't be doing this. Typically, you should create and use resources when and where you need them, including the DataContext object. There is no need to open a database connection without any requirement, to close it so that it will be released to the pool again to service another database connection request. As I said, if you let the DataContext handle the database connection, you don't need to do anything special about the database connection.
If your application crashes and exits unexpectedly, you should be fine as all users will die, including open database connections and the underlying connection pool associated with your application domain.
source to share
Bring up the data context when you need it and get rid of it when you're done.
Having one global data file means you have to pass it on to whatever it needs. (Every method you write that needs database access has an additional parameter in its signature.)
Having a single global datacontext will also hurt if you ever decide to multithread one of your applications.
source to share
Have you considered SQL connection pooling in .NET? Take a look at the article http://msdn.microsoft.com/en-us/library/8xx3tyca%28VS.80%29.aspx . The connection pool takes care of all the dirty work for you and is automatically cleaned up after itself if an error occurs in your program. This is quite efficient at reusing connections, so there is very little overhead when reusing connections (creating the first connection when you run your program still has the same cost). This might be overkill for small applications, but it is probably a good habit to get into big projects.
source to share