. NET medium and large project development essential -- efficient paging

Related downloads:

DeveloperSharp.dll component
DeveloperSharp.dll component

Efficient paging - sample code (dp8 paging. RAR)
Efficient paging - sample code (dp8 paging. RAR)

 

Data paging is a necessary function of almost any application system. However, when the amount of data is large, the efficiency of paging operation will become very low. When paging a large amount of data, it is possible for an operation to take 5 seconds, 10 seconds or even longer, but this is unacceptable from the perspective of users

 

There are three common schemes for data paging.

The first is to read all the relevant data stored in the database into the code / memory, and then the code will page it.

The second is to page the relevant data directly in the database, and then output the paged data to the code program.

Third, first read all the relevant data in the database into the "cache", and then the code program reads and pages the data in the "cache".

 

This article focuses on the first and second solutions, which are also directly based on "database".

(although the third scheme is fast, it requires third-party tools such as "cache" and complex "database cache" synchronization operation in case of data change, so it will not be detailed in this paper.)

 

◆ the first scheme is as follows

Introduce developer sharp DLL component, initialize IUtility tool

using DeveloperSharp.Framework.CoreUtility;
--------------------------

IUtility IU = new Utility();

There are three overloaded paging PagePartition methods built in iuutility:

PagePartition
 Statement: PagePiece PagePartition(DataTable Table, int PageSize, int PageIndex)
Purpose: paging function
 Parameters:(1)DataTable Table   --  Paging required DataTable
     (2)int PageSize  --  Page size
     (3)int PageIndex    --  Current page number(The minimum value is 1)
return: PagePiece   --  Page entity

PagePartition
 Statement: PagePiece<List<T>> PagePartition<T>(IQueryable<T> DataList, int pageSize, int pageIndex) where T : class
Purpose: paging function
 Parameters:(1)IQueryable<T> DataList   --  Paging required IQueryable
     (2)int PageSize  --  Page size
     (3)int PageIndex    --  Current page number(The minimum value is 1)
return: PagePiece   --  Page entity

PagePartition
 Statement: PagePiece<List<T>> PagePartition<T>(List<T> DataList, int pageSize, int pageIndex) where T : class
Purpose: paging function
 Parameters:(1)List<T> DataList   --  Pagination required List
     (2)int PageSize  --  Page size
     (3)int PageIndex    --  Current page number(The minimum value is 1)
return: PagePiece   --  Page entity

 

The return value of the paging method, pagepiece / pagepiece < T > class, contains the paged data set, total pages, total data, current page number, and a series of data often used after "paging". The properties of pagepiece / pagepiece < T > are described in detail as follows:

PageSize
 Statement:public int PageSize;
Purpose:int --Page size

TotalPageNumber
 Statement:public int TotalPageNumber;
Purpose:int --PageCount 

TotalRecordNumber
 Statement:public int TotalRecordNumber;
Purpose:int --Total records

CurrentStartIndex
 Statement:public int CurrentStartIndex;
Purpose:int --Record start number of the current page

CurrentEndIndex
 Statement:public int CurrentEndIndex;
Purpose:int --Record end number of the current page

CurrentPageSize
 Statement:public int CurrentPageSize;
Purpose:int --Number of records on the current page

CurrentPageIndex
 Statement:public int CurrentPageIndex;
Purpose:int --Current page number

Table
 Statement:public System.Data.DataTable Table;
Purpose: System.Data.DataTable--Data table of the current page
((or)
DataList
 Statement:public List<T> DataList;
Purpose: List<T>--Data of the current page

 

 

◆ the second scheme is as follows ◆ 2

To demonstrate how to use the "second paging scheme", we first create a new console project in Visual Studio. Then, we do the following three operations.

[step 1]: add developer sharp. For the project reference DLL component.

Step 2: create a "data source class" (text example: TestData.cs) to communicate with the database, as follows:

using DeveloperSharp.Structure.Model;//DataSource Namespace of
using DeveloperSharp.Framework.QueryEngine;//DatabaseType Namespace for

namespace YZZ
{
    [DataSource(DatabaseType.SQLServer, "Server=localhost;Database=Test;Uid=sa;Pwd=123")]
    public class TestData : DeveloperSharp.Structure.Model.DataLayer
    {
        //There is no code in the class
    }
}

Note: "data source class" (text example: TestData.cs) must inherit from developer sharp Structure. Model. Datalayer class, and set the initialization value of the DataSource property on it to database type and its link string.

 

Step 3: for the console application class, add the code for paging data by calling its PagePartition method through the "data source class" (TestData). Note: the core code is just one line!!

The code is as follows:

using DeveloperSharp.Structure.Model;//PagePiece Namespace where
using DeveloperSharp.Extension;//Table The namespace where the extension is located
-----------------------------
    class Program
    {
        static void Main(string[] args)
        {
            TestData td = new TestData();

            //paging
            PagePiece pp = td.PagePartition("select top 500000 * from t_Order where Id>10 order by Id desc", 20, 162);

            List<Product> Products = pp.Table.ToList<Product>();
            foreach (var P in Products)
            {
                Console.WriteLine(P.Name);
            }

            Console.ReadLine();
        }
    }

Product class code is as follows:

    public class Product
    {
        public string Id { get; set; }
        public string Name { get; set; }
        public int Quantity { get; set; }
    }

 

The PagePartition method here has two overloaded methods, and its detailed functions are described as follows:

PagePartition
 Statement:public PagePiece PagePartition(string RecordSet, string Id, int PageSize, int PageIndex)
Purpose: paging function(Have primary key)
Parameters:(1)string RecordSet     --The recordset to be paged can be a table, view, or SQL sentence
(2)string Id     --Primary key
(3)int PageSize     --Page size
(4)int PageIndex     --Current page number(The minimum value is 1)
return: PagePiece  --Page entity

PagePartition
 Statement:public PagePiece PagePartition(string RecordSet, int PageSize, int PageIndex)
Purpose: paging function(No primary key)
Parameters:(1)string RecordSet     -- The recordset to be paged can be a table, view, or SQL sentence
     (2)int PageSize    --Page size
(3)int PageIndex    --Current page number(The minimum value is 1)
return: PagePiece  --Page entity

be careful:

(1) When the data table you need to page has a "primary key" field, use the "paging function (with primary key)". Instead, use pagination (no primary key).

(2) RecordSet is the SQL statement of "total data set" that you need to page. The SQL statement has a variety of forms, including conditions, sorting, and even joint query of multiple tables.

 

Recommended reading

Keywords: C# Database SQL Server .NET

Added by santhosh_89 on Fri, 21 Jan 2022 12:16:09 +0200