如何更快地从SQL到WPF数据网格获取JSON数据？

本文介绍了如何更快地从SQL到WPF数据网格获取JSON数据？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 Hello Everyone,I am writing code for an application, which used WPF for Front end, SQL for backend and EF for DAL.This application has got lot of data, around millions of records are stored in DB. My DB structure is as given below:Eg: Col1 Col2 Col3 Col4 Col5 (JSON data is stored in string format)A11 B11 C11 D11 [{"PPWK":"201601","Vol":10},{"PPWK":"201602","Vol":11}, {"PPWK":"201603","Vol":23}]A11 B11 C11 D12 [{"PPWK":"201601","Vol":20},{"PPWK":"201602","Vol":22},{"PPWK":"201603","Vol":24}]A11 B11 C11 D13 [{"PPWK":"201601","Vol":30},{"PPWK":"201602","Vol":33},{"PPWK":"201603","Vol":25}]A12 B11 C12 D11 [{"PPWK":"201601","Vol":40},{"PPWK":"201602","Vol":44},{"PPWK":"201603","Vol":26}]A12 B12 C12 D11 [{"PPWK":"201601","Vol":50},{"PPWK":"201602","Vol":55},{"PPWK":"201603","Vol":27}]If table contains data as shown above. Collection of first four columns together becomes a unique key and column 5 contains data in JSON format which is declared as nvarchar(max) in table creation.I want to pull this data into my WPF application and show the result as below.(each unique PPWK should become a column and its correspondance value (Vol) should appear on the respective row.Expected output in WPF datagrid:Col1 Col2 Col3 Col4 201601 201602 201603A11 B11 C11 D11 10 11 23A11 B11 C11 D12 20 22 24A11 B11 C11 D13 30 33 25A12 B11 C12 D11 40 44 26A12 B12 C12 D11 50 55 27currently, my table has 11000 rows and in col5 it has got 256 set of values in each row (PPWK and Vol).For the above requirement I have written a small application using WPF C#, EF(LINQ), SQL.It takes around 9 secs to pull data into DataGrid in Windows application and 19 secs in WPF application.I will display the code that I have used to display the data. Please someone suggest me to reduce the time taken to pull the data.I am expecting the time taken for this operation should be less than one or two second. 我尝试了什么： What I have tried:please find the code that I have used for my application, Classes I have used are given below:public class Newtable { public string PPWK { get; set; } public string Vol { get; set; } }public partial class TableName { public string Col1 { get; set; } public string Col2 { get; set; } public string Col3 { get; set; } public string Col4 { get; set; } public string Col5 { get; set; } public IEnumerable <Newtable> Col5values { get; set; } } WPF code for getting the data:private void Button_Click(object sender, RoutedEventArgs e) { ProjectRepository obj = new ProjectRepository(); gvLoadData.ItemsSource = obj.LoadDataToTable().DefaultView; //gvLoadData is a DataGrid name }Code written in DAL:Repository.cs fileprivate DataTable LoadDataToTable() { var dataTable = new DataTable(); dataTable.Columns.Add(new DataColumn("Col1", typeof(string))); dataTable.Columns.Add(new DataColumn("Col2", typeof(string))); dataTable.Columns.Add(new DataColumn("Col3", typeof(string))); dataTable.Columns.Add(new DataColumn("Col4", typeof(string))); var JSONPOCDataList = (from db in ob.TableName select db); //This loop is consuming 90% of the execution time. foreach (var item in JSONPOCDataList.ToList<TableName>()) { var dataRow = dataTable.NewRow(); dataRow["Col1"] = item.Col1; dataRow["Col2"] = item.Col2; dataRow["Col3"] = item.Col3; dataRow["Col4"] = item.Col4; IEnumerable<Newtable> deserializedProduct = Newtonsoft.Json.JsonConvert.DeserializeObject<IEnumerable<Newtable>>(item.Col5); foreach (var jsonItem in deserializedProduct) { if (!dataTable.Columns.Contains(jsonItem.PPWK.ToString())) { dataTable.Columns.Add(new DataColumn(jsonItem.PPWK.ToString(), typeof(int))); } dataRow[jsonItem.PPWK.ToString()] = jsonItem.Vol; } dataTable.Rows.Add(dataRow); } return dataTable; } } 推荐答案 不要一次尝试拉11,000行数据。查看分页，一次返回100行。考虑一下您的用户他们如何一次看到，使用和处理11,000行数据。Don't try pull 11,000 rows of data at once. Looking into paging, returning say 100 rows at a time. Think about your users how could they possibly see, use, and work with 11,000 rows of data at once. 这篇关于如何更快地从SQL到WPF数据网格获取JSON数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！