unit1.pas

本代码通过delphi界面与数据库连接

unit unit1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
   Db, DBTables, StdCtrls, DBCtrls, CheckLst, Buttons,
  ExtCtrls, Grids, DBGrids;

type
  Tform1 = class(TForm)
    DataSource1: TDataSource;
    Query1: TQuery;
    Panel1: TPanel;
    ListBox1: TListBox;
    GroupBox1: TGroupBox;
    RadioButton1: TRadioButton;
    RadioButton2: TRadioButton;
    RadioButton3: TRadioButton;
    RadioButton4: TRadioButton;
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
    GroupBox2: TGroupBox;
    RadioButton5: TRadioButton;
    RadioButton6: TRadioButton;
    ListBox4: TListBox;
    BitBtn5: TBitBtn;
    BitBtn6: TBitBtn;
    BitBtn7: TBitBtn;
    BitBtn8: TBitBtn;
    Panel2: TPanel;
    ListBox2: TListBox;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    BitBtn4: TBitBtn;
    ListBox3: TListBox;
    BitBtn9: TBitBtn;
    Label1: TLabel;
    BitBtn10: TBitBtn;
    ComboBox3: TComboBox;
    Label2: TLabel;
    BitBtn12: TBitBtn;
    BitBtn11: TBitBtn;
    DBGrid1: TDBGrid;
    procedure FormActivate(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure BitBtn5Click(Sender: TObject);
    procedure BitBtn6Click(Sender: TObject);
    procedure BitBtn7Click(Sender: TObject);
    procedure RadioButton4Click(Sender: TObject);
    procedure BitBtn8Click(Sender: TObject);
    procedure BitBtn12Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  form1: Tform1;
  s0,s,s1,s2:string;//条件变量
  seltab:string;//选择的表名
implementation

{$R *.DFM}

//字段选择列表
procedure Tform1.FormActivate(Sender: TObject);
var i:integer;
begin
query1.DatabaseName:=ExtractFilePath(Application.ExeName);
  seltab:=combobox3.text;
  listbox1.Clear;
  listbox2.Clear;
  listbox3.Clear;
  query1.close;
  query1.sql.Clear;
  query1.sql.Add('select * from '+seltab);
  try
    query1.Open;
  except
    showmessage('不能打开数据库！');
    exit;
  end;
  for i:=0 to query1.FieldCount-1 do
     begin
       listbox2.Items.add(query1.fields[i].displayname);
       listbox1.Items.add(query1.fields[i].DisplayName);
       query1.next;
     end;
end;

//选择要查询的字段名
procedure Tform1.BitBtn1Click(Sender: TObject);
var i,p:integer;
begin
//为了保证“已选字段”的顺，这里加入了一个参数，确定增加的位置
p:=listbox3.Count;
   for i:=ListBox2.Items.Count-1 downTo 0 do
     if ListBox2.Selected[i] then
       begin
          ListBox3.Items.Insert(p,ListBox2.Items.Strings[i]);
          listbox2.items.Delete(i);
       end;
end;
//从“已选择字段”中移动字段
procedure Tform1.BitBtn2Click(Sender: TObject);
var i:integer;
begin
    for i:=ListBox3.Items.Count-1 downTo 0 do
      if listbox3.selected[i] then
        begin
          listbox2.items.insert(0,listbox3.items[i]);
          listbox3.items.delete(i);
        end;
end;
//选择所有字段名
procedure Tform1.BitBtn3Click(Sender: TObject);
var i:integer;
begin
   listbox3.Items.Clear;
   for i:=0 to listbox2.items.count-1 do
   begin
     listbox3.Items.Add(listbox2.items[i]);
     listbox2.Items.Delete(listbox2.itemindex);
   end;
end;
//清除选择字段
procedure Tform1.BitBtn4Click(Sender: TObject);
var i:integer;
begin
  listbox3.Items.Clear;
  listbox2.Items.clear;
  query1.close;
  query1.sql.clear;
  query1.sql.add('select * from '+seltab);
  query1.open;
  for i:=0 to query1.FieldCount-1 do
    begin
     listbox2.items.add(query1.fields[i].DisplayName );
    end;
end;
//生成查询条件
procedure Tform1.BitBtn5Click(Sender: TObject);
var i:integer;
    tj,lj:string;
begin
 if groupbox2.Visible=false
 then
   groupbox2.visible:=true;
 if radiobutton1.Checked then
    tj:='='; //等于
 if radiobutton2.Checked then
    tj:='<>';//不等于
 if radiobutton3.Checked then
    tj:=' like ';//相似于
 if listbox4.Items.text<>'' then
    begin
    if radiobutton5.Checked then
       lj:=' and '
    else
       lj:=' or ';
    end
  else
    lj:='';

 if not radiobutton4.checked then
 begin
 if not radiobutton3.Checked then
 begin
   //选择等于、不等于时生成条件
   for i:=listbox1.items.count-1 downto 0 do
     begin
       if ListBox1.Selected[i] then
         begin
          listbox4.items.add(lj+listbox1.items[listbox1.itemindex]+tj+'"'+combobox1.text+'"');
         end;
     end;
   end
  else
   //选择相似于时生成条件
   begin
    for i:=listbox1.items.count-1 downto 0 do
     begin
       if ListBox1.Selected[i] then
         begin
          listbox4.items.add(lj+listbox1.items[listbox1.itemindex]+tj+'"'+combobox1.text+'%"');
         end;
     end
   end;
 end
 else
 //选择围范时生成的条件
 for i:=listbox1.items.count-1 downto 0 do
   begin
     if ListBox1.Selected[i] then
       begin
          listbox4.items.add(lj+listbox1.items[listbox1.itemindex]+'>='+'"'+combobox1.text+'"'+' and '+listbox1.items[listbox1.itemindex]+'<='+'"'+combobox2.text+'"');
       end;
   end;
end;
//删除生成的条件
procedure Tform1.BitBtn6Click(Sender: TObject);
var i:integer;
begin
  for i:=listbox4.Items.Count - 1 downto 0 do
  if listbox4.selected[i] then
  begin
  listbox4.items.delete(listbox4.itemindex);
   end;
  if listbox4.Items.text='' then
  groupbox2.Visible:=false;
end;
//清空生成的条件
procedure Tform1.BitBtn7Click(Sender: TObject);
begin
  listbox4.Items.Clear;
  groupbox2.Visible:=false;
end;
//当选择”范围“选项时
procedure Tform1.RadioButton4Click(Sender: TObject);
begin
if radiobutton4.Checked then
   combobox2.Visible:=true
else
   combobox2.Visible:=false;   
end;
//执行查询操作
procedure Tform1.BitBtn8Click(Sender: TObject);
var i,j:integer;
   sql,sql1,sql2:string;
begin
   sql:='';
   sql1:='';
   sql2:='';
   for i:=0 to listbox3.items.count-1 do
     begin
       sql1:=sql1+listbox3.items[i]+',';
     end;
   sql1:=copy(sql1,0,length(sql1)-1);
   for i:=0 to listbox4.Items.count-1 do
     begin
       sql2:=sql2+listbox4.Items[i];
     end;
  query1.close;
  query1.sql.clear;
  if listbox3.Items.count=0 then
  sql1:='*';
  if listbox4.items.count=0 then
  sql:='select '+sql1+' from '+seltab
  else
  sql:='select '+sql1+' from '+seltab+' where '+sql2;
  query1.sql.add(sql);
  query1.open;
  //向DBGrid输出查询结果,没有选择的字段不显示出来
  if listbox3.items.Count<>0 then
    begin
      for j:=0 to dbgrid1.Columns.Count-1 do
        begin
          dbgrid1.Columns[j].Visible:=false;
        end;
     
     for i:=0 to listbox3.Items.Count-1 do
        for j:=0 to dbgrid1.Columns.Count-1 do
          begin
            if trim(dbgrid1.Columns[j].FieldName)=trim(listbox3.Items[i]) then
              dbgrid1.Columns[j].Visible:=true
          end;
    end
   else
     begin
       for j:=0 to dbgrid1.Columns.Count-1 do
       begin
         dbgrid1.Columns[j].Visible:=true;
       end;
     end;
label1.caption:='查询结果：'+inttostr(query1.recordcount);
end;
//选择不同的数据表
procedure Tform1.BitBtn12Click(Sender: TObject);
begin
  if combobox3.text<>'' then
    begin
      seltab:=combobox3.text;
      form1.FormActivate(sender);
    end
  else
    showmessage('请选择数据库！');
end;

end.