Solved

Best way to manage SQL connection

Posted on 2011-09-04
8
267 Views
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
0
Comment
Question by:rayluvs
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 15

Accepted Solution

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

Or

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

http://msdn.microsoft.com/en-us/library/ms971481.aspx

or you can check on how to use

sqlConnection
SqlCommand
sqlDataReaders
sqlDataSets
sqlDataAdapters
----------------


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

Author Comment

by:rayluvs
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.
0
 
LVL 15

Assisted Solution

by:AmmarR
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.

http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.80).aspx

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.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 18

Assisted Solution

by:deighton
deighton earned 200 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.

http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx


0
 

Author Comment

by:rayluvs
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'?
0
 
LVL 18

Assisted Solution

by:deighton
deighton earned 200 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.
0
 

Author Closing Comment

by:rayluvs
ID: 36516922
thanx
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now