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

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

chapmandewConnect With a Mentor Commented:
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.  
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.
Göran AnderssonConnect With a Mentor Commented:
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.
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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.

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.
marklyeAuthor Commented:
Thats great thanks guys...Ive split the points

All Courses

From novice to tech pro — start learning today.