CODEWITHSUNDEEP
c#sql
user9984958
user9984958

Reputation:

Manipulate selective rows from two DataTables

I have two DataTables both have the same type of schema but have different data. Basically, I want to do is that I want to subtract those rows of newDt which are less then newDt1. Up to that point, I wrote that code but it has some logical error.

Below is a newDt DataTable which contains original Data.

var newDt = dt.AsEnumerable()
          .GroupBy(r => r.Field<int>("batch_num"))
          .Select(g =>
          {
              var row = dt.NewRow();

              row["batch_num"] = g.Key;
              row["qty"] = g.Sum(r => Convert.ToInt32(r.Field<int>("qty")));
              return row;
          }).CopyToDataTable();

Below is newdt1 DataTable which contains Data With Different 'qty' column value but same 'batch_num'.

newDt1 = dt_1.AsEnumerable()
          .GroupBy(r => r.Field<int>("batch_num"))
          .Select(g =>
          {
              var row = dt_1.NewRow();

              row["batch_num"] = g.Key;
              row["qty"] = g.Sum(r => Convert.ToInt32(r.Field<int>("qty")));
              return row;
          }).CopyToDataTable();

Below is resulting DataTable which contains subtracted 'qty' of newDt from newDt1.

DataTable result = newDt.AsEnumerable() // IT IS SUBTRACTING NEWDt from NewDt1 and storing result in it.
        .Join(newDt1.AsEnumerable(), d1 => d1["batch_num"], d2 => d2["batch_num"], (d1, d2) => new { D1 = d1, D2 = d2 })
        .Select(r =>
        {
            var row = newDt1.NewRow();
            row["batch_num"] = Convert.ToInt32(r.D1["batch_num"]);

            row["qty"] = Convert.ToInt32(r.D2["qty"]) - Convert.ToInt32(r.D1["qty"]);
            return row;
        }).CopyToDataTable();

            for (int i = 0; i < result.Rows.Count; i++)
            {
                if (Convert.ToInt32(newDt.Rows[i]["qty"]) < (Convert.ToInt32(newDt1.Rows[i]["qty"])))
                {
                    SqlCommand command = new SqlCommand("update batch set sold_qty=sold_qty-@soldqty2, left_qty=left_qty+@soldqty2 where id=@id2", con);
                    command.Parameters.AddWithValue("@soldqty2", Convert.ToInt32(result.Rows[i]["qty"]));
                    command.Parameters.AddWithValue("@id2", Convert.ToInt32(result.Rows[i]["batch_num"]));
                    rexe = command.ExecuteNonQuery();
                }

            }

I want that only those 'qty' column rows of 'newDT1' should be subtracted which are less than row value of 'qty' column of 'newDT'.

Upvotes: 1

Views: 35

Answers (1)

user9984958
user9984958

Reputation:

Here is how we can subtract two DataTables.

DataTable Subtraction_result = newDt.AsEnumerable()
            .Join(newDt1.AsEnumerable(), d1 => d1["batch_num"], d2 => d2["batch_num"], (d1, d2) => new { D1 = d1, D2 = d2 })
            .Select(r =>
            {
                var row = newDt1.NewRow();
                row["batch_num"] = Convert.ToInt32(r.D1["batch_num"]);
                row["qty"] = Convert.ToInt32(r.D2["qty"]) - Convert.ToInt32(r.D1["qty"]);
                return row;
            }).CopyToDataTable();

Upvotes: 2

Related Questions