CODEWITHSUNDEEP
c#wpfdatagridduplicates
10101
10101

Reputation: 2402

Display only duplicates from databases in WPF DataGrid

I have two databases and while comparing them together duplicates can be found. Column NAME is the factor for duplicate detection. There are a lot of other columns and that is the point to getting all duplicates side by side to WPF DataGrid to see other columns - what does not match.

Example to understand the problem.

Database 1:

NRO    NAME      ADDRESS         POSTA
400086 Microsoft AvenuesStreet   [email protected]
400068 Amazon    StreetOfArt     [email protected] 
400059 Google    OperaStreet     [email protected]

Database 2:

NRO    NAME      ADDRESS         POSTA
300081 Microsoft AvenuesStreet   [email protected]
300032 Amazon    Street45        [email protected]
300084 Apple     StreetOfApple   [email protected]

WPF DataGRid should display:

NRO    NAME      ADDRESS         POSTA
400086 Microsoft AvenuesStreet   [email protected]
300081 Microsoft AvenuesStreet   [email protected]
400068 Amazon    StreetOfArt     [email protected] 
300032 Amazon    Street45        [email protected]

I have tried to do it on getting data from database:

private async void Button_Click_1(object sender, RoutedEventArgs e)
{
    try
    {
        ProgressBar.IsIndeterminate = true;

            // HERE COMES MERGE ATTEMPT
            var gridView = await GetDataAsync();
            var collectionView = new ListCollectionView(gridView) as ICollectionView;
            collectionView.Filter = (r) => gridView.where(t => t.Name == r.Name && t != r).Count() >= 2;

            DataGrid1.ItemsSource = collectionView;

        ProgressBar.IsIndeterminate = false;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

private Task<DataView> GetDataAsync()
{
    return Task.Run(() =>
    {

        string connectionStringSE = "Driver={Pervasive ODBC Client Interface};ServerName=DB123;dbq=@DBFSSE;Uid=ADMIN;Pwd=123;";

        string queryStringSE = "select NRO,NAME,NAMEA,NAMEB,ADDRESS,POSTA,POSTN,POSTB,CORPORATION,COUNTRY,ID,ACTIVE from COMPANY";

        string connectionStringFI = "Driver={Pervasive ODBC Client Interface};ServerName=DB123;dbq=@DBFSSE;Uid=ADMIN;Pwd=123;";

        string queryStringFI = "select NRO,NAME,NAMEA,NAMEB,ADDRESS,POSTA,POSTN,POSTB,CORPORATION,COUNTRY,ID,ACTIVE from COMPANY";

        DataTable dataTable = new DataTable("COMPANY");
        // using-statement will cleanly close and dispose unmanaged resources i.e. IDisposable instances
        using (OdbcConnection dbConnectionSE = new OdbcConnection(connectionStringSE))
        {
            dbConnectionSE.Open();
            OdbcDataAdapter dadapterSE = new OdbcDataAdapter();
            dadapterSE.SelectCommand = new OdbcCommand(queryStringSE, dbConnectionSE);

            dadapterSE.Fill(dataTable);

        }
        using (OdbcConnection dbConnectionFI = new OdbcConnection(connectionStringFI))
        {
            dbConnectionFI.Open();
            OdbcDataAdapter dadapterFI = new OdbcDataAdapter();
            dadapterFI.SelectCommand = new OdbcCommand(queryStringFI, dbConnectionFI);

            var newTable = new DataTable("COMPANY");
            dadapterFI.Fill(newTable);

            dataTable.Merge(newTable);

        }

        return dataTable.DefaultView;

    });
}

But this seems to be wrong and I am struggling to figure what is the right way of doing this?

My full code without duplicate detection:

using System.Data.Odbc;
using System.Windows;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System;
using System.Globalization;
using System.Windows.Data;

namespace DB_inspector_FilterTest
{

    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();

        }

        private async void Button_Click_1(object sender, RoutedEventArgs e)
        {
            try
            {
                ProgressBar.IsIndeterminate = true;

                DataGrid1.ItemsSource = await GetDataAsync();

                ProgressBar.IsIndeterminate = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private Task<DataView> GetDataAsync()
        {
            return Task.Run(() =>
            {

                string connectionStringSE = "Driver={Pervasive ODBC Client Interface};ServerName=DB123;dbq=@DBFSSE;Uid=ADMIN;Pwd=123;";

                string queryStringSE = "select NRO,NAME,NAMEA,NAMEB,ADDRESS,POSTA,POSTN,POSTB,CORPORATION,COUNTRY,ID,ACTIVE from COMPANY";

                string connectionStringFI = "Driver={Pervasive ODBC Client Interface};ServerName=DB123;dbq=@DBFSSE;Uid=ADMIN;Pwd=123;";

                string queryStringFI = "select NRO,NAME,NAMEA,NAMEB,ADDRESS,POSTA,POSTN,POSTB,CORPORATION,COUNTRY,ID,ACTIVE from COMPANY";

                DataTable dataTable = new DataTable("COMPANY");
                // using-statement will cleanly close and dispose unmanaged resources i.e. IDisposable instances
                using (OdbcConnection dbConnectionSE = new OdbcConnection(connectionStringSE))
                {
                    dbConnectionSE.Open();
                    OdbcDataAdapter dadapterSE = new OdbcDataAdapter();
                    dadapterSE.SelectCommand = new OdbcCommand(queryStringSE, dbConnectionSE);

                    dadapterSE.Fill(dataTable);

                }
                using (OdbcConnection dbConnectionFI = new OdbcConnection(connectionStringFI))
                {
                    dbConnectionFI.Open();
                    OdbcDataAdapter dadapterFI = new OdbcDataAdapter();
                    dadapterFI.SelectCommand = new OdbcCommand(queryStringFI, dbConnectionFI);

                    var newTable = new DataTable("COMPANY");
                    dadapterFI.Fill(newTable);

                    dataTable.Merge(newTable);

                }

                return dataTable.DefaultView;

            });
        }

        private Dictionary<string, string> _conditions = new Dictionary<string, string>();

        private void UpdateFilter()
        {
            try
            {
                var activeConditions = _conditions.Where(c => c.Value != null).Select(c => "(" + c.Value + ")");
                DataView dv = DataGrid1.ItemsSource as DataView;
                dv.RowFilter = string.Join(" AND ", activeConditions);
            }
            catch (Exception)
            {
                //MessageBox.Show(ex.Message);
            }
        }

        private void NameSearch_TextChanged(object sender, System.Windows.Controls.TextChangedEventArgs e)
        {
            string filter = NameSearch.Text;
            if (string.IsNullOrEmpty(filter))
                _conditions["name"] = null;
            else
                _conditions["name"] = string.Format("NAME Like '%{0}%'", filter);
            UpdateFilter();
        }

        private void ActiveCustomer_Click_1(object sender, RoutedEventArgs e)
        {
            if (ActiveCustomer.IsChecked == true)
            {
                _conditions["active"] = string.Format("ACTIVE Like '%{0}%'", "1");
                UpdateFilter();
            }
            else
            {
                _conditions["active"] = null;
                UpdateFilter();
            }
        }

        private void CheckBox_Click(object sender, RoutedEventArgs e)
        {
            if (OnlyFIandSE.IsChecked == true)
            {
                _conditions["onlyfiandse"] = string.Format("NRO Like '6%' OR NRO Like '7%'");
                UpdateFilter();

            }
            else
            {
                _conditions["onlyfiandse"] = null;
                UpdateFilter();
            }
        }
    }
}

EDIT:

using System.Data.Odbc;
using System.Windows;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System;
using System.Globalization;
using System.Windows.Data;

namespace DB_inspector_FilterTest
{

    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();

        }

        private async void Button_Click_1(object sender, RoutedEventArgs e)
        {
            try
            {
                ProgressBar.IsIndeterminate = true;

                DataGrid1.ItemsSource = await GetDataAsync();

                ProgressBar.IsIndeterminate = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private Task<DataView> GetDataAsync()
        {
            return Task.Run(() =>
            {

                string connectionStringSE = "Driver={Pervasive ODBC Client Interface};ServerName=DB123;dbq=@DBFSSE;Uid=ADMIN;Pwd=123;";

                string queryStringSE = "select NRO,NAME,NAMEA,NAMEB,ADDRESS,POSTA,POSTN,POSTB,CORPORATION,COUNTRY,ID,ACTIVE from COMPANY";

                string connectionStringFI = "Driver={Pervasive ODBC Client Interface};ServerName=DB123;dbq=@DBFSSE;Uid=ADMIN;Pwd=123;";

                string queryStringFI = "select NRO,NAME,NAMEA,NAMEB,ADDRESS,POSTA,POSTN,POSTB,CORPORATION,COUNTRY,ID,ACTIVE from COMPANY";

                DataTable dataTable = new DataTable("COMPANY");
                // using-statement will cleanly close and dispose unmanaged resources i.e. IDisposable instances
                using (OdbcConnection dbConnectionSE = new OdbcConnection(connectionStringSE))
                {
                    dbConnectionSE.Open();
                    OdbcDataAdapter dadapterSE = new OdbcDataAdapter();
                    dadapterSE.SelectCommand = new OdbcCommand(queryStringSE, dbConnectionSE);

                    dadapterSE.Fill(dataTable);

                }
                using (OdbcConnection dbConnectionFI = new OdbcConnection(connectionStringFI))
                {
                    dbConnectionFI.Open();
                    OdbcDataAdapter dadapterFI = new OdbcDataAdapter();
                    dadapterFI.SelectCommand = new OdbcCommand(queryStringFI, dbConnectionFI);

                    var newTable = new DataTable("COMPANY");
                    dadapterFI.Fill(newTable);

                    dataTable.Merge(newTable);

                    var duplicates = dataTable.AsEnumerable().GroupBy(col => col[2]).Where(gr => gr.Count() > 1).ToList();

                    duplicates.ForEach(i => Console.Write("{0}\t", i));

                    Console.Read();

                }

                return dataTable.DefaultView;

            });
        }

        private Dictionary<string, string> _conditions = new Dictionary<string, string>();

        private void UpdateFilter()
        {
            try
            {
                var activeConditions = _conditions.Where(c => c.Value != null).Select(c => "(" + c.Value + ")");
                DataView dv = DataGrid1.ItemsSource as DataView;
                dv.RowFilter = string.Join(" AND ", activeConditions);
            }
            catch (Exception)
            {
                //MessageBox.Show(ex.Message);
            }
        }

        private void NameSearch_TextChanged(object sender, System.Windows.Controls.TextChangedEventArgs e)
        {
            string filter = NameSearch.Text;
            if (string.IsNullOrEmpty(filter))
                _conditions["name"] = null;
            else
                _conditions["name"] = string.Format("NAME Like '%{0}%'", filter);
            UpdateFilter();
        }

        private void ActiveCustomer_Click_1(object sender, RoutedEventArgs e)
        {
            if (ActiveCustomer.IsChecked == true)
            {
                _conditions["active"] = string.Format("ACTIVE Like '%{0}%'", "1");
                UpdateFilter();
            }
            else
            {
                _conditions["active"] = null;
                UpdateFilter();
            }
        }

        private void CheckBox_Click(object sender, RoutedEventArgs e)
        {
            if (OnlyFIandSE.IsChecked == true)
            {
                _conditions["onlyfiandse"] = string.Format("NRO Like '6%' OR NRO Like '7%'");
                UpdateFilter();

            }
            else
            {
                _conditions["onlyfiandse"] = null;
                UpdateFilter();
            }
        }
    }
}

Upvotes: 0

Views: 91

Answers (1)

mm8
mm8

Reputation: 169160

If you only want to return duplicate rows, you could remove all rows that doesn't have a matching row with the same name in your GetDataAsync() method like this:

...
return dataTable.AsEnumerable()
              .GroupBy(x => x.Field<string>("NAME"))
              .Where(x => x.Count() > 1)
              .SelectMany(x => x)
              .CopyToDataTable().DefaultView;

Upvotes: 1

Related Questions