异步从MSSQL获取产品并更新全局静态列表的实现难题

需求背景 #

我正在从MSSQL数据库导入约20000条商品数据，把这个操作放在独立任务里执行。获取数据后要异步填充一个全局静态列表，这个列表后续会作为DataGrid的数据源，所以需要DataGrid能实时展示已导入的商品。

初始代码实现及卡顿问题 #

触发操作的按钮事件逻辑：

// 应用中需执行两项任务：从数据库导入大量商品，填充全局可用列表
// 该列表将在其他窗口打开时使用，目的是提前加载好列表，以便在内存中搜索商品
// 触发所有操作的事件：导入商品并填充全局列表：
private void btnImportArticles_Click(object sender, RoutedEventArgs e) { 
    if (MessageBox.Show("Sure?", "Data sync", MessageBoxButton.YesNo) != MessageBoxResult.Yes) 
        return; 
    Task.Factory.StartNew(() => ImportDataFromServer()) // 从数据库导入商品和分组
        .ContinueWith(task => { 
            // 在MSSQL导入完成后启动新任务更新全局可用列表，调用PrepareArticles()方法异步存储大量商品
            task.ContinueWith(task2 => { 
                PrepareArticles(); // 但应用严重卡顿
            }, CancellationToken.None, TaskContinuationOptions.None, TaskScheduler.FromCurrentSynchronizationContext()); 
        }, CancellationToken.None, TaskContinuationOptions.None, TaskScheduler.FromCurrentSynchronizationContext()); 
}

核心导入方法：

private void ImportDataFromServer() { 
    using (SqlConnection connection = GetSqlConnection()) { 
        connection.Open(); 
        ImportGroups(); 
        ImportArticles(); 
    } 
}

从数据库获取数据的方法：

private void ImportArticles() { 
    List<Article> newArticles = new List<Article>(); 
    using (SqlConnection connection = GetSqlConnection()) { 
        connection.Open(); 
        using (SqlCommand command = new SqlCommand()) { 
            command.CommandText = "SELECT COUNT(*) FROM [dbo].[Products] T1 INNER JOIN [dbo].[ProductGroup] T2 ON T1.Code = T2.Code"; 
            command.Connection = connection; 
        } 
        using (SqlCommand command = new SqlCommand()) { 
            command.CommandText = "SELECT T1.[Code], [Title], [Description],[Price] FROM [dbo].[Products] T1 INNER JOIN [dbo].[ProductGroup] T2 ON T1.Code = T2.Code"; 
            command.Connection = connection; 
            using (SqlDataReader reader = command.ExecuteReader()) { 
                while (reader.Read()) { 
                    // 简化代码 
                } 
            } 
        } 
    } 
}

private void ImportGroups() { 
    using (SqlConnection connection = GetSqlConnection()) { 
        connection.Open(); 
        using (SqlCommand command = new SqlCommand()) { 
            command.CommandText = "SELECT [GroupId] FROM [dbo].[Groups]"; 
            command.Connection = connection; 
            using (SqlDataReader reader = command.ExecuteReader()) { 
                if (reader.Read()) { 
                    // 简化代码 
                } 
            } 
        } 
    } 
    using (SqlConnection connection = GetSqlConnection()) { 
        connection.Open(); 
        using (SqlCommand command = new SqlCommand()) { 
            command.CommandText = "SELECT [GroupId], [Title] FROM [dbo].[GroupsArticles]"; 
            command.Connection = connection; 
            SqlDataReader reader = command.ExecuteReader(); 
            while (reader.Read()) { 
                // 简化代码 
            } 
        } 
    } 
}

填充全局列表的方法：

private void PrepareArticles() { 
    Task.Factory.StartNew(() => Globals.GetArticlesReady()) 
        .ContinueWith(task3 => { 
        }, System.Threading.CancellationToken.None, TaskContinuationOptions.None, TaskScheduler.FromCurrentSynchronizationContext()); 
}

public static void GetArticlesReady() { 
    // 导入完成后从数据库获取所有商品 
    Articles = new List<Article>(); 
    Articles = ArticlesController.GetAll(); 
}

遇到的问题：调用PrepareArticles()时应用出现严重卡顿。

经Camilo帮助后的代码修改 #

private async void btnImportArticles_Click(object sender, RoutedEventArgs e) { 
    if (MessageBox.Show("Sure?", "Data sync", MessageBoxButton.YesNo) != MessageBoxResult.Yes) 
        return; 
    // 调用ImportDataFromServer从数据库获取商品 
    await ImportDataFromServer(); 
    // 将MSSQL中的商品导入本地数据库后，获取数据填充C#全局列表 
    await PrepareArticles(); 
}

private async Task ImportDataFromServer() { 
    using (SqlConnection connection = GetSqlConnection()) { 
        await connection.OpenAsync(); 
        // 父方法是Task，子方法是否无需声明为Task？ 
        ImportGroups(); 
        ImportArticles(); 
    } 
}

private async void ImportGroups() { 
    using (SqlConnection connection = GetSqlConnection()) { 
        await connection.OpenAsync(); 
        using (SqlCommand command = new SqlCommand()) { 
            command.CommandText = "SELECT [GroupId] FROM [dbo].[Groups]"; 
            command.Connection = connection; 
            using (SqlDataReader reader = await command.ExecuteReaderAsync()) { 
                if (await reader.ReadAsync()) { 
                    // 简化代码 
                } 
            } 
        } 
    } 
    using (SqlConnection connection = GetSqlConnection()) { 
        connection.OpenAsync(); 
        using (SqlCommand command = new SqlCommand()) { 
            command.CommandText = "SELECT [GroupId], [Title] FROM [dbo].[GroupsArticles]"; 
            command.Connection = connection; 
            SqlDataReader reader = await command.ExecuteReaderAsync(); 
            while (await reader.ReadAsync()) { 
                // 简化代码 
            } 
        } 
    } 
}

private async void ImportArticles() { 
    List<Article> newArticles = new List<Article>(); 
    using (SqlConnection connection = GetSqlConnection()) { 
        await connection.OpenAsync(); 
        using (SqlCommand command = new SqlCommand()) { 
            command.CommandText = "SELECT COUNT(*) FROM [dbo].[Products] T1 INNER JOIN [dbo].[ProductGroup] T2 ON T1.Code = T2.Code"; 
            command.Connection = connection; 
        } 
        using (SqlCommand command = new SqlCommand()) { 
            command.CommandText = "SELECT T1.[Code], [Title], [Description],[Price] FROM [dbo].[Products] T1 INNER JOIN [dbo].[ProductGroup] T2 ON T1.Code = T2.Code"; 
            command.Connection = connection; 
            using (SqlDataReader reader = await command.ExecuteReaderAsync()) { 
                while (await reader.ReadAsync()) { 
                    // 简化代码 
                } 
            } 
        } 
    } 
}

// 应声明为Task还是仅async void？
private async Task PrepareArticles() { 
    // 父方法是Task，此方法是否可保留为async void？ 
    Globals.GetArticlesReady(); 
}

public static async void GetArticlesReady { 
    // 从之前导入的数据库中获取商品 
    Articles = new List<Article>(); 
    Articles = ArticlesController.GetAll(); 
}

全局列表使用场景补充：

public SearchForArticlesForm() { 
    InitializeComponent(); 
    // 使用全局列表是为了打开该窗口时无需等待15秒加载所有商品 
    // 因此提前在独立任务中加载 
    databaseArticles = new ObservableCollection<Article>(Globals.Articles); 
    dtgArticles.ItemsSource = databaseArticles; 
}

仍存在的问题：应用依旧出现冻结现象。

经mm8帮助后的修改及新错误 #

private async void btnImportArticles_Click(object sender, RoutedEventArgs e) { 
    try { 
        if (MessageBox.Show("Sure?", "Data sync", MessageBoxButton.YesNo) != MessageBoxResult.Yes) 
            return; 
        await Task.Run(() => { 
            // 执行数据库操作 
            ImportDataFromServer(); 
        }); 
        // 此处本想填充全局列表，但未执行就报错 
    } 
    catch (Exception ex) { 
        // 错误信息：调用线程无法访问该对象，因为另一个线程拥有它 
        MessageBox.Show(ex.Message); 
    } 
}

补充细节：导入过程中会更新进度条，ImportArticles方法内调用了UpdateProgressBarOnImport：

// 注：ImportArticles是ImportDataFromServer的子方法
private void ImportArticles() { 
    List<Article> newArticles = new List<Article>(); 
    using (SqlConnection connection = GetSqlConnection()) { 
        connection.Open(); 
        using (SqlCommand command = new SqlCommand()) { 
            command.CommandText = "SELECT COUNT(*) FROM [dbo].[Products] T1 INNER JOIN [dbo].[ProductGroup] T2 ON T1.Code = T2.Code"; 
            command.Connection = connection; 
        } 
        using (SqlCommand command = new SqlCommand()) { 
            command.CommandText = "SELECT T1.[Code], [Title], [Description],[Price] FROM [dbo].[Products] T1 INNER JOIN [dbo].[ProductGroup] T2 ON T1.Code = T2.Code"; 
            command.Connection = connection; 
            using (SqlDataReader reader = command.ExecuteReader()) { 
                while (reader.Read()) { 
                    // 调用UpdateProgressBarOnImport方法 
                    UpdateProgressBarOnImport(..); 
                } 
            } 
        } 
    } 
}

进度条更新方法：

public void UpdateProgressBarOnImport (double percentage) { 
    Dispatcher.BeginInvoke(DispatcherPriority.Background, (SendOrPostCallback)delegate { 
        progressBar.SetValue(ProgressBar.ValueProperty, percentage); 
    }, null); 
}

新错误：调用线程无法访问该对象，因为另一个线程拥有它。

内容的提问来源于stack exchange，提问作者Roxy'Pro