none
DataRelation problem with more than 32 columns - Comparing two different datatables RRS feed

  • Question

  • Hi Team,

    1. Task to compare two different source of database with same set of tables.
    2. So select queries are made dynamic and getting the DataSet, which will hold maximum of 5 tables.
    3. I need to do deep comparison, so taking Source Environment's DataTable1 and compare with Target1 Environment DataTable1, Target2 Environment DateTable1
    4. I've done the comparison with the help of DataRelation, but facing issues with more than 32 columns.
    5. Is this is the limitation or Am I doing any wrong implementation.
    6. With some online reference written this code. Please help on this

    Source:

    public DataTable CompareDataTables(DataTable first, DataTable second)
            {
                first.TableName = Guid.NewGuid().ToString();
                second.TableName = Guid.NewGuid().ToString();

                //Create Empty Table
                DataTable table = new DataTable(Guid.NewGuid().ToString());

                try
                {
                    //Must use a Dataset to make use of a DataRelation object
                    using (DataSet ds = new DataSet())
                    {
                        var firstCopy = first.Copy();
                        firstCopy.TableName = Guid.NewGuid().ToString();
                        var secondCopy = second.Copy();
                        secondCopy.TableName = Guid.NewGuid().ToString();
                        //Add tables
                        ds.Tables.AddRange(new DataTable[] { firstCopy, secondCopy });

                        //renaming the table
                        for (int i = 0; i < ds.Tables.Count; i++)
                        {
                            ds.Tables[i].TableName = Guid.NewGuid().ToString();
                        }

                        //Get Columns for DataRelation
                        DataColumn[] firstcolumns = new DataColumn[ds.Tables[0].Columns.Count];

                        for (int i = 0; i < firstcolumns.Length; i++)
                        {
                            firstcolumns[i] = ds.Tables[0].Columns[i];
                        }

                        DataColumn[] secondcolumns = new DataColumn[ds.Tables[1].Columns.Count];

                        for (int i = 0; i < secondcolumns.Length; i++)
                        {
                            secondcolumns[i] = ds.Tables[1].Columns[i];
                        }

                        //Create DataRelation
                        DataRelation r = new DataRelation(string.Empty, secondcolumns, firstcolumns, false);

                        ds.Relations.Add(r);

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

                        //If First Row not in Second, Add to return table.
                        table.BeginLoadData();

                        foreach (DataRow parentrow in ds.Tables[1].Rows)
                        {
                            DataRow[] childrows = parentrow.GetChildRows(r);
                            if (childrows == null || childrows.Length == 0)
                                table.LoadDataRow(parentrow.ItemArray, true);
                        }

                        table.EndLoadData();
                    }
                }
                catch (Exception ex)
                {
                    throw;
                }

                first.TableName = Guid.NewGuid().ToString();
                second.TableName = Guid.NewGuid().ToString();

                var approximateDiff = first.Rows.Count - second.Rows.Count;

                if ((table.Rows.Count == 0 || approximateDiff > table.Rows.Count) && !(first.Rows.Count == second.Rows.Count))
                {
                    var initialDiff = table;
                    var swapA = first;
                    first = second;
                    second = swapA;
                    initialDiff.Merge(CompareDataTables(first, second));
                    return initialDiff;
                }

                return table;
            }

    Thanks,

    Anil.


    Wednesday, May 16, 2018 9:46 AM

All replies

  • Hi Anilsurendar,

    According to your description and related code, I create a simple and reproduce your issue on my side, it is a by design issue, but based on your requirement, I think that you could datatable.Merge to achieve it. like this:

    public DataTable CompareDataTables(DataTable first, DataTable second) { first.Merge(second);

    // return different. //return first.GetChanges();

    //return all the merged records.

    return first; }

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 17, 2018 2:28 AM
    Moderator