Best way to manage SQL connection

Posted on 2011-09-04
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
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
  • 3
  • 2
  • 2
  • +1
LVL 15

Accepted Solution

AmmarR earned 200 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


 Entity Framework

Good luck
LVL 13

Assisted Solution

by:F Igor
F Igor earned 100 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.
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

LVL 15

Assisted Solution

AmmarR earned 200 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 200 total points
ID: 36483176
are you using VBA, VB6 or  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 200 total points
ID: 36487264 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

756 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