Show state of MySqlConnection in Windows Forms

Question

I created a Windows Forms application with C# that checks if a MySQL connection is open or closed. For each state, the text of a label should change.

My problem is: if the application keeps running and the connection is closed or terminated, the text of the label does not change.

I tried using the form_load events, but it didn't work.

This is my code:

string connectionString = "Data Source=localhost;Initial Catalog=table;User ID=root;Password=";

public Form1() 
{          
    InitializeComponent();           
    MySqlConnection cnn = new MySqlConnection(connectionString);

    try
    {
        cnn.Open();
        toolStripStatusLabel1.BackColor = System.Drawing.Color.Green;
        toolStripStatusLabel1.ForeColor = System.Drawing.Color.White;
        toolStripStatusLabel1.Text = "Connection Success";
    }
    catch (Exception ex)
    {
        toolStripStatusLabel1.BackColor = System.Drawing.Color.Red;
        toolStripStatusLabel1.ForeColor = System.Drawing.Color.White;
        toolStripStatusLabel1.Text = "Connection failed!";
    }
    finally
    {
        cnn.Close();
    }
}

Subscribe to the StateChange event https://dev.mysql.com/doc/dev/connector-net/8.0/html/T_MySql_Data_MySqlClient_MySqlConnection.htm and in your handler update your labels depending on the value of the StateChange parameter — auburg, Mar 24 '21 at 09:15
Your code is in the Form's `constructor` method, but a constructor always runs *only once* (when the form is created). You could look at using a `Timer` component, see e.g. https://www.youtube.com/watch?v=98c200lL-OY. I would suggest not to make it run every second, or your database might get slow because of that. — Peter B, Mar 24 '21 at 09:17
@PeterB Thank you very much i did it with timer and it worked perfectly — MuhammadJavadi, Mar 25 '21 at 07:56

mamen · Answer 1 · 2021-03-24T09:48:47.943

0

You could listen for the StateChange-Event (documentation) to update the label for each possible state of the connection.

In your constructor, you add this:

cnn.StateChange += OnStateChange;

So it should look like this:

public Form1() {          
    InitializeComponent();           
    MySqlConnection cnn = new MySqlConnection(connectionString);

    cnn.StateChange += OnStateChange;

    try
    {
        cnn.Open();
    }
    catch (Exception ex)
    {
        toolStripStatusLabel1.BackColor = System.Drawing.Color.Red;
        toolStripStatusLabel1.ForeColor = System.Drawing.Color.White;
        toolStripStatusLabel1.Text = "Connection failed!";
    }
    finally
    {
        cnn.Close();
        cnn.StateChange -= OnStateChange;
    }
 }

Then you add the eventHandler-method:

protected virtual void OnStateChange(object connection, StateChangeEventArgs stateChange) {
    ...
}

The StateChangeEventArgs-object contains two properties (CurrentState and OriginalState) of type ConnectionState (documentation) which is an enum.

In your OnStateChange-method, you can check, what the new state is and update the label accordingly.

protected virtual void OnStateChange(object connection, StateChangeEventArgs stateChange) {
    if(stateChange == null || stateChange.CurrentState == null) {
        return;
    }

    switch(stateChange.CurrentState) {
        case ConnectionState.Closed:
            [UPDATE LABEL HERE]
        break;
        .
        .
        .

    }
}

edited Mar 24 '21 at 09:48

answered Mar 24 '21 at 09:19

mamen

1,202
6
26

For doing this solution i did add "using system.data" and i did follow your instructure. but still nothings happen and labels aren't changes. should i add some events in my form? – MuhammadJavadi Mar 24 '21 at 09:41
Does the method `OnStateChange` get called when the connection-state changes? And did you add the `cnn.StateChange += OnStateChange;` to your constructor? – mamen Mar 24 '21 at 09:47
I got zero errors, and it's still not working real time, it's exactly like my C# From like if you want to see the new result, you should re-run the program – MuhammadJavadi Mar 24 '21 at 09:59
I'm pretty sure that `OnStateChange` does *not* give an accurate reflection of the state of your database **server** at all times. What it can reveal is actually very limited. See e.g. this answer to the question [When is DbConnection.StateChange called?](https://stackoverflow.com/a/37535832/1220550) – Peter B Mar 24 '21 at 10:12
@PeterB The question was not to get the current state of the **server** but to get the current state of the **connection** to the server. For this purpose, the `OnStateChange` event should be sufficient. – mamen Mar 24 '21 at 12:45

MuhammadJavadi · Accepted Answer · 2021-03-25T08:01:32.920

I did it in other way with "timer" component and it worked perfectly

STEP1: - Create method for check state of MYSQL Connection

string connectionString = "Data Source=localhost;Initial Catalog=portal;User ID=root;Password=";
    public void checkmysqlconnection()
    {
        //Real Time MYSQL Connection Stuation
        MySqlConnection cnn = new MySqlConnection(connectionString);
        try
        {
            cnn.Open();
            toolStripStatusLabel1.BackColor = System.Drawing.Color.Green;
            toolStripStatusLabel1.ForeColor = System.Drawing.Color.White;
            toolStripStatusLabel1.Text = "Connection success";
        }
        catch (Exception ex)
        {
            toolStripStatusLabel1.BackColor = System.Drawing.Color.Red;
            toolStripStatusLabel1.ForeColor = System.Drawing.Color.White;
            toolStripStatusLabel1.Text = "Connection error!";
        }
        finally
        {
            cnn.Close();

        }
    }

STEP2: - Add timer to form and calls MYSQL Connection method on it

    private Timer timer1;
    public void InitTimer()
    {
        timer1 = new Timer();
        timer1.Tick += new EventHandler(timer1_Tick);
        timer1.Interval = 10000; // in miliseconds
        timer1.Start();
    }
    private void timer1_Tick(object sender, EventArgs e)
    {
        checkmysqlconnection();
    }

STEP3: - Calls timer in Form1()

 InitTimer();

And it's work properly and every 10second or less check database connection and updates label status

Show state of MySqlConnection in Windows Forms

2 Answers2