CODEWITHSUNDEEP
c#sqllistview
Fenwyr
Fenwyr

Reputation: 3

How to fill a c# windows form listview with datas from 2 tables?

I'm doing a "load" button to load up all datas from a SQL database, to put them in a listview.

if(lstEnsemble.Items.Count == 0)
{
    MySqlConnection connexion = OpenConnection();
    MySqlCommand reqRemplissageClient = new MySqlCommand("select * from client order by idClient ASC;", connexion);
    MySqlCommand reqRemplissagePanne = new MySqlCommand("select * from Panne order by idClient ASC;", connexion);

    MySqlDataReader resultatClient = reqRemplissageClient.ExecuteReader();
    MySqlDataReader resultatPanne = reqRemplissageClient.ExecuteReader();

    while (resultatClient.Read() && resultatPanne.Read())
    {
        ListViewItem item = new ListViewItem(resultatClient["nomCli"].ToString());
        item.SubItems.Add(resultatClient["prenomCli"].ToString());
        if (resultatClient["idClient"] == resultatPanne["idClient"])
        {
            item.SubItems.Add(resultatPanne["appareil"].ToString());
        }
        item.SubItems.Add(resultatClient["villeCli"] + " " + resultatClient["cpCli"] + " " + resultatClient["rueCli"]);
        item.SubItems.Add(resultatClient["telCli"].ToString());
        if (resultatClient["idClient"] == resultatPanne["idClient"])
        {
            item.SubItems.Add(resultatPanne["description"].ToString());
            item.SubItems.Add(resultatPanne["dateEntree"].ToString());
        }
        item.SubItems.Add(resultatClient["mailCli"].ToString());
        lstEnsemble.Items.Add(item);
    }

    CloseConnection(connexion);
}

This does not work, obviously, but I can't manage to find another way to do this. I tried to do both of requests separately, but again I'm missing the logic to this.

Upvotes: 0

Views: 105

Answers (2)

René Vogt
René Vogt

Reputation: 43876

In your question it's not fully clear what kind of data you are working with. It seems your presuming (or better hoping) that the idClient values match exactly in both tables.

I presume that you actually want to do a JOIN. If this is not true, I will delete this post again.

So you should do only one query using a JOIN:

if(lstEnsemble.Items.Count == 0)
{
    string sql = @"SELECT nomCli, prenomCli, appareil, villeCli, rueCli,
                   cpCli, telCli, description, dateEntree, mailCli
                   FROM client LEFT JOIN Panne ON (client.idClient = Panne.idClient)";

    MySqlConnection connexion = OpenConnection();
    MySqlCommand request = new MySqlCommand(sql, connexion);

    MySqlDataReader resultat = requestExecuteReader();
    while (resultat.Read())
    {
        ListViewItem item = new ListViewItem(resultat["nomCli"].ToString());
        item.SubItems.Add(resultat["prenomCli"].ToString());
        item.SubItems.Add(resultat["appareil"].ToString());
        item.SubItems.Add(resultat["villeCli"] + " " + resultat["cpCli"] + " " + resultat["rueCli"]);
        item.SubItems.Add(resultat["telCli"].ToString());
        item.SubItems.Add(resultat["description"].ToString());
        item.SubItems.Add(resultat["dateEntree"].ToString());
        item.SubItems.Add(resultat["mailCli"].ToString());
        lstEnsemble.Items.Add(item);
    }

    CloseConnection(connexion);
}

But you should be aware that for rows in client with no matching rows in Panne there will be null values in the results. So you need to check for them first using resultat.IsDbNull() on the column index (position in sql query).
Alternativly you may use an INNER JOIN instead of a LEFT JOIN to get only rows that exist in both tables.

Upvotes: 1

Adviese
Adviese

Reputation: 1

You should change a few things, then it becomes pretty easy.

  1. Make a single SQL Query. Just JOIN your tables together and SELECT what you need.
  2. Separate the Database access from filling your listview. Put your result in an array or datatable.
  3. With your data available you can easily bind it or iterate through it to generate ListViewItems.

Upvotes: 0

Related Questions