How to compare values of two datatable columns

Question

I want to validate quantity columns of two datatable. Given below is the sample datatable. I want to take the sum of the quantity of each item from dtItems and compare with the quantity of same item in dtMasterItem. If dtItems quantity become greater than dtMasterItem quantity then I want show the message like Available quantity is 1000 for itemcode

dtMasterItem

ItemCode      Quantity

  A            3000    
  B            5000    
  C            6000 

dtItems

ItemCode      Quantity

   A           2000
   A           1000
   A           500
   B           3000  
   B           2000  
   C           6000 

As per my logic if we validate the above table data the output will be like Available quantity is 3000 for A

Answer 1

Try this using linq query

    DataTable dt1 = new DataTable("dtMasterItem");
    dt1.Columns.Add(new DataColumn() { DataType = typeof(string), ColumnName = "ItemCode" });
    dt1.Columns.Add(new DataColumn() { DataType = typeof(int), ColumnName = "Quantity" });

    DataTable dt2 = new DataTable("dtItems");
    dt2.Columns.Add(new DataColumn() { DataType = typeof(string), ColumnName = "ItemCode" });
    dt2.Columns.Add(new DataColumn() { DataType = typeof(int), ColumnName = "Quantity" });

    dt1.Rows.Add("A", 3000);
    dt1.Rows.Add("B", 5000);
    dt1.Rows.Add("C", 6000);

    dt2.Rows.Add("A", 2000);
    dt2.Rows.Add("A", 1000);
    dt2.Rows.Add("A", 500);
    dt2.Rows.Add("B", 3000);
    dt2.Rows.Add("B", 2000);
    dt2.Rows.Add("C", 6000);

   var result = (from k in
                  (from x in dt2.AsEnumerable()
                           group x by x["ItemCode"] into entryGroup
                           select new
                           {
                               ItemCode = entryGroup.Key,
                               Quantity = entryGroup.Sum(i => Convert.ToInt32(i["Quantity"]))
                           })
                      join y in dt1.AsEnumerable() on k.ItemCode equals y["ItemCode"] into DataGroup
                      from item in DataGroup.DefaultIfEmpty()
                      select new
                      {
                          ItemCode = k.ItemCode,
                          Q1 = k.Quantity,
                          Q2 = item["Quantity"],
                          Remarks = Convert.ToInt32(item["Quantity"]) < k.Quantity ? "Available quantity is " + k.Quantity + " for " + k.ItemCode : null
                      }).AsEnumerable().Where(i => i.Remarks != null);

Output :

Answer 2

Your question isn't that clear but according to what I understood from it this ought to do it for you.

        foreach (DataRow rowMasterItems in dtMasterItem.Rows)
        {
            int lintSumOfItems = 0;
            foreach (DataRow rowItems in dtItems.Rows)
            {
                if (rowMasterItems["ItemCode"].ToString().Equals(rowItems["ItemCode"].ToString()))
                {
                    lintSumOfItems += Convert.ToInt16(rowItems["Quantity"]);
                }
            }
            if (Convert.ToInt16(rowMasterItems["Quantity"]) >= lintSumOfItems)
                MessageBox.Show("OK");
            else
                MessageBox.Show("Available quantity is " + Convert.ToInt16(rowMasterItems["Quantity"]) + " for itemcode: " + rowMasterItems["ItemCode"].ToString());
        }

Answer 3

Try this code, it worked for me:

        DataTable dt1 = new DataTable();
        DataTable dt2 = new DataTable();
        dt1.Columns.Add("ItemCode", typeof(string));
        dt1.Columns.Add("Quantity", typeof(int));
        dt2.Columns.Add("ItemCode", typeof(string));
        dt2.Columns.Add("Quantity", typeof(int));
        dt1.Rows.Add("A", 3000);
        dt1.Rows.Add("B", 5000);
        dt1.Rows.Add("C", 6000);
        dt2.Rows.Add("A", 2000);
        dt2.Rows.Add("A", 1000);
        dt2.Rows.Add("A", 500);
        dt2.Rows.Add("B", 3000);
        dt2.Rows.Add("B", 2000);
        dt2.Rows.Add("c", 6000);

        var query = from row in dt2.AsEnumerable()
                    group row by row.Field<string>("ItemCode") into grp
                    select new
                    {
                        ItemCode = grp.Key,
                        Quantity = grp.Sum(r => r.Field<int>("Quantity"))
                    };
        int i = 0;
        foreach (var item in query)
        {
            if (Convert.ToInt32(item.Quantity) > Convert.ToInt32(dt1.Rows[i]["Quantity"]))
            {
                Console.WriteLine("Available quantity is "+ dt1.Rows[i]["Quantity"].ToString()+ " for "+item.ItemCode);
            }
            i++;
        }

        Console.Read();