Retrieve data from SQL database and use dynamically created Labels to show Ping results

Question

I need to display two fields from my SQL database in dynamically created labels. So for every entry into the database it needs to display it on the main window in labels underneath each other. But it is not showing the info in the label.

So this is what needs to happen.

the users saves the info into the SQL database (Host name, IP address and Device)
on the main screen (windows) it then needs to be displayed like this:

Server 1 192.168.x.x "Ping Result"

This is my code I have at them moment (this does not include the ping results label yet)

private void AddDynamicLabels()
{
    string ConString = @"Data Source=PC\SQL;Initial Catalog=PingMonitorDB;Integrated Security=True";
    SqlConnection con = new SqlConnection(ConString);
    string CmdString = "SELECT host, ip FROM addhosts";
    SqlCommand cmd = new SqlCommand(CmdString, con);
    con.Open();
    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        Label lbl = new Label();
        lbl.Visible = true;
        lbl.Text = reader["host" + "ip" + "   "].ToString();
        lbl.BackColor = System.Drawing.Color.Orange;
        this.Controls.Add(lbl);
    }
    con.Close();
}

So for every entry added it should in theory look like this:

 Server 1  192.168.x.x   "ping reply"
 Server 2  192.168.x.x   "ping reply"
 Server 3  192.168.x.x   "ping reply"
 ....
 etc

EDIT

private async void frmMainWindow_Load(object sender, EventArgs e)
{
    SqlConnection cnn = new SqlConnection(@"Data Source=PC\SQL;Initial Catalog=PingMonitorDB;Integrated Security=True");
    cnn.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = cnn;
    SqlDataAdapter dp = new SqlDataAdapter();

    DataTable dtTable = new DataTable();
    cmd.CommandText = "SELECT host, ip FROM addhosts";
    cmd.CommandType = CommandType.Text;
    dp.SelectCommand = cmd;
    dp.Fill(dtTable);
    cnn.Close();
    for (int i = 0; i < dtTable.Rows.Count; i++)
    {
        ipAddress.Add("ip");
        Label lbl = new Label();
        lbl.Visible = true;
        lbl.AutoSize = true;
        lbl.Font = new Font("Arial", 12, FontStyle.Bold);
        lbl.Text = dtTable.Rows[i]["host"].ToString() + "  " + dtTable.Rows[i]["ip"].ToString();
        lbl.BackColor = System.Drawing.Color.Orange;
        this.Controls.Add(lbl);
        flowLayoutPanel1.Controls.Add(lbl);

        var controls = new Control[] { /* a list of existing Controls */ };
        // The Addresses count must match the Controls'
        var addresses = ["0.0.0.0"]; => Not sure what to do here though?
        var massPing = new MassPing();
        await massPing.PingAll(addresses, controls, 2000);
    }
}

The the MassPing Class is shown in a previous question:
How to change the image of a PictureBox when the Ping Round Trip Time is more that 50ms?

Jimi · Accepted Answer · 2021-08-23T08:08:55.300

I've modified the MassPing Class and generalized the procedure, creating a Progress<T> delegate in a different way, outside the MassPing class.
A method, SetPingProgressDelegate(), creates the delegate right before the MassPing.PinAll() method is called.

In your code, you're calling the PingAll() method each time a new record is read and you add a new Control the same way.
This of course doesn't work: you need to create a List of Ip Addresses and a List of Controls beforehand, then add the created Controls to a FlowLayoutPanel and pass the List of addresses to MassPing and await its results.

The database access procedure is modified to dispose of all disposable objects created.
Also awaiting the async methods SqlClient provides.

Moved the calling code to the Shown event handler (instead of Load).

private async void frmMainWindow_Shown(object sender, EventArgs e)
{
    var ipAddresses = new List<string>();
    var ipControls = new List<Control>();
    var font = new Font("Segoe UI", 20, FontStyle.Regular, GraphicsUnit.Pixel);

    string sql = "SELECT [host], [ip] FROM addhosts";
    string connString = @"Data Source=PC\SQL;Initial Catalog=PingMonitorDB;Integrated Security=True";

    using (var conn = new SqlConnection(connString))
    using (var cmd = new SqlCommand(sql, conn)) {
        await conn.OpenAsync();
        using (var reader = await cmd.ExecuteReaderAsync()) {
            int ipCount = 0;
            while (await reader.ReadAsync()) {
                var lbl = new Label() {
                    AutoSize = false,
                    BackColor = Color.LightGray,
                    ForeColor = Color.Black,
                    Font = font,
                    Name = $"lblAddress{ipCount}",
                    Size = new Size(flowLayoutPanel1.ClientSize.Width, font.Height + 4),
                    Text = $"{reader["host"]} {reader["ip"]} "
                };
                ipAddresses.Add(reader["ip"].ToString());
                ipControls.Add(lbl);
            }
        }
    }

    // If the Reader returned some results, add the Control to a FlowLayoutPanel
    // and pass the list of addresses to MassPing, plus the Progress<T> delegate
    if (ipAddresses.Count > 0) {
        flowLayoutPanel1.Controls.AddRange(ipControls.ToArray());
        var massPing = new MassPing();
        var progress = SetPingProgressDelegate(ipControls);
        await massPing.PingAll(ipAddresses.ToArray(), progress, 2500);
    }
}

Specialized IProgress<T> delegate:
Add this method in the same Form. Or change the access modifier to public and add it to an internal class that the Form can reach.
Change the Colors to fit the UI you have designed.

private IProgress<(int, object)> SetPingProgressDelegate(List<Control> uiControls)
{
    var controls = uiControls;
    var obj = new object();

    // Create a Progress<T> delegate to receive status updates  
    // Each Task reports back here. This code is executed in the UI Thread
    var progress = new Progress<(int seq, object reply)>(report => {
        lock (obj) { // Not strictly necessary. Test without it
            var status = IPStatus.Unknown;
            var color = Color.LightGray;
            if (report.reply is PingReply pr) {
                status = pr.Status;
                color = status is IPStatus.Success
                    ? pr.RoundtripTime > 10 ? Color.Yellow : Color.LightGreen
                    : Color.OrangeRed;
            }
            else if (report.reply is SocketError socErr) {
                if (socErr == SocketError.HostNotFound) {
                    status = IPStatus.DestinationHostUnreachable;
                    color = Color.Red;
                }
                else {
                    // Something else went wrong. Handle if you care.
                    status = IPStatus.Unknown;
                }
            }
            controls[report.seq].BackColor = color;
            controls[report.seq].Text += status.ToString();
        }
    });
    return progress;
}

Modified MassPing class:
Receives a list of Ip Addresses (as strings) and initializes a corresponding number of PingAsync() Tasks. Each PingAsync() Task reports back its results, including the sequence indicator that was generated when the Tasks are created. See the notes in:
How to change the image of a PictureBox when the Ping Round Trip Time is more that 50ms?

using System.Collections.Generic;
using System.Net.NetworkInformation;
using System.Net.Sockets;
using System.Threading.Tasks;

public class MassPing
{
    public async Task PingAll(string[] addresses, IProgress<(int seq, object reply)> progress,  uint timeout = 2000)
    {
        var tasks = new List<Task>();

        // Add all tasks
        for (int seq = 0; seq < addresses.Length; seq++) {
            tasks.Add(PingAsync(addresses[seq], (int)timeout, seq, progress));
        }
        await Task.WhenAll(tasks);
    }

    private async Task PingAsync(string ipAddress, int timeOut, int sequence, IProgress<(int seq, object reply)> progress)
    {
        var buffer = new byte[32];
        var ping = new Ping();

        try {
            var options = new PingOptions(64, true);
            PingReply reply = await ping.SendPingAsync(ipAddress, timeOut, buffer, options);
            progress.Report((sequence, reply));
        }
        catch (PingException pex) {
            if (pex.InnerException is SocketException socEx) {
                progress.Report((sequence, socEx.SocketErrorCode));
            }
            else {
                // InnerException should be a SocketException. If not, something went bad
                // Handle specific cases if this kind of reporting is needed
                progress.Report((sequence, SocketError.SocketError));
            }
        }
        catch (InvalidOperationException) {
            // Overlapping sequence. Should never happen
            progress.Report((sequence, SocketError.AlreadyInProgress));
        }
        finally {
            ping.Dispose();
        }
    }
}

score 0 · Answer 2 · answered Aug 20 '21 at 09:10

use this code :

SqlConnection cnn = new SqlConnection("....");
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
SqlDataAdapter dp = new SqlDataAdapter();

cmd.CommandText = "SELECT host, ip FROM addhosts";
cmd.CommandType = CommandType.Text;
dp.SelectCommand = cmd;
dp.Fill(dtTable);
cnn.Close();
for (int i = 0; i < dtTable.Rows.Count; i++)
{
    Label lbl = new Label();
    lbl.Visible = true;
    lbl.Text = dtTable.Rows[i]["host"].ToString() + dtTable.Rows[i]["ip"].ToString();
    lbl.BackColor = System.Drawing.Color.Orange;
    this.Controls.Add(lbl);
}

Retrieve data from SQL database and use dynamically created Labels to show Ping results

2 Answers2

Linked