Question

C#winform如何实现数据库查询并分页

如何为6个表查询数据后。并且分页显示？
数据量特别大。怎么也得有三十多万条吧？
如何在这上面添加？每页显示1000条。。。
这是存储过程
ALTER procedure [dbo].[SP_OnceSum_SelectPageByCompany]
@CompanyId int,
@PageSize int,
@PageIndex int,
@ConsumeMode int,
@StartDate datetime,
@EndDate datetime,
@ColumnName nvarchar(50),
@ColumnValue nvarchar(50),
@MinMoney float,
@MaxMoney float
as
begin
select
dbo.OnceSum.Id AS '编号',
row_number() over (order by Driver.Number) as '序号' ,
dbo.Driver.Number AS '服务证号',
dbo.Driver.Name AS '姓名',
convert(nvarchar(10),dbo.OnceSum.Date,111) AS '日期',
subString(convert(nvarchar(7),dbo.OnceSum.StartDate,108),1,5) AS '上车时间',
subString(convert(nvarchar(7),dbo.OnceSum.EndDate,108),1,5) AS '下车时间',
dbo.OnceSum.WaitDates AS '等待时间（分钟）',
dbo.OnceSum.OperateDistance AS '营运里程（Km）',
convert(numeric(18,2),dbo.OnceSum.[Money]) AS '计费金额（元）',
dbo.OnceSum.EmptyDistance AS '空驶里程（Km）',
dbo.Fun_GetConsumeMode(dbo.OnceSum.ConsumeMode) AS '消费方式',
dbo.Car.Number AS '自编号',
dbo.Car.CarNumber AS '车牌号',
dbo.CarGroup.Name AS '公司',
dbo.CarGroup.Number AS '公司代码',
dbo.CarType.Name AS '车型',
convert(numeric(18,2),dbo.OnceSum.EmptyDistance/(dbo.OnceSum.OperateDistance+dbo.OnceSum.EmptyDistance)) as '空驶率'
from
dbo.Car ,
dbo.CarGroup ,
dbo.CarType ,
dbo.Company ,
dbo.Driver ,
dbo.OnceSum
where
dbo.Car.Id = dbo.Driver.CarId and
dbo.CarGroup.Id = dbo.Driver.CarGroupId and
dbo.CarType.Id = dbo.Car.CarTypeId and
dbo.Company.Id = dbo.CarGroup.CompanyId and
dbo.Company.Id = dbo.Car.CompanyId and
dbo.Driver.Id = dbo.OnceSum.DriverId and
dbo.Company.Id=@CompanyId and
(
(dbo.OnceSum.ConsumeMode = 0 and @ConsumeMode = 0 ) or
(dbo.OnceSum.ConsumeMode = 1 and @ConsumeMode = 1) or
@ConsumeMode = 2
) and
(
(@ColumnName='CarGroupId' and Convert(nvarchar(50),CarGroup.Id) like '%'+@ColumnValue+'%') or
(@ColumnName='Car_Number' and Car.Number like '%'+@ColumnValue+'%') or
(@ColumnName='DriverNumber' and Driver.Number like '%'+@ColumnValue+'%') or
(@ColumnName='CarNumber' and Car.CarNumber like '%'+@ColumnValue+'%') or
(@ColumnName='' and @ColumnValue='') or
(@ColumnName='CarGroupId' and @ColumnValue='')
) and
dbo.OnceSum.[Money] >= @MinMoney and
dbo.OnceSum.[Money] <= @MaxMoney and
dbo.OnceSum.[StartDate] >= @StartDate and
dbo.OnceSum.[EndDate] <= @EndDate
end

Answer 1

使用子查询可以分页
select top 1000 * from 表 where 主键 not in
(select top (页码*1000) 主键 from 表 )

第一个*号代表所有，
第二个*号代表乘以

Answer 2

我想如果你学过数列这个问题相对来说就好解决多了，数列对应的通项公式算出的值就是每页的第一行！对于自增量就是你每页显示数据的条目数了！

数据量非常大，比如说你每页显示十条数据，建立一个存储数据A的地方，先存放100条，客户端要的数据都在这里曲，A的数据都来源于B，b每次到数据库中读取整段数据，a.b类似于数据缓存！