C# SQL: How to initiate code for each distinct value in SQL table column?

Question

Trying to generate one invoice foreach distinct value in my SQL database column.

I have a SQL table, where one of the columns holds names of companies. 9 distinct ones, 20 in total.

Here's an abbreviated example to avoid any confusion:

ID	CompanyName	Value1	Value2	Value3
1	Company1	5	3	8
2	Company2	9	9	1
3	Company2	4	4	2
4	Company3	2	2	2
5	Company3	5	9	7
6	Company3	2	2	1

Essentially, what I am trying to do, is bind this table to an ObservableCollection, and then create a PDF for each distinct company.

So, I am thinking that I need to iterate over the column data, and initiate my method to create PDFs foreach distinct value. Please have a look at my code below.

try {

    using (SqlConnection con = new(//my connection string))
    using (SqlCommand cmd = new("spMyStoredProcedure", con) { CommandType = CommandType.StoredProcedure })
    using (SqlDataAdapter da = new(cmd))
    using (DataSet ds = new()) {

        con.Open();
        da.Fill(ds);

        foreach (DataRow dr in ds.Tables[0].Rows) {

            MyObservableCollection.Add(New MyClass {

                ID = (int)dr[0],
                CompanyName = dr[1].ToString(),
                Value1 = (decimal)dr[2],
                Value2 = (decimal)dr[3],
                Value3 = (decimal)dr[4],
            });
        }

    } catch (Exception ex) {

}

There are some holes here.

I need to understand how to do the following:

Where do I call my method to create PDFs? Do I need to wrap the above code in another foreach?
Do I also need to reference the names of my columns (= "CompanyName")? How?
How can I make sure that I only initiate my code for each distinct, unique value?

Please only one quesion at a time – Klaus Gütter Sep 24 '21 at 08:37 — Klaus Gütter, Sep 24 '21 at 08:37

score 1 · Accepted Answer · answered Sep 24 '21 at 09:18

You can try doing the following:

foreach (var item in MyObservableCollection.Distinct(/*your custom equality comparer if you have one*/))
        {
            //your pdf generating code here
        }

For finding distinct records based on a fild, you have two options:

Write a custom EqualityComparer and pass it as parameter to LINQ's .Distinct()
Use LINQ's .GroupBy() as described in the answer here and loop with foreach through already sorted list.

After you get all unique items, you can run your code in a loop.

C# SQL: How to initiate code for each distinct value in SQL table column?

1 Answers1