Compare dataset or best idea

Question

How to compare the values ​​of one data set with another.

1st dataset ["correct records"] comes from SQL Server with column names

[id], [subsNumber] 

The 2nd data set ["correct and broken records"] comes from the execution database with different columns except 1, which is subsNumber

How can I go and make another dataset that has all of [subsNumber] from ["correct records"] with corresponding records from 2nd datset ["proper inproper records"]?

or

delete all records in the 2nd data set ["valid and inactive records"] that do not match the "subsNumber" column in the 1st data set

or any other idea

basically How to get all records from a second dataset that has the same "subsNumber" as the first dataset

Answer 1

The key uses System.Data.DataRelation to combine your two data into a common column (or columns).

Here is some code derived from a KC message . See Sharp report.

 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 2

I solved the problem:

1st dataset -> loop throuhg and get subsNumber

Call the function and pass subsNumber and the 2nd data set → to it Then run a new loop for the new data set

Continue if the number of subbands does not match. If subsNumber matches the work on such data, for example, add columns to the 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; } 

Answer 3

To get all the records from the 2nd data set that correspond to the records from the 1st data set, it would be something like this:

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

Something like that!