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)
SELECT Name, COUNT(DISTINCT Name)
FROM YourTable
GROUP BY Name
Ad 2)
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:
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]