JUST SHARE - MySQL DataSet into Json Format using delphi SuperObject

Question

Just share my experience using Json using Delphi SuperObject

The idea is how to convert my query result into json format easily.

I use string to represent all data format (including BLOB, DATE, DATETIME). In future the json file can be imported into database easily.

I do not write the code from scratch, i just made a little modification suited into my own need

Here is the code :

function TTableJSon.CreateJsonValueByFieldMySql(Json: ISuperObject;
  Field: TField): Boolean;
var
  JsonTyp, FieldTyp : string;
  tmpStr : string;
begin
  Result := False;
  if Field Is TDateField then begin
      Json.O[Field.FieldName] := SO('"'+FormatDateTime('yyyy-mm-dd',Field.AsDateTime)+'"')
  end else if Field Is TDateTimeField then begin
      Json.O[Field.FieldName] := SO('"'+FormatDateTime('yyyy-mm-dd hh:nn:ss',Field.AsDateTime)+'"')
  end else if Field is TMemoField then begin
      Json.S[Field.FieldName] := EncodeString(Field.AsString)
  end else if Field is TBlobField then begin
      Json.S[Field.FieldName] := EncodeString(Field.AsString)
  end else if Field is TFloatField then begin
      Json.O[Field.FieldName] := SO(ReplaceStr(Field.AsString,',','.'))
  end else begin
        Json.O[Field.FieldName] := SO(Field.Value);
  end;
  Result := True;
end;


function TTableJSon.JSonFromDataSet(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 TMemoField then
        JsonTyp := 'memo'
      else if Field is TBlobField then
        JsonTyp := 'blob'
      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
            CreateJsonValueByFieldMySql(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;