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.SqlConnecti
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?
In VB 2005, I am declaring a connection object in a Module like this:
Public cnMain As New Data.SqlClient.SqlConnecti
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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).
Connection pooling is low cost, compared to that of reopening connections.
(Ref. ADO.NET 2.0, Rebecca Riordan).
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
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
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
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
ASKER
Thank you everyone.
Darren