Checked If there is Connection to SQL Server or not in WPF

Question

My WPF application connected to SQL Server database via a connection string in App.config and it works over local network.

I have a login screen "4 PassCode digit Login", after enter the fourth digit I checked "As usual" if true or not.

My question here how can I check if client machine has connection to the server or not? If not I want to show message.

I don't know from where should I start :(

Hope this clear. Thanks

score 3 · Accepted Answer · edited May 23 '17 at 12:23

3

You can simply check perform a check whether the client can open a connection.

Code lifted from this answer.

/// <summary>
/// Test that the server is connected
/// </summary>
/// <param name="connectionString">The connection string</param>
/// <returns>true if the connection is opened</returns>
private static bool IsServerConnected(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        try
        {
            connection.Open();
            return true;
        }
        catch (SqlException)
        {
            return false;
        }
        finally
        {
            // not really necessary
            connection.Close();
        }
    }
}

edited May 23 '17 at 12:23

Community

1
1

answered Mar 30 '14 at 21:31

Oren Hizkiya

4,420
2
23
33

Sorry for resurrecting this, but how do you initialize this method? `IsServerConnected();` doesn't work. – Adephx Oct 26 '18 at 06:04
@Adephx You'll have to be a bit more specific than "doesn't work." Do you have a class you are copying this method into? – Oren Hizkiya Oct 26 '18 at 17:29

score 0 · Answer 2 · answered Mar 19 '16 at 08:47

try this

private void ConnectToServer()
    {
        string sServerName = _fileIO.GetServerInfo("ServerName");
        string sSqlInstance = _fileIO.GetServerInfo("SqlInstance");
        string sDataBase = _fileIO.GetServerInfo("DataBase");
        string sUserID = _fileIO.GetServerInfo("UserID");
        string sPassWord = _fileIO.GetServerInfo("PassWord");
         //"Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword";
        string sConnectionString = string.Format("Server={0};Database={2};User Id={3};Password={4}",
                                                sServerName,sSqlInstance,sDataBase,sUserID,sPassWord);


        try
        {
            _cnn = new SqlConnection(sConnectionString); 
            _cnn.Open();
            if (_cnn.State.ToString() != "Open")
            {
                MessageBox.Show("Application could not connect to server ","Connection Failed ",MessageBoxButton.OK,MessageBoxImage.Error);
            }
            _cnn.Close();
        }
        catch (Exception _ex)
        {
            MessageBox.Show(_ex.ToString());
        }

    }

score 0 · Answer 3 · answered Oct 26 '18 at 07:05

App.config

<add name="databaseName" connectionString="Data Source=localhost; Initial Catalog=ABC_DB; Persist Security Info=True; User ID=sa;Password=1234"/>

Window.xaml.cs

public MainWindow()
{
    InitializeComponent();
    OpenConnection();
}

public SqlConnection con = new SqlConnection
{
    ConnectionString = ConfigurationManager.ConnectionStrings["databaseName"].ConnectionString
};

public void OpenConnection()
{
    try
    {
        con.Open();
    }
    catch (SqlException ex)
    {
        MessageBox.Show(ex.Message, "Warning", MessageBoxButton.OK, MessageBoxImage.Error);
        /// If you want the program to exit completely
        /// Environment.Exit(0);
    }
}

Additional, you can add something like this:

public void Whatever()
{
    if (con.State == ConnectionState.Open)
        {
            /// Your code
        }
    else
    {
        MessageBox.Show("Could not connect to server", "Warning", MessageBoxButton.OK, MessageBoxImage.Error);
        OpenConnection();
    }
}

Checked If there is Connection to SQL Server or not in WPF

3 Answers3