Check Database Connectivity Using Stored Procedure
This may not be the best way to go about checking database connectivity from within your app, but it is a method that works for me. If you have a different way, please comment. The point of this is to make a dummy call out to the database and if the call succeeds, then we have access to the database. If the call fails or fails to connect, we then know something is wrong and it can trigger an alert.
It’s relatively simple and rather I am thinking about renaming the post to calling a stored procedure through C# but never the less let’s look.
protected void CheckDBHealthStatus()
{
try
{
using (SqlConnection conn = new SqlConnection(ConnectionString)) //Create new SqlConnection with ConnectionString to DB
{
using (SqlCommand sqlCommand = new SqlCommand())//Create a new SqlCommand to call stored proc
{
sqlCommand.Connection = conn; //Add the Connection
sqlCommand.CommandTimeout = 60; //Timeout After 1 mins
sqlCommand.CommandText = "dummyProc_HealthCheck"; //Name Of Stored Procedure
sqlCommand.CommandType = CommandType.StoredProcedure; //Type obviously Stored Procedure
//add the parameters
SqlParameter newParam = new SqlParameter("@ID", SqlDbType.VarChar, 10) { SqlValue = "Adam" }; //Create New Parameter
sqlCommand.Parameters.Add(newParam); //Add to the SqlCommand we created above.
conn.Open();//Open Our Connection to the DB
sqlCommand.ExecuteNonQuery();//Execute the query.
}
}
}
catch (Exception ex)
{
string errorMessage = "Critical - Error when connecting to Database." + ex.Message;
}
}
All the comments are inline. Obviously there is not really a need to have a parameter but I think it could come in useful for a person looking for how to add one. Overall, fairly simple and a quick and dirty way of reaching out to the database, making some dummy call, and reporting if it succeeded or if it failed. You can then take this farther and capture the error message and exception and have a service report to you when this goes wrong.
Adam,
I'd consider a one column one row table in the database and running a select query against that. Otherwise, You can always wrap that around sp_executesql. There is no perfect solution to take into account custom roles, cross database queries or other weird things though.
I would have to agree. This was to help in a situation where our DBA's are strict and won't allow us to add tables that won't serve in their eyes a huge purpose. However I do agree with your thoughts, thank you for the input!