Each command uses two connections?

[Michael]

Hi,
After running the code:

SqlConnection conn = new
SqlConnection("server=.;database=Northwind;uid=sa");
SqlCommand cmd = new SqlCommand("select * from Customers", conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
dr.Close();
conn.Close();
conn = null;

The SQL Server Activity Monitor shows there are two sleeping
connections to Northwind database. Why?
Even I add:

conn.Dispose();

there is still one connection to Northwind. This connection will exists
until
my application is terminated.
According this situation, if I issue two commands, then there will be four
connections. Is this alright?

 

[Paul Clement]

¤ Hi,
¤ After running the code:
¤
¤ SqlConnection conn = new
¤ SqlConnection("server=.;database=Northwind;uid=sa");
¤ SqlCommand cmd = new SqlCommand("select * from Customers", conn);
¤ conn.Open();
¤ SqlDataReader dr = cmd.ExecuteReader();
¤ dr.Read();
¤ dr.Close();
¤ conn.Close();
¤ conn = null;
¤
¤ The SQL Server Activity Monitor shows there are two sleeping
¤ connections to Northwind database. Why?
¤ Even I add:
¤
¤ conn.Dispose();
¤
¤ there is still one connection to Northwind. This connection will exists
¤ until
¤ my application is terminated.
¤ According this situation, if I issue two commands, then there will be four
¤ connections. Is this alright?

I doubt it. I'm not sure why you have two connections to begin with, but you have to keep in mind
that released (closed) connections are pooled and if not re-used for the certain period of time are
eventually destroyed.

What you are probably seeing in the Activity Monitor is a function of connection pooling. With
respect to the additional connection, is there any chance the application is implicitly creating a
connection?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Michael]

Hi, Paul:

This question is asked by one of my student in my class.
When he show me the test result, I also doubt it. So I
write a very simple project to test it, and I got the same
result.

I guess it's ADO.NET component used another connection
to query table shema. But it's just a wild guess.

It's very easy to reproduce, maybe you can try and see if
you got the same result.

Michael

 

[Marina]

This is due to connection pooling, and the pool creating that extra
connection in anticipation of multiple users and requiring more connections.
By the way, you can Google groups to find answers to many common questions
like this, it has been asked many times before.