Solved

Vb.net to sql Server connection

Posted on 2012-03-16
2
215 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

623 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