这个里面有点小问题，不知道发布者自己到底调试了没有，还是直接拷贝。
现将修正后能完全通过的给到大家。
function DBToJSON(DB: TFDMemTable; sTable: string; sKey: string; sNoField: string): string;
var
ItemKey, ItemNoField: TStringlist;
JA: TJSONArray;
Jvalue :TJSONValue;
sField, sValue, sSQL, sTmp, sName,formatkey: string;
i, j: Integer;
begin
JA := TJSONArray.Create;
ItemKey := TStringlist.Create;
ItemNoField := TStringlist.Create;
ItemKey.Delimiter := ',';
ItemKey.DelimitedText := sKey;
ItemNoField.Delimiter := ',';
ItemNoField.DelimitedText := sNoField;
sField := ';
sValue := ';
sSQL := ';
with DB.Delta.DataView.Rows do
begin
for I := 0 to Count - 1 do
begin
sField := ';
sValue := ';
sSQL := ';
//判断数据的操作状态 :插入
if ItemsI[i].RowState = TFDDatSRowState.rsInserted then
begin
//循环对应的数据字段
for j := 0 to DB.Fields.Count - 1 do
begin
//获取字段名称
sName := DB.Fields[J].FieldName;
//排除不需要插入的字段信息
if ItemNoField.IndexOf(sName) > -1 then
Continue;
//判断字段的数据类型
case DB.Fields[J].DataType of
ftString, ftWideString, ftMemo:
sTmp := VarToStr(ItemsI[i].GetValues(sName)).QuotedString;
ftSmallint, ftInteger, ftFloat, ftCurrency, ftBCD:
sTmp := VarToStr(ItemsI[i].GetValues(sName));
ftDateTime:
begin
sTmp := VarToStr(ItemsI[i].GetValues(sName));
//日期如何没有填写默认为Null
if sTmp = ' then
sTmp := 'Null'
else
sTmp := FormatDateTime('yyyy-mm-dd hh:ss:mm', StrToDateTime(sTmp)).QuotedString;
end;
ftBoolean:
begin
if ItemsI[i].GetValues(sName) = True then
sTmp := '1'
else
sTmp := '0';
end;
else
sTmp := VarToStr(ItemsI[i].GetValues(sName)).QuotedString;
end;
//累积字段和插入值
sField := sField + sName + ',';
sValue := sValue + sTmp + ',';
end;
//拼接成SQL并插入到数组中
JA.Add('Insert Into ' + sTable + ' (' + Copy(sField, 1, Length(sField) - 1) + ')' + #13#10 + ' Values(' + Copy(sValue, 1, Length(sValue) - 1) + ')' + #13#10);
end
//判断数据的操作状态 :修改
else if ItemsI[i].RowState in [TFDDatSRowState.rsModified, TFDDatSRowState.rsEditing] then
begin
for j := 0 to DB.Fields.Count - 1 do
begin
sName := DB.Fields[J].FieldName;
//排除不需要插入的字段信息
if ItemNoField.IndexOf(sName) > -1 then
Continue;
//判断字段的数据类型
case DB.Fields[J].DataType of
ftString, ftWideString, ftMemo:
sTmp := VarToStr(ItemsI[i].GetValues(sName)).QuotedString;
ftSmallint, ftInteger, ftFloat, ftCurrency, ftBCD:
sTmp := VarToStr(ItemsI[i].GetValues(sName));
ftDateTime:
begin
sTmp := VarToStr(ItemsI[i].GetValues(sName));
if sTmp = ' then
sTmp := 'Null'
else
sTmp := FormatDateTime('yyyy-mm-dd hh:ss:mm', StrToDateTime(sTmp)).QuotedString;
end;
ftBoolean:
begin
if ItemsI[i].GetValues(sName) = True then
sTmp := '1'
else
sTmp := '0';
end;
else
sTmp := VarToStr(ItemsI[i].GetValues(sName)).QuotedString;
end;
//累积更新字段值
sField := sField + sName + '=' + sTmp + ',';
if ItemKey.IndexOf(sName) > -1 then
case DB.Fields[J].DataType of
ftString, ftWideString, ftMemo:
formatkey := VarToStr(ItemsI[i].GetData(sName,rvOriginal)).QuotedString;
ftSmallint, ftInteger, ftFloat, ftCurrency, ftBCD:
formatkey := VarToStr(ItemsI[i].GetData(sName,rvOriginal));
ftDateTime:
begin
formatkey := VarToStr(ItemsI[i].GetData(sName,rvOriginal));
if formatkey = ' then
formatkey := 'Null'
else
formatkey := FormatDateTime('yyyy-mm-dd hh:ss:mm', StrToDateTime(formatkey)).QuotedString;
end;
ftBoolean:
begin
if ItemsI[i].GetData(sName,rvOriginal) = True then
formatkey := '1'
else
formatkey := '0';
end;
else
formatkey := VarToStr(ItemsI[i].GetData(sName,rvOriginal)).QuotedString;
end;
//修改数据关键字段，条件值
if ItemKey.IndexOf(sName) > -1 then
// sSQL := sSQL + ' And ' + sName + ' = ' + VarToStr(ItemsI[i].GetData(sName, rvOriginal)); //原来的值
// //这一句有问题，没能判断原来值的数据类型，导致出错
sSQL := sSQL + ' And ' + sName + ' = ' + formatkey ;
end;
//拼接成SQL并插入到数组中
JA.Add('Update ' + sTable + ' Set ' + Copy(sField, 1, Length(sField) - 1) + ' Where ' + Copy(sSQL, 5, Length(sSQL)));
end
else if ItemsI[i].RowState in [TFDDatSRowState.rsDeleted] then
begin
sSQL := ';
//删除数据关键字段，条件值
for J := 0 to ItemKey.Count - 1 do
begin
sValue := ItemsI[i].GetValues(ItemKey.Strings[j]);
if sValue.Trim <> ' then
sSQL := sSQL + ' And ' + ItemKey.Strings[j] + ' = ' + QuotedStr(sValue.Trim);
end;
//拼接成SQL并插入到数组中
if sSQL <> ' then
JA.Add('Delete from ' + sTable + ' Where ' + Copy(sSQL, 5, Length(sSQL)));
end;
end;
end;
//Result := JA.ToString;
for Jvalue in JA do
Result:=Result+ '; '+jvalue.Value;
Result:=Copy(Result,2);
FreeAndNil(JA);
FreeAndNil(ItemKey);
FreeAndNil(ItemNoField);
end; |
