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
  • 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.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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 ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

856 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