Delphi数据的通用查询方法

Delphi + MSSQL控件开发桌面应用软件，数据的查找是使用最多的功能，开发人员针对每个界面设计查询代码，会费时费力。建立一个母FORM，母FORM中设计好数据通用查询代码，再继承给实例FORM，将能事半功倍。

在桌面应用软件中，用 AdoQuery.Filter 做过滤，效果好过动态编辑SQL语句重取数据，下面是实际应用中的代码，不同版本可能有些行不同：

方案一：【即视生管】
procedure TfmwManage.doFind(GridN:TDbGridEh);	
var
  sInput,sField,sSign,sFilter,sS:String;
  tType:TFieldType;
  iPos:integer;
begin
  try
    sInput:=InputBox('找Find: ','','');
    if sInput = '' then exit;
    with GridN do
    begin
      if SelectedField.FieldKind <> fkData then exit;
      sField:=SelectedField.FieldName;
      tType:=SelectedField.DataType;
      if copy(sInput,1,2)='>=' then
      begin
        sSign:='>=';
        sInput:=copy(sInput,3,length(sInput)-2);
      end else if copy(sInput,1,2)='<=' then
      begin
        sSign:='<=';
        sInput:=copy(sInput,3,length(sInput)-2);
      end else if copy(sInput,1,1)='>' then
      begin
        sSign:='>';
        sInput:=copy(sInput,2,length(sInput)-1);
      end else if copy(sInput,1,1)='<' then
      begin
        sSign:='<';
        sInput:=copy(sInput,2,length(sInput)-1);
      end else if pos('..',sInput)>0 then
      begin
        iPos:=pos('..',sInput);
        sSign:='>=';
        sS:=sInput;
        sS:=copy(sInput,1,iPos-1)+''''+' and ['+sField+'] <= '+'''';
        sInput:=sS+Copy(sInput,iPos+2,length(sInput)-iPos-1);
      end else
      begin
        if (tType=ftString)or(tType=ftWideString) then
        begin
           sSign:='like';
           sInput:='*'+sInput+'*';
        end else if(tType=ftDateTime) then
        begin
           sSign:='>=';
           sS:=DateToStr(StrToDate(sInput)+1);
           sInput:=sInput+''''+' and ['+sField+'] < '+''''+sS;
        end else
        begin
           sSign:='=';
           sInput:=sInput;
        end;
      end;
      sFilter:='['+sField+']'+' '+sSign+' '+''''+sInput+'''';
      DataSource.DataSet.filtered:=false;
      DataSource.DataSet.Filter:=sFilter;
      DataSource.dataset.Filtered:=true;
    end;
 except on E:Exception do
   showmessage(E.message);
 end;
end;

方案二：【金汇ERP】
procedure TfmjhApp.SpBtn1Click(Sender: TObject);	
var
 sInput,sField,sSign,sFilter,sS:String;
 tType:TFieldType;
 iPos:integer;
begin
 if(ActiveMdiChild=nil)or(DbNv0.DataSource=nil) then Exit;
 with ActiveMDIChild do
 begin
  if ActiveControl is TDbGridEh then
  begin
   sField:=TDbGridEh(ActiveControl).SelectedField.FieldName;
   tType:=TDbGridEh(ActiveControl).SelectedField.DataType;
  end else if ActiveControl is TDbEdit then
  begin
   sField:=TDbEdit(ActiveControl).Field.FieldName;
   tType:=TDbEdit(ActiveControl).Field.DataType;
  end else if ActiveControl is TDbDateTimeEditEh then
  begin
   sField:=TDbDateTimeEditEh(ActiveControl).Field.FieldName;
   tType:=TDbDateTimeEditEh(ActiveControl).Field.DataType;
  end else if ActiveControl is TDbComboBoxEh then
  begin
   sField:=TDbComboBoxEh(ActiveControl).Field.FieldName;
   tType:=TDbComboBoxEh(ActiveControl).Field.DataType;
  end else if ActiveControl is TDbLookupComboBoxEh then
  begin
   sField:=TDbLookupComboBoxEh(ActiveControl).Field.FieldName;
   tType:=TDbLookupComboBoxEh(ActiveControl).Field.DataType;
  end else exit;
 end;
  //取得栏位后如果是下拉栏位却掉lkup
  if copy(sField,1,4)='lkup' then sField:=copy(sField,5,length(sField)-4);
  //取得输入值并根据输入结果生成有效条件值
  sInput:=InputBox('过滤器','过滤内容','');
 if sInput>'' then
 begin
   if copy(sInput,1,2)='>=' then
   begin
    sSign:='>=';
    sInput:=copy(sInput,3,length(sInput)-2);
   end else if copy(sInput,1,2)='<=' then
   begin
    sSign:='<=';
    sInput:=copy(sInput,3,length(sInput)-2);
   end else if copy(sInput,1,1)='>' then
   begin
    sSign:='>';
    sInput:=copy(sInput,2,length(sInput)-1);
   end else if copy(sInput,1,1)='<' then
   begin
    sSign:='<';
    sInput:=copy(sInput,2,length(sInput)-1);
   end else if copy(sInput,1,1)='*' then
   begin
    sSign:='like';
    sInput:=sInput+'*';
   end else if pos('..',sInput)>0 then
   begin
    iPos:=pos('..',sInput);
    sSign:='>=';
    sS:=sInput;
    sS:=copy(sInput,1,iPos-1)+''''+' and '+sField+' <= '+'''';
    sInput:=sS+Copy(sInput,iPos+2,length(sInput)-iPos-1);
   end else
   begin
    if (tType=ftString)or(tType=ftWideString) then
    begin
     sSign:='like';
     sInput:='*'+sInput+'*';
    end else
    begin
     sSign:='=';
     sInput:=sInput;
    end;
   end;
  try
   if DbNv0.DataSource.DataSet.Filter>'0' then
    sFilter:=DbNv0.DataSource.DataSet.Filter+' and ['+sField+']'+' '+sSign+' '+''''+sInput+''''
   else
    sFilter:='['+sField+']'+' '+sSign+' '+''''+sInput+'''';
   DbNv0.DataSource.DataSet.Filter:=sFilter;
   DbNv0.DataSource.DataSet.Filtered:=true;
  except
   showmessage('本栏位不支持过滤或过滤内容不合法, 过滤失败');
   DbNv0.DataSource.DataSet.Filter:='';
   DbNv0.DataSource.DataSet.Filtered:=false;
  end;
 end; 
end;