Best Practice - Keep SQL connection open, or open new each time?

Hi there

Im developing an app in vb .net that uses a SQL database.  My question is, is it best to publicly declare and keep open an SQL connection, or best to open and close the connection each time needed?

Users of my app could potentially keep the app open for days at a time (yes, they're not very computer savvy).

So, what do you think is best option?

Hope my question isnt too vague...appreicate your help

Cheers
Mark
LVL 1
marklyeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
Open the connection, do what you need to do w/ it, and then close it.  This will release the resources back to the connection pool so that other connections can use it.  
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jorge PaulinoIT Pro/DeveloperCommented:
Also regarding to security it's better to always close the connection. The time that it take to open and close doesn't justify to leave it open.
0
Göran AnderssonCommented:
Open the connection when you need it.

If you keep a connection open, it will time out after a couple of minutes, so it would have to reconnect anyway.

Connections are pooled in .NET, so it will reuse connections if possible. So, when you open a new connection it will reuse a previously released connection if there is one, instead of creating a completely new connection to the database.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

chapmandewCommented:
That isn't necesaarrily true....connections will time out if you have them in use and they are not able to get data from the db...otherwise they will wait for the next command to execute.

Tim
0
Göran AnderssonCommented:
Tim, that is the command timeout. Unused connections time out, otherwise all database servers would have to be restarted regularly to keep working.
0
marklyeAuthor Commented:
Thats great thanks guys...Ive split the points

Cheers
Mark
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.