ufrmview.pas

数据库通用工具

        ss := ','+ss;
    end
    else
    begin
      if i< a-1 then
        ss := ss+',';
    end;
    mo2.Lines.Add( '  '+ss );
  end;

  mo2.Lines.Add(')');
  mo2.Lines.Add('values');
  mo2.Lines.Add('(');
  for i := 0 to a-1 do
  begin
    ss := DBGrid1.Columns.Items[i].FieldName ;
    //if i< a-1 then ss := ss+',';
    if CheckBox4.Checked then//逗号前置 &Z
    begin
      if i>=1 then  
        ss := ',:'+ss;
        
      if i=0 then
        ss := ':'+ss;
    end
    else
    begin
      if i< a-1 then
        ss := ':'+ss+',';
    end;
    //mo2.Lines.Add( '  :'+ss );
    mo2.Lines.Add( '  '+ss );
  end;
  mo2.Lines.Add(')');

end;

procedure TfrmView.bbtEditClick(Sender: TObject);
var
  i,a : integer;
  ss: string;
begin
  //修改 &E
  if ListBox1.Items.Count<=0 then exit;
  if ListBox1.ItemIndex<0 then exit;
  
  ss := ListBox1.Items.Strings[ListBox1.ItemIndex];
  ss := 'update '+ss+' set ';
  mo2.Text := ss;
  {
  declare @OperName	  varchar(10)
  declare @presType	  int

  select @DrugName = :DrugName
  select @presType = :presType
  }
  a := DBGrid1.FieldCount;
  for i := 1 to a-1 do
  begin
    ss := DBGrid1.Columns.Items[i].FieldName ;
    ss := ss+'=:'+ss;
    
    //if i< a-1 then ss := ss+',';
    if CheckBox4.Checked then//逗号前置 &Z
    begin
      if i>=2 then
        ss := ','+ss;
    end
    else
    begin
      if i< a-1 then
        ss := ss+',';
    end;

    mo2.Lines.Add( '  '+ss );
  end;
  mo2.Lines.Add( 'where id=:id');

end;

procedure TfrmView.bbtDelClick(Sender: TObject);
var
  ss : string;
  i : Integer;
begin
  //删除&S
  if ListBox1.Items.Count<=0 then exit;
  if ListBox1.ItemIndex<0 then exit;
  
  ss := ListBox1.Items.Strings[ListBox1.ItemIndex];
  ss := 'delete from '+ss;
  mo2.Text := ss;

  if CheckBox5.Checked then
  begin
    mo2.Lines.Clear;
    for i := 0 to ListBox1.Items.Count-1 do
    begin
      mo2.Lines.Add( 'delete from '+ListBox1.Items.Strings[i] );
    end;

  end;

  
end;

procedure TfrmView.Timer1Timer(Sender: TObject);
begin
  Timer1.Enabled := False;
  labMSG.Caption := '';
end;

function TfrmView.listRandomPersonInfo( iType:integer ):Integer;
var
  ss : string;
begin
  //
  Result := 0;
  ss := dxcSL.Text;
  mo2.Lines.Clear;
  with adoQry do
  begin
    Close;
    case iType of
      1: SQL.Text := 'select top '+ss+' cname as svalues from person ';//姓名
      2: SQL.Text := 'select top '+ss+' c_name as svalues from p_address ';//地址
      3: SQL.Text := 'select top '+ss+' telephone as svalues from person ';//电话
      4: SQL.Text := 'select top '+ss+' postCode as svalues from person ';//邮编
      5: SQL.Text := 'select top '+ss+' birthday as svalues from person ';//生日
      6: SQL.Text := 'select top '+ss+' IDNumber as svalues from person ';//身份证
      7: SQL.Text := 'select top '+ss+' cjmc as svalues from medcj ';//厂家
    else
      SQL.Text := 'select cname as svalues from person ';
    end;
    
    try
      Open;
    except
      exit;
    end;
                            
    while not Eof do
    begin
      {
      case iType of
        1: ss := FieldByName('svalues').AsString ;//姓名
        2: ss := FieldByName('svalues').AsString ;//地址
        3: ss := FieldByName('svalues').AsString;//电话
        4: ss := FieldByName('svalues').AsString ;//邮编
        5: ss := FieldByName('svalues').AsString ;//生日
        6: ss := FieldByName('svalues').AsString ;//身份证
        7: ss := FieldByName('svalues').AsString ;//厂家
      else
        ss := '';
      end;
      //}
      ss := Trim( FieldByName('svalues').AsString );//
      
      if Trim(ss)<>'' then
      begin
        mo2.Lines.Add( ss );
      end;  

      Next;
    end;
    
  end;
  Result := mo2.Lines.Count;
end;

function TfrmView.GetRandomPersonInfo( iType,iNum :integer ):string;
var
  iID : integer;
begin
  iID := 1+random(iNum);
  with adoQry do
  begin
    Close;
    case iType of
      1: SQL.Text := 'select cname as svalues from person where id=:id';//姓名
      2: SQL.Text := 'select address as svalues from person where id=:id';//地址
      3: SQL.Text := 'select telephone as svalues from person where id=:id';//电话
      4: SQL.Text := 'select postCode as svalues from person where id=:id';//邮编
      5: SQL.Text := 'select birthday as svalues from person where id=:id';//生日
      6: SQL.Text := 'select IDNumber as svalues from person where id=:id';//身份证
    else ;
      SQL.Text := 'select cname as svalues from person where id=:id';
    end;
    Parameters.ParamByName('id').Value := iID;
    try
      Open;
    except
      GetRandomPersonInfo := '空';
      exit;
    end;
    if not IsEmpty then
    begin
      GetRandomPersonInfo := Trim(FieldByName('svalues').AsString);
      exit;
    end
    else
    begin
      GetRandomPersonInfo := '空';
      exit;
    end;

  end;
  //-------------------------------
end;

function TfrmView.getRandomPersonInfo2():string;
var
  k,iID : integer;
begin
  Result := '';
  k := mo2.Lines.Count;
  if k<=0 then Exit;
  
  iID := random(k);
  
  Result := mo2.Lines.Strings[iid];
  //-------------------------------
end;

procedure TfrmView.BitBtn5Click(Sender: TObject);
var
  ss : string;
begin
  if cbxZD.ItemIndex<0 then Exit;
  if Query1.IsEmpty then Exit;
  //Query1.First;
  {
  1 姓名
  2 地址
  3 电话
  4 邮编
  5 生日
  6 身份证
  }

  Query2.Close;
  Query2.DatabaseName := Query1.DatabaseName;

  Query2.Close;
  Query2.SQL.Text := Query1.SQL.Text;
  try
    Query2.Open;
  except
  end;  

  while not Query2.Eof do
  begin

    //ss := GetRandomPersonInfo( cbxNR.ItemIndex+1,33900);//随机
    ss := getRandomPersonInfo2;
    //mo.Lines.Add(ss);
    with Query3 do
    begin
      Close;
      SQL.Text := 'update '+ListBox1.Items.Strings[ListBox1.ItemIndex]+' set '+cbxZD.Text+'=:snr where id=:id';
      ParamByName('snr').AsString := ss;
      ParamByName('id').AsString := Query2.FieldByName('id').AsString;
      
      try
        ExecSQL;
      except
      end;
    end;

    Query2.Next;
    
  end;

  ShowMessage('修改完毕！');
  //bbtQry.Click;

end;

procedure TfrmView.cbxNRChange(Sender: TObject);
var
  k : Integer;
begin
  k := listRandomPersonInfo(cbxNR.ItemIndex+1);
  ShowMessage('查到 '+IntToStr(k)+' 项 【 '+cbxNR.Text+' 】');
end;

procedure TfrmView.N3Click(Sender: TObject);
var
  i : Integer;
  ss : string;
begin
  //生成导表语句
  mo2.Lines.Clear;
  for i := 0 to ListBox1.Items.Count-1 do
  begin
    ss := ListBox1.Items.Strings[i];
    mo2.Lines.Add('select * into dbName.dbo.'+ss+' from '+ss)
  end;

end;

procedure TfrmView.listField( sTableName: string; bAutoWidth: Boolean=True );
var
  i,k : Integer;
begin
  //

  if CheckBox1.Checked and Database1.Connected = true then
  begin
    labTBCap.Caption := sTableName;
      
    with Query4 do
    begin
      Close;
      ParamByName('tbName').AsString := sTableName;
      
      try
        Open;
      except
        exit;
      end;
      
    end;

    if bAutoWidth then
    begin
      k := DBGrid2.Columns.Count;
      k := DBGrid2.Width div k;
      for i := 0 to DBGrid2.Columns.Count-1 do
      begin
        DBGrid2.Columns.Items[i].Width := k-6;
      end;
    end;

  end;

end;

procedure TfrmView.ListBox1DblClick(Sender: TObject);
var
  ss : string;
begin
  //
  if CheckBox1.Checked and Database1.Connected = true then
  begin                                           

    ss := ListBox1.Items.Strings[ListBox1.ItemIndex];
    if pos('.',ss)<>0 then
    begin
      exit;
    end;

    listField(ss);

    labTBCap.Caption := ss;
    Panel10.BringToFront;
    Panel10.Visible := True;
  end;

end;

procedure TfrmView.BitBtn6Click(Sender: TObject);
begin
  Panel10.Visible := False;
end;

procedure TfrmView.Panel11MouseMove(Sender: TObject; Shift: TShiftState; X,
  Y: Integer);
const
  SC_DragMove   =   $F012;     {   a   magic   number   }
begin
  //控件移动
  ReleaseCapture;
  Panel10.Perform( WM_SysCommand,   SC_DragMove,   0);
end;

procedure TfrmView.CheckBox6Click(Sender: TObject);
begin
  if CheckBox6.Checked then
  begin
    DBMemo1.Visible := True;
    DBMemo1.DataSource := DataSource1;
    DBMemo1.DataField := cbxZD.Text;
  end
  else
  begin
    DBMemo1.Visible := false;
    DBMemo1.DataSource := nil;
    DBMemo1.DataField := '';
  end;

end;

procedure TfrmView.cbxZDChange(Sender: TObject);
begin
  if CheckBox6.Checked then
  begin
    DBMemo1.Visible := True;
    DBMemo1.DataSource := DataSource1;
    DBMemo1.DataField := cbxZD.Text;
  end;
end;

procedure TfrmView.BitBtn7Click(Sender: TObject);
var
  ss : string;
  ff : TBlobField;
begin
  if Query1.IsEmpty then exit;
  if SaveDialog2.Execute then
  begin
    ss := SaveDialog2.FileName;
    self.Caption := ss;
    try
      ff := TBlobField( Query1.FieldByName( cbxZD.Text ) );
      ff.SaveToFile( ss );
    except
      labMSG.Caption := '读取数据失败！';
      Exit;
    end;
    labMSG.Caption := '读取数据成功。';
  end;

end;

procedure TfrmView.BitBtn8Click(Sender: TObject);
var
  ss : string;
  ff : TBlobField;
begin
  if Query1.IsEmpty then exit;
  if OpenDialog1.Execute then
  begin
    ss := OpenDialog1.FileName;
    self.Caption := ss;
    with qryPub do
    begin
      Close;
      SQL.Text := 'update '+ListBox1.Hint+' set '+cbxZD.Text+'=:imgData where id=:id';
      ParamByName('id').AsString := Query1.FieldByName('id').AsString;
      ParamByName('imgData').LoadFromFile( ss,ftBlob	);

      try
        ExecSQL;
      except
        labMSG.Caption := '存入数据库失败！';
        Exit;
      end;
      labMSG.Caption := '存入数据库成功。';
    end;

  end;

end;

procedure TfrmView.btnSSClick(Sender: TObject);
begin
  with qryPub do
  begin
    close;
    SQl.Clear;
    SQL.Add('use master');
    SQL.Add('dump transaction '+Edit2.Text+' with no_log');
    SQL.Add('backup log '+Edit2.Text+' with no_log');
    SQL.Add('dbcc shrinkdatabase ('+Edit2.Text+')');
    try
      ExecSQL;
      Close;
      ShowMessage('压缩成功,可进行其它操作');
      bbtDisl.Click;
    except
      Close;
      ShowMessage('压缩失败,请检查网络情况');
    end;
  end;
end;

end.