Conversion between datasets and JSON objects

In Delphi, datasets are the most common data access methods. Therefore, the interaction between JSON and tdataset must be established to achieve communication and conversion between data. It is worth noting that this is only a normal conversion between tdataset and JSON. Because CDs contains delta data packets, its data format is far more complex than ordinary tdataset.
 
The dataset field information is a complete dictionary information. Therefore, we must also create dictionary information in JSON to create the field information of the dataset. The JSON information is set as follows:
Cols: [field list information], for example:
"Cols": [{"jsontype": "integer", "fieldindex": 0, "fieldtype": "integer", "fieldsize": 0, "fieldname ": "ID", "required": false },{ "jsontype": "string", "fieldindex": 1, "fieldtype": "string", "fieldsize": 100, "fieldname": "title", "required": false },{ "jsontype": "variant", "fieldindex": 2, "fieldtype": "blob ", "fieldsize": 0, "fieldname": "picture", "required": false}]
The data information uses data as the node and is also an array nested record information:
"Data": [record set information]
Put the following code directly:
Unit udbjson;
Interface
Uses
Sysutils, classes, variants, DB, dbclient, superobject;
Type
Ttablejson = Class
Private
Const cstfieldtype = 'fieldtype ';
Const cstfieldname = 'fieldname ';
Const cstfieldsize = 'fieldsize ';
Const cstjsontype = 'jsontype ';
Const cstrequired = 'required ';
Const cstfieldindex = 'fieldindex ';
Const cstcols = 'cols ';
Const cstdata = 'data ';
Public
Class function jsonfromdataset (Dataset: tdataset): string;
Class function createfieldbyjson (fields: tfielddefs; colsjson: isuperobject): Boolean;
Class function importdatafromjson (Dataset: tdataset; datajson: isuperobject): integer;
Class function cdsfromjson (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. cdsfromjson (CDS: tclientdataset;
JSON: isuperobject): Boolean;
VaR
Colsjson: isuperobject;
Begin
Result: = false;
If JSON = nil then
Exit;
CDs. close;
CDs. Data: = NULL;
// Create a field
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 // The auto-increment field cannot be entered and must be changed
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
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 function ttablejson. importdatafromjson (Dataset: tdataset;
Datajson: isuperobject): integer;
VaR
Subjson: isuperobject;
I: integer;
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. 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 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 ();
// Create a column
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;
// Create the dataset data
Dataset. disablecontrols;
Dataset. first;
AJ: = SA ([]);
While not dataset. EOF do
Begin
Sj2: = So ();
For I: = 0 to dataset. fieldcount-1 do
Begin
// Sj2.s [inttostr (Dataset. Fields [I]. Index)]: = vartostrdef (Dataset. Fields [I]. Value ,'');
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.
Call example:
// Convert a dataset to a JSON object or JSON text
VaR
JSON: ttablejson;
S: string;
Begin
S: = JSON. jsonfromdataset (adodataset1 );
// Use tstringstream to read the string S and save it as text to see its format.
End;
// Loads a JSON object or text to a dataset
VaR
JSON: isuperobject;
Begin
JSON: = tsuperobject.parsefile('json.txt ', false );
Ttablejson. cdsfromjson (cdsjson, JSON );
End;