Solved

Best practice with a Connection object in VB 2005

Posted on 2007-03-29
10
294 Views
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?
0
Comment
Question by:Sethi
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 18

Assisted Solution

by:DarrenD
DarrenD earned 100 total points
ID: 18816216
Hi,

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.

Darren
0
 
LVL 67

Assisted Solution

by:sirbounty
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.
0
 
LVL 18

Expert Comment

by:DarrenD
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.

Darren
0
 
LVL 14

Accepted Solution

by:
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.
0
 
LVL 27

Assisted Solution

by:VBRocks
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.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 18

Author Comment

by:Sethi
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 planet-source-code.com, 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.
0
 
LVL 27

Expert Comment

by:VBRocks
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).
0
 
LVL 18

Expert Comment

by:DarrenD
ID: 18816651
Hi,

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

Darren
0
 
LVL 34

Expert Comment

by:Sancler
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.

Roger
0
 
LVL 18

Author Comment

by:Sethi
ID: 18820821
Thank you everyone.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

896 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

14 Experts available now in Live!

Get 1:1 Help Now