Hey All -

I've been running into a problem, think I have a solution, and would
like some input on it. I'm using ado.net 2.0 and sql server 2005. I'm
writing a layer to create and maintain the database of our product. The
code is given a server name, database name, and credentials. The
credentials do have the correct permission, and each of the following
steps happen on their own...that is they each create a new connection
and dispose it when done. The steps it takes are:

1. Try to open the database as specified. If it works, check the schema
version and continue; all is well.
2. If the initial open fails, try to create the database by dropping
the database name from the connection string, connecting, and trying a
create command.
3. If the create succeeds, repeat step one again to ensure the db is
created.

So there are 2 connection strings, causing 2 pools, one to the
database, one to master. When I simply let this run without debugging,
step 3 throws an exception, even though it did succeed creating the
database. Actually, the next attempt to open the database with the
orignal connection string results in the exception, regardless of when
it happens.

I've found 2 things that fix this; disabling connection pooling or
calling SqlConnection.ClearPool() on the connection when it tries to
open. This leads me to believe that the pooled connection retains the
exception from the original open attempt and when the next open attempt
happens, it rethrows that same exception from before.

Is this behaviour correct and as designed?

The other catch is that if I step through these steps with the
debugger, it succeeds, making me think that the pool does indeed
eventually clean out the errored connection, but not right away,
requiring the pool to be clear explicitly.

Any input on this?

Thanks,
John

SqlCommand slow on INSERT
Q: Whitespaces in WHERE
Connections in pool not being reused
Cascading Deletes in ADO.NET (Please Help!!!)
How to use bind variables effectively for Sql performance?