Solved

Vb.net to sql Server connection

Posted on 2012-03-16
2
207 Views
Last Modified: 2012-03-16
I have a winForms application that is doing tons of reads/writes to a database on hundreds of installed clients.    My question is ->  Is it better to open a connection to the server and keep it open during the session (hours - sometimes days).

(One issue here is that if computer goes to sleep after inactivity SQL will drop the SSID.)

OR

is it beneficial to Open/Close connection on every read/write.   This would be cause some additional overhead.  But, would always ensure that a new connection is established (if server is available).

What is your experience?
0
Comment
Question by:browe68
2 Comments
 
LVL 38

Accepted Solution

by:
PaulHews earned 500 total points
ID: 37731087
If you keep it open, you are frustrating the server's ability to manage the connection pool.

SQL Server Connection Pooling (ADO.NET)
http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

Note the following from the above link:
We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool. You can do this using either the Close or Dispose methods of the Connection object, or by opening all connections inside a using statement in C#, or a Using statement in Visual Basic. Connections that are not explicitly closed might not be added or returned to the pool. For more information, see using Statement (C# Reference) or How to: Dispose of a System Resource (Visual Basic) for Visual Basic.

Generally it is recommended that you do not keep a connection open longer than needed.
0
 

Author Closing Comment

by:browe68
ID: 37731146
Thanks!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

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

18 Experts available now in Live!

Get 1:1 Help Now