Paged queries

SQLBuilder

Always specify orderby for paging; otherwise order can be unstable or duplicated.

TIP

With ROW_NUMBER paging, SQL looks like: with tmp as ( select row_number() over( partition by ... order by ... ) as rowm,.... ) select * from tmp where rowm> ... order by ... So ORDER BY may appear on the outer query and differ from non-paged queries.

var dt = kit.select("a.ZH_DangerAreaOID,a.SYS_Created")
    .from("ZH_DangerArea a")
    .where("(a.SYS_Deleted is null or a.SYS_Deleted=0)")
    .setPage(pageSize,pageNum)
    .rowNumber("DA_Idx","rowm")
    .orderby("rowm asc")
    .query();

New (2025+): when only orderby is set, window paging reuses that order for sorting—no extra rowNumber sort needed.

var dt = kit.select("a.ZH_DangerAreaOID,a.SYS_Created")
    .from("ZH_DangerArea a")
    .where("(a.SYS_Deleted is null or a.SYS_Deleted=0)")
    .setPage(pageSize,pageNum)
    .orderby("DA_Idx","rowm")
    .query();

TIP

For efficiency, configure the database version in connection settings; the library picks the best paging SQL for that version.

{
    "Position": 9,
    "Name": "Master",
    "DbType": "MSSQL",
    "ConnectString": "Enlist=false;Data Source=137.12.*.*;Database=******;User Id=****;Password=****;Encrypt=True;TrustServerCertificate=True;",
    "Version": "13.0"
}

Repository

Paged list with Clip-based filters:

var res=Rep.GetPageList(input.Page, input.PageSize, (c, d) => {
    c.where(()=>d.CreateTime,input.StartTime,">=")
    .where(()=>d.CreateTime,input.EndTime,"<=")
    .orderByDesc(()=>d.CreateTime);
});

SQLClip

var clip = DBCash.useClip(0);

var tar = clip.from<HHDutyItem>(out var d)
    .where(() => d.Di_Idx, 1)
    .select(d)
    .setPage(50, 1)
    .queryPage();