如何检查数据库的可用性

I have the following code to test DB connection, it runs periodically to test for DB availability:

我有以下代码测试DB连接，它定期运行以测试DB可用性:

private bool CheckDbConn()
{
   SqlConnection conn = null;
   bool result = true;

   try
   {
       conn = DBConnection.getNewCon();
       ConnectionState conState = conn.State;

       if (conState == ConnectionState.Closed || conState == ConnectionState.Broken)
       {
          logger.Warn(LogTopicEnum.Agent, "Connection failed in DB connection test on CheckDBConnection");
          return false;
       }             
   }
   catch (Exception ex)
   {
      logger.Warn(LogTopicEnum.Agent, "Error in DB connection test on CheckDBConnection", ex);
      return false; // any error is considered as db connection error for now
   }
   finally
   {
      try
      {
         if (conn != null)
         {
            conn.Close();
         }
      }
      catch (Exception ex)
      {
         logger.Warn(LogTopicEnum.Agent, "Error closing connection on CheckDBConnection", ex);
         result = false;
      }
   }
   return result;
}

And:

和:

static public SqlConnection getNewCon()
{
    SqlConnection newCon = new SqlConnection();
    newCon.ConnectionString = DBConnection.ConnectionString; // m_con.ConnectionString;
    newCon.Open();
    return newCon;
}

My question is: will this work as expected?

我的问题是:这能像预期的那样工作吗?

Specifically, I'm concerned aobut the test of the ConnectionState. Is it possible that the state will be: connecting (since Open() is synchronous)?

具体地说，我关心的是连接状态的测试。是否可能状态是:连接(因为Open()是同步的)?

What should I do in that case?

在那种情况下我该怎么办?

Thanks in advance, Omer

谢谢你提前,俄梅珥

6 个解决方案

#1

You can try like this.

你可以这样试试。

    public bool IsServerConnected()
    {
        using (var l_oConnection = new SqlConnection(DBConnection.ConnectionString))
        {
            try
            {
                l_oConnection.Open();
                return true;
            }
            catch (SqlException)
            {
                return false;
            }
        }
    }

#2

SqlConnection will throw a SqlException when it cannot connect to the server.

当SqlConnection不能连接到服务器时，它将抛出一个SqlException。

public static class SqlExtensions
{
    public static bool IsAvailable(this SqlConnection connection)
    {
        try
        {
            connection.Open();
            connection.Close();
        }
        catch(SqlException)
        {
            return false;
        }

        return true;
    }
}

Usage:

用法:

using(SqlConnection connection = GetConnection())
{
    if(connection.IsAvailable())
    {
        // Success
    }
}

#3

Your code seems fine, but you really need to use the IDisposable pattern, and some naming convention too:

您的代码看起来很好，但是您确实需要使用IDisposable模式，以及一些命名约定:

private bool CheckDbConnection(string connectionString)
{
    try
    {
        using(var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            return true;
        }
    }
    catch (Exception ex)
    {
        logger.Warn(LogTopicEnum.Agent, "Error in DB connection test on CheckDBConnection", ex);
        return false; // any error is considered as db connection error for now
    }
}

And connection.Close() is not supposed to throw. Just use the using block and your are fine.

close()不应该抛出。只要使用“使用”块就可以了。

No need to test the Close state, since you have just opened it.
More about the Broken state:

不需要测试关闭状态，因为您刚刚打开它。更多关于破碎状态:

Broken The connection to the data source is broken. This can occur only after the connection has been opened. A connection in this state may be closed and then re-opened. (This value is reserved for future versions of the product.)

断开与数据源的连接。只有在打开连接之后才会发生这种情况。在这种状态下的连接可以被关闭，然后重新打开。(此值预留给未来版本的产品。)

So really, no need to test that.

所以，不需要测试。

The Connecting state could be catch if you are in a multithread context and your instance of connection is shared. But it is not your case here.

如果您在多线程上下文中，并且您的连接实例是共享的，那么可以捕获连接状态。但这不是你的情况。

#4

I cannot comment so...

我不能评论所以…

... also avoid catching general Exceptions "catch(Exception ex)" and try to catch specific exceptions like the examples above "catch(SqlException ex)"

…还要避免捕获一般异常“catch(Exception ex)”，并尝试捕获特定异常，如上面的示例“catch(SqlException ex)”

#5

actually, in visual studio, connection class has sonnectionstate property.

实际上，在visual studio中，connection类具有sonnectionstate属性。

when connection state changes, connections statechange event is been trigerred.

当连接状态发生改变时，连接状态改变事件将被重新处理。

you might want to check this article.

您可能想要查看这篇文章。

https://msdn.microsoft.com/en-us/library/aa326268(v=vs.71).aspx

https://msdn.microsoft.com/en-us/library/aa326268(v = vs.71). aspx

#6

I was using @Ramesh Durai's solution but found that on my setup at least (the app calling/testing periodically after the app had started; using .Net 3.5 with Sql Server 2012 database) that the first call to IsConnected() after taking the database offline was returning true. However, it was throwing the expected exception on the ExecuteScalar() line below:

我使用的是@Ramesh Durai的解决方案，但我发现至少在我的设置上(应用启动后会定期调用/测试;使用。net 3.5和Sql Server 2012数据库)，将数据库脱机后对IsConnected()的第一个调用返回为true。但是，它对下面的ExecuteScalar()行抛出了预期的异常:

public bool IsConnected() {
    using (var conn = new SqlConnection(DBConnection.ConnectionString)) {
        using (var cmd = New SqlCommand("SELECT 1", conn)) {
            try {
                conn.Open();
                cmd.ExecuteScalar();
                return true;
            } catch (SqlException) {
                return false;
            }
        }
    }
}

#1