Click here to Skip to main content
14,875,113 members
Please Sign up or sign in to vote.
3.11/5 (3 votes)
I have a listview (Listview 1) which I filled with data. Now I want to charge a second listview (listview 2) on the first listview. This should indicate Collem [1] listview1 and count the number of occurrences of the name. See Example listview 2.

Listview 1
ID *  NAAM  *  DATE
1 .     MO            01-01-2016
1 .     DE             01-01-2016
1 .     MO            02-01-2016
1 .     MO            02-01-2016
1 .     DE             02-01-2016
1 .     MO            03-01-2016
1 .     MO            02-01-2016


Listview 2
NAAM  *  Total count
MO    (TOTAL 5)         
DE     (TOTAL 3)


What I have tried:

I found this; but this script makes no distinction in names..

C#
var query = listView.Items
                    .Cast<listviewitem>()
                    .Where(item => item.SubItems[3].Text == "asdf");
foreach (var item in query)
{
    ...
}
Posted
Updated 9-Aug-16 9:16am
v2
Comments
Karthik_Mahalingam 9-Aug-16 9:07am
   
how you are binding the data ?
post the binding code.
MaikelO1 9-Aug-16 9:11am
   
private void Registratiefilter()
{
ListBoxUren.TabIndex = 0;
ListBoxUren.Items.Clear();
ListBoxUren.View = View.Details;

ListBoxUren.MultiSelect = false;
ListBoxUren.FullRowSelect = true;

using (var connection = new SqlConnection(connectionstring))
{

var query = "SELECT ID, Start, Stop, Pauze, SoortOmschrijving, Naam, IDProjecten, Organisatie, AardOmschrijving, ExtraOmschrijving, (Projectnummer + ' - ' + Organisatie + ' - ' + Referentie) AS REFProject, (MaterieelNR + ' - ' + Materieelstuk) AS REFMaterieel FROM UrenREG LEFT OUTER JOIN Medewerkers ON UrenReg.FK_IDMedewerker = Medewerkers.IDMedewerker LEFT OUTER JOIN UrenRegSoortActiviteit ON UrenReg.FK_SoortActiviteitID = UrenRegSoortActiviteit.SoortID LEFT OUTER JOIN Projecten ON UrenReg.FK_ProjectID = Projecten.IDProjecten LEFT OUTER JOIN Klanten ON Projecten.FK_IDKlant = Klanten.IDKlanten LEFT OUTER JOIN UrenRegAardVanActiviteit ON UrenReg.Fk_AardVanActiviteitID = UrenRegAardVanActiviteit.AardVanActiviteitID LEFT OUTER JOIN Materieel ON UrenReg.FK_materiaalID = Materieel.IDMaterieel LEFT OUTER JOIN UrenRegExtra ON UrenReg.FK_ExtraID = UrenRegExtra.ExtraID WHERE Start like @Date";

using (var adapter = new SqlDataAdapter(query, connection))
{

SqlParameter parm1 = adapter.SelectCommand.Parameters.AddWithValue("@Date", "%" + LB_DatePick.Text + "%");

try
{
DataTable ProjectLijst = new DataTable();
adapter.Fill(ProjectLijst);

for (int i = 0; i < ProjectLijst.Rows.Count; i++)
{
DataRow dr = ProjectLijst.Rows[i];
ListViewItem listitem = new ListViewItem(dr["Naam"].ToString());
listitem.SubItems.Add(dr["Start"].ToString());
listitem.SubItems.Add(dr["Stop"].ToString());
listitem.SubItems.Add(dr["Pauze"].ToString());
listitem.SubItems.Add(dr["AardOmschrijving"].ToString());
listitem.SubItems.Add(dr["SoortOmschrijving"].ToString());
listitem.SubItems.Add(dr["REFProject"].ToString() + dr["REFMaterieel"].ToString());
listitem.SubItems.Add(dr["ExtraOmschrijving"].ToString());

listitem.Tag = dr["ID"];
ListBoxUren.Items.Add(listitem);

ListBoxUren.AutoResizeColumns(ColumnHeaderAutoResizeStyle.ColumnContent);
ListBoxUren.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize);
}
}
catch (Exception)
{
Karthik_Mahalingam 9-Aug-16 9:14am
   
you need distinct ,count of NAAM column in the second listbox?
MaikelO1 9-Aug-16 9:18am
   
Yes
Patrice T 9-Aug-16 11:10am
   
Use Improve question to update your question.
So that everyone can pay attention to this information.

MaikelO1[^] wrote in comment:
C#
private void Registratiefilter()
        {
            ListBoxUren.TabIndex = 0;
            ListBoxUren.Items.Clear();
            ListBoxUren.View = View.Details;

            ListBoxUren.MultiSelect = false;
            ListBoxUren.FullRowSelect = true;

            using (var connection = new SqlConnection(connectionstring))
            {

                var query = "SELECT ID, Start, Stop, Pauze, SoortOmschrijving, Naam, IDProjecten, Organisatie, AardOmschrijving, ExtraOmschrijving, (Projectnummer + ' - ' + Organisatie +  ' - ' + Referentie) AS REFProject, (MaterieelNR + ' - ' + Materieelstuk) AS REFMaterieel  FROM UrenREG LEFT OUTER JOIN Medewerkers ON UrenReg.FK_IDMedewerker = Medewerkers.IDMedewerker LEFT OUTER JOIN UrenRegSoortActiviteit ON UrenReg.FK_SoortActiviteitID = UrenRegSoortActiviteit.SoortID LEFT OUTER JOIN Projecten ON UrenReg.FK_ProjectID = Projecten.IDProjecten LEFT OUTER JOIN Klanten ON Projecten.FK_IDKlant = Klanten.IDKlanten LEFT OUTER JOIN UrenRegAardVanActiviteit ON UrenReg.Fk_AardVanActiviteitID = UrenRegAardVanActiviteit.AardVanActiviteitID LEFT OUTER JOIN Materieel ON UrenReg.FK_materiaalID = Materieel.IDMaterieel LEFT OUTER JOIN UrenRegExtra ON UrenReg.FK_ExtraID = UrenRegExtra.ExtraID WHERE Start like @Date";
                
                using (var adapter = new SqlDataAdapter(query, connection))
                {

                    SqlParameter parm1 = adapter.SelectCommand.Parameters.AddWithValue("@Date", "%" + LB_DatePick.Text + "%");

                    try
                    {
                        DataTable ProjectLijst = new DataTable();
                        adapter.Fill(ProjectLijst);

                        for (int i = 0; i < ProjectLijst.Rows.Count; i++)
                        {
                            DataRow dr = ProjectLijst.Rows[i];
                            ListViewItem listitem = new ListViewItem(dr["Naam"].ToString());
                            listitem.SubItems.Add(dr["Start"].ToString());
                            listitem.SubItems.Add(dr["Stop"].ToString());
                            listitem.SubItems.Add(dr["Pauze"].ToString());
                            listitem.SubItems.Add(dr["AardOmschrijving"].ToString());
                            listitem.SubItems.Add(dr["SoortOmschrijving"].ToString());
                            listitem.SubItems.Add(dr["REFProject"].ToString() + dr["REFMaterieel"].ToString());
                            listitem.SubItems.Add(dr["ExtraOmschrijving"].ToString());

                            listitem.Tag = dr["ID"];
                            ListBoxUren.Items.Add(listitem);

                            ListBoxUren.AutoResizeColumns(ColumnHeaderAutoResizeStyle.ColumnContent);
                            ListBoxUren.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize);
                        }
                    }
                    catch (Exception)
                    {


First of all, second of all and finally: You should work on data, not on controls!

As to your confusion about how to get distinct count of some data:
You've got 2 ways to achieve that:
1) you can "ask" your database and return result to the second ListView
2) you can get the count of distinct values by grouping data from the datatable via using Linq

Ad 1)
SQL
SELECT Name, COUNT(DISTINCT Name)
FROM YourTable
GROUP BY Name


Ad 2)
C#
var result = ProjectLijs.AsEnumerable()
    .GroupBy(x => x.Field<string>("Name"))
    .Select(grp => new
          {
              Name = grp.Key,
              Count = grp.Distinct().Count()
          })
     .ToList()


All what you have to do right now, is to bind that data to the second ListView.

Try the method you prefer! Note, that Linq solution for large data amount may cause efficiency issues.

For further details, please see:
LINQ to DataSet[^]
LINQ to DataSet Examples[^]
Queries in LINQ to DataSet[^]
Querying DataSets (LINQ to DataSet)[^]
101 LINQ Samples in C#[^]

[EDIT]
Example:

C#
DataTable dt = new DataTable();

dt.Columns.Add(new DataColumn("ID", typeof(int)));
dt.Columns.Add(new DataColumn("Name", typeof(string)));
dt.Columns.Add(new DataColumn("Date", typeof(DateTime)));

dt.Rows.Add(new object[]{1, "MO", new DateTime(2016,1,1)});
dt.Rows.Add(new object[]{1, "DE", new DateTime(2016,1,1)});
dt.Rows.Add(new object[]{1, "MO", new DateTime(2016,1,2)});
dt.Rows.Add(new object[]{1, "MO", new DateTime(2016,1,2)});
dt.Rows.Add(new object[]{1, "DE", new DateTime(2016,1,2)});
dt.Rows.Add(new object[]{1, "MO", new DateTime(2016,1,3)});
dt.Rows.Add(new object[]{1, "MO", new DateTime(2016,1,2)});

var result = dt.AsEnumerable()
		.GroupBy(x=>x.Field<string>("Name"))
		.Select(grp=>new
			{
				Name = grp.Key,
				Count = grp.Distinct().Count()
			});


Above code returns:
MO 5 
DE 2 


[/EDIT]
   
v2
Comments
Karthik_Mahalingam 10-Aug-16 0:30am
   
5 for the explanation
Maciej Los 10-Aug-16 1:34am
   
Thank you, Karthik.
Animesh Datta 10-Aug-16 1:19am
   
My 5
Maciej Los 10-Aug-16 1:34am
   
Thank you.
[no name] 10-Aug-16 2:20am
   
A 5 for this.
Maciej Los 10-Aug-16 2:24am
   
Thank you, Bruno.
CPallini 10-Aug-16 13:40pm
   
5.
Maciej Los 10-Aug-16 14:36pm
   
Thank you, Carlo.
MaikelO1 11-Aug-16 3:16am
   
thanks for your reply! I'm going to delve into this solution
Maciej Los 11-Aug-16 8:55am
   
You're very welcome.
refer this example

C#
private void Form3_Load(object sender, EventArgs e)
       {
           listView1.View = View.Details;
           listView1.GridLines = true;


           ListViewItem item1 = new ListViewItem("1");
           item1.SubItems.Add("MO");
           item1.SubItems.Add("01-01-2016");
           ListViewItem item2 = new ListViewItem("1");
           item2.SubItems.Add("DE");
           item2.SubItems.Add("01-01-2016");
           ListViewItem item3 = new ListViewItem("1");
           item3.SubItems.Add("MO");
           item3.SubItems.Add("01-01-2016");
           ListViewItem item4 = new ListViewItem("1");
           item4.SubItems.Add("MO");
           item4.SubItems.Add("01-01-2016");
           ListViewItem item5 = new ListViewItem("1");
           item5.SubItems.Add("DE");
           item5.SubItems.Add("01-01-2016");
           ListViewItem item6 = new ListViewItem("1");
           item6.SubItems.Add("MO");
           item6.SubItems.Add("01-01-2016");
           ListViewItem item7 = new ListViewItem("1");
           item7.SubItems.Add("MO");
           item7.SubItems.Add("01-01-2016");


           listView1.Columns.Add("ID"  );
           listView1.Columns.Add("NAAM");
           listView1.Columns.Add("DATE" );

           listView1.Items.AddRange(new ListViewItem[] { item1, item2, item3, item4,item5,item6,item7 });

           int columnToCheckIndex = 1; // set the index value of the column to be checked
          List<string> lst = new List<string> ();
           foreach (ListViewItem item in listView1.Items)
               lst.Add(item.SubItems[columnToCheckIndex].Text);
           var data = lst.GroupBy(k => k).Select(k => new { key = k.Key, count = k.Count() }).ToList();
           foreach (var item in data)
           {
               ListViewItem lvi = new ListViewItem(item.key);
               lvi.SubItems.Add(item.count.ToString());
               listView2.Items.Add(lvi);

           }

           listView2.Columns.Add("NAAM");
           listView2.Columns.Add("Total Count");
           listView2.View = View.Details;
           listView2.GridLines = true;





       }
   
Comments
[no name] 9-Aug-16 11:34am
   
A 5, especally for introducing int columnToCheckIndex = 1; to make maintenance more easy.
Karthik_Mahalingam 9-Aug-16 11:36am
   
Thank you 0x01AA
Maciej Los 9-Aug-16 15:17pm
   
Karthik, sorry, but you're wrong. Please, read my answer to find out why...
Karthik_Mahalingam 10-Aug-16 0:29am
   
Hi Maciej
The difference i found was, i am picking the data from an existing listbox, whereas you are getting it from a datatable source.
for this reason only i have asked the OP at the first place " how you are binding the data ?
post the binding code.

but he has posted some code dump which is not much relevant to the question he asked, so i just demoed him some piece of code..
The approach might be wrong, but it works correct maciej :)
However your solution has detailed information in which OP needs or anyone in future.

Thanks
karthik
Maciej Los 10-Aug-16 1:46am
   
Well... I have say Sorry, because i wasn't clear enough. When i said, that you're wrong, i thought about "data context", i didn't say that your solution is wrong. It's correct, but we have keep in mind that we should work on data, not on controls. That's the reason i voted arbitrarily (3).
Cheers,
Maciej
Karthik_Mahalingam 10-Aug-16 2:08am
   
cool :)
Please take this solution not too serious, I have no clue about this Linq stuff. I have also no idea whether this is the correct way. But if I have a look to the result, for me at least it solves the request.
C#
private void buttonListViewLinq_Click(object sender, EventArgs e)
{
    // Test Source ListView
    listViewLinq.Items.Clear();
    listViewLinq.Items.Add(new ListViewItem (new string[]{"1", "A", "2016-08-01"}));
    listViewLinq.Items.Add(new ListViewItem (new string[]{"1", "A", "2016-08-02"}));
    listViewLinq.Items.Add(new ListViewItem (new string[]{"1", "B", "2016-08-03"}));
    listViewLinq.Items.Add(new ListViewItem (new string[]{"1", "B", "2016-08-04"}));
    listViewLinq.Items.Add(new ListViewItem (new string[]{"1", "B", "2016-08-05"}));
    listViewLinq.Items.Add(new ListViewItem (new string[]{"2", "B", "2016-08-06"}));
    listViewLinq.Items.Add(new ListViewItem (new string[]{"1", "C", "2016-08-07"}));

    // Get IEnumerable to go on with linq
    IEnumerable<ListViewItem> lv = listViewLinq.Items.Cast<ListViewItem>();

    // Here grouping happens
    var res = lv.GroupBy(gb => gb.SubItems[1].Text)
                .Select(group => new { groupVal = group.Key, 
                                       groupCount = group.Count() });

    // Columns of Result ListView
    listViewRes.Columns.Clear();
    listViewRes.Columns.Add("NAAM");
    listViewRes.Columns.Add("Total Count", 100);
    listViewRes.View = View.Details;

    // Add Result ListViewItems
    listViewRes.Items.AddRange(res.Select(g => new ListViewItem(new string[]
         {g.groupVal, 
          String.Format("(TOTAL {0})", g.groupCount.ToString())
         })).ToArray());
}


Note: According your example result you are interesting in Count and _not_ in Distinct Count.
I hope it helps.
   
v3
Comments
MaikelO1 9-Aug-16 10:25am
   
The name is working , only the count , or the addition of the count to the string does not work :(
[no name] 9-Aug-16 10:28am
   
I think I don't get the Point, sorry.
Or do you mean you dont see the Count Column? If it is this, set the resListView.View= Details;
[no name] 9-Aug-16 10:33am
   
<pre lang="text">For my tests the resListView shows:
NAAM Count
---- -----
A 2
B 4
C 1</pre>
[no name] 9-Aug-16 11:07am
   
See updates solution, which shows i.e. "(TOTAL 5)" for Count column.
Maciej Los 9-Aug-16 15:17pm
   
Bruno, sorry, but you're wrong. Please, read my answer to find out why...
[no name] 10-Aug-16 2:11am
   
Hello Maciej, no Problem. That's why I warned the OP in the beginning. But the solution works and OP asked in the Q to do it with ListViews. For me it was a great excersice with this linq stuff, I didn't bother about the datasource :)
Maciej Los 10-Aug-16 2:28am
   
Bruno, of course, your solution is working as well, but we have to keep in mind that we should work on data, not on controls. Yeah, Linq is powerful tool! I really do like it, because Linq queries are very similar to SQL queries.
Cheers, Maciej.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900