Best practice with a Connection object in VB 2005

In VB6, we used to open a Public Connection object in Sub Main() and the Connection object used to be open till the application was running.

In VB 2005, I am declaring a connection object in a Module like this:
Public cnMain As New Data.SqlClient.SqlConnection("Data Source=localhost;Initial Catalog=dbAccounts;Integrated Security=True")

and in the Application_startup event I am opening the connection object which remains open througout the application.

My question is that what I am doing is the optimized way of doing it or is there some other better way to do this?
LVL 18
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DarrenSenior Software EngineerCommented:

Keeping a connection open for the lifetime of an application can cause problems as each user will maintain a connection and use up resources on the server.

You should create a connection component and only access the database when needed.

For better performance, you should only open the connection when you're using it.
If you're performing a read, setup a sub that opens the connection and stores your results (query) in a datareader.
If you're writing - use a update/insert query and execute that against the data source inside another sub...

Let me know if you need code samples.
DarrenSenior Software EngineerCommented:
There are loads of articles on this and you should probably even look it up in EE as this question has surely been asked loads of times.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

If this is a single instance application then it is ok if you keep the connection open throughout the life of the application. If the application is going to run on many clients and if the database access if not required frequently then you can open and close the connections as required.
You can also create a common DataAccessLayer which you can use in all of your applications... look for Data Access Layer in CodeProject and on Google.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
In VB.NET, connections are valuable resources and are not left open throughout the entire life-cycle of the application.  Connection objects are most frequently used with DataAdapters, TableAdapters and Commands,  and are opened only to perform an action against the database (such as to fill a dataset), and then closed again.

A new term in .NET is "connection pooling" - if you look up this term, you will find quite a bit of information about the uses and life-cycles of connections in .NET.
SethiAuthor Commented:
All the answers are appreciated.

Let me highlight something that I had in mind with respect to a desktop application that I am developing for a client where the no. of concurrent users may not be more than 5. In VB6 we never used to close the connection because the overhead and time factor to open a connection was also considered. I suppose in VB 2005 also should we consider this? I mean wont opening the connection everytime we access the database take some fraction of time that is visible to the user? I completely agree than in web applications the policy of opening and closing the connection should be followed.

Darren: I am sorry, I did search some articles but was not satsfied, so I asked this question.

Sirbounty: I have written a few applications in the past and also have some source code from, BUT links from you will be appreciated as they will throw some more light.

shahprabal: I will look inot this as it is something new to me.
Opening and closing the connection object are expensive procedures.  .NET pools connections to improve performance.  A connection pool is a group of connections that are available for any bit of code that requires them.  If, while a connection is in the pool, another request is made to open a connection with the same connection string, the pooled connection is provided to the calling procedure, saving the processing expense of creating and opening a new connection to the data source.  If the connection is not reused within a certain time, the connection will be closed and the connection object will be made available to the garbage collector.

Connection pooling is low cost, compared to that of reopening connections.

(Ref. ADO.NET 2.0, Rebecca Riordan).
DarrenSenior Software EngineerCommented:

Basically in answer to your question

'My question is that what I am doing is the optimized way of doing it or is there some other better way to do this?'

The answer is it depends on your problem. Optimized is a difficult word to answer. There are different methods for different application types.

1: If as you say you only have 5 clients connecting to the server then you can keep a connection object open for the duration of the application.

2: If there are loads of users then you are better off opening and closing the connection to keep resources low on the server. Also using connection pooling here is a very good idea as it does quicken the connection times to the server.

3: If you are talking about code reuse then creating a Data Access Layer (DAL) is a very good way to go as you can reuse the same data access code again and again for different applications.

Hope this helps some more

Looking at the issues here from a slightly different angle, keeping a connection open was probably sensible in VB6 because its data-handling model was not disconnected.  If you wanted to do something with data, you needed an open connection to do it with/through.  But VB.NET/ADO.NET works with a disconnected model.  The app gets the data when it starts, does what it wants with it during its life (without any reference being necessary to the database), and saves back changes in one block when it closes.

That's all a vast oversimplification, of course.  There can be all sorts of reasons, even with the ADO.NET disconnected model, more frequent trips to the database may be desirable.

It seems to me, however, that the difference in approaches between VB.NET and VB6 means that whereas, in the latter, the norm would be continuous connection and one would want to look for reasons to do something different in the former it is more likely to be infrequent more or less instantaneous connections with one needing good reasons to do something different.

SethiAuthor Commented:
Thank you everyone.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.