Best practice with a Connection object in VB 2005

Posted on 2007-03-29
Last Modified: 2013-11-07
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?
Question by:Sethi
  • 3
  • 2
  • 2
  • +3
LVL 18

Assisted Solution

DarrenD earned 100 total points
ID: 18816216

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.

LVL 67

Assisted Solution

sirbounty earned 100 total points
ID: 18816221
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.
LVL 18

Expert Comment

ID: 18816229
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.

LVL 14

Accepted Solution

shahprabal earned 200 total points
ID: 18816249
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.
LVL 27

Assisted Solution

VBRocks earned 100 total points
ID: 18816260
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.
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

LVL 18

Author Comment

ID: 18816462
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.
LVL 27

Expert Comment

ID: 18816556
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).
LVL 18

Expert Comment

ID: 18816651

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

LVL 34

Expert Comment

ID: 18816743
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.

LVL 18

Author Comment

ID: 18820821
Thank you everyone.

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Open a folder with partial name 14 28
Duplicate a row 2 29
Copy/Clone an object. 9 10
XML & .net 5 16
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now