By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,824 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,824 IT Pros & Developers. It's quick & easy.

SQLConnection questions

P: n/a
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.
May 16 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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?

--
cheers,
RL
"fniles" <fn****@pfmail.comwrote in message
news:Oo**************@TK2MSFTNGP05.phx.gbl...
>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.

May 16 '07 #2

P: n/a
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" <robertlo@NO_SHAW.CAwrote in message
news:uY**************@TK2MSFTNGP02.phx.gbl...
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?

--
cheers,
RL
"fniles" <fn****@pfmail.comwrote in message
news:Oo**************@TK2MSFTNGP05.phx.gbl...
>>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.


May 16 '07 #3

P: n/a
It does not matter
--
cheers,
RL
"fniles" <fn****@pfmail.comwrote in message
news:eH**************@TK2MSFTNGP04.phx.gbl...
>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" <robertlo@NO_SHAW.CAwrote in message
news:uY**************@TK2MSFTNGP02.phx.gbl...
>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?

--
cheers,
RL
"fniles" <fn****@pfmail.comwrote in message
news:Oo**************@TK2MSFTNGP05.phx.gbl...
>>>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.



May 16 '07 #4

P: n/a
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 ?

"Egghead" <robertlo@NO_SHAW.CAwrote in message
news:eW**************@TK2MSFTNGP06.phx.gbl...
It does not matter
--
cheers,
RL
"fniles" <fn****@pfmail.comwrote in message
news:eH**************@TK2MSFTNGP04.phx.gbl...
>>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" <robertlo@NO_SHAW.CAwrote in message
news:uY**************@TK2MSFTNGP02.phx.gbl...
>>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?

--
cheers,
RL
"fniles" <fn****@pfmail.comwrote in message
news:Oo**************@TK2MSFTNGP05.phx.gbl...
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.



May 16 '07 #5

P: n/a
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.
fniles wrote:
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.

May 18 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.