working with database Best practise?

Question

I have a C# database layer that with static read method that is called every second in background timer.

currently I create SqlCommand, SqlConnection once as a class memeber.

In every method call I execute the command to get the results,I am doing so to avoid creation of connection and command every second, but I am afraid from exception occurs in this method that will break the connection or put the object in the invalid state.

This is my current implementation (Timer Handler)

    static void GetBarTime(object state)
    {
        lock (_staticConnection)
        {
            SqlDataReader dataReader = null;
            try
            {
                dataReader = _getMaxTimeCommand.ExecuteReader();
                dataReader.Read();
                _currentTick = dataReader.GetInt32(0);
            }
            catch (Exception ex)
            {
                //Log the error
            }
            finally
            {
                dataReader.Dispose();
            }
        }
    }

What is the best practise?

MORE DETAILS:

I am doing this in a timer as there is another prorcess update my table every second, and there is another exposed method used by set of clients and called every second to get the latest value.

So instead of executing select statement every second for each client, I am doing it in a timer and update global variable that is used by the clients.

Answer 1

It can be pretty difficult to find out if an execption means that the connection is a dead duck. To be on the safe side, you could close and reopen the SqlConnection and SqlCommand whenever you encounter an exception, just in case. That doesn't cause any overhead when everything works alright.

Answer 2

SqlConnection has pooling built in; you would see almost no difference if you used:

using(SqlConnection conn = new SqlConnection(connectionString)) {
    conn.Open();
    // your code
}

each time. And that can react automatically to dead (underlying) connections.

Currently you have a bug, btw; if the command fails, the reader will still be null... either check for null before calling Dispose():

if(dataReader !=null) {dataReader.Dispose();}

or just use using:

 try
 {
     using(SqlDataReader dataReader = _getMaxTimeCommand.ExecuteReader())
     {
         dataReader.Read();
         _currentTick = dataReader.GetInt32(0);
     }
 }
 catch (Exception ex)
 {
    //Log the error
 }