upd.~pas

存储过程脚本程序源码

unit uPD;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, StdCtrls, ComCtrls, Buttons, ExtCtrls;

type
  TfrmPD = class(TForm)
    Label2: TLabel;
    gbTableList: TGroupBox;
    lvTable: TListView;
    gbFieldList: TGroupBox;
    lvField: TListView;
    gbType: TGroupBox;
    btnADD: TButton;
    btnUpdate: TButton;
    btnAlter: TButton;
    edtPrefixion: TEdit;
    table: TADOTable;
    Splitter1: TSplitter;
    btnAllCheck: TButton;
    procedure btnAllCheckClick(Sender: TObject);
    procedure btnADDClick(Sender: TObject);
    procedure btnAlterClick(Sender: TObject);
    procedure lvTableClick(Sender: TObject);
    procedure lvTableCustomDrawItem(Sender: TCustomListView;
      Item: TListItem; State: TCustomDrawState; var DefaultDraw: Boolean);
    procedure lvFieldClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure lvFieldChange(Sender: TObject; Item: TListItem;
      Change: TItemChange);
    procedure connOBJAfterConnect(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure lvFieldSelectItem(Sender: TObject; Item: TListItem;
      Selected: Boolean);
  private
   procedure CreatePD;
  public
    selFieldList:array of array of string;
    selFieldType:array of array of string;
    selFieldMemo:array of array of string;
    strConn:string;
  end;

var
  frmPD: TfrmPD;

implementation

uses uPDMod, uProcess, uMain, uDBName;

{$R *.dfm}

        var     //选中表数     选中字段数
                SelTableCount,SelFieldCount:integer;
                TableList:tstringlist;

procedure TfrmPD.btnAllCheckClick(Sender: TObject);
var
        i:integer;
begin
        SelFieldCount:=table.FieldCount;
        setlength(selFieldList[SelTableCount-1],SelFieldCount);
        setlength(SelFieldType[SelTableCount-1],SelFieldCount);
        setlength(SelFieldMemo[SelTableCount-1],SelFieldCount);

        for i:=0 to table.FieldCount-1 do
        begin
            lvField.Items.Item[i].Checked := true;
             selFieldList[SelTableCount-1][i]:=lvField.Items.Item[i].SubItems[0];

                if (lvField.Items.Item[i].SubItems[1] = 'char') or (lvField.Items.Item[i].SubItems[1] = 'decimal') or (lvField.Items.Item[i].SubItems[1] = 'nchar') or
                   (lvField.Items.Item[i].SubItems[1] = 'ntext') or (lvField.Items.Item[i].SubItems[1] = 'numeric') or (lvField.Items.Item[i].SubItems[1] = 'nvarchar') or
                   (lvField.Items.Item[i].SubItems[1] = 'sql_variant') or (lvField.Items.Item[i].SubItems[1] = 'varchar') then
                SelFieldType[SelTableCount-1][i]:=lvField.Items.Item[i].SubItems[1]+'('+lvField.Items.Item[i].SubItems[2]+')'
                else
                        SelFieldType[SelTableCount-1][i]:=lvField.Items.Item[i].SubItems[1];
                try
                        SelFieldMemo[SelTableCount-1][i]:='--'+lvField.Items.Item[i].SubItems[4];
                except
                        SelFieldMemo[SelTableCount-1][i]:='--';
                end;
        end;
end;

procedure TfrmPD.btnADDClick(Sender: TObject);
begin
        if not assigned(frmPDMod) then
                frmPDMod:= tfrmPDMod.Create(self);

       CreatePD;
end;

procedure TfrmPD.btnAlterClick(Sender: TObject);
begin
        if frmMain.DBConn.Connected then
        begin
                if not assigned(frmPDMod) then
                        frmPDMod:= tfrmPDMod.Create(self);
                frmPDMod.showmodal;
        end else
                application.MessageBox('没有进行数据库连接','提示',mb_ok);
end;

procedure TfrmPD.lvTableClick(Sender: TObject);
var
        intFieldIndex:integer;
        myFieldsList:tstringlist;
        qryTmp:tadoquery;
        item:tlistitem;

begin
        lvTable.Selected.Checked := not lvTable.Selected.Checked;
        lvField.Clear;
        if lvTable.Selected.Checked then
        begin

                SelTableCount:=SelTableCount+1;
                myFieldsList:= tstringlist.Create ;
                frmMain.DBConn.GetFieldNames(lvTable.Selected.SubItems.Text ,myFieldsList);
                qryTmp:=tadoquery.Create(nil);
                qrytmp.Connection := frmMain.DBConn;

                table.Close;
                table.Connection := frmMain.DBConn;
                table.TableName := lvTable.Selected.SubItems[0];// .Caption;// .SubItems[1];
                table.Open;

                if not Assigned(frmProcess) then
                        frmProcess := tfrmProcess.Create(nil);
                frmProcess.Show;// .Show;
                frmProcess.Gauge1.Progress:=0;//进度条归0
                frmProcess.Gauge1.MaxValue:=table.FieldCount -1;
                frmProcess.Label1.Caption :='正在打开表,请稍等.....';

                TableList.Add(table.TableName);

               // setlength(selFieldList,SelTableCount);
               // setlength(selFieldType,SelTableCount);
               // setlength(selFieldMemo,SelTableCount);

                lvField.Clear;
                for intFieldIndex:=0 to table.FieldCount -1 do
                begin
                        item:= lvfield.Items.Add;
                        qrytmp.Close;
                        qrytmp.SQL.Clear;
                        //qrytmp.SQL.Text := 'Select value  from sysproperties '+
                        //           'where id in (Select id  from sysindexes '+
                        //           'where name='''+ table.TableName +''') and '+
                        //           'smallid='''+inttostr(intFieldIndex+1)+'''';
                        qryTmp.SQL.Text :='SELECT A.Name  TableName, B.Colid Colid,B.Name ColName,b.length length,D.Name ColType,b.isnullable isnull,'+
                                          'C.Value MemoText '+
                                          'FROM SysObjects A '+
                                          'LEFT JOIN SysColumns B   ON A.Id = B.Id '+
                                          'LEFT JOIN SysProperties C ON A.Id = C.Id AND B.Colid = C.Smallid '+
                                          'LEFT JOIN SysTypes D ON B.xType = D.xType '+
                                          'WHERE A.XType = ''U'' AND a.name='''+table.TableName+''''+
                                          ' and b.name ='''+table.Fields[intFieldIndex].FieldName+''' ORDER BY B.Colid';
                       // showmessage(qrytmp.SQL.text);
                        qrytmp.Open;
                        Item.SubItems.Add(table.Fields[intFieldIndex].FieldName );
                        Item.SubItems.Add(qrytmp.Fields[4].AsString );
                        Item.SubItems.Add(qrytmp.Fields[3].AsString);

                        if qrytmp.Fields[5].AsString='1' then
                                Item.SubItems.Add('√')
                        else
                                Item.SubItems.Add('ⅹ') ;
                        if qrytmp.Fields[6].AsString <>'' then
                                Item.SubItems.Add(qrytmp.Fields[6].AsString);
                        application.ProcessMessages;
                        frmProcess.Gauge1.AddProgress(1);
                end;
                frmProcess.Close;
                frmProcess.Free;
                frmProcess:=nil;
       end else
                begin
                        SelTableCount:=SelTableCount-1;
                        TableList.Delete(tablelist.IndexOf(lvTable.Selected.SubItems[0])); 
                end;
                setlength(selFieldList,SelTableCount);
                setlength(selFieldType,SelTableCount);
                setlength(selFieldMemo,SelTableCount);
end;

procedure TfrmPD.lvTableCustomDrawItem(Sender: TCustomListView;
  Item: TListItem; State: TCustomDrawState; var DefaultDraw: Boolean);
begin
        if item.Selected or item.Checked  then
        begin
                (Sender as TListView).Canvas.font.Color := clwindow;
                (Sender as TListView).Canvas.Brush.Color := clHighlight;
        end;
end;

procedure TfrmPD.lvFieldClick(Sender: TObject);
begin
       //if lvField.Items[lvField.Selected.Index].Checked  then
       // lvField.Selected.Checked:=false
       // else
       // lvField.Selected.Checked:=true;
       //lvField.Selected.Checked := not lvField.Selected.Checked;
       //  lvField.Items[lvField.Selected.Index].Checked := not lvField.Items[lvField.Selected.Index].Checked;
        {if lvField.Selected.Checked  then
        begin
                SelFieldCount:= SelFieldCount+1;
                setlength(selFieldList[SelTableCount-1],SelFieldCount);
                setlength(SelFieldType[SelTableCount-1],SelFieldCount);