Delphi 数据分页显示(存储过程及ado控件)

2016-08-22 10:23:04来源:http://jayai.blog.51cto.com/510093/1199775作者:aammjian人点击


1、ado控件,缺点是速度慢:

procedure showViewData(PageSize,AbsolutePage : Integer);

var

i : Integer;

begin

try

adoPCustomer.Recordset.PageSize := PageSize;

if adoPCustomer.RecordCount<1 then

Exit;

adoPCustomer.Recordset.AbsolutePage :=AbsolutePage;

if adoPCustomer.Recordset.PageCount<> AbsolutePage then

grdTVCustomer.DataController.RecordCount := adoPCustomer.Recordset.PageSize

else

grdTVCustomer.DataController.RecordCount := adoPCustomer.RecordCount-adoPCustomer.Recordset.PageSize*(AbsolutePage-1);

adoPCustomer.First;

adoPCustomer.MoveBy((AbsolutePage-1)*adoPCustomer.Recordset.PageSize);

grdTVCustomer.BeginUpdate;

for i :=0 to grdTVCustomer.DataController.RecordCount-1 do

begin

grdTVCustomer.DataController.Values[i,0] := adoPCustomer.FieldbyName('编号').AsString;

grdTVCustomer.DataController.Values[i,1] := adoPCustomer.FieldbyName('客户代码').AsString;

grdTVCustomer.DataController.Values[i,2] := adoPCustomer.FieldbyName('客户名称').AsString;

adoPCustomer.Next;

end;

grdTVCustomer.EndUpdate;

grdTVCustomer.ApplyBestFit();

cxTextEdit1.Text := IntToStr(AbsolutePage);

except

on e:Exception do

raise Exception.Create('执行查询时出错!'+#13+'出错原因:'+e.Message);

end;

end;

2、SQL存储过程分页,缺点是每次都要查询:

ALTER PROC [dbo].[P_Customer_QuerySaleCustomer_分页]

(

@User NVARCHAR(10),

@PageSize INT, --显示页数

@CurrPage INT, --当前页数

@RecordCount NVARCHAR(10) OUTPUT --总页数

)

AS

BEGIN

SET @RecordCount=(SELECT COUNT(1) FROM dbo.T_Customer_Sale (NOLOCK) WHERE CardOwner=@User OR IsShared=0);

WITH CUSTOMER AS (

SELECT TOP (@PageSize * @CurrPage)

ROW_NUMBER() OVER (ORDER BY ID)ROWNUMBER ,CardCode 客户代码, CardName 客户名称

,CASE IsHot WHEN 0 THEN '是' ELSE '否' END 是否热点

,CASE HotLev WHEN 0 THEN '低' WHEN 1 THEN '中' ELSE '高' END 热点程度

,CASE HotType WHEN 0 THEN '新客户' WHEN 1 THEN '老客户' ELSE '新合作' END 热点类型, HotNote 热点说明

,CASE CreditLev WHEN 0 THEN '低' WHEN 1 THEN '中' ELSE '高' END 信用等级

,(SELECT DicName FROM dbo.T_Customer_Dictionary WHERE ID=CardType) 类型

,(SELECT DicName FROM dbo.T_Customer_Dictionary WHERE ID=CardIndustry) 行业

,(SELECT DicName FROM dbo.T_Customer_Dictionary WHERE ID=CardSource) 来源

,(SELECT DicName FROM dbo.T_Customer_Dictionary WHERE ID=CardCusLev) 关系等级

,(SELECT DicName FROM dbo.T_Customer_Dictionary WHERE ID=CardSize) 规模

,CardNote 备注, CardAddress 地址

,(SELECT DicName FROM dbo.T_Customer_Dictionary WHERE ID=CardProvince) 省份, CardCity 城市

,(SELECT DicName FROM dbo.T_Customer_Dictionary WHERE ID=CardArea) 区域, CardWeb 网址,ConRate 维护周期,

IsShared 是否共享,CardOwner 所有者

FROM dbo.T_Customer_Sale (NOLOCK)

WHERE CardOwner=@User OR IsShared=0

)

SELECT *

FROM CUSTOMER

WHERE ROWNUMBER > ((@CurrPage-1) *@PageSize)

--PRINT @RecordCount

END


最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台