How to compare(Row and Column) two DataTable and get the unique records

1
votes

I have 2 DataTable

string query = "Select * from " + ServerTableName;
DataTable oDtSeverData = GetDataTable(query);

string dbQuery = "SELECT * from " + LocalSystemTableName;
DataTable oDtLocalSystemData = dataService.GetDataTable(dbQuery);

I want to compare(Row and Column) both the datatable, to get same column(exist in both the datatable) with Unique records(unique Row).

Let me explain in details:

oDtServerData have columns (Column1, Column2, Column3, Column4) with few rows. oDtLocalSystemData have columns (Column1, Column2, Column3) with few rows.

it is possible that oDtServerData can have less columns and oDtLocalSystemData. but in any case I want the column (Column1, Column2, Column3) which is matching in both the datatable with unique rows(data should be unique).

Someone please help me in this and give me some idea with few examples to solve my problems.

2
c#datatablecompare
Are the tables expected to have the same structure? Is there a primary key? – Richard
no there is not primary key in table. it is dynamically created table from 2 different place. but yes, max structure(columns) would be same. – Rocky
Without any kind of key, you would be stuck iterating through all the rows in one DataTable and then checking to see if they exist in the second DataTable, either by doing a SQL query against the data source, or by using the DataTable's Find method. Both are likely to be very costly and slow. – Richard
I know it can be slow, but I don't have other choice. Its windows application user can wait. I am not using any Session kind of things – Rocky
Ok now I have made one column as primary key, now can any one pls tell me the solution – Rocky

2 Answers

3
votes

you can use the below code to compare two DataTable,

public static DataTable CompareTwoDataTable(DataTable dt1, DataTable dt2)
{ 
  dt1.Merge(dt2);
  DataTable d3 = dt2.GetChanges();
  return d3;
}

For more information about DataTable.Merge(), please refer to DataTable.Merge Method (DataTable) on MSDN.

0
votes
ArrayList commonColumns = new ArrayList();

for (int iServerColumnCount = 0; iServerColumnCount < oDtSeverData .Columns.Count; iServerColumnCount ++)
{
  for (int iLocalColumnCount = 0;
                             iLocalColumnCount < oDtLocalSystemData .Columns.Count;
                             iLocalColumnCount ++)
    {
      if (oDtSeverData .Columns[iServerColumnCount ].ColumnName.ToString()
             .Equals(oDtLocalSystemData .Columns[iLocalColumnCount].ColumnName.ToString()))
      {
         commonColumns.Add(oDtLocalSystemData .Columns[iLocalColumnCount].ColumnName.ToString());
      }
    }
}

DataTable oDtData = CompareTwoDataTable(oDtLocalSystemData, oDtSeverData,commonColumns);

public DataTable CompareTwoDataTable(DataTable dtOriginalTable, DataTable dtNewTable, ArrayList columnNames)
    {
        DataTable filterTable = new DataTable();
        try
        {
            filterTable = dtNewTable.Copy();
            string filterCriterial;
            if (columnNames.Count > 0)
            {
                for (int iNewTableRowCount = 0; iNewTableRowCount < dtNewTable.Rows.Count; iNewTableRowCount++)
                {
                    filterCriterial = string.Empty;
                    foreach (string colName in columnNames.ToArray())
                    {

                        filterCriterial += "ISNULL("+colName.ToString() + ",'')='" + dtNewTable.Rows[iNewTableRowCount][colName].ToString() + "' AND ";
                    }
                    filterCriterial = filterCriterial.TrimEnd((" AND ").ToCharArray());
                    DataRow[] dr = dtOriginalTable.Select(filterCriterial);
                    if (dr.Length > 0)
                    {
                        filterTable.Rows[filterTable.Rows.IndexOf(filterTable.Select(filterCriterial)[0])].Delete();
                        filterTable.AcceptChanges();
                    }
                }
            }

        }
        catch (Exception ex)
        {
        }

        return filterTable;
    }

I was trying to insert data to table to I used bulk insert for that i used same common column

 public bool BulkInsertDataTable(string tableName, DataTable dataTable, string[] commonColumns)
    {
        bool isSuccuss;
        try
        {

            SqlConnection SqlConnectionObj = GetSQLConnection();
            SqlBulkCopy bulkCopy = new SqlBulkCopy(SqlConnectionObj, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null);
            bulkCopy.DestinationTableName = tableName;
            bulkCopy.ColumnMappings.Clear();
            for (int iDtColumnCount = 0; iDtColumnCount < dataTable.Columns.Count; iDtColumnCount++)
            {
                for (int iArrCount = 0; iArrCount < commonColumns.Length; iArrCount++)
                {
                    if (dataTable.Columns[iDtColumnCount].ColumnName.ToString().Equals(commonColumns[iArrCount].ToString()))
                    {
                        bulkCopy.ColumnMappings.Add(dataTable.Columns[iDtColumnCount].ColumnName.ToString(),
                                                    commonColumns[iArrCount].ToString());
                    }
                }
            }

            bulkCopy.WriteToServer(dataTable);
            isSuccuss = true;
        }
        catch (Exception ex)
        {
            isSuccuss = false;
        }
        return isSuccuss;
    }