SQL connection open/close
SQL connection open/close
SQL connection open/close
SQL connection open/close
SQL connection open/close
SQL connection open/close SQL connection open/close SQL connection open/close SQL connection open/close SQL connection open/close SQL connection open/close SQL connection open/close SQL connection open/close
SQL connection open/close SQL connection open/close
SQL connection open/close
Go Back  Xtreme Visual Basic Talk > > > SQL connection open/close


Reply
 
Thread Tools Display Modes
  #1  
Old 12-16-2011, 10:13 AM
jaccog's Avatar
jaccog jaccog is offline
Regular
 
Join Date: Mar 2005
Location: The Netherlands
Posts: 96
Question SQL connection open/close


Hi,

SQL connections confuse me sometimes

In almost every SQL related code on the internet the routine is like this;
* Open connection
* Execute SQL command
* Close connection

Is it good practice to just open a connection when the application starts and use that connection until the application closes?

I know that sequential connections are required when processing more queries, but what if I need to poll a certain Table every x seconds? Isn't is better to keep that single connection open for as long as the application is running (and re-open when a failure occurs) instead of opening a new connection every x seconds?

Using VS2010 by the way.

Thanks for clearing the air!
__________________
Jacco.
-The one who says it cannot be done should never interrupt the one who is doing it-
Reply With Quote
  #2  
Old 12-16-2011, 10:22 AM
PlausiblyDamp's Avatar
PlausiblyDampSQL connection open/close PlausiblyDamp is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Newport, Wales
Posts: 2,058
Default

Depends on the type of application really, if this is being done from a web app then the connection isn't tied to the end user's browsing session in any way shape or form so opening it and not closing it will result in the connection being open until either the GC kicks in and collects it or the session expires and then the GC kicks in and it is closed. This can result in far more connections being opened than needed and could even be a denial of service by exhausting all available connections.

If the connection is opened, used and closed immediately this can prevent this resource leakage, personally I would wrap the usage in a Using block to prevent connections being leaked if errors occur as well.

Performance wise .Net would normally be pooling connections so once a connection is opened and used closing it isn't terminating the underlying SQL connection, merely marking it as available to be used again - this means subsequent attempts to open a connection can simply use the existing connection object. As more concurrent users are on the system the number of open connections will increase but often at a much lower rate than the rate user counts increase, this gives an overall performance win.

Depending on how you are are accessing the database this may not need to be your problem anyway e.g. DataAdapters will take care of opening and closing a connection for you, Linq2Sql and EF contexts will open it automatically and also close it when Disposed (again wrap the calls in a using block).
__________________
Intellectuals solve problems; geniuses prevent them.
-- Albert Einstein

Posting Guidelines Forum Rules Use the code tags
Reply With Quote
  #3  
Old 01-02-2012, 05:31 AM
jaccog's Avatar
jaccog jaccog is offline
Regular
 
Join Date: Mar 2005
Location: The Netherlands
Posts: 96
Default

Thanks PlausibyDamp,

Thanks for the info.
I am using winform applications and my data-exchange is more like a Question and answer-routine. That is why i hardly use datareaders.
Got my solution working fine now

Ciao,
Jacco
__________________
Jacco.
-The one who says it cannot be done should never interrupt the one who is doing it-
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
SQL connection open/close
SQL connection open/close
SQL connection open/close SQL connection open/close
SQL connection open/close
SQL connection open/close
SQL connection open/close SQL connection open/close SQL connection open/close SQL connection open/close SQL connection open/close SQL connection open/close SQL connection open/close
SQL connection open/close
SQL connection open/close
 
SQL connection open/close
SQL connection open/close
 
-->