How can I force connection pooling to keep at least one connection alive ?

[TheSteph]

Hi,

(using C#, VS2005, .NET 2.0.)



I sometimes need to access my database (SQL Server) in SINGLE_USER mode.



That works fine but after a few minutes the connection pooling seems to
automatically free the connection to the server, and doing so other computer
can connect to the database and "steal the Single User session".



Here is my question : How can I force connection pooling to keep at least
one connection open ? (that will prevent other computer to "steal" the
single user session)

Thanks for your help !

Steph.

 

[Marc Gravell]

I haven't tested it, but you could try setting a load balance timeout
in the connection string?

http://msdn2.microsoft.com/en-us/li...nt.sqlconnection.connectionstring(VS.80).aspx

Marc

 

[Guest]

Connection pooling works based on a unique connection string, each one
defining a pool. So in this case, you would simply use a different connection
string with "pooling=off" in it.

--Peter
"Inside every large program, there is a small program trying to get out."
http://www.eggheadcafe.com
http://petesbloggerama.blogspot.com
http://www.blogmetafinder.com

 

[Marc Gravell]

In this scenario, that might make the problem worse; as soon as the
code closes the connection (assuming standard "open, use, close"
pattern of data-access) the /actual/ connection would be closed and
the database would be open. At least at the moment the connection is
held long enough to prevent other computers stealing access.

Of course, the better approach is to not relinqish the connection
until you are done with it, but that could require changes, especially
if the code is designed to use the "open, use, close" pattern at all
other times.

Marc

 

[TheSteph]

Thank-you for the helpfull link !

To use " load balance timeout" I have to give a time in minute... and it
work only with "clustered pooling" (I don't know what it is by the way...)

Instead you gave me the idea to use the "Min Pool Size=1" option; since I
use the same connection string for everything in my application I think that
this way at least one connection will remain in the pool until the
application is closed.
I tested it and it seems to work fine : In Single_User mode I do not loose
my session on the server anymore so others users cannot steal it...

Thanks !

Steph.

 

[Willy Denoyette [MVP]]

Thank-you for the helpfull link !

To use " load balance timeout" I have to give a time in minute... and it
work only with "clustered pooling" (I don't know what it is by the
way...)

Instead you gave me the idea to use the "Min Pool Size=1" option; since I
use the same connection string for everything in my application I think
that
this way at least one connection will remain in the pool until the
application is closed.
I tested it and it seems to work fine : In Single_User mode I do not loose
my session on the server anymore so others users cannot steal it...

Keep in mind that doing this will keep a client connected to the DB Server
for as long as the application is running, if you have many clients like
these, you'll exhaust the max. number of licensed connection with the DB
server for no good reason, more you are preventing other users to connect
even when you don't effectively need the connection.

Willy.

 

[TheSteph]

Yes, It was a problem...

But I solved it by doing the change in the connection string programatically
(add "Min Pool Size=1") only on the computer that enable the Single_User
mode. I then make a call to SqlConnection.ClearAllPool(), and open/close a
SqlConnection with the new connection string, and the pool will keep 1
connection only ont this computer. A check is also made at application
stratup. and a Reverse operation is made when user go back to MULTIUSER
mode.

Until now it seems to work perfectly....

Thank for your advices !

steph.