.Net DataTable.Merge

The DataTable class has a Merge property that lets you merge two data tables. It sounds like an useful method, but how exactly does it resolve row state conflicts? Does it do what you want?

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>

The only interesting thing in the code is that I've overridden the default DataViews to include deleted records. Normally they are filtered out.

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));

        }

    }

}

If we run the application we see the default behavior which is to return all rows in both tables.

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

Document Thumbnails

I was wondering how hard it would be to show thumbnails of attachments the user had added to my accounts payable and purchasing applications. Some analysis shows the vast majority are pdf files, with image files a distant second. There are also some Office documents and emails but they total less than five percent of all the attachments my users have saved.

I found a reasonably priced product ($600) that will create an image of a pdf page and has a free demo version. The product is called Spire.pdf. Other products such as ExpertPdf and ActivePdf don't appear to offer this feature.

Creating a thumbnail from an image file is trivial and requires no special downloads.

I also wanted to leverage the Shell dll to display application icons for the other file types.

Start a new WPF project called FileThumbnails. I'm using Visual Studio 2019, C#, and targeting Framework 4.7.

Start by adding the Spire demo product using NuGet. Browse to Tools -> NuGet Package Manager -> Manage NugetPackages for Solution. Click Browse and search for Spire.pdf. Install FreeSpire.pdf.

Now we need to install the windows api code pack. Select Browse and search for microsoft.windowsapicodepack.shell. Pick the one from Microsoft and install it into the project.

Now add a reference to System.Drawing.

The application will list three files (substitute your own). Each one will have a tooltip which is either a thumbnail of the first page of the file or the icon of the owning application.

Here's the XAML.

<Window x:Class="FileThumbnails.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:FileThumbnails"

        mc:Ignorable="d"

        DataContext="{Binding RelativeSource={RelativeSource Self}}"

        Title="MainWindow" Height="450" Width="800">

    <StackPanel Orientation="Vertical">

        <TextBlock Text="{Binding pdfFileName}">

            <TextBlock.ToolTip>

                <Image Source="{Binding pdfTooltip}" Stretch="Uniform" Width="200"/>

            </TextBlock.ToolTip>

        </TextBlock>

        <TextBlock Text="{Binding imgFileName}">

            <TextBlock.ToolTip>

                <Image Source="{Binding imgTooltip}" Stretch="Uniform" Width="200"/>

            </TextBlock.ToolTip>

        </TextBlock>

        <TextBlock Text="{Binding otherFileName}">

            <TextBlock.ToolTip>

                <Image Source="{Binding otherTooltip}" Stretch="Uniform" Width="200"/>

            </TextBlock.ToolTip>

        </TextBlock>

    </StackPanel>

</Window>

By specifying Stretch="Uniform" Width="200" we cause the thumbnail to maintain its aspect ratio and resize to 200 width.

Here's the code-behind. We'll complete the interesting bits later.

using System;

using System.Windows;

using System.Windows.Media.Imaging;

using Microsoft.WindowsAPICodePack.Shell;

using System.Drawing;

using Spire.Pdf;

using System.IO;

using System.Drawing.Imaging;

using System.ComponentModel;

namespace FileThumbnails

{

    public partial class MainWindow : Window, INotifyPropertyChanged

    {

        public String pdfFileName { get; set; }

        public BitmapImage pdfTooltip

        {

            get { return GetToolTip(pdfFileName); }

        }

        public String imgFileName { get; set; }

        public BitmapImage imgTooltip

        {

            get

            {

                return GetToolTip(imgFileName);

            }

        }

        public String otherFileName { get; set; }

        public BitmapImage otherTooltip

        {

            get

            {

                return GetToolTip(otherFileName);

            }

        }

        public MainWindow()

        {

            pdfFileName = @"C:\Users\me\documents\Expectations.pdf";

            imgFileName = @"C:\Users\me\documents\20190421.jpg";

            otherFileName = @"C:\Users\me\documents\ELTS.docx";

            InitializeComponent();

        }

        public BitmapImage GetToolTip(String FileName)

        {

            String Extension = System.IO.Path.GetExtension(FileName).ToUpper();

            switch (Extension)

            {

                case ".PDF": return GetPDFThumbnail(FileName);

                case ".PNG":

                case ".BMP":

                case ".JPG": return GetImageThumbnail(FileName);

                default: return GetApplicationIcon(FileName);

            }

        }

        private BitmapImage GetPDFThumbnail(String FileName)

        {

        }

        private BitmapImage GetImageThumbnail(String FileName)

        {

        }

        private BitmapImage GetApplicationIcon(String FileName)

        {

        }

        public event PropertyChangedEventHandler PropertyChanged;

        public void PropChanged(string name)

        {

            if (PropertyChanged != null)

                PropertyChanged(this, new PropertyChangedEventArgs(name));

        }

    }

}

Let's start with the pdf.

private BitmapImage GetPDFThumbnail(String FileName)

{

    PdfDocument doc = new PdfDocument();

    doc.LoadFromFile(pdfFileName);

    Bitmap bmp = (Bitmap)doc.SaveAsImage(0);

    using (MemoryStream ms = new MemoryStream())

    {

        bmp.Save(ms, ImageFormat.Png);

        ms.Position = 0;

        BitmapImage bmi = new BitmapImage();

        bmi.BeginInit();

        bmi.StreamSource = ms;

        bmi.CacheOption = BitmapCacheOption.OnLoad;

        bmi.EndInit();

        return bmi;

    }

}

PdfDocument is a Spire.pdf class. It's very easy to load it with a pdf file and get a bitmap of the first page. We have to convert that into a BitmapImage in order to use it as a tooltip. Most of the complexity is there.

Now the image, which is trivial.

private BitmapImage GetImageThumbnail(String FileName)

{

    BitmapImage bmi = new BitmapImage();

    bmi.BeginInit();

    bmi.UriSource = new Uri(FileName);

    bmi.DecodePixelWidth = 200;

    bmi.EndInit();

    return bmi;

}

The only interesting thing here is bmi.DecodePixelWidth = 200 which speeds up the resizing.

The last method we need to complete fetches the icon of the owner of the specified file type and converts that to a BitmapImage. Again - the bulk of the code is involved with converting from one type of image to another.

private BitmapImage GetApplicationIcon(String FileName)

{

    BitmapSource bms = ShellFile.FromFilePath(FileName).Thumbnail.BitmapSource;

    JpegBitmapEncoder encoder = new JpegBitmapEncoder();

    MemoryStream ms = new MemoryStream();

    BitmapImage bmi = new BitmapImage();

    encoder.Frames.Add(BitmapFrame.Create(bms));

    encoder.Save(ms);

    ms.Position = 0;

    bmi.BeginInit();

    bmi.StreamSource = new MemoryStream(ms.ToArray());

    bmi.EndInit();

    return bmi;

}

Here are the results.

Image tool tip

Other tool tip

Pdf tool tip