文章/答案/技术大牛

发布

社区首页 >问答首页 >在WPF DataGrid中只显示来自数据库的副本。

问在WPF DataGrid中只显示来自数据库的副本。
EN

Stack Overflow用户

提问于 2020-02-22 09:56:44

回答 1查看 66关注 0票数 0

我有两个数据库，在比较它们时，可以找到重复的数据库。列NAME是重复检测的因素。还有许多其他列，这就是让所有重复的DataGrid并排查看其他列的关键所在--不匹配的列。

了解问题的例子。

数据库1：

NRO    NAME      ADDRESS         POSTA
400086 Microsoft AvenuesStreet   microsoft@micorosoft.com
400068 Amazon    StreetOfArt     amazonging@amazon.com 
400059 Google    OperaStreet     microsoft@micorosoft.com

数据库2：

NRO    NAME      ADDRESS         POSTA
300081 Microsoft AvenuesStreet   microsoftAS@micorosoft.com
300032 Amazon    Street45        zinomaz@amazon.com
300084 Apple     StreetOfApple   apple@apple.com

WPF DataGRid应该显示：

NRO    NAME      ADDRESS         POSTA
400086 Microsoft AvenuesStreet   microsoft@micorosoft.com
300081 Microsoft AvenuesStreet   microsoftAS@micorosoft.com
400068 Amazon    StreetOfArt     amazonging@amazon.com 
300032 Amazon    Street45        zinomaz@amazon.com

我试着从数据库中获取数据：

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;

    });
}

但这似乎是错误的，我正在努力弄清楚什么是正确的方式来做到这一点？

我的完整代码没有重复检测：

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

编辑：

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

wpf

datagrid

duplicates

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-02-24 13:51:16

如果只想返回重复行，可以删除GetDataAsync()方法中没有匹配行的所有行，如下所示：

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

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/60350967

复制

相似问题

问在WPF DataGrid中只显示来自数据库的副本。
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在WPF DataGrid中只显示来自数据库的副本。EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在WPF DataGrid中只显示来自数据库的副本。
EN