none
Nested Foreach loop RRS feed

  • Question

  • Hi there,

    I am translating a program into c#;

     The code below iterates over the same rowset even though there is a nested do while loop. It was a very elegent way of summing up the rows that shared the same invnum but still keeping its position in the outer loop.

    do while not rowset.endofset invNum = int(rowset.fields["invNum"].value) netTotal = 0 do while not rowset.endofset and invNum = int(rowset.fields["invNum"].value) netTotal = netTotal + rowset.fields["lineValue"].value rowset.next() enddo // Do something with netTotal

    enddo enddo

                            

    I am trying to reproduce the same behaviour using foreach loops in c#.

    foreach(DataRow row in allMyRows)
    {
       invNum = row["invNum"];
       netTotal = 0;
    
       foreach(DataRow insideRow in allMyRows (Where the allMyRows[invNum] is invnum
       {
          netTotal = netTotal + insideRow["net"];
       }
    
    // At this point I need the outer loop to have moved on to next row of the inside loop
    
    }

    Its complicated to describe what I am trying to do. Perhaps I just cant use foreach loops in this scenario ??

    And I will just use do while with a counter for the amount of rows ??

    Thanks,

    J



    jppnn

    Thursday, July 5, 2018 12:54 PM

Answers

  • Try working with a GroupBy and Sum

    private void demo()
    {
        DataTable dt = new DataTable { TableName = "MyTable" };
    
        dt.Columns.Add(new DataColumn { ColumnName = "Serial", DataType = typeof(int) });
        dt.Columns.Add(new DataColumn { ColumnName = "Name", DataType = typeof(string) });
        dt.Columns.Add(new DataColumn { ColumnName = "Date", DataType = typeof(DateTime) });
        dt.Columns.Add(new DataColumn { ColumnName = "Value", DataType = typeof(int) });
    
        DataTable resultTable = dt.Clone();
    
        dt.Rows.Add(new object[] { 222, "IBM", new DateTime(2016, 1, 13), 10 });
        dt.Rows.Add(new object[] { 111, "Microsoft", new DateTime(2017, 1, 12),5 });
        dt.Rows.Add(new object[] { 333, "Apple", new DateTime(2010, 5, 15),20 });
        dt.Rows.Add(new object[] { 111, "Microsoft", new DateTime(2017, 1, 1), 5 });
        dt.Rows.Add(new object[] { 222, "IBM", new DateTime(1980, 12, 12),109 });
    
        var StudentData = dt.AsEnumerable()
            .GroupBy((student) => student.Field<int>("Serial"))
            .Select((group) => new
            {
                Serial = group.Key,
                Name = group.OrderBy((x) => x.Field<string>("Name")).FirstOrDefault(),
                Total = group.Sum(x => x.Field<int>("Value"))
            }).OrderBy(x => x.Serial).ToList();
    
    
        foreach (var row in StudentData)
        {               
            Console.WriteLine($"{row.Serial} - {row.Total}");
        }
    
    }

    Results in the IDE output window

    111 - 10
    222 - 119
    333 - 20


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by john pp nn Thursday, July 5, 2018 7:04 PM
    Thursday, July 5, 2018 3:35 PM
    Moderator

All replies

  • Have you considered using a Lambda statement such as the following?

    Here allMyRows is a DataTable.

    int value = 3;
    var netTotal = allMyRows.AsEnumerable()
        .Where(row => row.Field<int>("someField") == value)
        .Select(row => row.Field<int>("net")).Sum();


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, July 5, 2018 1:34 PM
    Moderator
  • Hi Karen,

    Would doing it your way require me to to get the distinct invNums from allMyRows ?

    Something like...

    foreach(distinct invNum in allMyRows.Rows) // pseudo code

    {

    int value =invNum; var netTotal = allMyRows.AsEnumerable() .Where(row => row.Field<int>("invNum") == value) .Select(row => row.Field<int>("net")).Sum();

    }

    J


    jppnn



    • Edited by john pp nn Thursday, July 5, 2018 3:15 PM
    Thursday, July 5, 2018 3:13 PM
  • Try working with a GroupBy and Sum

    private void demo()
    {
        DataTable dt = new DataTable { TableName = "MyTable" };
    
        dt.Columns.Add(new DataColumn { ColumnName = "Serial", DataType = typeof(int) });
        dt.Columns.Add(new DataColumn { ColumnName = "Name", DataType = typeof(string) });
        dt.Columns.Add(new DataColumn { ColumnName = "Date", DataType = typeof(DateTime) });
        dt.Columns.Add(new DataColumn { ColumnName = "Value", DataType = typeof(int) });
    
        DataTable resultTable = dt.Clone();
    
        dt.Rows.Add(new object[] { 222, "IBM", new DateTime(2016, 1, 13), 10 });
        dt.Rows.Add(new object[] { 111, "Microsoft", new DateTime(2017, 1, 12),5 });
        dt.Rows.Add(new object[] { 333, "Apple", new DateTime(2010, 5, 15),20 });
        dt.Rows.Add(new object[] { 111, "Microsoft", new DateTime(2017, 1, 1), 5 });
        dt.Rows.Add(new object[] { 222, "IBM", new DateTime(1980, 12, 12),109 });
    
        var StudentData = dt.AsEnumerable()
            .GroupBy((student) => student.Field<int>("Serial"))
            .Select((group) => new
            {
                Serial = group.Key,
                Name = group.OrderBy((x) => x.Field<string>("Name")).FirstOrDefault(),
                Total = group.Sum(x => x.Field<int>("Value"))
            }).OrderBy(x => x.Serial).ToList();
    
    
        foreach (var row in StudentData)
        {               
            Console.WriteLine($"{row.Serial} - {row.Total}");
        }
    
    }

    Results in the IDE output window

    111 - 10
    222 - 119
    333 - 20


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by john pp nn Thursday, July 5, 2018 7:04 PM
    Thursday, July 5, 2018 3:35 PM
    Moderator
  • Hi Karen,

    Thanks for this. This looks exactly like what I am looking for.

    Much appreciated.

    J


    jppnn

    Thursday, July 5, 2018 7:04 PM