Fetch rows from Multiple tables with same value in a column

[ashok.k]

Hi,

I get product details in a DataTable from a webservice that has 4 columns.

RowId (unique), ProductId,SubProductId, Price

I get a DataTable from webservice in batches. It can return only 5000 rows at a time. I pass Start RowId and End Row Id as parameters so that products within that range is returned in a datatable. Row Id is unique but same Product Id can be in multiple rows.

If I call the webservice method three times, I get 3 Datatables with 5000 rows in each table.

I want to get the rows with same Product Id from all the three tables and get the maximum price.

Can I merge 3 table into one table and loop through or can I use LINQ query to fetch data from multiple tables?

How to store the max price of each product in a new table?

Thanks
Ashok

 

[IanRyder]

Hi,

You have actually asked a few questions here so I have created this example to demonstrate how you can achieve this. I have used the Northwind database to simulate 3 tables being received from your web service. These are then merged to a single table, summarised by Product ID using LINQ and then saved to a new data table.

  Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    Dim sqlConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
 
    Dim daOrderDetails As New SqlDataAdapter("select * From [Order Details]", sqlConn)
    Dim tblOrderDetails1 As New DataTable
    Dim tblOrderDetails2 As New DataTable
    Dim tblOrderDetails3 As New DataTable
 
    'this represents three data tables that have been received into your app from your web service
    daOrderDetails.Fill(tblOrderDetails1)
    daOrderDetails.Fill(tblOrderDetails2)
    daOrderDetails.Fill(tblOrderDetails3)
 
    'Here I use the first order details table to accumulate the other data tables received from the web service
    'this will only work on the basis that the datatable structures are of the same schema
    For Each RowToAdd As DataRow In tblOrderDetails2.Rows
      tblOrderDetails1.ImportRow(RowToAdd)
    Next
    For Each RowToAdd As DataRow In tblOrderDetails3.Rows
      tblOrderDetails1.ImportRow(RowToAdd)
    Next
    'tblOrderDetails1 now has the accumulated records form the three tables.
 
    'create the new table to hold the summary details
    Dim myProductSummaryTable As New DataTable
    With myProductSummaryTable.Columns
      .Add("ProductID", GetType(String))
      .Add("MaxPrice", GetType(Decimal))
    End With
 
    'define and run the LINQ query to group the order details by Product ID and get the max price values
    Dim ValuesByProductID = (From Prods As DataRow In tblOrderDetails1 Select ProdID = Prods.Item("ProductID"), Price = Prods.Item("UnitPrice") Group By ProdID Into G = Group, MaxPrice = Max(Price))
 
    'add the resulting LINQ query into the new data table
    For Each MaxValByProdID In ValuesByProductID
      Dim summaryRow As DataRow = myProductSummaryTable.NewRow
      summaryRow.Item("ProductID") = MaxValByProdID.ProdID
      summaryRow.Item("MaxPrice") = MaxValByProdID.MaxPrice
      myProductSummaryTable.Rows.Add(summaryRow)
    Next
 
    'run though the new created summary table to display the results of the LINQ grouping
    For Each ViewSummary As DataRow In myProductSummaryTable.Rows
      MsgBox(ViewSummary.Item("ProductID"))
      MsgBox(ViewSummary.Item("MaxPrice"))
    Next
  End Sub

Hope that helps.

Cheers,

Ian

 

[ashok.k]

Hi Ian,

Thank you very much. Your code is really helpful.

Thanks
Ashok