//数据字段值转化
function VarToSql(value: variant): string;
begin
if VarIsNull(value) then
Result := 'NULL'
else
case VarType(value) of
vardate:
Result := Quotedstr(DatetimeToStr(VarToDatetime(value)));
varString, varOlestr:
Result := Quotedstr(Trim(VarToStr(value)));
varBoolean:
begin
if value then
result := '1'
else
result := '0';
end;
else
Result := Quotedstr(Trim(VarToStr(value)));
end;
end;
//根据CDS的Delta数据生成对应的SQL语句列表字符串
function GenSqls(pdelta: OleVariant; const ptablename: WideString): WideString;
var
i, j: integer;
s1, s2: string;
Cmdstr: string;
FieldList, Keylist: TstringList;
uCDS: TClientDataSet;
sqlstr: WideString;
begin
if VarIsNull(pdelta) then Exit;
uCDS := TClientDataSet.Create(nil);
try
uCDS.data := pdelta;
if not uCDS.Active then uCDS.Open;
FieldList := TstringList.Create;
FieldList.Delimiter := ',';
FieldList.DelimitedText := Common.GetTableFieldNames(ptablename);
Keylist := TstringList.Create;
Keylist.Delimiter := ',';
Keylist.DelimitedText := Common.GetTableKeyNames(ptablename);
for i := 1 to FieldList.Count do
if uCDS.FindField(FieldList[i - 1]) <> nil then
uCDS.FindField(FieldList[i - 1]).tag := 1;
FieldList.Free;
if uCDS.RecordCount > 0 then
begin
s1 := '';
s2 := '';
uCDS.First;
while not uCDS.Eof do
begin
Cmdstr := '';
case uCDS.UpdateStatus of
usUnmodified: //从原数据行取得修改条件
begin
s2 := '';
for j := 1 to Keylist.Count do
begin
if s2 = '' then
s2 := Keylist[j - 1] + '=' + VarToSql(uCDS[Keylist[j - 1]])
else
s2 := s2 + ' and ' + Keylist[j - 1] + '=' + VarToSql(uCDS[Keylist[j - 1]]);
end;
end;
usModified:
begin
s1 := '';
for i := 1 to uCDS.FieldCount do
begin
if (not uCDS.Fields[i - 1].isNull) and (uCDS.Fields[i - 1].tag = 1) then
begin
if s1 = '' then
s1 := Trim(uCDS.Fields[i - 1].FieldName) + ' = ' + VarToSql(uCDS.Fields[i - 1].value)
else
s1 := s1 + ',' + Trim(uCDS.Fields[i - 1].FieldName) + ' = ' + VarToSql(uCDS.Fields[i - 1].value);
end;
end;
if s1 <> '' then
begin
Cmdstr := ' update ' + ptablename + ' set ' + s1 + ' where ' + s2;
end;
end;
usInserted:
begin
s1 := '';
s2 := '';
for i := 1 to uCDS.FieldCount do
if (not uCDS.Fields[i - 1].isNull) and (uCDS.Fields[i - 1].tag = 1) then
begin
if s1 = '' then
begin
s1 := Trim(uCDS.Fields[i - 1].FieldName);
s2 := VarToSql(uCDS.Fields[i - 1].value);
end
else
begin
s1 := s1 + ',' + Trim(uCDS.Fields[i - 1].FieldName);
s2 := s2 + ',' + VarToSql(uCDS.Fields[i - 1].value);
end;
end;
if s1 <> '' then
begin
Cmdstr := ' Insert into ' + ptablename + '(' + s1 + ') Values(' + s2 + ')';
end;
end;
usDeleted:
begin
s2 := '';
for j := 1 to Keylist.Count do
begin
if s2 = '' then
s2 := Keylist[j - 1] + '=' + VarToSql(uCDS[Keylist[j - 1]])
else
s2 := s2 + ' and ' + Keylist[j - 1] + '=' + VarToSql(uCDS[Keylist[j - 1]]);
end;
Cmdstr := 'Delete ' + ptablename + ' Where ' + s2;
end;
end;
if Cmdstr <> '' then
sqlstr := sqlstr + Cmdstr + ';' + chr(13) + chr(10);
uCDS.Next;
end;
end;
finally
uCDS.close;
uCDS.Free();
end;
Result := sqlstr;
end;
function VarToSql(value: variant): string;
begin
if VarIsNull(value) then
Result := 'NULL'
else
case VarType(value) of
vardate:
Result := Quotedstr(DatetimeToStr(VarToDatetime(value)));
varString, varOlestr:
Result := Quotedstr(Trim(VarToStr(value)));
varBoolean:
begin
if value then
result := '1'
else
result := '0';
end;
else
Result := Quotedstr(Trim(VarToStr(value)));
end;
end;
//根据CDS的Delta数据生成对应的SQL语句列表字符串
function GenSqls(pdelta: OleVariant; const ptablename: WideString): WideString;
var
i, j: integer;
s1, s2: string;
Cmdstr: string;
FieldList, Keylist: TstringList;
uCDS: TClientDataSet;
sqlstr: WideString;
begin
if VarIsNull(pdelta) then Exit;
uCDS := TClientDataSet.Create(nil);
try
uCDS.data := pdelta;
if not uCDS.Active then uCDS.Open;
FieldList := TstringList.Create;
FieldList.Delimiter := ',';
FieldList.DelimitedText := Common.GetTableFieldNames(ptablename);
Keylist := TstringList.Create;
Keylist.Delimiter := ',';
Keylist.DelimitedText := Common.GetTableKeyNames(ptablename);
for i := 1 to FieldList.Count do
if uCDS.FindField(FieldList[i - 1]) <> nil then
uCDS.FindField(FieldList[i - 1]).tag := 1;
FieldList.Free;
if uCDS.RecordCount > 0 then
begin
s1 := '';
s2 := '';
uCDS.First;
while not uCDS.Eof do
begin
Cmdstr := '';
case uCDS.UpdateStatus of
usUnmodified: //从原数据行取得修改条件
begin
s2 := '';
for j := 1 to Keylist.Count do
begin
if s2 = '' then
s2 := Keylist[j - 1] + '=' + VarToSql(uCDS[Keylist[j - 1]])
else
s2 := s2 + ' and ' + Keylist[j - 1] + '=' + VarToSql(uCDS[Keylist[j - 1]]);
end;
end;
usModified:
begin
s1 := '';
for i := 1 to uCDS.FieldCount do
begin
if (not uCDS.Fields[i - 1].isNull) and (uCDS.Fields[i - 1].tag = 1) then
begin
if s1 = '' then
s1 := Trim(uCDS.Fields[i - 1].FieldName) + ' = ' + VarToSql(uCDS.Fields[i - 1].value)
else
s1 := s1 + ',' + Trim(uCDS.Fields[i - 1].FieldName) + ' = ' + VarToSql(uCDS.Fields[i - 1].value);
end;
end;
if s1 <> '' then
begin
Cmdstr := ' update ' + ptablename + ' set ' + s1 + ' where ' + s2;
end;
end;
usInserted:
begin
s1 := '';
s2 := '';
for i := 1 to uCDS.FieldCount do
if (not uCDS.Fields[i - 1].isNull) and (uCDS.Fields[i - 1].tag = 1) then
begin
if s1 = '' then
begin
s1 := Trim(uCDS.Fields[i - 1].FieldName);
s2 := VarToSql(uCDS.Fields[i - 1].value);
end
else
begin
s1 := s1 + ',' + Trim(uCDS.Fields[i - 1].FieldName);
s2 := s2 + ',' + VarToSql(uCDS.Fields[i - 1].value);
end;
end;
if s1 <> '' then
begin
Cmdstr := ' Insert into ' + ptablename + '(' + s1 + ') Values(' + s2 + ')';
end;
end;
usDeleted:
begin
s2 := '';
for j := 1 to Keylist.Count do
begin
if s2 = '' then
s2 := Keylist[j - 1] + '=' + VarToSql(uCDS[Keylist[j - 1]])
else
s2 := s2 + ' and ' + Keylist[j - 1] + '=' + VarToSql(uCDS[Keylist[j - 1]]);
end;
Cmdstr := 'Delete ' + ptablename + ' Where ' + s2;
end;
end;
if Cmdstr <> '' then
sqlstr := sqlstr + Cmdstr + ';' + chr(13) + chr(10);
uCDS.Next;
end;
end;
finally
uCDS.close;
uCDS.Free();
end;
Result := sqlstr;
end;