Put (only) all different values from a datagridview column in an array.

Question

0.00/5 (No votes)

See more:

Hi guys thanks for your help in prior.
I`m not a programmer, i just try to solve a problem that costs me a lot of time each time i have to do it manually so if it`s stupid be gentle please.

Here is what I try to do:

I have a csv-outputs from a database with sales figures including all kind of transactions.
I now try to come up with a littke tool that just sums up the sales and costs for each customer and writes that info back to excel.

I figured out how to read the csv-file, how to put the data into a datatable as well as how to show them via datagridview, managed to filter

If i knew the customers in prior this would be quite easy, but its different for each month/year) so i try to figure out a way to get all the customers in the current file so i can write a loop which sums the numbers up for each of them.

What I have tried:

So far i have the data itself in a datatable and use a bindingsource to show the data in a datagridview.
I don`t link the datagridview direct to the datatable because I need the option to filter for time periods that i want to analyse.
which i do like this:

C#

bs.Filter = "[Datum Status] >= #" + anfang.ToString("MM dd yyyy")
    + "# AND [Datum Status] <= #" + ende.ToString("MM dd yyyy") + "#";
//anfang + ende are values I pick from dateTimepickers
dataGridView2.DataSource = bs;

I would now love to have something like:

C#

foreach (string s in customerArray)
{
   bs.compute("SUM([Salesvalue])");
   //save it somewhere etc...
   //I know that bindingsources can`t "compute" - just so you get what i try to do
}

But first i need to populate that array somehow with the values for the different customers.
>> Some customers have just one row in the data, others have a lot of them.

I just can`t get the loop right so it puts the different customers just once in the array.

What i need is something similar to this (syntax is wrong i know):

C#

 foreach (row in bs)
{
   if (Customervalue is not in customerArray yet)
   { 
       put cellvalue as string in the array
   }
}

Maybe one of you can help me there?

Thanks a lot and have a nice christmas time :)

Posted 19-Dec-16 5:19am

Member 12912306

Updated 19-Dec-16 6:33am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2016-12-19T06:33:00

Solution 1

It sounds like you're looking for LINQ[^]:

C#

string[] customers = bs.AsEnumerable()
    .Select(r => r.Field<string>("Customer"))
    .Distinct()
    .ToArray();

var customersWithTotals = bs.AsEnumerable()
    .GroupBy(r => r.Field<string>("Customer"), (customer, rows) => new
    {
        Customer = customer,
        SalesValue = rows.Sum(r => r.Field<decimal>("SalesValue")),
    });

You'll obviously need to specify the correct column names, and the correct type for the SalesValue column.

Posted 19-Dec-16 6:33am

Richard Deeming

Comments

Member 12912306 20-Dec-16 5:25am

Thanks a lot thats a good start.
I know exactly zero about LINQ but i will look into it :)

The Problem that i still have is that i only need the sumed up values of the filtered bindingsource. But the Bindingsource doesn`t contain a AsEnumerable method.
Would you recommend to put the filtered values of the bindingsource in a new datatable and do the calculation from there or is there a way to do it directly with the bindingsource?

Thanks

Richard Deeming 20-Dec-16 8:27am

Sorry, I thought the bs variable was the DataTable.

You'll need to use the DataTable, not the BindingSource:

string[] customers = dt.AsEnumerable()
    .Where(r => r.Field<DateTime>("Datum Status") >= anfang && r.Field<DateTime>("Datum Status") <= ende)
    .Select(r => r.Field<string>("Customer"))
    .Distinct()
    .ToArray();

var customersWithTotals = dt.AsEnumerable()
    .Where(r => r.Field<DateTime>("Datum Status") >= anfang && r.Field<DateTime>("Datum Status") <= ende)
    .GroupBy(r => r.Field<string>("Customer"), (customer, rows) => new
    {
        Customer = customer,
        SalesValue = rows.Sum(r => r.Field<decimal>("SalesValue")),
    });