Compare dataset or a better idea

问题:

How do I compare values of one data set from another.

1st dataset ["proper records"] is coming from SQL Server with column names

 [id], [subsNumber]

2nd dataset ["proper and inproper records"] is coming from progress database, with different columns 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

回答1:

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;
}

回答2:

I solved the problem:

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

Call function and pass subsNumber and 2nd dataset--> to it Then start another loop for new dataset

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

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;
     }

回答3:

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!