How to check a dataset belongs to another dataset

Question

I have one dataset which contains Student Fee Structure of a single FeeID as mentioned below:

        FeeID   Amount    FeeItem             Type
      *---------------------------------------------*
        10      7500      Admission Fee        T
        10      900       Annual Fee           T
        10      150       Application Fee      T
        10      850       Boy's Uniform        T
        10      50        Computer Fee         R

For example I have another dataset having following data:

        FeeID   Amount    FeeItem             Type
      *---------------------------------------------*
        9       8500      Admission Fee        T
        9       950       Annual Fee           T
        9       150       Application Fee      T
        9       850       Boy's Uniform        T
        9       50        Computer Fee         R
        11      7500      Admission Fee        T
        11      900       Annual Fee           T
        11      150       Application Fee      T
        11      850       Boy's Uniform        T
        11      50        Computer Fee         R

I want to check whether the set comprising of last three columns belongs to another dataset that contains data of all Fee Structures where FeeID may vary. Actual I want to retrieve matching FeeID having same Fee Structure.

In the above example if I search for the first one in the second it will return True and matching FeeID will be 11.

Answer 1

Edit: I've made ade some improvements to below code. Following DataTable.IndexOf(otherTable) extension method checks if a table is a subset of another table.

You can pass column names that you want to ignore in the comparison. The order of the columns does not matter but the order of DataRows(the subset must have the same order of rows).

It returns -1 when no equal subset of DataRows was found or the first index in the main-table(this in the extension) where the equal subset starts.

public static class DataTableExtensions
{
    public static int IndexOf(this DataTable tblMain, DataTable tblSub, params String[] ignoreColumns)
    {
        if (tblMain == null)
            throw new ArgumentNullException("tblMain");
        if (tblSub.Rows.Count == 0)
            throw new ArgumentException("tblSub must not be empty", "tblSub");

        if (tblSub.Rows.Count > tblMain.Rows.Count)
            return -1;

        IEnumerable<String> relevantColumnNames = tblSub.Columns.Cast<DataColumn>()
            .Select(c => c.ColumnName)
            .Except(ignoreColumns)
            .ToArray();

        foreach (String colName in relevantColumnNames)
            if (!tblMain.Columns.Contains(colName))
                return -1;

        for (int mainRowIndex = 0; tblMain.Rows.Count - mainRowIndex >= tblSub.Rows.Count; mainRowIndex++)
        {
            // check if current window is equal to tblSub
            bool allRowsAreEqual = true;
            for (int windowIndex = mainRowIndex; windowIndex < tblSub.Rows.Count + mainRowIndex; windowIndex++)
            {
                DataRow currentMain = tblMain.Rows[windowIndex];
                DataRow currentSub = tblSub.Rows[windowIndex - mainRowIndex];
                bool allFieldsAreEqual = relevantColumnNames.All(colName =>
                    Object.Equals(currentMain[colName], currentSub[colName]));
                if (!allFieldsAreEqual)
                {
                    allRowsAreEqual = false;
                    break; // continue with next window in main-table
                }
            }
            if (allRowsAreEqual)
                return mainRowIndex;
        }
        // no equal window found in main-table
        return -1;
    }
}

Your sample data:

var TblSub = new DataTable();
var TblMain = new DataTable();
TblSub.Columns.Add("FeeID", typeof(int));
TblSub.Columns.Add("Amount", typeof(int));
TblSub.Columns.Add("FeeItem", typeof(string));
TblSub.Columns.Add("Type", typeof(char));
TblMain.Columns.Add("FeeID", typeof(int));
TblMain.Columns.Add("Amount", typeof(int));
TblMain.Columns.Add("FeeItem", typeof(string));
TblMain.Columns.Add("Type", typeof(char));

TblSub.Rows.Add(10, 7500, "Admission Free", 'T');
TblSub.Rows.Add(10, 900, "Annual Fee", 'T');
TblSub.Rows.Add(10, 150, "Application Free", 'T');
TblSub.Rows.Add(10, 850, "Boy's Uniform", 'T');
TblSub.Rows.Add(10, 50, "Computer Free", 'R');

TblMain.Rows.Add(9, 8500, "Admission Free", 'T');
TblMain.Rows.Add(9, 950, "Annual Fee", 'T');
TblMain.Rows.Add(9, 150, "Application Free", 'T');
TblMain.Rows.Add(9, 850, "Boy's Uniform", 'T');
TblMain.Rows.Add(9, 50, "Computer Free", 'R');
TblMain.Rows.Add(10, 7500, "Admission Free", 'T');
TblMain.Rows.Add(11, 900, "Annual Fee", 'T');
TblMain.Rows.Add(11, 150, "Application Free", 'T');
TblMain.Rows.Add(11, 850, "Boy's Uniform", 'T');
TblMain.Rows.Add(11, 50, "Computer Free", 'R');

You can use it in this way:

int firstIndex = TblMain.IndexOf(TblSub, "FeeID");
if (firstIndex == -1)
    Console.Write("Second table does not contain first table");
else
    Console.Write("Second table does contain first table at row-index " + firstIndex);

Output is:

Second table does contain first table at row-index 5

---

old approach:

You could use following method which checks whether or not two DataTables are equal.

You need to add using System.Linq for Enumerable.SequenceEqual. If you cannot use Linq, use loops.

static bool TablesEqual(DataTable table1, DataTable table2, params int[] skipColumns)
{
    if (table1.Rows.Count != table2.Rows.Count)
        return false;

    for (int i = 0; i < table1.Rows.Count; i++)
    {
        var array1 = table1.Rows[i].ItemArray
            .Where((obj, index) => !skipColumns.Contains(index));
        var array2 = table2.Rows[i].ItemArray
            .Where((obj, index) => !skipColumns.Contains(index)); ;
        if (!array1.SequenceEqual(array2))
        {
            return false;
        }
    }
    return true;
}

You just have to pass the indices of the columns you want o ignore, like:

bool allEqual = TablesEqual(t1, t2); // all equal
bool equalIgnore0 = TablesEqual(t1, t2, 0); // ignore first column
bool equalIgnore0and2 = TablesEqual(t1, t2, 0, 2); // ignore first and third column

Answer 2

Finally I am able to solve this issue. The following is my function. I am using the function TablesEqual as provided by Tim Schmelter. The function will return a valid FeeID which is a positive integer else -1 if no match is found.

private int MatchFeeID(DataTable Dt)
{
  DataTable mainDt = bl.GetDataSet("Select * From FeeMaster"); //assume this is a function that will return all the fee structures from database.

  var fids = (from row in mainDt.AsEnumerable().Distinct()
            group row by row.Field<string>("fid") into rowGroup
            select new
            {
              fid = rowGroup.Key
            });

  foreach (var fid in fids)
  {
    string id = fid.fid;

    DataTable t1 = new DataTable();
    DataTable t2 = new DataTable();

    DataRow[] dr1 = mainDt.Select(String.Format("fid = '{0}'", id));

    t1 = dr1.CopyToDataTable();
    t2 = Dt;

    bool res = TablesEqual(t1, t2, 0, 1);
    if (res) return Convert.ToInt32(id);
  }

  return -1;
}

Answer 3

You can use this trick

Dataset ds1
DataSet ds2
DataSet dest;
dest.Merge(ds1);
dest.AcceptChanges();
dest.Merge(ds2);
diff = destination.GetChanges()

and check if diff is empty