sqledit.~pas

很久以前用delphi写的一个SQLServer外部的企业管理器

unit SQLEdit;

interface

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

type
  TSQLEditForm = class(TForm)
    Panel1: TPanel;
    Splitter1: TSplitter;
    StatusBar1: TStatusBar;
    SQLText: TMemo;
    Panel2: TPanel;
    ResultPage: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    ResultFrame: TScrollBox;
    ResultFramePanel: TPanel;
    SQLInfo: TMemo;
    FontDialog: TFontDialog;
    ColorDialog: TColorDialog;
    SaveDialog: TSaveDialog;
    procedure FormResize(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure SQLTextChange(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
    
  private
    { Private declarations }
    ResultADO:TADOQuery;
    ResultSource:TDataSource;
    ResultGrid:TDBGrid;
    ResultSplit:TSplitter;
    ResultPanel:TPanel;
    savetofilename:String;
    Function AddNewResult(SQLStr:String):integer;
    Procedure CloseResult;
    Procedure ResultPanelCanResize(Sender: TObject; var NewWidth,NewHeight: Integer; var Resize: Boolean);
  public
    { Public declarations }
    Procedure RunSQL;
    Procedure TheCheckSQL;
    Procedure SetFont;
    Procedure SetBKColor;
    Procedure ExecuteSQL(SQLStr:String);
    Procedure SaveText;
    Procedure SaveASText;
    Procedure OpenText(filename:string);
  end;

var
  SQLEditForm: TSQLEditForm;

implementation

uses MainUnit;

{$R *.dfm}

Procedure TSQLEditForm.TheCheckSQL;
var SQLCount,i,j,ReturnCount:integer;
    SQLString:array of String;
    tempstr:string;
    StartCount,
    EndCount: TLargeInteger;
    Frequency: TLargeInteger;
    ElapsedTime: Extended;

begin
 try
    QueryPerformanceFrequency(Frequency);
    QueryPerformanceCounter(StartCount);
    ReturnCount:=0;
    statusbar1.Panels[0].Text:='正在检查批查询...';
    statusbar1.Panels[1].Text:='';
    statusbar1.Panels[2].Text:='';
    CloseResult;
    tempstr:='';
    for i:=0 to SQLText.Lines.Count-1 do
    begin
       if pos('//',SQLText.Lines[i])<>0 then          //t处理注释的情况
         tempstr:=tempstr+copy(SQLText.lines[i],1,Pos('//',SQLText.lines[i])-1)
       else if pos('--',SQLText.Lines[i])<>0 then
        tempstr:=tempstr+copy(SQLText.lines[i],1,Pos('--',SQLText.lines[i])-1)
       else
         tempstr:=tempstr+SQLText.lines[i];
    end;


   // tempstr:=SQLText.Text;


    SQLCount:=GetSplitCount(tempstr,';');
    if SQLCount=0 then
    begin
         SQLInfo.Lines.Add('没有找到结束符');
         ResultPage.ActivePageIndex:=1;
         QueryPerformanceCounter(EndCount);
         statusbar1.Panels[0].Text:='出现错误';
         statusbar1.Panels[1].Text:='???';
         statusbar1.Panels[2].Text:='???';
         ResultPage.ActivePageIndex:=1;exit;
    end;
    SetLength(SQLString,SQLCount);
    j:=1;
    for i:=0 to SQLCount-1 do
        SQLString[i]:=SplitStr(tempstr,';',j);  //获得SQL

    if not CheckSQL(SQLBrowseManager.ADOConnection,SQLString) then
     begin
         SQLInfo.Lines.Add('SQL语句错误');
         QueryPerformanceCounter(EndCount);
         statusbar1.Panels[0].Text:='检查出现错误';
         statusbar1.Panels[1].Text:='???';
         statusbar1.Panels[2].Text:='???';
         ResultPage.ActivePageIndex:=1;
         exit;
     end;

     ResultPage.ActivePageIndex:=1;
     statusbar1.Panels[0].Text:='批查询完成';
     SQLInfo.Lines.Add('批查询完成');
     QueryPerformanceCounter(EndCount);
     ElapsedTime := (EndCount - StartCount)/Frequency;
     statusbar1.Panels[1].Text:='耗时:'+FloatToStr(ElapsedTime);

  except
   on e:exception do
   begin
         QueryPerformanceCounter(EndCount);
         SQLInfo.Lines.Add(e.Message);
         ResultPage.ActivePageIndex:=1;
         SQLInfo.Lines.Add('检查出现错误');
         statusbar1.Panels[0].Text:='检查出现错误';
         statusbar1.Panels[1].Text:='???';
         statusbar1.Panels[2].Text:='???';
         exit;
   end;
 end;

end;

Procedure TSQLEditForm.OpenText(filename:string);
begin
     if filename='' then exit;
       savetofilename:=filename;
       SQLText.Lines.LoadFromFile(savetofilename);
end;

Procedure TSQLEditForm.SaveText;
begin
   if savetofilename<>'' then
     SQLText.Lines.SaveToFile(savetofilename)
   else SaveASText;
end;


Procedure TSQLEditForm.SaveASText;
begin
    SaveDialog.Execute;
    if SaveDialog.filename='' then exit;

    savetofilename:=SaveDialog.filename;
    case SaveDialog.FilterIndex of
      1: savetofilename:=savetofilename+'.sql';
      2: savetofilename:=savetofilename+'.txt';
      3: savetofilename:=savetofilename+'.sql';
    end;


    SaveText;
end;


Procedure TSQLEditForm.SetBKColor;
begin
    ColorDialog.Execute;
    SQLText.Color:=ColorDialog.Color;
end;

Procedure TSQLEditForm.SetFont;
begin
     FontDialog.Font:=SQLText.Font;
     FontDialog.Execute;
     if not assigned(fontdialog.Font) then exit;
     SQLText.Font:=fontdialog.Font;
end;

Procedure TSQLEditForm.ExecuteSQL(SQLStr:String);
begin
   SQLText.Clear;
   SQLText.Lines.Add(sqlstr);
   AddNewResult(SQLStr);
   ResultPage.ActivePageIndex:=0;
end;


Procedure TSQLEditForm.RunSQL;
var SQLCount,i,j,ReturnCount:integer;
    SQLString:array of String;
    tempstr:string;
    StartCount,
    EndCount: TLargeInteger;
    Frequency: TLargeInteger;
    ElapsedTime: Extended;

begin
 try
    QueryPerformanceFrequency(Frequency);
    QueryPerformanceCounter(StartCount);
    ReturnCount:=0;
    statusbar1.Panels[0].Text:='正在执行批查询...';
    statusbar1.Panels[1].Text:='';
    statusbar1.Panels[2].Text:='';
    CloseResult;
    tempstr:='';
    for i:=0 to SQLText.Lines.Count-1 do
    begin
       if pos('//',SQLText.Lines[i])<>0 then          //t处理注释的情况
         tempstr:=tempstr+copy(SQLText.lines[i],1,Pos('//',SQLText.lines[i])-1)
       else if pos('--',SQLText.Lines[i])<>0 then
        tempstr:=tempstr+copy(SQLText.lines[i],1,Pos('--',SQLText.lines[i])-1)
       else
         tempstr:=tempstr+SQLText.lines[i];
    end;


   // tempstr:=SQLText.Text;


    SQLCount:=GetSplitCount(tempstr,';');
    if SQLCount=0 then
    begin
         SQLInfo.Lines.Add('没有找到结束符');
         ResultPage.ActivePageIndex:=1;
         QueryPerformanceCounter(EndCount);
         statusbar1.Panels[0].Text:='出现错误';
         statusbar1.Panels[1].Text:='???';
         statusbar1.Panels[2].Text:='???';
         ResultPage.ActivePageIndex:=1;exit;
    end;
    SetLength(SQLString,SQLCount);
    j:=1;
    for i:=0 to SQLCount-1 do
        SQLString[i]:=SplitStr(tempstr,';',j);  //获得SQL

    if not CheckSQL(SQLBrowseManager.ADOConnection,SQLString) then
     begin
         SQLInfo.Lines.Add('SQL语句错误');
         QueryPerformanceCounter(EndCount);
         statusbar1.Panels[0].Text:='出现错误';
         statusbar1.Panels[1].Text:='???';
         statusbar1.Panels[2].Text:='???';
         ResultPage.ActivePageIndex:=1;
         exit;
     end;

   { for i:=SQLCount-1 downto 0 do
     if not CheckSQL(SQLBrowseManager.ADOConnection,SQLString[i]) then                 //检查SQL
     begin
         SQLInfo.Lines.Add('SQL语句错误');
         ResultPage.ActivePageIndex:=1;
         exit;
     end;
   }

    for i:=SQLCount-1 downto 0 do
        ReturnCount:=ReturnCount+AddNewResult(SQLString[i]);    //运行SQL
     ResultPage.ActivePageIndex:=0;
     statusbar1.Panels[0].Text:='批查询完成';
     statusbar1.Panels[2].Text:='共'+inttostr(ReturnCount)+'行';
     QueryPerformanceCounter(EndCount);
     ElapsedTime := (EndCount - StartCount)/Frequency;
     statusbar1.Panels[1].Text:='耗时:'+FloatToStr(ElapsedTime);


 except
   on e:exception do
   begin
         QueryPerformanceCounter(EndCount);
         SQLInfo.Lines.Add(e.Message);
         ResultPage.ActivePageIndex:=1;
         statusbar1.Panels[0].Text:='出现错误';
         statusbar1.Panels[1].Text:='???';
         statusbar1.Panels[2].Text:='???';
         exit;
   end;
 end;
end;

Procedure TSQLEditForm.CloseResult;
begin
      SQLInfo.clear;
   try
     while ResultFramePanel.ComponentCount>0 do
     begin
        if ((ResultFramePanel.Components[0] as tobject).Tag<>888) then continue;
          if (ResultFramePanel.Components[0] is TADOQuery) then
        begin
           ((ResultFramePanel.Components[0] as TADOQuery)).Close;
           ((ResultFramePanel.Components[0] as TADOQuery)).Free;
        end
        else if (ResultFramePanel.Components[0] is TDataSource) then
        begin
           ((ResultFramePanel.Components[0] as TDataSource)).Free;
        end
        else if (ResultFramePanel.Components[0] is TDBGrid) then
        begin
           ((ResultFramePanel.Components[0] as TDBGrid)).Free;
        end
        else if  (ResultFramePanel.Components[0] is TSplitter) then
        begin
           ((ResultFramePanel.Components[0] as TSplitter)).Free;
        end
        else if  (ResultFramePanel.Components[0] is TPanel) then
        begin
           ((ResultFramePanel.Components[0] as TPanel)).Free;
        end
     end;
   except
     showmessage('error');
   end;
    ResultFramePanel.Height:=300;
end;

Procedure TSQLEditForm.ResultPanelCanResize(Sender: TObject; var NewWidth,NewHeight: Integer; var Resize: Boolean);
begin
     if NewHeight<10 then Resize:=false
     else resize:=true;
end;


function TSQLEditForm.AddNewResult(SQLStr:String):integer;
begin
try
   ResultFramePanel.Height:=ResultFramePanel.height+100;
   ResultSplit:=TSplitter.Create(self.ResultFramePanel);
   ResultSplit.Parent:=self.ResultFramePanel;
   ResultSplit.Tag:=888;
   ResultSplit.Height:=20;
 //  ResultSplit.Top:=1000;
   ResultSplit.Color:=clGray;
   ResultSplit.Align:=altop;
   ResultSplit.MinSize:=1;
   ResultSplit.Top:=0;

   ResultPanel:=TPanel.Create(self.ResultFramePanel);
   ResultPanel.Parent:=self.ResultFramePanel;
   ResultPanel.Tag:=888;
 //  ResultPanel.Height:=100;
 //  ResultPanel.Top:=1100;;
   ResultPanel.Align:=altop;
 //  ResultPanel.Color:=clred;
   ResultPanel.OnCanResize:=ResultPanelCanResize;
   {ResultPanel.OnMouseDown:=ResultPanelMouseDown;
   ResultPanel.OnMouseMove:=ResultPanelMouseMove;
   ResultPanel.OnMouseUp:=ResultPanelMouseUp;
    }
    ResultADO:=TADOQuery.Create(self.ResultFramePanel);
    ResultADO.Tag:=888;
    ResultADO.Connection:=SQLBrowseManager.ADOConnection;

    ResultADO.SQL.Clear;
    ResultADO.SQL.Add(SQLStr);
    ResultADO.Open;

    ResultSource:=TDataSource.Create(self.ResultFramePanel);
    ResultSource.Tag:=888;
    ResultSource.DataSet:=ResultADO;
    
    ResultGrid:=TDBGrid.Create(self.ResultFramePanel);
    ResultGrid.Parent:=self.ResultPanel;
    ResultGrid.Tag:=888;
    ResultGrid.Height:=150;
    ResultGrid.Align:=alClient;
    ResultGrid.DataSource:=ResultSource;
    
    SQLInfo.lines.Add('');
    SQLInfo.lines.Add('（所影响的行数为 '+inttostr(ResultADO.RecordCount)+' 行)');
    result:=ResultADO.RecordCount;
except
   result:=-1;
end;


end;


procedure TSQLEditForm.FormResize(Sender: TObject);
begin
    ResultFramePanel.Width:=ResultFrame.Width;
end;



procedure TSQLEditForm.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
   CloseResult;
   action:=cafree;
end;

procedure TSQLEditForm.SQLTextChange(Sender: TObject);
begin
    statusbar1.Panels[5].Text:='行 '+inttostr(SQLText.CaretPos.x)+' ,列 '+inttostr(SQLText.CaretPos.y);
end;

procedure TSQLEditForm.FormCreate(Sender: TObject);
begin
    savetofilename:='';
end;

procedure TSQLEditForm.FormCloseQuery(Sender: TObject;
  var CanClose: Boolean);
var returnvalue:integer;
begin
   if SQLText.Modified then
   begin
       if savetofilename='' then
          returnvalue:=MessageDlg('"无标题"   文档中的文本已修改,'+#13+'是否保存更改？', mtInformation,[mbYes,mbNo,mbCancel],0)
       else
          returnvalue:=MessageDlg('"'+ExtractFileName(savetofilename)+'"   文档中的文本已修改,'+#13+'是否保存更改？', mtInformation,[mbYes,mbNo,mbCancel],0);
       case returnvalue of
        mrYes:begin SaveText; CanClose:=true; end;
        mrNo: CanClose:=true;
        mrCancel: CanClose:=false;
       end;

   end;
end;

end.