Compare dataset or a better idea

Question

How do I compare values of one data set from another. 1st dataset["proper records"](coming from sqlserver, with column name [id],[subsNumber]),

2nd dataset["proper and inproper records"](coming from progress database, with diffrent coulums except 1 which is[subsNumber]).

how do I go and make another dataset which has all the [subsNumber] from ["proper records"] with matching records from 2nd datset["proper inproper records"]

or

delete all the records in 2nd dataset["proper and inproper records"] which don't match the "subsNumber" column in the 1st dataset

or any other idea

basically How do I get all records from 2nd dataset which has same "subsNumber" as the 1st dataset

Answer 1

To get all the records from 2nd dataset that match the records from the 1st dataset would be something like this:

IEnumerable list3 = list2.Where(l2=>list1.Contains(l1=>l1.subsNumber == l2.subsNumber));

Something along those lines!

Answer 2

The key is using System.Data.DataRelation to join your 2 datatables on a common column (or columns).

Here's some code derived from a post at KC's See Sharp Blog

public DataTable GetImproperRecords(DataTable ProperRecords, DataTable ImproperRecords) {
  DataTable relatedTable = new DataTable("Difference");
  try {
     using (DataSet dataSet = new DataSet()) {
        dataSet.Tables.AddRange(new DataTable[] { ProperRecords.Copy(), ImproperRecords.Copy() });

        DataColumn properColumn = new DataColumn();
        properColumn = dataSet.Tables[0].Columns[1]; // Assuming subsNumber is at index 1

        DataColumn improperColumn = new DataColumn();
        improperColumn = dataSet.Tables[1].Columns[0]; // Assuming subsNumber is at index 0

        //Create DataRelation
        DataRelation relation = new DataRelation(string.Empty, properColumn, improperColumn, false);

        dataSet.Relations.Add(relation);

        //Create columns for return relatedTable
        for (int i = 0; i < ImproperRecords.Columns.Count; i++) {
           relatedTable.Columns.Add(ImproperRecords.Columns[i].ColumnName, ImproperRecords.Columns[i].DataType);
        }

        relatedTable.BeginLoadData();

        foreach (DataRow parentrow in dataSet.Tables[1].Rows) {
           DataRow[] childrows = parentrow.GetChildRows(relation);

           if (childrows != null && childrows.Length > 0)
              relatedTable.LoadDataRow(parentrow.ItemArray, true);

        }

        relatedTable.EndLoadData();

     }
  }
  catch (Exception ex) {
     Console.WriteLine(ex.Message);
  }

  return relatedTable;
}

Answer 3

I solved problem:

1st dataset--> loop throuhg and get the subsNumber

Call fucntion and pass subsNumber and 2nd dataset--> to it then start another lopp for new dataset

continue if subsnumber don't match if subsNumber match work on that data like add columns to sqlserver table etc.

Thanks for your help guys code:

 foreach (DataRow row in ecommDS.Tables["EcommData"].Rows)
            {

                //string statCode = ""
                string prdCode = "";   //declaring var for getting string format from ecomm
                string checking = "";
                prdCode = row["PRD-CDE"].ToString();
                checking = row["SUBS-NUM"].ToString();

                if(checking != subsNum)
                {
                    continue;
                }