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.C loseConnection); . 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.C loseConnection);
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;
}
