WPF中实现每30秒检测SQL Server连接状态的方法咨询

Hey there! Let's walk through this together. Your approach to checking SQL Server connectivity is already on the right track—opening a connection is actually the most reliable way to verify if the server is reachable, since SQL Server doesn't have a dedicated "ping" API. Let's optimize your code and add the 30-second automatic check for your WPF app.

1. Refine the Connectivity Check Method #

Your existing IsServerConnected logic is solid, but we can tweak it for better reliability and UI compatibility:

• Add a shorter connection timeout to avoid hanging the UI
• Handle non-SQL exceptions (like network issues)
• Ensure UI updates happen on the correct thread

private bool IsServerConnected()
{
    // Replace with your actual connection string (or pull from ConnexionSGBD())
    string connectionString = "Data Source=YOUR_SERVER;Initial Catalog=YOUR_DB;Integrated Security=True;";
    
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        try
        {
            // Limit wait time to 5 seconds to prevent UI freezes
            connection.ConnectionTimeout = 5;
            connection.Open();
            Console.WriteLine("SQL Connection successful.");
            return true;
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"SQL Connection failed: {ex.Message}");
            ShowConnectionError(); // Your error message method, adjusted for WPF
            return false;
        }
        catch (Exception ex)
        {
            // Catch network or other unexpected errors
            Console.WriteLine($"Connection error: {ex.Message}");
            ShowConnectionError();
            return false;
        }
    }
}

2. Add 30-Second Automatic Check in WPF #

For WPF, use DispatcherTimer to run the check on the UI thread—this makes it safe to update error messages or UI controls:

private DispatcherTimer _connectionCheckTimer;

public YourMainWindow()
{
    InitializeComponent();
    SetupConnectionTimer();
}

private void SetupConnectionTimer()
{
    _connectionCheckTimer = new DispatcherTimer();
    _connectionCheckTimer.Interval = TimeSpan.FromSeconds(30);
    _connectionCheckTimer.Tick += OnConnectionCheckTick;
    
    // Run a check immediately when the window loads
    IsServerConnected();
    _connectionCheckTimer.Start();
}

private void OnConnectionCheckTick(object sender, EventArgs e)
{
    // Trigger the connectivity check every 30 seconds
    IsServerConnected();
}

// WPF-safe error message method (ensures UI updates run on the correct thread)
private void ShowConnectionError()
{
    if (!Dispatcher.CheckAccess())
    {
        // Switch to UI thread if we're on a background thread
        Dispatcher.Invoke(ShowConnectionError);
        return;
    }
    
    MessageBox.Show("Could not connect to SQL Server. Please check your network or database settings.", 
                   "Connection Error", 
                   MessageBoxButton.OK, 
                   MessageBoxImage.Error);
}

3. Optimize Your GetTable Method #

Your current code calls IsServerConnected then re-opens the connection—we can simplify this to avoid redundant checks:

public DataTable GetTable()
{
    string connectionString = "Data Source=YOUR_SERVER;Initial Catalog=YOUR_DB;Integrated Security=True;";
    string sqlQuery = "SELECT * FROM BD;";
    DataTable table = new DataTable();

    try
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.ConnectionTimeout = 5;
            connection.Open();
            
            using (SqlCommand cmd = new SqlCommand(sqlQuery, connection))
            {
                table.Load(cmd.ExecuteReader());
            }
        }
        return table;
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Failed to load table: {ex.Message}");
        ShowConnectionError();
        return null;
    }
}

Key Tips to Remember #

• Connection Timeout: Keep it short (5 seconds max) to prevent UI freezes during checks
• UI Thread Safety: Always use Dispatcher.Invoke when updating UI elements from background operations
• Resource Management: Use using blocks for SqlConnection and SqlCommand to avoid resource leaks

内容的提问来源于stack exchange，提问作者S.Snoy