unit1.pas

sql重复值工具代码

{—————江西理工科协——————by __sxw———————————---}


unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids, DBGrids, DB, DBTables, ADODB, ExtCtrls;

type
  TForm1 = class(TForm)
    GroupBox1: TGroupBox;
    Edit1: TEdit;
    Label1: TLabel;
    Button1: TButton;
    Memo1: TMemo;
    Button2: TButton;
    GroupBox2: TGroupBox;
    dbname: TComboBox;
    tbname: TComboBox;
    Label2: TLabel;
    Label3: TLabel;
    GroupBox3: TGroupBox;
    Button3: TButton;
    Button4: TButton;
    GroupBox4: TGroupBox;
    DBGrid1: TDBGrid;
    ADOC1: TADOConnection;
    ADOQuery1: TADOQuery;
    ADOQuery2: TADOQuery;
    ADOc2: TADOConnection;
    ds1: TDataSource;
    ADOQuery3: TADOQuery;
    ado: TADOQuery;
    Edit2: TEdit;
    Label4: TLabel;
    Label5: TLabel;
    Edit3: TEdit;
    Button5: TButton;
    procedure Edit1KeyPress(Sender: TObject; var Key: Char);
    procedure Button2Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure tbnameChange(Sender: TObject);
    procedure dbnameChange(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Edit2KeyPress(Sender: TObject; var Key: Char);
    procedure Edit3KeyPress(Sender: TObject; var Key: Char);
    procedure Button5Click(Sender: TObject);
  private
   FList: TList; //保存指针
    { Private declarations }
    
    procedure clearlist;   //删除form中的checkbox
  //  procedure sqlchk;
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

uses Unit2;
{$R *.dfm}


procedure TForm1.ClearList;
begin
  while FList.Count > 0 do
  begin
    Tcheckbox(FList[0]).Free;
    FList.Delete(0);
  end;
end;

procedure TForm1.Edit1KeyPress(Sender: TObject; var Key: Char);
begin
if key=chr(13) then
begin
memo1.Lines.Delete(4);
memo1.Lines.Insert(4,'Data Source='+edit1.Text+';');
end;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
memo1.Clear;
memo1.Lines.Add('Provider=SQLOLEDB.1;');
memo1.Lines.Add('Password=;');
memo1.Lines.Add('Persist Security Info=True;');
memo1.Lines.Add('User ID=sa;');
memo1.Lines.Add('Data Source=(local);');
memo1.Lines.Add('Use Procedure for Prepare=1;');
memo1.Lines.Add('Auto Translate=True;');
memo1.Lines.Add('Packet Size=4096;');
memo1.Lines.Add('Workstation ID=;');
memo1.Lines.Add('Use Encryption for Data=False;');
memo1.Lines.Add('Tag with column collation when possible=False');
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
try
{---动态设置acoconnection的连接属性---------------}
adoc1.Connected:=false;
adoc1.ConnectionString:=memo1.Text;
adoc1.Connected:=true;
 {---------over--------------------------}
adoquery1.SQL.Clear;
//-------查询数据库名
dbname.Items.Clear;
with adoquery1 do
begin
close;
sql.Clear;
sql.Add('select name from master..sysdatabases');
open;
while not Eof do
begin
dbname.items.add(FieldByName('name').asString);
next;
end;
end;    //with query1 over

//-----结束
except
application.MessageBox('登陆本地服务器失败'+chr(13)+'查看登陆参数是否正确','提示');
end;
end;

procedure TForm1.tbnameChange(Sender: TObject);
var chb:Tcheckbox;
k,i,j:integer;
begin
ClearList;  //----清除checkbox
i:=0;
j:=0;
k:=1;
try
with adoquery2 do
begin
close;
sql.Clear;
sql.Add('select name from dbo.syscolumns where id=object_id('''+tbname.text+''')');
open;
while not Eof do
begin
 chb:=Tcheckbox.Create(self);
 chb.Parent:=self;        //这行很重要，否则不能在form显示
 FList.Add(chb);
 if (k mod 6)=0 then        //每行5个
 begin
 j:=j+3;
 i:=0;
 k:=1;
 end;

  chb.Top:=170+j*10;
  chb.Left:=50+i*10;
  chb.Caption:=FieldByName('name').asString;
  next;
  k:=k+1;
  i:=i+10;
   end;
 end;
with adoquery3 do
begin
    Close;
    sql.Clear;
     sql.Add('select * from '+tbname.text);
     open;
 end;
 except
 application.MessageBox('运行错误','提示');
 exit;
 end;
end;

procedure TForm1.dbnameChange(Sender: TObject);
begin
try
adoc2.Connected:=false;
ADOc2.ConnectionString:=adoc1.ConnectionString;
adoc2.DefaultDatabase:=dbname.Text;
adoc2.Connected:=true;
with adoquery2 do
begin
close;
sql.Clear;
sql.Add('select * from sysobjects where xtype='+'''U''');
open;
tbname.Items.Clear;
while not Eof do
begin
tbname.items.add(FieldByName('name').asString);
next;
end;
end;
except
application.MessageBox('操作失败','失败');
end;


end;

procedure TForm1.Button3Click(Sender: TObject);
begin
if button4.Enabled then
button4.Enabled:=false
else
button4.Enabled:=true;
end;

procedure TForm1.Button4Click(Sender: TObject);
var Num:integer;
cond:string;
begin
if application.MessageBox('确定开始重复值处理吗？','确认',mb_yesno)=id_no then exit;
try
{-------重复值处理----------------------------}
with ado do
begin
cond:='';
close;
sql.Clear;
    for Num:=0 to FList.Count-1  do          //这里选出选定的checkbox
     if Tcheckbox(FList[Num]).Checked then
     begin
     cond:=cond+Tcheckbox(Flist[Num]).Caption+' ';
     end;
     if trim(cond)='' then
     begin
      application.MessageBox('没有设置的重复值条件'+chr(13)+'请小心操作','提示');
      exit;
     end;
     sql.Add('select '+cond+' into temptablename from '+tbname.Text);   //重复值处理,动态创建存储过程.
     sql.Add('group by '+cond +'having count(*)>1');
     ExecSQL;
     close;
     sql.Clear;
     sql.Add('delete '+tbname.Text);
     sql.Add('where 1=1');
     for Num:=0 to Flist.Count-1 do
     if Tcheckbox(FList[Num]).Checked then
     begin
       cond:=Tcheckbox(Flist[Num]).Caption;
      sql.Add('and '+cond+' in (select '+cond+' from temptablename)');
      end;
    execsql;
    Close;
    SQL.Clear;
    SQL.Add('drop table temptablename') ;
    execsql;
   Close;
   SQL.clear;
   sql.add('select * from '+tbname.Text);
Open;
end;
application.MessageBox('数据执行成功'+chr(13)+'相同记录的数据已经删除','提示');
 {---end  重复值处理结束-----------------}

 except
 application.MessageBox('创建数据处理脚本失败'+chr(13)+'是否存在名为temptablename的表名？','提示');
 end;

end;

procedure TForm1.FormCreate(Sender: TObject);
begin
FList := TList.Create;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
ClearList;
  FList.Free;
end;

procedure TForm1.Edit2KeyPress(Sender: TObject; var Key: Char);
begin
if key=chr(13) then
begin
memo1.Lines.Delete(3);
memo1.Lines.Insert(3,'User ID='+edit2.Text);
edit3.SetFocus;
end;
end;

procedure TForm1.Edit3KeyPress(Sender: TObject; var Key: Char);
begin
if key=chr(13) then
begin
memo1.Lines.Delete(1);
memo1.Lines.Insert(1,'Password='+edit3.Text);
button1.SetFocus;
end;
end;

procedure TForm1.Button5Click(Sender: TObject);
begin
with form2 do
begin
marquee1.Active:=true;
form2.show;
end;
end;

end.