C# 如何检查数据库可用性

How to check for database availability

提问by omer schleifer

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

我有以下代码来测试数据库连接，它会定期运行以测试数据库可用性：

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)?

具体来说，我担心ConnectionState. 状态是否可能是：连接（因为Open()是同步的）？

What should I do in that case?

在这种情况下我该怎么办？

Thanks in advance, Omer

提前致谢，奥默

采纳答案by Ramesh Durai

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;
            }
        }
    }

回答by Dustin Kingen

SqlConnectionwill throw a SqlExceptionwhen it cannot connect to the server.

SqlConnectionSqlException当它无法连接到服务器时会抛出一个。

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
    }
}

回答by Cyril Gandon

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 usingblock and your are fine.

并且connection.Close()不应该扔。只需使用该using块，您就可以了。

No need to test the Closestate, since you have just opened it.
More about the Brokenstate:

无需测试Close状态，因为您刚刚打开它。
更多关于Broken状态：

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 Connectingstate could be catch if you are in a multithread context and your instance of connection is shared. But it is not your case here.

Connecting如果您处于多线程上下文中并且您的连接实例是共享的，则状态可能会被捕获。但这不是你的情况。

回答by nadir

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

实际上，在Visual Studio 中，连接类具有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

回答by monty

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;
            }
        }
    }
}

回答by Miguel

This code is for Mysql.

此代码用于 Mysql。

public class Program
{
string connection = "SERVER=localhost; user id=root; password=; database=dbname";
private void Form1_Load(object sender, System.EventArgs e)
{
checkifconnected();
}

private void checkifconnected()
{
MySqlConnection connect = new MySqlConnection(connection);
try{
connect.Open();
MessageBox.Show("Database connected");
}
catch
{
MessageBox.Show("you are not connected to database");
}
}

public static void Main()
{

}
}