-2

i need ur help .I need to develop a function in Application WPF , that will be fired each 30 seconds, to ping to server and check if it's not connect to sql server, we will display Error message .Is there a way to simply "ping" from C# SQL Server?

Thanks in advance!

Below is my connection code if I'm missing anything .

/// Test that the server is connected
private  bool IsServerConnected()
{
    using (SqlConnection connection = ConnexionSGBD())
    {
        try
        {
            connection.Open();
            Console.WriteLine("SQL Connection successful.");
            return true;
        }
        catch (SqlException)
        {
            Console.WriteLine("SQL Connection failled.");
            msgException();
            return false;

        }
    }
}

//La fonction qui permet d'exécuter une requête SQL et stocker le résultat dans un datatable

public System.Data.DataTable GetTable()
{

        SqlConnection connection = ConnexionSGBD();
        string sqlrequete = "SELECT *  FROM BD;";
        System.Data.DataTable table = new DataTable();
    if (IsServerConnected()==true)
    {
        connection.Open();
        using (SqlCommand cmd = new SqlCommand(sqlrequete, connection))
        {
            table.Load(cmd.ExecuteReader());                                          
        }
        return table;

    }
    else
    {

        msgException();
        return null;
    }
}
Camilo Terevinto
  • 31,141
  • 6
  • 88
  • 120
S.Snoy
  • 39
  • 8
  • What doesn't work with your current code? – Camilo Terevinto Jan 02 '18 at 15:46
  • Getting a working connection is a good enough response – Grantly Jan 02 '18 at 15:47
  • 1
    https://stackoverflow.com/questions/1153537/ms-sql-ping-external-server – Steve Jan 02 '18 at 15:47
  • 3
    First your code works. Second why would you want to do that? You could simply catch the exception when you are trying to execute query and let the user know that the connection is lost. – Steve Jan 02 '18 at 15:48
  • 2
    Even if SQL Server responded positively to a "ping" at some recent point in the past (e.g. 10s of milliseconds ago), that does *not* mean that any future use of the same server will complete without any network errors. No amount of pre-checking can protect you. You need to write code that can cope with errors occurring *when the real code is executing* anyway. So why write even more code that tells you nothing useful? – Damien_The_Unbeliever Jan 02 '18 at 15:54
  • @camilo when I'm not connected to the Data base ,and I execute the application , msgException() display after long time , – S.Snoy Jan 02 '18 at 15:55
  • 1
    @S.Snoy Yes, after 30 seconds, the default connection timeout. – Camilo Terevinto Jan 02 '18 at 15:57
  • @camilo I need that ,if I'm not connected to Data base , to see the error message .So how can I do this ?? – S.Snoy Jan 02 '18 at 16:03
  • You should not be keeping a connection to Sql Server open. Each time you query, you should establish the connection, get your data, and then close the connection. You will not have a constant connect to the server. That doesn't even make much sense. – Chris Dunaway Jan 02 '18 at 16:57
  • Don't. Damien is correct, and so is chris. – Zohar Peled Jan 02 '18 at 22:29

1 Answers1

0

Just Check SqlConnection.State property, like in this other question is explained:

Check if SQL Connection is Open or Closed

Samuel A
  • 81
  • 9