Re-opening SqlConnection instance

Question

hi

1) I noticed that after you close SqlConnection instance, you are still able to re-open the same connection:

a) can you tell me what is going on behind the scenes? Meaning, after we close file stream object, we can’t re-open it, but instead must create a new object – so why isn’t the same with SqlConnection objects?

b) If pooling is enabled, then I assume the connection object is returned back to the connection pool and can later be reused?!

But then how do we know that after re-opening the connection we will get the same Sqlconnection instance as before we initially closed it? I’m asking this because as far as I know, even if connection pool has several connections available, these SqlConnection objects could be in different state than original SqlConnection object ( by “different state” I mean that some properties could hold different values than those in “original” connection object)?!

c) What if pooling isn’t enabled? Does .Net recreate SqlConnection object “from scratch”? Again, before we initially closed the SqlConnection object, we might have changed values of some properties? How will compiler know how to recreate the state SqlConnection object was in before we closed it?

thank you

Answer 1

SqlConnection isn't directly the "real" connection in the first place. The connection pool looks after that. I don't know for sure, but I wouldn't be surprised to find that if you reopen the SqlConnection you could end up on a different "physical" network connection.

You could write a FileStream which allowed reopening - if it kept track of the name of the file as well as the underlying handle, it could just try to reopen it, with all the possible failures etc. It's just not very useful. (I can't say that I find being able to reopen SqlConnection useful personally, admittedly.)

In terms of knowing that you'll get "the same SqlConnection object" - you aren't calling a method which returns a reference to a SqlConnection object, you're just calling a method on an existing object. That reference won't change - just the state of the object (or possibly the state of something else it refers to).

I don't know what would happen if pooling weren't enabled. It couldn't create a brand new SqlConnection object (or rather it could, but that wouldn't replace the one you already had a reference to). I suspect it all boils down to SqlConnection being a wrapper around "something else" - and the "something else" could certainly be recreated.

Answer 2

hi

> In terms of knowing that you'll get "the same SqlConnection object" - you aren't calling a method which returns a reference to a SqlConnection object, you're just calling a method on an existing object.

So when you re-open the connection, you will always get the same object?!

That reference won't change - just the state of the object (or possibly the state of something else it refers to).

But why would the state of an object change, if the returned object is the same object we used before closing the connection?

I don't know what would happen if pooling weren't enabled. It couldn't create a brand new SqlConnection object (or rather it could, but that wouldn't replace the one you already had a reference to). I suspect it all boils down to SqlConnection being a wrapper around "something else" - and the "something else" could certainly be recreated.

Bottom line…when you close an SqlConnection instance, only way you can get back that same instance ( after re-opening the connection ) is if connection pooling is enabled?!

thanx mate