使用原生ADO+DataSet内存表进行分页显示
ADO分页显示，通常有2种方式：
一种是利用原生ADO的页次定位功能，指定PageSize后，用AbsolutePage切换到Page的位置，而后再读取对应页的数据。但这种方式，必须使用本地游标（adUseClient）模式，会将服务上的数据全部下到本地，存在打开速度慢、服务器压力大的缺陷。
另一种构建复杂的SQL语句（或存储过程），采用SQL的TOP子句获取指定量的数据，效率很高。这种方式的SQL语句是特别改写的，无法做到原汁原味，有些特别复杂的SQL语句难以改写。
能否有第一种方式不用改写SQL语句而又高效便捷的ADO分页方式呢？
事实上，ADO 的服务游标下单向数据可以有很高的数据查询效率。
笔者采用原生ADO的服务游标下单向数据模式进行了分页显示尝试，取得了很好的效果。
实例：在一个15万的邮编库中取页大小为200的任一页数据，并在DBGRID中显示出来，耗时仅百毫秒。
Dcopyboy
Email：dcopyboy@tom.com
QQ:445235526
{**********
单元名称：使用原生ADO+DataSet内存表进行分页显示
创建日期：2010-07-18
创建者 卢良红 Dcopyboy
功能: 使用原生ADO+DataSet内存表t进行分页显示
当前版本：
Email：dcopyboy@tom.com
QQ:445235526
**********}
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, ADODB, Grids, DBGrids, StdCtrls, ExtCtrls, ComObj, OleDB, ADOInt;
type
TForm1 = class(TForm)
Panel1:TPanel;
Button1:TButton;
DBGrid1:TDBGrid;
DataSource1:TDataSource;
DadoQ:TADODataSet;
Button2:TButton;
Button3:TButton;
Button4:TButton;
Label1:TLabel;
procedure Button1Click(Sender:TObject);
procedure Button4Click(Sender:TObject);
procedure Button2Click(Sender:TObject);
procedure Button3Click(Sender:TObject);
private
{ Private declarations }
aPage:Integer;
public
{ Public declarations }
end;
var
Form1:TForm1;
implementation
{$R *.dfm}
//实现分页处理的核心过程
function SavePageToQuery(SQLText:string; Dadoq:TADODataSet; PageSize,
Page:integer):integer;
function ADOTypeToFieldType(const ADOType:DataTypeEnum; EnableBCD:Boolean =
True):TFieldType;
begin
case ADOType of
adEmpty:Result := ftUnknown;
adTinyInt, adSmallInt:Result := ftSmallint;
adError, adInteger, adUnsignedInt:Result := ftInteger;
adBigInt, adUnsignedBigInt:Result := ftLargeInt;
adUnsignedTinyInt, adUnsignedSmallInt:Result := ftWord;
adSingle, adDouble:Result := ftFloat;
adCurrency:Result := ftBCD;
adBoolean:Result := ftBoolean;
adDBDate:Result := ftDate;
adDBTime:Result := ftTime;
adDate, adDBTimeStamp, adFileTime, adDBFileTime:Result := ftDateTime;
adChar:Result := ftFixedChar;
adVarChar:Result := ftString;
adBSTR, adWChar, adVarWChar:Result := ftWideString;
adLongVarChar, adLongVarWChar:Result := ftMemo;
adLongVarBinary:Result := ftBlob;
adBinary:Result := ftBytes;
adVarBinary:Result := ftVarBytes;
adChapter:Result := ftDataSet;
adPropVariant, adVariant:Result := ftVariant;
adIUnknown:Result := ftInterface;
adIDispatch:Result := ftIDispatch;
adGUID:Result := ftGUID;
adDecimal, adNumeric, adVarNumeric:
if EnableBCD then Result := ftBCD
else Result := ftFloat;
else
Result := ftUnknown;
end;
end;
procedure AddFieldDef(F:variant; FieldDefs:TFieldDefs);
var
FieldType:TFieldType;
FieldDef:TFieldDef;
I:Integer;
FName:string;
FSize:Integer;
FPrecision:Integer;
begin
FieldType := ADOTypeToFieldType(F.Type, true);
if FieldType <> ftUnknown then
begin
FSize := 0;
FPrecision := 0;
FieldDef := FieldDefs.AddFieldDef;
with FieldDef do
begin
FieldNo := FieldDefs.Count;
I := 0;
FName := F.Name;
while (FName = '') or (FieldDefs.IndexOf(FName) >= 0) do
begin
Inc(I);
if F.Name = '' then
FName := Format('COLUMN%d', [I]) else { Do not localize }
FName := Format('%s_%d', [F.Name, I]);
end;
Name := FName;
if (F.Type = adNumeric) and (F.NumericScale = 0) and
(F.Precision < 10) then
FieldType := ftInteger;
case FieldType of
ftString, ftWideString, ftBytes, ftVarBytes, ftFixedChar:
FSize := F.DefinedSize;
ftBCD:
begin
FPrecision := F.Precision;
FSize := ShortInt(F.NumericScale);
if FSize < 0 then FSize := 4;
end;
ftInteger:FSize := 4;
ftGuid:FSize := 38;
end;
if ((adFldRowID and F.Attributes) <> 0) then
Attributes := Attributes + [faHiddenCol];
if ((adFldFixed and F.Attributes) <> 0) then
Attributes := Attributes + [faFixed];
if (((adFldUpdatable + adFldUnknownUpdatable) and F.Attributes) = 0) or
(FieldType = ftAutoInc) then
Attributes := Attributes + [faReadOnly];
DataType := FieldType;
Size := FSize;
Precision := FPrecision;
end;
end;
end;
var
x, i:word;
a, RecordCount:integer;
AConnection, SQuery:variant;
begin
//有人说，ADO在打开时就会读入全部数据，这似乎不可能哦！
//笔者采用15万的邮编库，保存到文件中的流大小约5.8M，正常打开
//用时数毫秒，移动数据指针用时百毫秒，这就是单向数据快的地方
//为了保证能双向翻页，不的不每次重新打开数据
AConnection := CreateOleObject('ADODB.Connection');
AConnection.Open('Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=AYZD;Data Source=.');
a := GetTickCount;
SQuery := CreateOleObject('ADODB.RecordSet');
// SQuery.CursorLocation:=adUseClient; //不能采用本地游标，否则超慢！！
//下面这几句是为了取得记录数的，不用每次均查吧！！
{SQuery.open('select count(*) as RecordCount from (' + SQLText + ') as bb',
AConnection, adOpenForwardOnly, adLockReadOnly, adCmdText);
RecordCount := SQuery.Fields['RecordCount'].Value;
SQuery.close;}
RecordCount:=153726;
SQuery.open(SQLText, AConnection, adOpenForwardOnly,
adLockReadOnly, adCmdText);
if page <= 0 then page := 1;
if (page - 1) * pagesize > RecordCount then begin
page := RecordCount div PageSize;
if page * pagesize < RecordCount then
inc(page);
end;
Result := Page;
//服务器端游标时下列2句不能用
{SQuery.PageSize := PageSize;
SQuery.AbsolutePage := Page;}
SQuery.move((page - 1) * PageSize);
//下边这段是要讲数据转换到 ADODataSet，以便在DBGRID中直接显示
//如采用StringGrid则要自己重写
Dadoq.DisableControls;
Dadoq.Close;
Dadoq.FieldDefs.Clear;
for i := 0 to SQuery.Fields.count - 1 do
AddFieldDef(SQuery.Fields[I], Dadoq.FieldDefs);
Dadoq.CreateDataSet;
Dadoq.Open;
for x := 0 to PageSize - 1 do begin
Dadoq.Append;
for i := 0 to SQuery.fields.count - 1 do begin
try
Dadoq.Fields[i].Value := SQuery.Fields[i].Value;
except
end;
end;
Dadoq.post;
SQuery.MoveNext;
if SQuery.Eof then break;
end;
Dadoq.EnableControls;
Form1.Label1.Caption := '用时:' + inttostr(GetTickCount - a);
SQuery.close;
AConnection.close;
end;
procedure TForm1.Button1Click(Sender:TObject);
begin
aPage := SavePageToQuery('select * from 邮编', DadoQ, 200, 1);
end;
procedure TForm1.Button4Click(Sender:TObject);
begin
aPage := SavePageToQuery('select * from 邮编', DadoQ, 200, 999999);
end;
procedure TForm1.Button2Click(Sender:TObject);
begin
aPage := SavePageToQuery('select * from 邮编', DadoQ, 200, aPage - 1);
end;
procedure TForm1.Button3Click(Sender:TObject);
begin
aPage := SavePageToQuery('select * from 邮编', DadoQ, 200, aPage + 1);
end;
end. |
