ufrmview.pas

数据库通用工具

1）选择当第一个工作薄第一个工作表
asheet1:=ExcelApplication1.Workbooks[1].Worksheets[1];

2）增加一个二维图
achart:=asheet1.chartobjects.add(100,100,200,200);

3）选择二维图的形态
achart.chart.charttype:=4;

4）给二维图赋值
series:=achart.chart.seriescollection;
range:=sheet1!r2c3:r3c9;
series.add(range,true);

5）加上二维图的标题
achart.Chart.HasTitle:=True;
achart.Chart.ChartTitle.Characters.Text:=’ Excle二维图’

运筹帷幄，决胜万里，化难为易，精益求精
}
procedure TfrmView.N1Click(Sender: TObject);
var
  i:integer;
begin
  //反选(&F)
  for i := 0 to CheckListBox1.Items.Count - 1 do
  begin
    CheckListBox1.Checked[i] := not CheckListBox1.Checked[i];
  end;
end;

procedure TfrmView.N2Click(Sender: TObject);
var
  i:integer;
begin
  //全选(&A)
  for i := 0 to CheckListBox1.Items.Count - 1 do
  begin
    CheckListBox1.Checked[i] := true;
  end;
end;

procedure TfrmView.bbtReadClick(Sender: TObject);
var
  i,k:integer;
  ss:string;
begin

  if Database1.Connected = false then
  begin
    exit;
  end;
  
  //mo.Align := alClient;
  mo.Lines.Clear;
  CheckBox2.Checked := true;
  mo.Visible := true;
  with Query1 do
  begin
    k := Query1.FieldCount;
    for i := 0 to k-1 do
    begin
      //ss := qryMessage.FieldDefs.Items[i].DisplayName;
      ss := FieldDefs.Items[i].Name ;
      ss := ss + ',  ' + FieldDefs.Items[i].GetNamePath ;
      ss := ss + ',  ' + FieldList.Fields[i].ClassName;  
      ss := ss + ' 【' + IntToStr( FieldDefs.Items[i].Size )+'】';
      mo.Lines.Add( ss );
    end;
  end;
  //}
  {LowerCase()
  TAutoIncField
  TIntegerField
  TSmallintField
  TStringField
  TDateTimeField
  TBooleanField
  TFloatField
  TCurrencyField
  TMemoField
  TBlobField
  TBytesField
  //}
end;

procedure TfrmView.CheckBox2Click(Sender: TObject);
begin
  mo.Visible := CheckBox2.Checked;
  DBGrid1.Visible := not mo.Visible;
end;

procedure TfrmView.O1Click(Sender: TObject);
var
  ss,sb:string;
  i:integer;
begin

  if CheckBox1.Checked and Database1.Connected = true then
  begin
    ss := ListBox1.Items.Strings[ListBox1.ItemIndex];
    if pos('.',ss)<>0 then
    begin
      exit;
    end;
    //}

    i := CheckListBox1.ItemIndex;
    {
    for i := 0 to CheckListBox1.Items.Count - 1 do
    begin
      if CheckListBox1.Selected[i] then
      begin
        sb := CheckListBox1.Items.Strings[i];
        break;
      end;

    end;
    //}
    if i<0 then
    begin
      exit;
    end;

    sb := CheckListBox1.Items.Strings[i];
    ss := 'select * from '+ss+' order by '+sb;
    
    with Query1 do
    begin
      Close;
      SQL.Text := ss;
      try
        Open;
      except
        exit;
      end;

    end;

  end;

end;

procedure TfrmView.PopupMenu1Popup(Sender: TObject);
begin

  if CheckListBox1.ItemIndex<0 then
    O1.Enabled := false
  else
    O1.Enabled := true;

end;

procedure TfrmView.bbtQryClick(Sender: TObject);
var
  bSel : Boolean;
begin

  if Database1.Connected = false then
  begin
    exit;
  end;

  if trim(mo2.Text)='' then exit;
  bSel := False;
  with Query1 do
  begin
    Close;
    SQL.Text := mo2.Text;
    try
      if Pos('select',LowerCase(mo2.Text))>0 then
      begin
        bSel := True;
        Open;
      end
      else
      begin
        ExecSQL;
      end;
    except
      //ShowMessage('语句有误，查询出错！');
      exit;
    end;

    if bSel then
    begin
      labMSG.Caption := '记录数：'+IntToStr( Query1.RecordCount );
    end
    else
    begin
      labMSG.Caption := '--';
    end;
    
  end;
  
end;

procedure TfrmView.bbtBackClick(Sender: TObject);
var
  sto,sTableSel:string;
begin

  if Database1.Connected = false then
  begin
    exit;
  end;

  SaveDialog1.FileName := Edit2.Text + FormatDateTime('yymmdd',Date)+'.dat';
  mo2.Text := 'backup database '+Edit2.Text+' to disk=''D:\''';;
  
  if SaveDialog1.Execute then
  begin
    sto := SaveDialog1.FileName ;
    if trim(sto)='' then
    begin
      ShowMessage('目的路径不存在！');
      exit;
    end;
    {
    ss := copy(sto,length(sto),1);
    if ss = '\' then
    begin
      sto := copy(sto,1,length(sto)-1);
    end;

    sTableSel := sto +'\'+ sTableSel + '.dat';
    //}
    sTableSel := sto;
    if FileExists( sTableSel ) = true then
    begin
      if Application.Messagebox('目的路径已有该文件，是否覆盖？ ','提示', mb_okcancel+mb_defbutton2+mb_iconquestion) <> id_ok then
        exit;

      if DeleteFile( sTableSel ) = false then
      begin
        showmessage('删除同名文件失败，无法保存！');
        exit;
      end;
    end;
  end
  else
  begin
    exit;
  end;

  //backup database testDB to disk='f:\ss.dat' 
  with Query1 do
  begin
    Close;
    SQL.Text := 'backup database '+Edit2.Text+' to disk='''+sTableSel+'''';
    try
      ExecSQL;
    except
      showmessage('备份失败！');
    end;
  end;
  
  mo2.Text := 'backup database '+Edit2.Text+' to disk='''+sTableSel+'''';
  showmessage('已成功备份到 -> '+sTableSel);

end;

procedure TfrmView.bbtHFClick(Sender: TObject);
begin
  bbtDisl.Click;
  Edit1.Text := sServer;
  Edit2.Text := sDatabase;
  Edit3.Text := sUser;
  Edit4.Text := sPass;
end;

procedure TfrmView.mo2KeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if Key = VK_ESCAPE then
  begin
    BitBtn1.Click;
  end;

  if ssCtrl in Shift then
  begin
    if Key = 65 then
    begin
      mo2.SelectAll;
      SendMessage (mo2.Handle, WM_Copy, 0, 0);
      Timer1.Enabled := True;
      labMSG.Caption := 'SQL 语句已经保存至剪切板！';
    end;
    {
    if Key = 83 then
    begin
      mo2.Lines.SaveToFile('f:\ss.txt');
    end;
    //self.Caption := IntToStr( Key );
    //}
  end;
end;

procedure TfrmView.bbtCopyClick(Sender: TObject);
begin
  mo2.SelectAll;
  SendMessage (mo2.Handle, WM_Copy, 0, 0);
  Timer1.Enabled := True;
  labMSG.Caption := 'SQL 语句已经保存至剪切板！';
end;

procedure TfrmView.FormKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if Key = VK_F1 then
  begin
    ListBox1.SetFocus;
  end;

  if Key = VK_F2 then
  begin
    CheckListBox1.SetFocus;
  end;

  if Key = VK_F3 then
  begin
    mo2.SetFocus;
  end;

  if Key = VK_F4 then
  begin
    DBGrid1.SetFocus;
  end;

end;

procedure TfrmView.BitBtn2Click(Sender: TObject);
begin
  Application.CreateForm(TfrmDBList, frmDBList);
  if frmDBList.ShowModal = 1 then
  begin
    Database1.Connected := false;
    edit1.Text := frmDBList.lv.Selected.Caption;
    edit2.Text := frmDBList.lv.Selected.SubItems.Strings[0];
    edit3.Text := frmDBList.lv.Selected.SubItems.Strings[1];
    edit4.Text := frmDBList.lv.Selected.SubItems.Strings[2];
  end;
  frmDBList.Free;
end;

procedure TfrmView.SetConnStr;
var
  servername,username,password:string;
begin
  servername:=Trim(Edit1.Text);
  username:=Trim(Edit3.Text);
  password:=Trim(Edit4.Text);
  if (servername='') or (username='') then
  begin
    MessageBox(Handle,'输入为空','提示',MB_OK+MB_ICONWARNING);
    exit;
  end;
  ConnStr:=Format(conn,[servername,'master',username,password]);

end;

procedure TfrmView.DataBaseList;
//var
  //Query:TAdoQuery;
  //i:integer;
begin
  {
  Query:=TAdoQuery.Create(nil);
  try
    Query.ConnectionString := ConnStr;
    Query.SQL.Clear;
    Query.SQL.Add('exec sp_databases');
    Query.Open;

    cmbList.Items.Clear;
    if Query.RecordCount<1 then
    begin
      
    end
    else
    begin
      while not eof do
      begin
        cmbList.Items.Add( Query.FieldByName('DATABASE_NAME').AsString );
        //Format('%.2f MB',[Query.FieldByName('DATABASE_SIZE').AsFloat/1024]);
        Query.Next;
      end;
      Query.close;
      Query.free;
    end;
  except
    showmessage('读取失败');
    Query.Close;
    Query.Free;
  end;
  //}
  try
    Database2.Connected := false;
    Database2.Params.Clear();
    Database2.Params.Values['SERVER NAME']  := Edit1.Text;
    Database2.Params.Values['DATABASE NAME']:= 'master';
    Database2.Params.Values['USER NAME']    := Edit3.Text;
    Database2.Params.Values['PASSWORD']     := Edit4.Text;//BLOB SIZE:=32
    //Database1.Params.Values['BLOB SIZE']    := '512';//64;yym 0730 mod
    Database2.Connected := true;
        
  except
    exit;
  end;

  if Database2.Connected = true then
  begin
    {
    use master
    select fileName,*
    from sysdatabases
    order by name
    }
    Query2.SQL.Clear;
    //Query2.SQL.Add('exec sp_databases');
    //Query2.SQL.Add('exec sp_helpdb');
    Query2.SQL.Text := 'use master select * from sysdatabases order by name';
    Query2.Open;

    cmbList.Items.Clear;

    while not Query2.eof do
    begin
      //cmbList.Items.Add( Query2.FieldByName('DATABASE_NAME').AsString );
      cmbList.Items.Add( Query2.FieldByName('name').AsString );
      //Format('%.2f MB',[Query.FieldByName('DATABASE_SIZE').AsFloat/1024]);
      Query2.Next;
    end;

    Query2.close;
    Database2.Connected := false;
    
    if cmbList.Items.Count>0 then
    begin
      cmbList.ItemIndex := 0;
    end;

  end;
end;

procedure TfrmView.BitBtn4Click(Sender: TObject);
begin
  //SetConnStr;
  //showmessage(ConnStr);
  DataBaseList;
end;

procedure TfrmView.cmbListChange(Sender: TObject);
begin
  bbtDisl.Click;
  //Edit2.Enabled := true;
  Edit2.Text := cmbList.Text;
end;

procedure TfrmView.bbtAddClick(Sender: TObject);
var
  i,a : integer;
  ss: string;
begin
  //添加 &A
  if ListBox1.Items.Count<=0 then exit;
  if ListBox1.ItemIndex<0 then exit;
  
  ss := ListBox1.Items.Strings[ListBox1.ItemIndex];
  ss := 'insert into '+ss;
  mo2.Text := ss;
  mo2.Lines.Add('(');

  a := DBGrid1.FieldCount;
  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