Connection Pool

[jendeacott]

Hi,

I'm doing some testing reqarding db connections and something is driving me nuts!

I'm checking the connections made to sql via sp_who2 and using a console app is VS2005.

This is the simple code i'm using, that as far as I know should open and close the connection fine.

SqlConnection connection = new SqlConnection("String");
SqlCommand command = new SqlCommand(@"INSERT INTO Role(RoleName) values('test')", connection);
connection.Open(); 
command.ExecuteNonQuery();
connection.Close();
command.Dispose();

Console.Read();

I set three breakpoints

[A] at before connection.open()
at command.ExecuteNonQuery();
[C] at Console.Read().

Then I check the connections using sp_who2 in QA.

At point [A] there are no connections (except QA) - Cool

At point there is one connection - Cool

52 sleeping sa JOHNE-PC . test AWAITING COMMAND 0 0 01/01 00:00:00 .Net SqlClient Data Provider 52

at point [C] the same connection is still there = NOT Cool

When I close down the console app the connection is removed.

Is this normal, hopefully I'm missing something!!!!

Cheers,

Web Design Wetherby
Personal Web Design Service

 

[ca8msm]

Yes, it's normal. ASP.NET manages it's own connections and you'll find sometimes they are open when you are not expecting them. It's not necessarily a bad thing though as there are pros and cons to this.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[chrissie1]

If you really want them to close you should use connection.closeconnectionpool or something like that (can't remember the property) but remember that creating a connection takes time (more then a second) and that's why .net keeps one open all the time.

Christiaan Baes
Belgium

"My new site" - Me

 

[jendeacott]

Ah, right that makes sense then!

I should have posted before wasting an hour testing

Cheers guys.

Web Design Wetherby
Personal Web Design Service