Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
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 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


 Entity Framework

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.
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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  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 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

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 post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

688 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