public static string ReplacePipesWithCommas(string sText)
{
string sRes = "";
bool bLock = false;
while (sText.Length > 0)
{
if (bLock)
{
sRes += ",";
}
string sTxt = DBTools.PrepareString(Tools.RetriveSubString(ref sText));
sRes += "'" + sTxt + "'";
bLock = true;
}
return(sRes);
}
/// <summary>
/// Compares and merges two tables with same schema into one table with
/// properly set rowStates for all rows.
/// Both tables must have primary keys.
/// </summary>
/// <param name="originalTable">DataTable containing original data.</param>
/// <param name="changedTable">DataTable containing changed data.</param>
/// <returns>Merged DataTable with properly set rowStates.</returns>
public static DataTable MergeTables(DataTable originalTable, DataTable changedTable)
{
if (originalTable == null || changedTable == null)
{
return(null);
}
DataTable resultTable = originalTable.Clone();
Hashtable hashOrigRows = new Hashtable(originalTable.Rows.Count);
Hashtable hashChangedRows = new Hashtable(changedTable.Rows.Count);
// put changed rows references to hash table
foreach (DataRow changedRow in changedTable.Rows)
{
if (changedRow.RowState == DataRowState.Deleted || changedRow.RowState == DataRowState.Detached)
{
continue;
}
hashChangedRows[GetKeyValues(changedRow)] = changedRow;
}
// chcek for rows deleted in changedTable
foreach (DataRow originalRow in originalTable.Rows)
{
if (originalRow.RowState == DataRowState.Deleted || originalRow.RowState == DataRowState.Detached)
{
continue;
}
hashOrigRows[GetKeyValues(originalRow)] = originalRow;
bool bFound = false;
// check if originalRow exists in changedTable
if (hashChangedRows[GetKeyValues(originalRow)] != null)
{
bFound = true;
}
if (!bFound)
{
// originalRow doesn't exist in changedTable, so it has been removed
DataRow row = resultTable.NewRow();
// copy all data from original row to new row
foreach (DataColumn col in originalTable.Columns)
{
row[col.ColumnName] = originalRow[col.ColumnName];
}
resultTable.Rows.Add(row);
row.AcceptChanges();
// change new row state to Deleted
row.Delete();
}
}
// chcek for rows changed or inserted in changedTable
foreach (DataRow changedRow in hashChangedRows.Values)
{
bool bFound = false;
DataRow originalRow = hashOrigRows[GetKeyValues(changedRow)] as DataRow;
// check if originalRow exists in changedTable
if (originalRow != null)
{
bFound = true;
}
if (!bFound)
{
// changedRow doesn't exist in originalTable, so it has been added
DataRow row = resultTable.NewRow();
// copy all data from changed row to new row
foreach (DataColumn col in changedTable.Columns)
{
row[col.ColumnName] = changedRow[col.ColumnName];
}
resultTable.Rows.Add(row);
row.AcceptChanges();
// change new row state to Added
row.SetAdded();
}
else
{
// changedRow exists in originalTable, it should be unchanged or modified
DataRow row = resultTable.NewRow();
bool bModified = false;
// copy all data from changed row to new row and check if row was modified or is unchanged
foreach (DataColumn col in changedTable.Columns)
{
if (originalRow != null)
{
string originalValue = DBTools.GetRowValue(originalRow, col.ColumnName);
string changedValue = DBTools.GetRowValue(changedRow, col.ColumnName);
if (!originalValue.Equals(changedValue))
{
// original row data differs from changed row
bModified = true;
}
row[col.ColumnName] = originalRow[col.ColumnName];
}
else
{
row[col.ColumnName] = changedRow[col.ColumnName];
}
}
resultTable.Rows.Add(row);
row.AcceptChanges();
// change new row state to Modified
if (bModified)
{
foreach (DataColumn col in changedTable.Columns)
{
row[col.ColumnName] = changedRow[col.ColumnName];
}
}
}
}
return(resultTable);
}