Sqlconn.open() doesn't work after starting sql server. Weird problem.

[Sam]

I have an application that sits in the traybar that calls a msde sp3a
database to verify that the connection to the sql server instance exists.
If the sql server is up obviously the application runs perfectly. If the
database is brought down on purpose or by a machine shutting down and the
VerifyDBConnection() function is called it fails. So far so good. Now if
the VerifyDBConnection() is called repeatedly while the database is down it
no longer connects to the database even if the database instance is
restarted
again. The exception happens on sqlConn.Open();. What makes me feel horrible
is that if the application is closed and opened again the database
connectivity runs perfectly again. This rules out any ip
address/password/network failure issues.

Why does it require that the application restart before connecting properly
again. It should connect correctly to the database as soon as the
verifydbconnection() function is called on the instance that is restarted.
I performed the test again and again maybe thinking that the datareader is
pooling connections. So I began using the sqlDataReader =
sqlCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); . It
still does the same thing.

private bool VerifyDBConnection()

{

Console.WriteLine(DateTime.Now.ToString() + ":" + "VerifyDBConnection");

bool bResult = false;

SqlConnection sqlConn;

SqlDataReader sqlDataReader;

SqlCommand sqlCmd;


sqlConn = new System.Data.SqlClient.SqlConnection();

sqlConn.ConnectionString = m_strConnection;

sqlCmd = new SqlCommand("SELECT getdate() as dateverify", sqlConn);


try

{

sqlConn.Open();

}

catch (System.Exception ex)

{

Console.WriteLine(DateTime.Now.ToString() + ":" + "Exception
VerifyDBConnection SqlConnection open:" + ex.Message);

this.Cursor = Cursors.Default;

bResult = false;

return bResult;

}

finally

{

}

try

{

sqlDataReader =
sqlCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);


if(sqlDataReader.Read())

{

bResult = true;

}

sqlDataReader.Close();


}

catch (System.Exception ex)

{

Console.WriteLine(DateTime.Now.ToString() + ":" + "Exception
VerifyDBConnection DataReaderException");

this.Cursor = Cursors.Default;

}

finally

{


sqlConn.Close();

}

this.Cursor = Cursors.Default;

return bResult;

}

 

[W.G. Ryan MVP]

Sam - this is a known behavior due to connection pooling. If the app is
connected to the db and the db goes down, then it goes back up the app will
need to cycle through everything in the pool (which is a fancy way of saying
you'll get a bunch of exceptions even though the db is available). In the
2.0 Framework, this will only happen once with the SqlClient provider and w/
the Oracle provider, it won't happen at all.