如何实现新增数据自动同步显示至DataGrid？

我的应用从邮件收集数据，存储到数据库后在DataGrid中展示和操作，要求仅保留原始数据无重复。现在希望每次保存新对象时，无需手动操作就能直接在DataGrid中显示该数据，但目前只有点击按钮后才会加载数据。

窗口初始化代码 #

public AdminWindow()
{
    InitializeComponent();
    LoadAlertGrid();
    Task.Run(() => Test(new EmailParser(true, "abc", "defg")));
}

数据提取去重保存方法 #

public void Test(EmailParser emailParser)
{
    bool help = true;

    do
    {
        using (var client = new ImapClient())
        {
            using (var cancel = new System.Threading.CancellationTokenSource())
            {
                client.Connect(emailParser.ServerName, emailParser.Port, emailParser.IsSSLuse,
                    cancel.Token);

                client.Authenticate(emailParser.Username, emailParser.Password, cancel.Token);

                var inbox = client.Inbox;
                inbox.Open(FolderAccess.ReadOnly, cancel.Token);
           
                for (int i = 0; i < inbox.Count; i++)
                {
                    var message = inbox.GetMessage(i, cancel.Token);
                    GetBodyText = message.TextBody;
                    Problem problem = new Problem(message.MessageId);
                    if (!dAOProblem.GetAll().Any(x => x.Message_Id.Equals(problem.Message_Id)))
                    {
                        dAOProblem.Save(problem);
                        Alert alert = new Alert(message.MessageId, message.Date.DateTime, message.From.ToString(), 1, problem.Id);
                        if (!dAOAlert.GetAll().Any(x => x.Id_MimeMessage.Equals(alert.Id_MimeMessage)))
                        {
                            dAOAlert.Save(alert);
                            LoadAlertGrid();
                        }
                    }
                }
                //client.Disconnect(true, cancel.Token);
            }
        }
    } while (help != false);
}

DataGrid加载方法 #

public void LoadAlertGrid()
{
    var conn = new SqlConnection(DatabaseSingleton.connString);
    conn.Open();
    using (SqlCommand comm = new SqlCommand("SELECT Alert.email ,Alert.date , AMUser.name as 'admin', Problem.nameOfAlert, [Value].[value] , Result.result FROM Alert JOIN AMUser ON Alert.AMUser_ID = AMUser.id JOIN Problem ON Alert.Problem_ID = Problem.id JOIN[Value] ON Problem.Value_ID = [Value].id JOIN Result ON Problem.Result_ID = Result.id ; ", conn))
    {
        using (DataTable table = new DataTable())
        {
            using (SqlDataReader sdr = comm.ExecuteReader())
            {
                table.Load(sdr);
                BindingOperations.EnableCollectionSynchronization(dAOAlert.GetAll(), alertLock);
                lock (alertLock)
                {
                    AlertTable.ItemsSource = table.DefaultView;
                }
                sdr.Close();
            }
        }
    }
}

1. 解决跨线程UI更新问题 #

Test方法在后台线程执行，直接调用LoadAlertGrid会触发跨线程操作UI的异常，需要切换到UI线程执行更新：

// 替换Test方法中的LoadAlertGrid();为以下代码
Dispatcher.Invoke(() => LoadAlertGrid());

2. 优化全量加载性能 #

每次新增数据都重新查询全表效率低下，可改为仅查询新增记录并添加到现有DataTable：

• 先在窗口类中声明全局DataTable引用：

private DataTable _alertDataTable;
private readonly object _tableLock = new object();

• 初始化时加载完整数据并绑定：

public AdminWindow()
{
    InitializeComponent();
    _alertDataTable = LoadAlertData();
    AlertTable.ItemsSource = _alertDataTable.DefaultView;
    BindingOperations.EnableCollectionSynchronization(_alertDataTable, _tableLock);
    Task.Run(() => Test(new EmailParser(true, "abc", "defg")));
}

// 拆分数据查询与UI绑定逻辑
private DataTable LoadAlertData()
{
    var table = new DataTable();
    using (var conn = new SqlConnection(DatabaseSingleton.connString))
    {
        conn.Open();
        using (var comm = new SqlCommand("SELECT Alert.email ,Alert.date , AMUser.name as 'admin', Problem.nameOfAlert, [Value].[value] , Result.result FROM Alert JOIN AMUser ON Alert.AMUser_ID = AMUser.id JOIN Problem ON Alert.Problem_ID = Problem.id JOIN[Value] ON Problem.Value_ID = [Value].id JOIN Result ON Problem.Result_ID = Result.id ; ", conn))
        using (var sdr = comm.ExecuteReader())
        {
            table.Load(sdr);
        }
    }
    return table;
}

• 新增数据后仅查询单条记录并添加到DataTable：

// 在Test方法保存Alert后，替换LoadAlertGrid为以下代码
var newAlertRow = GetSingleAlertRow(alert.Id_MimeMessage);
Dispatcher.Invoke(() => 
{
    lock (_tableLock)
    {
        _alertDataTable.ImportRow(newAlertRow);
    }
});

// 新增单条数据查询方法
private DataRow GetSingleAlertRow(string messageId)
{
    using (var conn = new SqlConnection(DatabaseSingleton.connString))
    {
        conn.Open();
        var table = new DataTable();
        using (var comm = new SqlCommand("SELECT Alert.email ,Alert.date , AMUser.name as 'admin', Problem.nameOfAlert, [Value].[value] , Result.result FROM Alert JOIN AMUser ON Alert.AMUser_ID = AMUser.id JOIN Problem ON Alert.Problem_ID = Problem.id JOIN[Value] ON Problem.Value_ID = [Value].id JOIN Result ON Problem.Result_ID = Result.id WHERE Alert.Id_MimeMessage = @MessageId; ", conn))
        {
            comm.Parameters.AddWithValue("@MessageId", messageId);
            using (var sdr = comm.ExecuteReader())
            {
                table.Load(sdr);
            }
        }
        return table.Rows[0];
    }
}

3. 优化去重逻辑性能 #

循环中频繁调用GetAll().Any()会重复查询全表，建议一次性获取已存在的ID集合：

// 在Test方法的for循环前添加以下代码
var existingProblemMsgIds = dAOProblem.GetAll().Select(x => x.Message_Id).ToHashSet();
var existingAlertMsgIds = dAOAlert.GetAll().Select(x => x.Id_MimeMessage).ToHashSet();

// 循环内的去重判断改为集合Contains
for (int i = 0; i < inbox.Count; i++)
{
    var message = inbox.GetMessage(i, cancel.Token);
    GetBodyText = message.TextBody;
    string msgId = message.MessageId;
    
    if (!existingProblemMsgIds.Contains(msgId))
    {
        Problem problem = new Problem(msgId);
        dAOProblem.Save(problem);
        existingProblemMsgIds.Add(msgId);
        
        Alert alert = new Alert(msgId, message.Date.DateTime, message.From.ToString(), 1, problem.Id);
        if (!existingAlertMsgIds.Contains(msgId))
        {
            dAOAlert.Save(alert);
            existingAlertMsgIds.Add(msgId);
            // 调用新增数据的UI更新逻辑
            var newAlertRow = GetSingleAlertRow(msgId);
            Dispatcher.Invoke(() => 
            {
                lock (_tableLock)
                {
                    _alertDataTable.ImportRow(newAlertRow);
                }
            });
        }
    }
}

4. 修正无限循环逻辑 #

当前do-while为无限循环，建议添加间隔时间避免频繁连接IMAP服务器：

do
{
    // 现有业务逻辑...
    
    // 每次循环后暂停5分钟（可根据需求调整）
    System.Threading.Thread.Sleep(TimeSpan.FromMinutes(5));
} while (help != false);

内容的提问来源于stack exchange，提问作者jirina