TFDMemTable 数据转成SQL脚本更新

{

 引用System.JSON;

 

  函数名称：DBToJSON（TFDMemTable 数据转成SQL脚本更新）

 

  参数：

  DB：TFDMemTable数据集

  sTable：更新表名

  sKey:关键字段，写法：ID,Name 以逗号区分

  sNoField:不需要修改或增加的字段： 写法：ID,Sex  以逗号区分

}

function DBToJSON(DB:TFDMemTable;sTable:string;sKey:String;sNoField:String):String;

Var

 ItemKey,ItemNoField:TStringlist;

  JA:TJSONArray;

 sField,sValue,sSQL,sTmp,sName: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

 

     //判断数据的操作状态 :插入

     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

           sSQL:=sSQL+' And '+ sName+' = '+VarToStr(ItemsI[i].GetData(sName,rvOriginal));

 

       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 '+sTable+' Where '+Copy(sSQL,5,Length(sSQL)) );

     end;

   end;

  end;

  Result:=JA.ToString;

  FreeAndNil(JA);

  FreeAndNil(ItemKey);

  FreeAndNil(ItemNoField);

 

end;