It seems the algorithm is asymmetrical - it matters which data table is being merged into which.
If the two data tables have the same schema then there is only one parameter that matters. Its cryptically called "preserve changes" and the Microsoft documentation doesn't explain what it does very well.
One thing that's important is to define primary keys on your data tables. If you don't do that then the merge simply appends all the rows from one data table to the other data table.
I've created a demo that shows how it works. You can alter the value of "preserve changes", optionally specify primary keys, and switch the two data tables. The only tricky part is you can't display deleted row in a data grid so each of the data tables has a display version which is suitable to be bound to a data grid.
Start a new WPF project called DatatableMerge in C#. I'm using Visual Studio 2019 and targeting framework 4.7.
There are three checkboxes that control options, then two data grids side-by-side, and a results grid below. The code will merge the right hand data table into the left hand data table and display the results below.
The XAML looks like this.
<Window x:Class="DatatableMerge.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:local="clr-namespace:DatatableMerge"
mc:Ignorable="d"
DataContext="{Binding RelativeSource={RelativeSource Self}}"
Title="Merge
right datatable into left datatable" Height="450" Width="800">
<Grid>
<Grid.RowDefinitions>
<RowDefinition Height="auto"/>
<RowDefinition Height="*"/>
<RowDefinition Height="*"/>
</Grid.RowDefinitions>
<Grid.ColumnDefinitions>
<ColumnDefinition Width="*"/>
<ColumnDefinition Width="*"/>
<ColumnDefinition Width="2*"/>
</Grid.ColumnDefinitions>
<CheckBox Grid.Row="0" Grid.Column="0" Content="Preserve
Changes" IsChecked="{Binding preserveChanges}"/>
<CheckBox Grid.Row="0" Grid.Column="1" Content="With
Primary Key" IsChecked="{Binding withPrimaryKey}"/>
<CheckBox Grid.Row="0" Grid.Column="2" Content="Switch tables" IsChecked="{Binding switchTables}"/>
<DataGrid Grid.Row="1" Grid.Column="0" Grid.ColumnSpan="2" ItemsSource="{Binding dtDisplay1}"
AutoGenerateColumns="False" IsReadOnly="True">
<DataGrid.Columns>
<DataGridTextColumn Binding="{Binding RowState}"/>
<DataGridTextColumn Binding="{Binding ID}"/>
<DataGridTextColumn Binding="{Binding Code}"/>
<DataGridTextColumn Binding="{Binding Description}" Width="*"/>
</DataGrid.Columns>
</DataGrid>
<DataGrid Grid.Row="1" Grid.Column="2" ItemsSource="{Binding dtDisplay2}"
AutoGenerateColumns="False" IsReadOnly="True">
<DataGrid.Columns>
<DataGridTextColumn Binding="{Binding RowState}"/>
<DataGridTextColumn Binding="{Binding ID}"/>
<DataGridTextColumn Binding="{Binding Code}"/>
<DataGridTextColumn Binding="{Binding Description}" Width="*"/>
</DataGrid.Columns>
</DataGrid>
<DataGrid Grid.Row="2" Grid.Column="0" Grid.ColumnSpan="3" ItemsSource="{Binding dtDisplayResult}" AutoGenerateColumns="False" IsReadOnly="True">
<DataGrid.Columns>
<DataGridTextColumn Binding="{Binding RowState}"/>
<DataGridTextColumn Binding="{Binding ID}"/>
<DataGridTextColumn Binding="{Binding Code}"/>
<DataGridTextColumn Binding="{Binding Description}" Width="*"/>
</DataGrid.Columns>
</DataGrid>
</Grid>
</Window>
using System.ComponentModel;
using System.Data;
using System.Windows;
namespace DatatableMerge
{
public partial class MainWindow : Window, INotifyPropertyChanged
{
public DataTable dt1;
public DataTable dt2;
public DataTable dtResult;
public DataView dvDisplay1 { get; set; }
public DataView dvDisplay2 { get; set; }
public DataView dvDisplayResult { get; set; }
private bool
_preserveChanges;
public bool
preserveChanges
{
get { return
_preserveChanges; }
set
{
_preserveChanges = value;
PropChanged("preserveChanges");
DoMerge();
}
}
private bool
_switchTables;
public bool switchTables
{
get { return
_switchTables; }
set
{
_switchTables = value;
PropChanged("switchTables");
Refresh();
}
}
private bool
_withPrimaryKey;
public bool
withPrimaryKey
{
get { return
_withPrimaryKey; }
set
{
_withPrimaryKey = value;
PropChanged("withPrimaryKey");
Refresh();
}
}
public MainWindow()
{
Refresh();
InitializeComponent();
}
private void
Refresh()
{
dt1 = new DataTable();
dt2 = new DataTable();
dtResult = new DataTable();
dt1.Columns.Add(new DataColumn("ID", typeof(int)));
dt1.Columns.Add(new DataColumn("Code", typeof(string)));
dt1.Columns.Add(new DataColumn("Description", typeof(string)));
dt1.TableName = "dt1";
dt2 = dt1.Clone();
dt2.TableName = "dt2";
dtResult = dt1.Clone();
dtResult.TableName = "dtResult";
if (!switchTables)
{
dt1 = PopulateSet1();
dt2 = PopulateSet2();
}
else
{
dt1 = PopulateSet2();
dt2 = PopulateSet1();
}
dvDisplay1 = new DataView(dt1, "", "",
DataViewRowState.CurrentRows | DataViewRowState.Deleted);
dvDisplay2 = new DataView(dt2, "", "",
DataViewRowState.CurrentRows | DataViewRowState.Deleted);
if (withPrimaryKey)
{
dt1.PrimaryKey = new DataColumn[] { dt1.Columns[0] };
dt2.PrimaryKey = new DataColumn[] { dt2.Columns[0] };
dtResult.PrimaryKey = new DataColumn[] { dtResult.Columns[0]
};
}
PropChanged("dvDisplay1");
PropChanged("dvDisplay2");
DoMerge();
}
private DataTable PopulateSet1()
{
DataTable dt = dt1.Clone();
dt.Rows.Add(1, "Apples", "Fresh rosy apples just off the tree");
dt.Rows.Add(2, "Bananas", "Get your hands on my loverly bananas");
dt.AcceptChanges();
dt.Rows[1].Delete();
dt.Rows.Add(3, "Cherries", "I've got the sweetest bunches of cherries ever");
return dt;
}
private DataTable PopulateSet2()
{
DataTable dt = dt2.Clone();
dt.Rows.Add(1, "Apples", "Fresh rosy apples just off the tree");
dt.Rows.Add(2, "Bananas", "Get your hands on my loverly bananas");
dt.AcceptChanges();
dt.Rows[1]["Description"] = "Get your hands on my lovely bananas";
dt.Rows.Add(4, "Dates", "Date shakes, date candy, dates for every day");
return dt;
}
private void
DoMerge()
{
dtResult = dt1.Copy();
dtResult.Merge(dt2,
preserveChanges);
dvDisplayResult = new DataView(dtResult, "", "",
DataViewRowState.CurrentRows | DataViewRowState.Deleted);
PropChanged("dvDisplayResult");
}
public event
PropertyChangedEventHandler PropertyChanged;
public void PropChanged(string name)
{
if (PropertyChanged != null)
PropertyChanged(this, new PropertyChangedEventArgs(name));
}
}
}
 |
| No primary key - dumb merge |
Now check "With Primary Key" and see an intelligent merge. Without "Preserve Changes" the data table on the right has precedence ie. dt1.Merge(dt2, false)
 |
| Bananas comes from the right |
Now check "Preserve Changes" and see how the row state of Bananas changes. You can see the data table on the left now has precedence ie. dt1.Merge(dt2, true). I don't know why the row state of Apples changes.
 |
| With "Preserve Changes" the data table on the left has precedence |
No comments:
Post a Comment