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;
