Passing around a SqlConnection

Question

I have created a TransactionScope and within the scope various items are created and updated in the database. During this process I make a lot of calls to the database. Originally I opened a SqlConnection in the beginning of the TransactionScope and passed it around to any function that made a DB call then I closed the connection after all the calls are made and before the transaction commits. Is it better to do this or to open and close a connection (using the same connection string) for each call?

Answer 1

If you're going to make a lot of calls in a row, and it's easy to pass in an open connection, then yes, reuse the open connection.

This is not incredibly important though. Not as much as it used to be. ADO.NET does a good job of managing this for you. See Connection Pooling. If your code is going to get complicated and weird to facilitate one single, open connection object, it's not worth it.

(Now, making sure your connection objects (whether reused or not) are disposed of is of course really important, as I bet you already know.)

Answer 2

Anyway if want to have several connections to DB use connection pool.

Answer 3

I tend to avoid passing connections around at all cost. I've just seen too many errors creep into a system when someone else, for example, closes a connection in the middle of a sequence of operations because they didn't know others were going to use the same connection.

Creating a SQLConnection is almost free due to ADO.NET's connection pooling mechanisms so don't think you're going to save any space/time by creating one and passing it around.

If, as in your situation, you truly do need to perform a number of DB operations within a transaction scope, then manage your scope appropriately, but create, use and dispose of your connections as and when you need them - it'll help keep your code A LOT cleaner and safer.

Answer 4

In general I would agree with what others have said. Note however, that once you use multiple connections inside a single TransactionScope instance, it will promote your (local) transaction to a distributed one, thus incurring some possibly significant overhead.

Multiple connections from the same pool, thus using the same database, also count as multiple connections in this sense.

If that is an issue for you, versus code structure or "cleanness", typical SQL statement execution time, etc., you need to decide.