1

is it be better to open connection to database -

make any querys...update....delete -

and after i use to close this connection

or

open the connection when the program load -

and close when the program close ?

thanks in advance

Gali
  • 14,511
  • 28
  • 80
  • 105

4 Answers4

6

In general, you Close (Dispose) as soon as possible in your code. With a try/finally or using block.

What actually happens depends on the ConnectionPool settings for your app.

Basically the presence of the ConnectionPool means you don't have to worry about the using of connections (how many, how long to maintain) anymore, it becomes an external configuration.

BTW: With the exception of the WinCE framework, slightly different rules there.

Community
  • 1
  • 1
H H
  • 263,252
  • 30
  • 330
  • 514
2

always close the ADO.NET connection when you finish with it. The reason is that connection are pooled behind by the ado.NET infrastructure, so even if open a connection the first time takes a while, by closing it you release to the pool so other part of the application can have a connection faster. Some exeption to this rule can be done with some embedded database, but we need to look at the single case.

Felice Pollano
  • 32,832
  • 9
  • 75
  • 115
2

You should always close your connections immediately using using blocks.

SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
1

Closing a connection is not the same as Disposing. A closed connection can be re-used by the connection pool based on a dictionary look-up on the connection string(your connection string must be identical to make use of pooling, but this feature is transparent). On the other hand, if you dispose or use USING, then the connection object will be destroyed and cannot be re-used.

If you plan on re-opening the connection a short time later, it would be more performant to use Close.

Bengie
  • 1,035
  • 5
  • 10
  • Just to clarify, calling Dispose does not remove the connection from the Connection Pool. And I would stay away from the performance issue of Close vs a Using statement. – kirk.burleson Sep 12 '11 at 20:17
  • Here's a quote from a Developer(http://social.msdn.microsoft.com/profile/david%20m.%20kean/) for Microsoft's Base Class Library Team(May 6th 2010): The exception to this is SqlConnection, et al, Close does not mean the same as Dispose. Close closes the connection, but allows you reuse the same connection instance later by calling Open. Dispose closes the connection, but does not allow you to reuse the same connection instance. http://social.msdn.microsoft.com/Forums/en-US/netfxbcl/thread/b3587ccc-74e0-4575-9b60-7c529d2607ac – Bengie Sep 15 '11 at 18:49
  • Read the "Adding Connections" section here: http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx – kirk.burleson Sep 15 '11 at 21:11