This seems to be a simple question, but I wonder the disadvantages of not calling the "close()" function.
Asked
Active
Viewed 2,498 times
6 Answers
10
Sooner or later, you'll run into the "The Maximum Connection Limit Has Been Reached" error. I'd call that a major disadvantage.
Erich Kitzmueller
- 36,381
- 5
- 80
- 102
7
Apart from exhausting the connection pool (as most answers so far have been), you are in danger of locking data.
If you are reading or writing to a table, some locking semantics will cause certain rows to be locked to other connections. This is especially true if you have any open transaction on the connection.
Reads and writes can then fail and the application will throw exceptions all over the place.
In short, always close the connection.
Oded
- 489,969
- 99
- 883
- 1,009
-
Even after the sql command using the connection is done with the rows? – Thanos Papathanasiou Jan 26 '10 at 13:04
-
If you have a transaction open, then yes. – Oded Jan 26 '10 at 13:07
-
2A commit should be enough to avoid that problem, though. – Erich Kitzmueller Jan 26 '10 at 13:27
-
You will need to commit all nested transactions. – Oded Jan 26 '10 at 14:57
1
The connection pool will fill up and any new connections will time out waiting for a new connection from the pool.
bleeeah
- 3,534
- 19
- 25
1
Each and every connection to SQL Server requires memory allocation.
So the more connections you have open the more memory that is being used and held, that could potentially be put to better use.
If you want to know just how much memory is used by connections in SQL Server, take a look at the following reference.
Shaul Behr
- 36,951
- 69
- 249
- 387
John Sansom
- 41,005
- 9
- 72
- 84
-
Updated your link to MSDN, but at ~300K per connection, this is probably one of the lesser considerations... it will take thousands, or tens of thousands of connections before this starts making itself felt. – Shaul Behr Nov 02 '11 at 08:36
0
a connection to the database server is open. suppose you have 100s of programs hitting the same server...
you should also dispose it
Thanos Papathanasiou
- 904
- 2
- 12
- 23
0
Quick simple answer is that the sooner you close, the sooner the connection can be re-used by the connection pool.
Justin Wignall
- 3,490
- 20
- 23
-
"using using is good" is a little mantra I say to myself when conencting to sql. SqlConnection implements iDisposable so simply wrap your sql code in a "using" block to ensure your close is called automatically. – Justin Wignall Jan 26 '10 at 13:02