Grouping dataset (in VB)RSS

• • Reply
  Bjarnoe

  None

  0 Points

  5 Posts

  Grouping dataset (in VB)

  Jul 09, 2018 01:50 PM|Bjarnoe|LINK

  
  Hi,
  I guess this should be fairly simple, but I haven't been able to find a solution.
  I'm retreiving orderlines from a db table, that I need to group by the OrderId. A simplified scheme of the table looks like this:

  OrderId	OrderLineTitle	OrderLinePrice	PurchaseDate	OrderTotal
  5566	Blue sweater	99.99	2018.07.07	199.99
  5566	Red sweater	100	2018.07.07	199.99
  7788	Yellow sweater	88.88	2018.06.06	88.88
  9900	Green sweater	77.77	2018.05.05	177.77
  9900	Brown sweater	100.00	2018.05.05	177.77

  What I need is this output:

  5566	2018.07.07	199.99
  Blue sweater	99.99
  Red sweater	100
  7788	2018.06.06	88.88
  Yellow sweater	88.88
  9900	2018.05.05	177.77
  Green sweater	77.77
  Brown sweater	100.00

  So basically I need to group the data by OrderId for each order and only display one row with the common values for each of the orderlines, e.g. PurchaseDate and OrderTotal.
  And below that I need to display all the orderlines in the order and display the OrderLineTitles.

  I´m using VB.

  Hope you can help, thanks in advance.

• • Reply
  rajesh93180

  Participant

  1644 Points

  792 Posts

  Re: Grouping dataset (in VB)

  Jul 09, 2018 03:06 PM|rajesh93180|LINK

  Hi Bjarnoe,

  Firstly, you get the distinct values of grouping fields to a datatable using the below code.

  dtGroup = dt.DefaultView.ToTable(true, "column1", "column2");

  Next make nested looping with dtGroup at initial loop and dt at inner loop as below.

  for (int i = 0; i < dtGroup.Rows.Count; i++)
          {
              for (int k = 0; k < dt.Rows.Count; k++)
              {
                  table += "<tr>" +
                      "<td>" + dtGroup.Rows[i]["Column1"].ToString() + "</td>" +
                      "<td colspan='2'>" + dtGroup.Rows[i]["Column2"].ToString() + "</td>" +
                      "</tr>";
                  if (dtGroup.Rows[i]["Column1"].ToString() == dt.Rows[k]["Column1"].ToString() &&
                      dtGroup.Rows[i]["Column2"].ToString() == dt.Rows[k]["Column2"].ToString())
                  {
                      table += "<tr>" +
                      "<td>" + dt.Rows[k]["Column3"].ToString() + "</td>" +
                      "<td>" + dt.Rows[k]["Column4"].ToString() + "</td>" +
                      "<td>" + dt.Rows[k]["Column5"].ToString() + "</td>" +
                      "</tr>";
                  }
              }
          }

  The table string can be assigned to div to render in html.

  Thanks,

  RajeshV.

  -Rajesh Vemunoori LinkedIn Profile
  
  Mark as answer if you find this post helpful.
• • Reply
  Bjarnoe

  None

  0 Points

  5 Posts

  Re: Grouping dataset (in VB)

  Jul 09, 2018 03:22 PM|Bjarnoe|LINK

  Thanks for your reply, but I´m using VB, are you able to convert it?

  Also, is it possible to do it using a DataReader and While instead of a datatable?

• • Reply
  rajesh93180

  Participant

  1644 Points

  792 Posts

  Re: Grouping dataset (in VB)

  Jul 09, 2018 03:31 PM|rajesh93180|LINK

  I woukd suggest you to convert datareader to datatable in the below way.

     Dim dr As SqlDataReader = cmd.ExecuteReader()
      Dim dt As DataTable = New DataTable()
      dt.Load(dr)

  Your code in VB would be like below.

  Dim dt As DataTable = New DataTable
  Dim dtGroup As DataTable = New DataTable
  Dim table As String = string.Empty
  Dim i As Integer = 0
  ,k = 0
  i = 0
  Do While (i < dtGroup.Rows.Count)
      k = 0
      Do While (k < dt.Rows.Count)
          table = (table + ("<tr>" + ("<td>"  _
                      + (dtGroup.Rows(i)("Column1").ToString + ("</td>" + ("<td colspan='2'>"  _
                      + (dtGroup.Rows(i)("Column2").ToString + ("</td>" + "</tr>"))))))))
          If ((dtGroup.Rows(i)("Column1").ToString = dt.Rows(k)("Column1").ToString)  _
                      AndAlso (dtGroup.Rows(i)("Column2").ToString = dt.Rows(k)("Column2").ToString)) Then
              table = (table + ("<tr>" + ("<td>"  _
                          + (dt.Rows(k)("Column3").ToString + ("</td>" + ("<td>"  _
                          + (dt.Rows(k)("Column4").ToString + ("</td>" + ("<td>"  _
                          + (dt.Rows(k)("Column5").ToString + ("</td>" + "</tr>")))))))))))
          End If
          
          k = (k + 1)
      Loop
      
      i = (i + 1)
  Loop

  Thanks,

  RajeshV.

  -Rajesh Vemunoori LinkedIn Profile
  
  Mark as answer if you find this post helpful.
• • Reply
  Yohann Lu

  Star

  11444 Points

  2439 Posts

  Microsoft

  Re: Grouping dataset (in VB)

  Jul 10, 2018 07:32 AM|Yohann Lu|LINK

  
  Hi Bjarnoe,

  You can use the Group by Functionality on a DataSet(DataTable)

  You can refer the following code.

  Dim team As New List(Of Staff)
  
          team.Add(New Staff With {.OrderId = 5566, .OrderLineTitle = "Blue sweater", .OrderLinePrice = "99.99", .PurchaseDate = "2018.07.07", .OrderTotal = "199.99"})
          team.Add(New Staff With {.OrderId = 5566, .OrderLineTitle = "Red sweater", .OrderLinePrice = "100", .PurchaseDate = "2018.07.07", .OrderTotal = "199.99"})
  
          team.Add(New Staff With {.OrderId = 7788, .OrderLineTitle = "Yellow sweater", .OrderLinePrice = "88.88", .PurchaseDate = "2018.06.06", .OrderTotal = "88.88"})
  
          team.Add(New Staff With {.OrderId = 9900, .OrderLineTitle = "Green sweater", .OrderLinePrice = "77.77", .PurchaseDate = "2018.05.05", .OrderTotal = "177.77"})
  
          team.Add(New Staff With {.OrderId = 9900, .OrderLineTitle = "Brown sweater", .OrderLinePrice = "100", .PurchaseDate = "2018.05.05", .OrderTotal = "177.77"})
  
  
          Dim summary1 = From member In team
                         Group member By keys = New With {Key member.OrderId, Key member.PurchaseDate, Key member.OrderTotal}
                 Into Group
                         Select New With {
                             Key .Dates = keys,
                             .value = Group
                             }
  
          For Each Info In summary1
  
  
              Response.Write(String.Format("{0}__{1}__{2}: <br/>", Info.Dates.OrderId, Info.Dates.PurchaseDate, Info.Dates.OrderTotal))
  
              For Each dr As Staff In Info.value
                  Response.Write(String.Format("{0} {1} <br/>", dr.OrderLineTitle, dr.OrderLinePrice))
              Next
  
          Next
  

  
  Group By Clause (Visual Basic)
  https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/queries/group-by-clause

  Implementing Simple SQL Group by Functionality with LINQ Technology on a DataSet
  https://www.codeproject.com/Articles/28132/Implementing-Simple-SQL-Group-by-Functionality-wit

  
  Best Regards,

  Yong Lu

  
  MSDN Community Support
  Please remember to "Mark as Answer" the responses that resolved your issue.
  
• • Reply
  Bjarnoe

  None

  0 Points

  5 Posts

  Re: Grouping dataset (in VB)

  Jul 10, 2018 09:54 AM|Bjarnoe|LINK

  r

• • Reply
  Bjarnoe

  None

  0 Points

  5 Posts

  Re: Grouping dataset (in VB)

  Jul 10, 2018 09:55 AM|Bjarnoe|LINK

  rajesh93180

  I woukd suggest you to convert datareader to datatable in the below way.

     Dim dr As SqlDataReader = cmd.ExecuteReader()
      Dim dt As DataTable = New DataTable()
      dt.Load(dr)

  Your code in VB would be like below.

  Dim dt As DataTable = New DataTable
  Dim dtGroup As DataTable = New DataTable
  Dim table As String = string.Empty
  Dim i As Integer = 0
  ,k = 0
  i = 0
  Do While (i < dtGroup.Rows.Count)
      k = 0
      Do While (k < dt.Rows.Count)
          table = (table + ("<tr>" + ("<td>"  _
                      + (dtGroup.Rows(i)("Column1").ToString + ("</td>" + ("<td colspan='2'>"  _
                      + (dtGroup.Rows(i)("Column2").ToString + ("</td>" + "</tr>"))))))))
          If ((dtGroup.Rows(i)("Column1").ToString = dt.Rows(k)("Column1").ToString)  _
                      AndAlso (dtGroup.Rows(i)("Column2").ToString = dt.Rows(k)("Column2").ToString)) Then
              table = (table + ("<tr>" + ("<td>"  _
                          + (dt.Rows(k)("Column3").ToString + ("</td>" + ("<td>"  _
                          + (dt.Rows(k)("Column4").ToString + ("</td>" + ("<td>"  _
                          + (dt.Rows(k)("Column5").ToString + ("</td>" + "</tr>")))))))))))
          End If
          
          k = (k + 1)
      Loop
      
      i = (i + 1)
  Loop

  Thanks,

  RajeshV.

  Thank you for your reply, I will try it out.

• • Reply
  Bjarnoe

  None

  0 Points

  5 Posts

  Re: Grouping dataset (in VB)

  Jul 10, 2018 09:58 AM|Bjarnoe|LINK

  Yohann Lu

  
  Hi Bjarnoe,

  You can use the Group by Functionality on a DataSet(DataTable)

  You can refer the following code.

  Dim team As New List(Of Staff)
  
          team.Add(New Staff With {.OrderId = 5566, .OrderLineTitle = "Blue sweater", .OrderLinePrice = "99.99", .PurchaseDate = "2018.07.07", .OrderTotal = "199.99"})
          team.Add(New Staff With {.OrderId = 5566, .OrderLineTitle = "Red sweater", .OrderLinePrice = "100", .PurchaseDate = "2018.07.07", .OrderTotal = "199.99"})
  
          team.Add(New Staff With {.OrderId = 7788, .OrderLineTitle = "Yellow sweater", .OrderLinePrice = "88.88", .PurchaseDate = "2018.06.06", .OrderTotal = "88.88"})
  
          team.Add(New Staff With {.OrderId = 9900, .OrderLineTitle = "Green sweater", .OrderLinePrice = "77.77", .PurchaseDate = "2018.05.05", .OrderTotal = "177.77"})
  
          team.Add(New Staff With {.OrderId = 9900, .OrderLineTitle = "Brown sweater", .OrderLinePrice = "100", .PurchaseDate = "2018.05.05", .OrderTotal = "177.77"})
  
  
          Dim summary1 = From member In team
                         Group member By keys = New With {Key member.OrderId, Key member.PurchaseDate, Key member.OrderTotal}
                 Into Group
                         Select New With {
                             Key .Dates = keys,
                             .value = Group
                             }
  
          For Each Info In summary1
  
  
              Response.Write(String.Format("{0}__{1}__{2}: <br/>", Info.Dates.OrderId, Info.Dates.PurchaseDate, Info.Dates.OrderTotal))
  
              For Each dr As Staff In Info.value
                  Response.Write(String.Format("{0} {1} <br/>", dr.OrderLineTitle, dr.OrderLinePrice))
              Next
  
          Next

  
  Group By Clause (Visual Basic)
  https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/queries/group-by-clause

  Implementing Simple SQL Group by Functionality with LINQ Technology on a DataSet
  https://www.codeproject.com/Articles/28132/Implementing-Simple-SQL-Group-by-Functionality-wit

  
  Best Regards,

  Yong Lu

  Thank you, I will try it out. I have been looking at Linq but haven't found anything yet that was as well explained as your example, so thank you for that.