如何加快数据加载到网格？或者如何从DAL层加载大量数据到网格？ [英] How to speed up data loading to grid? or how to load large number of data to grid from DAL layer?

问题描述

我在我的应用程序中使用3层

UI（WPF） - > BL-> DAL

Business Objects是从UI，BL& DAL



DB是oracle并使用Oracle.ManagedDataAccess.Client



我的查询返回一次300000条记录。

将数据读取器的整个结果添加到集合后，将该集合返回到BL，然后返回到UI。这需要一点时间。



我需要加速或者需要加载批量加载

通过将100/1000记录加载到网格中以便用户可以避免等待很长一段时间才能看到结果。



哪个是实现它的最佳方法？



任何帮助..非常感谢...请提供一些示例代码......或者请更改以下示例代码...



记录总数将始终高于300000及其中一些报告..



下面给出示例代码以了解我的编码方式去....



示例用户界面代码

I am using 3 layer in my application
UI (WPF) ->BL->DAL
Business Objects is referred from UI,BL & DAL

DB is oracle and using "Oracle.ManagedDataAccess.Client"

My query is returning around 300000 records at a time.
After adding whole result from data reader to collection, returning that collection to BL, then to UI. it take a little long time.

I need to speed up or need to load as batches
by loading 100/1000 records to the grid So that user can avoid waiting long time to see the result.

Which is the best method to achieve it?

Any helpsss.. really appreciated... Please provide some sample code..or please change in the following sample code...

Number of records will be always above 300000 and its for some reports..

Sample code is given below to know how my coding goes....

Sample UI Code

gridEmployee.ItemsSource =new BLEmployee().GetEmployeeTransactions(startDate,endDate);

示例业务层代码

Sample Business Layer Code

public ObservableCollection<BOEmployeeTransaction> GetEmployeeTransactions(DateTime startDate, DateTime endDate)
{
    return new DLEmployee().GetEmployeeTransactions(startDate, endDate);
}

示例数据访问LayerCode

Sample Data Access LayerCode

public ObservableCollection<BOEmployeeTransaction> GetEmployeeTransactions(DateTime startDate, DateTime endDate)
 {
           ObservableCollection<BOEmployeeTransaction> empTransactions = new                   ObservableCollection<BOEmployeeTransaction>();
            string query = "";
            BOEmployeeTransaction transaction = null;
            OracleConnection con = new OracleConnection();
            con.ConnectionString = oracleConString;
            con.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT Column1,Couln2... FROM TABLE1 WHERE TRUNC(A_DATE) BETWEEN TO_DATE('01-01-2015','MM-DD-YYYY') AND TO_DATE('02-02-2015','MM-DD-YYYY')"
            cmd.CommandType = CommandType.Text;
            using (OracleDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
               {
                  transaction =new BOEmployeeTransaction();
                  transaction.Name=dr["Name"].ToString();
                  .......
                  .......
                  .......
                  empTransactions.Add(BOEmployeeTransaction);
               }
           }
           con.Clone();
           con.Dispose();
           return transactions;
}

推荐答案

同时向UI提取300'000行可能太多了。



首先我要考虑的是：用户是否真的需要所有行？可以限制行数吗？



如果由于某种原因确实需要所有行，我会实现分页。有关分页，请查看 On Top-n和Pagination Queries [ ^ ]

Fetching 300'000 rows to an UI at the same time is probably too much.

First thing I'd consider is: Does the user really need all of the rows? Could the amount of rows be restricted?

If for some reason all of the rows are really needed, I would implement paging. For paging, have a look at On Top-n and Pagination Queries[^]

Hi
你需要使用以下查询从表中获取所有数据吗？

Hi U need to get all datas from table using following query ?

cmd.CommandText = "SELECT * FROM TABLE1 WHERE TRUNC(A_DATE) BETWEEN TO_DATE('01-01-2015','MM-DD-YYYY') AND TO_DATE('02-02-2015','MM-DD-YYYY')"

1.如果不需要，则指定列名。不要使用*一次检索更多数据。

2.试着按照上面提到的那样批处理。





问候

Aravidb

1.If no need then specify column names. Don't use "*" for retrieving more datas at a time.
2.Try to get batch wise that u mention above.


Regards
Aravidb

这篇关于如何加快数据加载到网格？或者如何从DAL层加载大量数据到网格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！