Leaving the connection open or closed?

[wyrd]

When using DataReaders and SqlCommands to retrieve/input data, is it wise to leave the SqlConnection open for the entire lifetime of the application, or close it after every command action (ie; retrieving data or insterting data)?

[wyrd]

I believe I found the answer I was looking for. A reply on another forum on this topic;

 

They should be closed as quickly as possible.

 

ADO.NET automatically generates a connection pool, so when you "close" a connection, you are really just returning it to the pool without disconnecting entirely from the database (then if it is not used for a certain amount of time, it is reclaimed).

 

If, however you leave the connection open, it cannot be returned to the pool (which is bad).

 

Note: for ADO.NET connection pooling to work, you must have _identical_ connection strings! (even differences in spaces can cause two conn-strings to be considered different. In this case, ADO.NET would create a seperate conn pool for each string, instead of sharing them properly.

 

~ Andrew

[Nerseus]

Keep in mind you're only sharing connections on the same machine for Access (sharing works on SQL Server as described).

 

This is different than connection pooling, which is what COM+ provides for you. If you have two people connecting to SQL Server, they may share a connection (if the strings are exactly the same), but they're not pooled.

 

Also, there are valid reasons to keep things disconnected (get rid of the connection as quickly as possible) and reasons for keeping a connection open. For now, I'd suggest closing the connections as quickly as possible and filling datasets to be used client side. I wouldn't worry about keeping connections open for now as there use is more limited (but still valid!).

 

-nerseus