如何在类分离场景下实现DataTable动态存储与DataGrid自动更新

我需要将数据库中的数据导入DataGrid进行展示，同时实现新数据进入时自动保存到数据库。目前数据收集方法与DataGrid分别位于不同的类中，我知晓可通过合并类的方式，在新对象存入数据库时同步添加至DataGrid，但不希望采用该方案。我曾考虑在数据收集类中创建静态DataTable对象，但认为该方案不够优雅，希望找到更合适的实现方式。

数据收集方法 #

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);
            count = dAOAlert.GetAll().Count();
        }
    }
}

数据展示方法 #

public void LoadAlertGrid()
{
    SqlConnection con = DatabaseSingleton.GetInstance();
    using (SqlCommand comm = new SqlCommand("SELECT Alert.email , Alert.date , AMUser.name as 'admin', Problem.nameOfAlert, Result.result " +
        "FROM ((Alert INNER JOIN AMUser ON Alert.AMUser_ID = AMUser.id)" +
        " INNER JOIN Problem ON Alert.Problem_ID = Problem.id " +
        "INNER JOIN Result ON Problem.Result_ID = Result.id); ", con))
    {
        DataTable table = new DataTable();
        SqlDataReader sdr = comm.ExecuteReader();
        table.Load(sdr);
        AlertTable.ItemsSource = table.DefaultView;
        sdr.Close();
    }
}

1. 基于事件的解耦方案 #

这是适配类分离场景的优雅方案，通过事件让数据收集类在保存新数据时通知UI类更新DataGrid，完全解耦两个类：

步骤1：在数据收集类中定义事件 #

public class DataCollector
{
    // 定义事件，传递新增的Alert对象
    public event Action<Alert> AlertAdded;

    public void CollectData(...)
    {
        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);
                    count = dAOAlert.GetAll().Count();
                    // 触发事件，通知外部新增了Alert
                    AlertAdded?.Invoke(alert);
                }
            }
        }
    }
}

步骤2：在UI类中订阅事件并更新DataGrid #

public class AlertGridManager
{
    private DataCollector _dataCollector;
    private DataTable _alertTable;

    public AlertGridManager()
    {
        _dataCollector = new DataCollector();
        // 订阅事件
        _dataCollector.AlertAdded += OnAlertAdded;
        // 初始化加载数据
        LoadAlertGrid();
    }

    public void LoadAlertGrid()
    {
        SqlConnection con = DatabaseSingleton.GetInstance();
        using (SqlCommand comm = new SqlCommand("SELECT Alert.email , Alert.date , AMUser.name as 'admin', Problem.nameOfAlert, Result.result " +
            "FROM ((Alert INNER JOIN AMUser ON Alert.AMUser_ID = AMUser.id)" +
            " INNER JOIN Problem ON Alert.Problem_ID = Problem.id " +
            "INNER JOIN Result ON Problem.Result_ID = Result.id); ", con))
        {
            _alertTable = new DataTable();
            SqlDataReader sdr = comm.ExecuteReader();
            _alertTable.Load(sdr);
            AlertTable.ItemsSource = _alertTable.DefaultView;
            sdr.Close();
        }
    }

    // 事件处理方法：新增Alert时更新DataTable
    private void OnAlertAdded(Alert newAlert)
    {
        // WPF/Silverlight中需通过Dispatcher切换到UI线程
        Application.Current.Dispatcher.Invoke(() =>
        {
            // 查询新增Alert的完整关联数据
            SqlConnection con = DatabaseSingleton.GetInstance();
            using (SqlCommand comm = new SqlCommand("SELECT Alert.email , Alert.date , AMUser.name as 'admin', Problem.nameOfAlert, Result.result " +
                "FROM ((Alert INNER JOIN AMUser ON Alert.AMUser_ID = AMUser.id)" +
                " INNER JOIN Problem ON Alert.Problem_ID = Problem.id " +
                "INNER JOIN Result ON Problem.Result_ID = Result.id) " +
                "WHERE Alert.Id_MimeMessage = @messageId;", con))
            {
                comm.Parameters.AddWithValue("@messageId", newAlert.Id_MimeMessage);
                SqlDataReader sdr = comm.ExecuteReader();
                if (sdr.Read())
                {
                    DataRow newRow = _alertTable.NewRow();
                    newRow["email"] = sdr["email"];
                    newRow["date"] = sdr["date"];
                    newRow["admin"] = sdr["admin"];
                    newRow["nameOfAlert"] = sdr["nameOfAlert"];
                    newRow["result"] = sdr["result"];
                    _alertTable.Rows.Add(newRow);
                }
                sdr.Close();
            }
        });
    }
}

2. 基于ObservableCollection的MVVM方案（更优雅的绑定方式） #

若可重构数据展示逻辑，用ObservableCollection<T>替代DataTable，配合MVVM模式可实现自动通知更新：

步骤1：创建数据模型类 #

public class AlertViewModel
{
    public string Email { get; set; }
    public DateTime Date { get; set; }
    public string Admin { get; set; }
    public string NameOfAlert { get; set; }
    public string Result { get; set; }
}

步骤2：在ViewModel中使用ObservableCollection #

public class MainViewModel
{
    public ObservableCollection<AlertViewModel> Alerts { get; } = new ObservableCollection<AlertViewModel>();
    private DataCollector _dataCollector;

    public MainViewModel()
    {
        _dataCollector = new DataCollector();
        _dataCollector.AlertAdded += OnAlertAdded;
        LoadAlerts();
    }

    private void LoadAlerts()
    {
        SqlConnection con = DatabaseSingleton.GetInstance();
        using (SqlCommand comm = new SqlCommand("SELECT Alert.email , Alert.date , AMUser.name as 'admin', Problem.nameOfAlert, Result.result " +
            "FROM ((Alert INNER JOIN AMUser ON Alert.AMUser_ID = AMUser.id)" +
            " INNER JOIN Problem ON Alert.Problem_ID = Problem.id " +
            "INNER JOIN Result ON Problem.Result_ID = Result.id); ", con))
        {
            SqlDataReader sdr = comm.ExecuteReader();
            while (sdr.Read())
            {
                Alerts.Add(new AlertViewModel
                {
                    Email = sdr["email"].ToString(),
                    Date = (DateTime)sdr["date"],
                    Admin = sdr["admin"].ToString(),
                    NameOfAlert = sdr["nameOfAlert"].ToString(),
                    Result = sdr["result"].ToString()
                });
            }
            sdr.Close();
        }
    }

    private void OnAlertAdded(Alert newAlert)
    {
        Application.Current.Dispatcher.Invoke(() =>
        {
            SqlConnection con = DatabaseSingleton.GetInstance();
            using (SqlCommand comm = new SqlCommand("SELECT Alert.email , Alert.date , AMUser.name as 'admin', Problem.nameOfAlert, Result.result " +
                "FROM ((Alert INNER JOIN AMUser ON Alert.AMUser_ID = AMUser.id)" +
                " INNER JOIN Problem ON Alert.Problem_ID = Problem.id " +
                "INNER JOIN Result ON Problem.Result_ID = Result.id) " +
                "WHERE Alert.Id_MimeMessage = @messageId;", con))
            {
                comm.Parameters.AddWithValue("@messageId", newAlert.Id_MimeMessage);
                SqlDataReader sdr = comm.ExecuteReader();
                if (sdr.Read())
                {
                    Alerts.Add(new AlertViewModel
                    {
                        Email = sdr["email"].ToString(),
                        Date = (DateTime)sdr["date"],
                        Admin = sdr["admin"].ToString(),
                        NameOfAlert = sdr["nameOfAlert"].ToString(),
                        Result = sdr["result"].ToString()
                    });
                }
                sdr.Close();
            }
        });
    }
}

步骤3：DataGrid绑定到ObservableCollection #

<DataGrid ItemsSource="{Binding Alerts}" AutoGenerateColumns="False">
    <DataGrid.Columns>
        <DataGridTextColumn Header="邮箱" Binding="{Binding Email}" />
        <DataGridTextColumn Header="日期" Binding="{Binding Date}" />
        <DataGridTextColumn Header="管理员" Binding="{Binding Admin}" />
        <DataGridTextColumn Header="告警名称" Binding="{Binding NameOfAlert}" />
        <DataGridTextColumn Header="结果" Binding="{Binding Result}" />
    </DataGrid.Columns>
</DataGrid>

方案优势 #

• 事件方案：完全解耦数据收集类和UI类，符合单一职责原则，无需修改原有类核心逻辑，仅需添加事件通知。
• ObservableCollection方案：贴合WPF的MVVM设计模式，自动触发DataGrid更新，代码简洁易维护。

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