Go Premium for a chance to win a PS4. Enter to Win


Best way to manage SQL connection

Posted on 2011-09-04
Medium Priority
Last Modified: 2012-05-12
We've been having SQL connection problem with our development VB & SQL.

We would like EE appreciation on this topic.  

Most apps we develop with a connection stay open until exiting.

Please advice
Question by:rayluvs
  • 3
  • 2
  • 2
  • +1
LVL 15

Accepted Solution

AmmarR earned 800 total points
ID: 36482222
hi Ramante

you have 3 ways to handle sql connection

Best way recommended (the only con is you open and close sql connection many times)
Open your SQL connection
Run your function
Close your SQL connection


Good (depending on each function sometimes the connection might stay open too long and perform many transactions)
Open your SQL connection
Run Function 1
Function 2
Function 3
Close your Sql conection

Or method 3 Which worst (but sometimes needed depending on requirements)
the connection stays open from the beginning of application till end
Open Your Application
Open SQL connection
Run you Program
Close SQL connection while closing the application

for more information & syntax you can check


or you can check on how to use


I would also recommend looking at LINQ and Entity Framework

Linq   http://msdn.microsoft.com/en-us/netframework/aa904594

 Entity Framework   http://msdn.microsoft.com/en-us/library/aa697427(v=vs.80).aspx

Good luck
LVL 13

Assisted Solution

by:F Igor
F Igor earned 400 total points
ID: 36482261
When you develop applications based on a persistent connection,
you need to take certain precautions:

*If the connection keeps inactive for a lot of time the server/client may disconnect it due to inactivity.
*Any connection or network related problem (even in inactivity) like network disconnection, loss of connectivity, database server maintenance or restart, will result in a lost of application connection. The application must have some mechanism for reconnection in order to keep it running.
*By the other hand, the persistent connection save resources (memory, networking) and have a more responsiveness, especially in real-time applications

*Using "on-demand" connections it's generally most appropiate in client-server connections that does not require constant data transactions, when the responsiveness a bit less important (every connection made needs some network-related work prior to start the data transactions) and when you need some multi-threaded applications making a lot of simultaneous or independent transactions. But:
*You need more control to the resources used (connections used and not closed may keep in memory all the running time, the same for cursors (DataReaders, Statements) that are querying data without closing or freeing their resources.
*If you use multiple connections for making related transactions, if some of the connection is lost there could result in some insonsistent transaction terminated.

Author Comment

ID: 36482404
Great info!

Our business environment if more toward financials.  That is, doing invoices, doing checks, reports, II guess the normal daily operation of an ERP solution.  At any one time, there may from 2 to 3 users running the module, but not 2 or 3 working with apps.  I mean that there can be 2 or more accessing Sales module, but only one doing invoices.

I think most of our apps is as in method 3 (the worst):
    - Open Your Application
    - Open SQL connection
    - Run you Program
    - Close SQL connection while closing the application

I think the best method is #1:
    - Open your SQL connection
    - Run your function
    - Close your SQL connection

We're willing to change all apps to point to this type of SQL connection/Access.

Some colleagues and programmers we have spoken with are more inclined on the #3; so our doubt on which to use.

We know that it's difficult for EE to give an accurate recommendation, specifically with little data at hand, but based on our description on how we use our programs, will method #1 tax our SQL database or instance?

Please advice.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 15

Assisted Solution

AmmarR earned 800 total points
ID: 36482467
It shouldn't tax your sql server

opening sql connection when needed and closing it soon after that, reduced overhead on resources.
and with that use connection pooling.


actually now if you use sqlDataAdapters, this is how it works already

The fill method automatically open the connection and when the dataset is filled properly then the coonection is closed.

i still recommend if you can spend time reading about LINQ or Entity Framework.
LVL 18

Assisted Solution

deighton earned 800 total points
ID: 36483176
are you using VBA, VB6 or VB.net?  Are you running out of connections?

Is it possible that you are opening, but then failing to close a connection.  If this happens multiple times, you can run out of connections available.

If you are using .net, then you should be able to take advantage of connection pooling - that way in code you always close your connection when finished, but rather than completely closing, it is returned to a 'pool' awaiting reuse when next requested.  If the connection(s) are not used for some time, they are then closed by the pool.

by default your connection string in .net will enable pooling, unless you code flags in the connection string to disable it.



Author Comment

ID: 36484090
we use all 3, VBA, VB6 and VB.NET, but 99% of the development is under VB.NET.  If we had to choose on which we would like an opinion, it would be on VB.NET.

Can you elaborate on 'Connection pooling'?
LVL 18

Assisted Solution

deighton earned 800 total points
ID: 36487264
VB.net can 'pool' connections so that you do not have to go through the process of reopening the connection each time it is used.  It's like a 'behind the scenes' management of connections.   Unless you ahve deliberately turned it off, then you are quite probably already using it.

If you are having the problem that you are running out of connections, then it is quite possible that you are opening a connection object and failing to dispose of it or close the connection.

The best way to manage connections in most cases in .net is to open a connection only when needed, for example to populate a dataset or execute a data reader, then close the connection when finished.  If you are using a connection multiple times in a routine, you probably want to open at the top of the routine and close at the end, but becasue of connection pooling, opening and closing connections is not a big overhead.

Author Closing Comment

ID: 36516922

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

877 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