backgroundworker优化DATAGRID实时获取sqlserver数据查询结果

说明：本文采用的是vs 2019与sql server2014

1.  在vs中新建一个wpf程序。本文使用的是vs 2019，打开vs后点击创建新项目：如下图

 2. 在创建新项目页面中选择WPF应用（.net Framework），并点击下一步设置项目名称与代码保存位置，无特别要求默认即可，然后点击创建。

3. 设置MainWindow.xaml页面。添加一个DataGrid与两个button控件，datagrid用于查询结果的显示，button则用于单线程与backgroundworker线程的比较，代码如下：

<Window x:Class="WpfApp1.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:WpfApp1"
        mc:Ignorable="d"
        Title="MainWindow" Height="450" Width="800">
    <Grid>
        <DataGrid HorizontalAlignment="Left" Height="420" VerticalAlignment="Top" Width="550" Name="DataGrid1"/>
        <Button Content="多线程" HorizontalAlignment="Left" Margin="641,141,0,0" VerticalAlignment="Top" Width="75" Name="btn1" Click="Btn1_Click"/>
        <Button Content="单线程" HorizontalAlignment="Left" Margin="641,251,0,0" VerticalAlignment="Top" Width="75" Name="btn2" Click="Btn2_Click"/>
    </Grid>
</Window>

4. MainWindow.xaml.cs代码的编写。

        （1）数据库连接语句：

        

        SqlConnection conn = new SqlConnection("Data Source=电脑名称;Initial Catalog=数据库名称;Integrated Security=True");//数据库连接请求

        （2）多线程button1_Click事件代码：

            conn.Open();//打开数据库连接
            BackgroundWorker backgroundWorker = new BackgroundWorker();
            backgroundWorker.WorkerReportsProgress = true;
            backgroundWorker.DoWork += backgroundWorker_DoWork;
            backgroundWorker.ProgressChanged += BackgroundWorker_ProgressChanged;
            backgroundWorker.RunWorkerCompleted += BackgroundWorker_RunWorkerCompleted;
            backgroundWorker.RunWorkerAsync(100);

         （3）单线程button2_Click事件代码：

            conn.Open();
            for (int i = 0; i < 100; i++)
            {
                string sql = "select * from autoupdate";
                SqlCommand cmd = new SqlCommand(sql, conn);
                DataTable dataTable = new DataTable();
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
                sqlDataAdapter.Fill(dataTable);
                DataGrid1.ItemsSource = dataTable.DefaultView;
                System.Threading.Thread.Sleep(10000);//线程挂起时间，单位ms，这里是每10s进行一次for循环语句，即每过10s对数据库进行一次查询并显示。
            }
            conn.Close();

5. MainWindow.xaml.cs整体代码如下：

using System.Windows;
using System.Data.SqlClient;
using System.Data;
using System.ComponentModel;

namespace WpfApp1
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {

        public MainWindow()
        {
            InitializeComponent();
        }
        SqlConnection conn = new SqlConnection("Data Source=电脑名;Initial Catalog=数据库名;Integrated Security=True");//数据库连接
        DataTable dataTable;
        private void Btn1_Click(object sender, RoutedEventArgs e)
        {
            conn.Open();//打开数据库连接
            BackgroundWorker backgroundWorker = new BackgroundWorker();
            backgroundWorker.WorkerReportsProgress = true;
            backgroundWorker.DoWork += backgroundWorker_DoWork;
            backgroundWorker.ProgressChanged += BackgroundWorker_ProgressChanged;
            backgroundWorker.RunWorkerCompleted += BackgroundWorker_RunWorkerCompleted;
            backgroundWorker.RunWorkerAsync(100);
        }
        private void BackgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            conn.Close();
        }

        private void BackgroundWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            DataGrid1.ItemsSource = dataTable.DefaultView;
        }

        private void backgroundWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            int max = (int)e.Argument;
            for (int i = 0; i < max; i++)
            {
                string sql = "select * from 表名";
                SqlCommand cmd = new SqlCommand(sql, conn);
                dataTable = new DataTable();
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
                sqlDataAdapter.Fill(dataTable);
                (sender as BackgroundWorker).ReportProgress(i, dataTable.DefaultView);//数据传递
                System.Threading.Thread.Sleep(1000);
            }

        }

        private void Btn2_Click(object sender, RoutedEventArgs e)
        {
            conn.Open();
            for (int i = 0; i < 100; i++)
            {
                string sql = "select * from autoupdate";
                SqlCommand cmd = new SqlCommand(sql, conn);
                DataTable dataTable = new DataTable();
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
                sqlDataAdapter.Fill(dataTable);
                DataGrid1.ItemsSource = dataTable.DefaultView;
                System.Threading.Thread.Sleep(10000);
            }
            conn.Close();
        }
    }
}

wpf新人，请多多指教，有什么问题欢迎在评论区指出