sql connnection, close as soon as possible - true for vb.net apps? or just asp.net

Question

When writing apps with asp.net, which I do more of than vb.net apps, I have always followed the rule of opening a connection to the server, getting the data, and closing the connection as quickly as possible. This seems like a best practice.

Is it equally important to do this on a client/server vb.net app? For example, if you had multiple forms, each either with a grid of data, or a form of data, would you typically open the connection when each form started, close it when the user left the form? or is it acceptable to define a global variable for the connection and keep it open the entire life span of the app?

Personally I would tend to open/close the connection with each form, but wondering what other folks recommend with a typical desktop c/s app?

Answer 1

Regardless of what application you are writing it is always best to utilize resources for a little amount of time as possible. This will reduce the possibility of competition for that resource.

Answer 2

Doesn't matter what type of application you're writing in .NET (asp.net, service, WPF, console application, windows forms, or just a library in a dll), they all use the same classes to access databases, which are known collectively as ADO.NET. And best practice is to open, close and dispose of your connections ASAP.

Answer 3

Remember to wrap your data access in a try block, with a finally statement which will close the connection.

This ensures that even if there is an issue, your connection will still close.

Answer 4

Only use it when you need it. Don't keep things lying around if you're not going to need it and use it right away. Same principle of declaring your variables and using them soon, rather than, say, having an enormous list of declarations near the top and using them later - this is confusing and can leave things unused unnecessarily.

Also, if you stick with the using statement pattern whenever you're using your SQL connection then it will do the closing for you. This is better than having it scattered and potentially forgetting to close it - depending on load this could tie up your connections and eventually lead to some connections being refused. I'm not saying you're not already doing this, but just in case. Most MSDN examples will show the using pattern for classes that implement IDisposable.

More on using here. The SqlConnection example shows the same thing. The using statement has the added benefit of acting like a try/finally block.