Data Connection - Best Practice

Hello All,

I was having a debate with a colleague today re: best practice when it comes to handling connections to online databases.
Imagine a situation like this:

• We connect to the database using one connection object
• We make a query
• As a result of that query, we need to make another

My colleague argues that it would be better to initiate a new connection object to make the new query; I argue that one should close the current connection object, wait for it to close and then re-connect using the same connection object to perform the new query.

Which do you prefer?

Jord

If I use a strict reading of the scenario, the answer is "Neither - use the same connection"... but, if the situation has logic between the the last two steps, then the answer is "who cares?" An object is just that, an object... doesn't matter if it's a new one or a recycled one, because the underlying connection is what really matters, and since ADO.NET uses connection pooling when it can, the object is irrelevant.

Generally speaking the generally accepted best practice is to have the connection open for only as long as you need to in order to perform the action desired. That action could consist of one query, or a series of queries, but once the action is done, you close the connection and move on. To that end, most people create the connection object, open it, use it, close it and dispose of it as quickly as possible... there's no need to keep the object around if you're not going to need it.

-tg

Quote:

---

Originally Posted by intraman

... wait for it to close...

---

Why do you say this?

Do you think that the "closing" of the connection is hand-in-hand with the committing of the last query run? Flushing of some buffer??

It is not related.

If you are holding a TRANSACTION open - then that's a different question.

Personally I use one connection for a solution that resides in a class project removed from the main project. Depending on the operations dicates if the connection stays open or is closed after operations.

MSDN VS2010 sample solution, refer to the data access project for MS_AccessConnection.vb