澳门新葡萄京官网注册delphi 辨析 Field、FieldDef、Fields、FieldDefs、FieldLi

1、Fields 是 Field 的集合, 它们主要用于运行时对字段元数据和字段值的访问.

unit uDBJson;

近期因做一个项目,需要用到交叉表,报表上倒是有,但客户要求在Grid上能操作,没有办法,只好自己写了一段代码用于普通查询到交叉表的实现,不敢独享,故上传,望能抛砖引玉,请名位大侠不吝指教。

2、FieldDefs 是 FieldDef 的集合, 它们主要用于构建数据集(表)和对字段元数据的访问.

interface

function CreateTmptab(const AFieldDefs:TFieldDefs):TDataSet;
var
TempTable:TatClientDataSet;
begin
TempTable:=nil;
Result:=nil;
if AFieldDefs<>nil then
begin
    try
        TempTable:=TatClientDataSet.Create(Application);
        TempTable.FieldDefs.Assign(AFieldDefs);
        TempTable.CreateDataSet;
        Result:=(TempTable as TDataSet);
    Except
    if TempTable<>nil then
        TempTable.Free;
        Result:=nil;
        raise;
    end
end;
end;
{
SouDataset源数据集
ColField交叉表动态列字段
RowField交叉表行字段
DataField数据字段
}
function
GenCrossTable(SouDataset:tdataset;ColField,RowField,DataField:string):tdataset;
var
Vdataset:tdataset;
tmpdataset:tatclientdataset;
DataSource:tdatasource;
tmpstrs:tstrings;
rowval,colval,dataval:string;
i,j:integer;
datatype:TFieldType;
DataSize:integer;
begin
result:=nil;
if (ColField=) or(RowField=)or(DataField=) then
  showmessage(All Field not be NULL!)
else
begin
  if (ColField=RowField)
      or(ColField=DataField)
      or(RowField=DataField) then
    showmessage(All Field not be Equ!)
  else
  if (self.SouDataSet.FieldByName(ColField).DataType=ftString)
    or
(self.SouDataSet.FieldByName(ColField).DataType<>ftWideString)
    or
(self.SouDataSet.FieldByName(ColField).DataType<>ftFixedChar)
    or (self.SouDataSet.FieldByName(ColField).DataType<>ftMemo)
    or
(self.SouDataSet.FieldByName(ColField).DataType<>ftFmtMemo)  then
  begin
  try
    tmpstrs:=tstringlist.Create;
    Vdataset:=SouDataSet;
    Vdataset.First;
    for i:=0 to Vdataset.RecordCount-1 do
    begin
      if (varisnull(SouDataSet.FieldValues[colfield])=false) and
(SouDataSet.FieldValues[colfield]<>) then
        if tmpstrs.IndexOf(SouDataSet.FieldValues[colfield])=-1 then
        begin
          tmpstrs.Add(SouDataSet.FieldValues[colfield]);
        end;
      Vdataset.Next;
    end;
    //生成动态列标题
    tmpdataset:=TClientDataSet.Create(Self);
    tmpdataset.FieldDefs.Add(rowfield,ftstring,50,False);
    for i:=0 to tmpstrs.Count-1 do
    begin
      with tmpdataset.FieldDefs do
      begin
        Add(tmpstrs.Strings[i],ftInteger,0,False);
      end;
    end;
    tmpdataset.FieldDefs.Add(Sum,ftInteger,0,False);
    DataSource:=tdatasource.Create(self);
    DataSource.DataSet:=tmpdataset;
    with DataSource do
    begin
      dataset:=Createtmptab(tmpdataset.FieldDefs);
      dataset.Open;
    end;
    //建立临时表
    Vdataset.First;
    for i:=0 to Vdataset.RecordCount-1 do
    begin
      rowval:=SouDataSet.fieldbyname(rowfield).AsString;
      colval:=SouDataSet.fieldbyname(colfield).AsString;
      dataval:=SouDataSet.fieldbyname(datafield).AsString;
      if dataval= then dataval:=0;
      if DataSource.DataSet.Locate(rowfield,rowval,[loPartialKey])
then
      begin
        DataSource.DataSet.Edit;
        DataSource.DataSet.FieldByName(colval).AsString:=dataval;
        DataSource.DataSet.FieldByName(Sum).AsInteger:=
          DataSource.DataSet.FieldByName(Sum).AsInteger+strtoint(dataval);
        DataSource.DataSet.Post;
      end
      else
      begin
        DataSource.DataSet.Append;
        DataSource.DataSet.FieldByName(rowfield).AsString:=rowval;
        for j:=1 to DataSource.DataSet.Fields.Count-1 do
          DataSource.DataSet.Fields[j].AsCurrency:=0;
        DataSource.DataSet.FieldByName(colval).AsString:=dataval;
        DataSource.DataSet.FieldByName(Sum).AsString:=dataval;
        DataSource.DataSet.Post;
      end;
      Vdataset.Next;
    end;
    result:=DataSource.DataSet;
    //生成交叉表数据集
    tmpstrs.Free;
  except
  end;
  end
  else
    showmessage(ColField Must be of Type String!) ;
end;
end;

3、FieldList 和 FieldDefList 分别是访问 Field 和 FieldDef 的快捷列表; 主要使用其 FieldByName、Find 方法和默认的数组属性访问数据; 它们是只读的.

{$HINTS OFF}

以上代码在D7和SQL Server 7.0/2000测试通过

4、通过 Fields、FieldList、Field 可以得到更多信息, 但必须是在数据集打开的情况下;
通过 FieldDefs、FieldDefList、FieldDef 只能获取定义时的信息, 但即使在数据集关闭时也能使用.

uses
  SysUtils, Classes, Variants, DB, DBClient, SuperObject;

5、顾名思义 FieldDef 是用于定义表的, 但通过 Field 也可以定义表;
用 FieldDef 定义表很方便, 用 Field 可以定义一些更复杂的表;
每个 FieldDef 都会对应一个 Field, 但一个 Field 不一定有 FieldDef 对应;
程序运行后 FieldDef 不能再改变, 而 Field 与 Fields 则可以动态改变或增减.

type
  TTableJSon = class

设计时两者是结合使用的.

  private const
    cstFieldType = ‘FieldType’;

澳门新葡萄京官网注册,  下面是通过三种方法动态建表的代码:

  const
    cstFieldName = ‘FieldName’;

//使用 TFieldDef 建表:  
begin 
 with  ClientDataSet1.FieldDefs do 
 begin 
  Add(‘Name’ , ftString, 12,  True); { True 表示是必填字段 } 
  Add(‘Age’, ftInteger); 
 end; 
  ClientDataSet1.CreateDataSet; 
end; 
 
//使用 TField(这里是用其子类)建表: 
begin  
 with TStringField.Create(Self) do 
 begin 
  FieldName :=  ‘Name’; 
  Size := 12; 
  Required := True; { 必填字段 } 
  DataSet  := ClientDataSet1; 
 end; 
 with TIntegerField.Create(Self) do 
  begin 
  FieldName := ’Age’; 
  DataSet := ClientDataSet1; 
  end; 
 ClientDataSet1.CreateDataSet; 
end; 
 
//混合使用(这好像就是设计时的情形): 
var 
 F: TIntegerField; 
begin 
 with  ClientDataSet1.FieldDefs.AddFieldDef do 
 begin 
  Name := ’Name’;  
  DataType := ftString; 
  Size := 12; 
  Required := True; 
  CreateField(ClientDataSet1); 
 end; 
 with  ClientDataSet1.FieldDefs.AddFieldDef do 
 begin 
  Name := ’Age’; 
  DataType := ftInteger; 
  { 指定最大值和最小值 } 
  F :=  CreateField(ClientDataSet1) as TIntegerField; 
  F.MinValue := 0; 
  F.MaxValue := 150; 
 end; 
 ClientDataSet1.CreateDataSet; 
end; 

  const
    cstFieldSize = ‘FieldSize’;

  const
    cstJsonType = ‘JsonType’;

  const
    cstRequired = ‘Required’;

  const
    cstFieldIndex = ‘FieldIndex’;

  const
    cstCols = ‘Cols’;

  const
    cstData = ‘Data’;

  public
    class function DataSetToJson(DataSet: TDataSet): ISuperObject;
    class function DataSetToJson2(DataSet: TDataSet): string;
    class function CreateFieldByJson(Fields: TFieldDefs;
      ColsJson: ISuperObject): Boolean;
    class procedure ImportDataFromJSon(DataSet: TDataSet;
      DataJson: ISuperObject);
    class function JSonToClientDataset(CDS: TClientDataSet; Json:
ISuperObject)
      : Boolean;
    class function GetValue(Json: ISuperObject; const Name: string):
Variant;

    class function CreateJsonValue(Json: ISuperObject; const Name:
string;
      const Value: Variant): Boolean;
    class function CreateJsonValueByField(Json: ISuperObject;
      Field: TField): Boolean;
    class function GetValue2Field(Field: TField;
      JsonValue: ISuperObject): Variant;
  end;

implementation

uses TypInfo, encddecd;

{ TTableJSon }

class function TTableJSon.JSonToClientDataset(CDS: TClientDataSet;
  Json: ISuperObject): Boolean;
var
  ColsJson: ISuperObject;
begin
  Result := False;
  if Json = nil then
    Exit;
  CDS.Close;
  CDS.Data := Null;
  // 创建字段
  ColsJson := Json.O[cstCols];
  CreateFieldByJson(CDS.FieldDefs, ColsJson);
  if CDS.FieldDefs.Count > 0 then
    CDS.CreateDataSet;
  ImportDataFromJSon(CDS, Json.O[cstData]);
  Result := True;
end;

class function TTableJSon.CreateFieldByJson(Fields: TFieldDefs;
  ColsJson: ISuperObject): Boolean;
var
  SubJson: ISuperObject;
  ft: TFieldType;
begin
  Result := False;
  Fields.DataSet.Close;
  Fields.Clear;
  for SubJson in ColsJson do
  begin
    ft := TFieldType(GetEnumValue(TypeInfo(TFieldType),
      ‘ft’ + SubJson.S[cstFieldType]));
    if ft = ftAutoInc then // 自增字段不能录入,必须更改
      ft := ftInteger;
    Fields.Add(SubJson.S[cstFieldName], ft,
SubJson.I[cstFieldSize],
      SubJson.B[cstRequired]);
  end;
  Result := True;
end;

class function TTableJSon.CreateJsonValue(Json: ISuperObject;
  const Name: string; const Value: Variant): Boolean;
begin
  Result := False;
  Json.O[Name] := SO(Value);
  Result := True;
end;

class function TTableJSon.CreateJsonValueByField(Json: ISuperObject;
  Field: TField): Boolean;
begin
  Result := False;
  if Field Is TDateTimeField then
    Json.O[Field.FieldName] := SO(Field.AsDateTime)
  else if Field is TBlobField then
    Json.S[Field.FieldName] := EncodeString(Field.AsString)
  else
    Json.O[Field.FieldName] := SO(Field.Value);
  Result := True;
end;

class function TTableJSon.GetValue(Json: ISuperObject;
  const Name: string): Variant;
begin
  case Json.DataType of
    stNull:
      Result := Null;
    stBoolean:
      Result := Json.B[Name];
    stDouble:
      Result := Json.D[Name];
    stCurrency:
      Result := Json.C[Name];
    stInt:
      Result := Json.I[Name];
    stString:
      Result := Json.S[Name];
  end;
end;

class function TTableJSon.GetValue2Field(Field: TField;
  JsonValue: ISuperObject): Variant;
begin
  if JsonValue.DataType = stNull then
    Result := Null
  else if Field is TDateTimeField then
    Result := JavaToDelphiDateTime(JsonValue.AsInteger)
  else if (Field is TIntegerField) or (Field is TLargeintField) then
    Result := JsonValue.AsInteger
  else if Field is TNumericField then
    Result := JsonValue.AsDouble
  else if Field is TBooleanField then
    Result := JsonValue.AsBoolean
  else if Field is TStringField then
    Result := JsonValue.AsString
  else if Field is TBlobField then
    Result := DecodeString(JsonValue.AsString)
end;

class procedure TTableJSon.ImportDataFromJSon(DataSet: TDataSet;
  DataJson: ISuperObject);
var
  SubJson: ISuperObject;
  iter: TSuperObjectIter;
begin
  if not DataSet.Active then
    DataSet.Open;
  DataSet.DisableControls;
  try
    for SubJson in DataJson do
    begin
      DataSet.Append;
      if ObjectFindFirst(SubJson, iter) then
      begin
        repeat
          if DataSet.FindField(iter.Ite.Current.Name) <> nil
then
            DataSet.FindField(iter.Ite.Current.Name).Value :=
              GetValue2Field(DataSet.FindField(iter.Ite.Current.Name),
              iter.Ite.Current.Value);
        until not ObjectFindNext(iter);
      end;
      DataSet.Post;
    end;
  finally
    DataSet.EnableControls;
  end;
end;

class function TTableJSon.DataSetToJson(DataSet: TDataSet):
ISuperObject;
  procedure GetFieldTypeInfo(Field: TField; var Fieldtyp, JsonTyp:
string);
  begin
    Fieldtyp := GetEnumName(TypeInfo(TFieldType),
ord(Field.DataType));
    Delete(Fieldtyp, 1, 2);
    if Field is TStringField then
      JsonTyp := ‘string’
    else if Field is TDateTimeField then
      JsonTyp := ‘integer’
    else if (Field is TIntegerField) or (Field is TLargeintField) then
      JsonTyp := ‘integer’
    else if Field is TCurrencyField then
      JsonTyp := ‘currency’
    else if Field is TNumericField then
      JsonTyp := ‘double’
    else if Field is TBooleanField then
      JsonTyp := ‘boolean’
    else
      JsonTyp := ‘variant’;
  end;

var
  sj, aj, sj2: ISuperObject;
  I: Integer;
  Fieldtyp, JsonTyp: string;
  List: TStringList;
begin
  sj := SO();
  // 创建列
  aj := SA([]);
  List := TStringList.Create;
  try
    List.Sorted := True;

    for I := 0 to DataSet.FieldCount – 1 do
    begin
      sj2 := SO();
      GetFieldTypeInfo(DataSet.Fields[I], Fieldtyp, JsonTyp);

      sj2.S[cstFieldName] := DataSet.Fields[I].FieldName;
      sj2.S[cstFieldType] := Fieldtyp;
      sj2.S[cstJsonType] := JsonTyp;
      sj2.I[cstFieldSize] := DataSet.Fields[I].Size;
      sj2.B[cstRequired] := DataSet.Fields[I].Required;
      sj2.I[cstFieldIndex] := DataSet.Fields[I].Index;
      aj.AsArray.Add(sj2);
      List.Add(DataSet.Fields[I].FieldName + ‘=’ + JsonTyp);
    end;
    sj.O[‘Cols’] := aj;
    // 创建数据集的数据
    DataSet.DisableControls;

    DataSet.First;
    aj := SA([]);
    while not DataSet.Eof do
    begin
      sj2 := SO();
      for I := 0 to DataSet.FieldCount – 1 do
      begin
        if VarIsNull(DataSet.Fields[I].Value) then
          sj2.O[DataSet.Fields[I].FieldName] := SO(Null)
        else
        begin
          CreateJsonValueByField(sj2, DataSet.Fields[I]);
        end;
      end;
      aj.AsArray.Add(sj2);
      DataSet.Next;
    end;
    sj.O[‘Data’] := aj;
    Result := sj;
  finally
    List.Free;
    DataSet.EnableControls;
  end;
end;

class function TTableJSon.DataSetToJson2(DataSet: TDataSet): string;
  procedure GetFieldTypeInfo(Field: TField; var Fieldtyp, JsonTyp:
string);
  begin
    Fieldtyp := GetEnumName(TypeInfo(TFieldType),
ord(Field.DataType));
    Delete(Fieldtyp, 1, 2);
    if Field is TStringField then
      JsonTyp := ‘string’
    else if Field is TDateTimeField then
      JsonTyp := ‘integer’
    else if (Field is TIntegerField) or (Field is TLargeintField) then
      JsonTyp := ‘integer’
    else if Field is TCurrencyField then
      JsonTyp := ‘currency’
    else if Field is TNumericField then
      JsonTyp := ‘double’
    else if Field is TBooleanField then
      JsonTyp := ‘boolean’
    else
      JsonTyp := ‘variant’;
  end;

var
  sj, aj, sj2: ISuperObject;
  I: Integer;
  Fieldtyp, JsonTyp: string;
  List: TStringList;
begin
  sj := SO();
  // 创建列
  aj := SA([]);
  List := TStringList.Create;
  try
    List.Sorted := True;

    for I := 0 to DataSet.FieldCount – 1 do
    begin
      sj2 := SO();
      GetFieldTypeInfo(DataSet.Fields[I], Fieldtyp, JsonTyp);

      sj2.S[cstFieldName] := DataSet.Fields[I].FieldName;
      sj2.S[cstFieldType] := Fieldtyp;
      sj2.S[cstJsonType] := JsonTyp;
      sj2.I[cstFieldSize] := DataSet.Fields[I].Size;
      sj2.B[cstRequired] := DataSet.Fields[I].Required;
      sj2.I[cstFieldIndex] := DataSet.Fields[I].Index;
      aj.AsArray.Add(sj2);
      List.Add(DataSet.Fields[I].FieldName + ‘=’ + JsonTyp);
    end;
    sj.O[‘Cols’] := aj;
    // 创建数据集的数据
    DataSet.DisableControls;

    DataSet.First;
    aj := SA([]);
    while not DataSet.Eof do
    begin
      sj2 := SO();
      for I := 0 to DataSet.FieldCount – 1 do
      begin
        if VarIsNull(DataSet.Fields[I].Value) then
          sj2.O[DataSet.Fields[I].FieldName] := SO(Null)
        else
        begin
          CreateJsonValueByField(sj2, DataSet.Fields[I]);
        end;
      end;
      aj.AsArray.Add(sj2);
      DataSet.Next;
    end;
    sj.O[‘Data’] := aj;
    Result := sj.AsString;
  finally
    List.Free;
    DataSet.EnableControls;
  end;
end;

end.

发表评论

电子邮件地址不会被公开。 必填项已用*标注