SQLConnection questions

[fniles]

I am using VB.NET 2003. When using SQLClient.SQLConnection with SQL 2005
database,
1. connection pooling is automatically used, right ? I mean, in the
connection string I do not need to explicitly write Pooling=true ?
2. What is the default Max Pool size ? After I open the connection, how can
I check what is the max pool size ?
3. I always open the db right before I fill dataset or open a reader, then
close it right away, but sometimes I still get the error "There is already
an open DataReader associated with this Connection which must be closed
first." What is the best way to avoid this ?

Thank you.

 

[Egghead]

1. The conn pool is at the .Net layer, and it is not at SQL sever layer.
2. I believe it is ~850 ( I did not close the connection at a .Net app once,
and it ran vey slow after I have over 850 call)
3. You mean you close the reader as well?

 

[fniles]

I thought the maximum pool size is 100 ? But, I am not sure what the default
maximum pool size is.

I close the database after the reader, then close the database right away
after that. Then, when I am done with the reader, I close and dispose it,
too.
Do you declare the connection as a global variable or local variable ?

 

[Egghead]

It does not matter
--
cheers,
RL

I thought the maximum pool size is 100 ? But, I am not sure what the
default maximum pool size is.

I close the database after the reader, then close the database right away
after that. Then, when I am done with the reader, I close and dispose it,
too.
Do you declare the connection as a global variable or local variable ?

 

[fniles]

So, it does not matter using local or global SQLConnection variable ?
When I use a local one, on each subroutine I set the SQLConnection variable
to new, open and close the connection everytime I fill a dataset, is it
correct ?
Or, do I need to use a global SQLConnection variable
1. new and open it on form_load (and do not close this one until
form_Closed)
2. and in each subroutine use this global SQLConnection variable but new,
open and close the connection in this subroutine ?

 

[cj]

I've been watching your post because I've just been through a similar
situation.

I have a multi-threaded TCP/IP Server app that needs to execute a stored
procedure as part of answering each connection\request it receives. A
thread is created to handle each connection\request and it takes less
than 2 seconds to answer the connection and the connection and thread
end. But I get so many requests the app is usually constantly handling
1 or 2 requests and I have seen 150 concurrent requests being handled.

So how do I handle making that connection the the SQL Server? Well I've
been told here and by a colleague who'd researched it before that SQL
will reuse old closed connections (basically sql sorts it all out) so I
should just Dim a new sqlconnection in each tread when it is created and
close the connection before the thread terminates. It works! That's
about all I can say. It's amazing to me that it does and that's why I'm
watching the responses here too.