upd.pas

存储过程脚本程序源码

                setlength(SelFieldMemo[SelTableCount-1],SelFieldCount);

                selFieldList[SelTableCount-1][SelFieldCount-1]:=lvField.Selected.SubItems[0];
                if (lvField.Selected.SubItems[1] = 'char') or (lvField.Selected.SubItems[1] = 'decimal') or (lvField.Selected.SubItems[1] = 'nchar') or
                   (lvField.Selected.SubItems[1] = 'ntext') or (lvField.Selected.SubItems[1] = 'numeric') or (lvField.Selected.SubItems[1] = 'nvarchar') or
                   (lvField.Selected.SubItems[1] = 'sql_variant') or (lvField.Selected.SubItems[1] = 'varchar') then
                SelFieldType[SelTableCount-1][SelFieldCount-1]:=lvField.Selected.SubItems[1]+'('+lvField.Selected.SubItems[2]+')'
                else
                        SelFieldType[SelTableCount-1][SelFieldCount-1]:=lvField.Selected.SubItems[1];
              try

                SelFieldMemo[SelTableCount-1][SelFieldCount-1]:='--'+lvField.Selected.SubItems[4];
              except

              end;
        end else
                SelFieldCount:= SelFieldCount-1;
      
end;

procedure TfrmPD.FormCreate(Sender: TObject);
begin
        SelTableCount:=0;
        SelFieldCount:=0;
        TableList:= tstringlist.Create ;
end;

procedure TfrmPD.lvFieldChange(Sender: TObject; Item: TListItem;
  Change: TItemChange);
begin
        if lvField.Items.Count =0 then
                btnAllCheck.Enabled := false
        else
                btnAllCheck.Enabled := true;

end;

procedure TfrmPD.CreatePD;
var
        i:integer;
        space:string;
begin
        if SelFieldCount= 0 then
        begin
                application.MessageBox('没有选中字段','提示',mb_ok);
                exit;
        end;
        frmPDMod.mContent.Lines.Add('CREATE PROCEDURE '+trim(edtPrefixion.Text)+TableList.Strings[SelTableCount-1]+'_Add');
        frmPDMod.mContent.Lines.Add('/*************************************************************'+#13+#10+
                                        '功能名称：增加'+TableList.Strings[SelTableCount-1]+'存储过程'+#13+#10+
                                        '创建日期：'+formatdatetime('YYYY-MM-DD',date)+#13+#10+
                                        '最后修订日期：YYYY-MM-DD'+#13+#10+
                                        '作    者：李绍龙'+#13+#10+
                                        '算法说明：'+#13+#10+
                                        '输    入：'+#13+#10+
                                        '输    出：返回记录集：0-表示增加成功、1-表示增加失败'+ #13+#10+
                                        'ModifyList：'+#13+#10+
                                      '**************************************************************/');
        frmPDMod.mContent.Lines.Add('('+#13+#10);

        for i:=0 to SelFieldCount-1 do
        begin
                if high(selFieldList)<0 then
                begin
                        messagedlg('此存储过程已经生成,修改请点击『修改』按钮',mtInformation,[mbOk], 0);
                        exit;
                end;        
                if i= SelFieldCount-1 then
                        frmPDMod.mContent.Lines.Add('@'+selFieldList[SelTableCount-1][i]+' '+selFieldType[SelTableCount-1][i]+selFieldMemo[SelTableCount-1][i])
                else
                        frmPDMod.mContent.Lines.Add('@'+selFieldList[SelTableCount-1][i]+' '+selFieldType[SelTableCount-1][i]+','+selFieldMemo[SelTableCount-1][i]);
        end;
        frmPDMod.mContent.Lines.Add(#13+#10+')'+#13+#10);
        frmPDMod.mContent.Lines.Add('AS'+#13+#10);
        frmPDMod.mContent.Lines.Add('BEGIN'+#13+#10);
        frmPDMod.mContent.Lines.Add('    SET NOCOUNT ON'+#13+#10);

        frmPDMod.mContent.Lines.Add('    BEGIN TRANSACTION'+#13+#10);
                
        frmPDMod.mContent.Lines.Add('    INSERT INTO '+TableList.Strings[SelTableCount-1]+'('+#13+#10);

        for i:=0 to SelFieldCount -1 do
        begin
                if i= SelFieldCount-1 then
                        frmPDMod.mContent.Lines.Add('                 '+stringofchar(' ',length(TableList.Strings[SelTableCount-1]))+table.Fields[i].FieldName)
                else
                        frmPDMod.mContent.Lines.Add('                 '+stringofchar(' ',length(TableList.Strings[SelTableCount-1]))+table.Fields[i].FieldName+',');
        end;
        frmPDMod.mContent.Lines.Add('                 '+stringofchar(' ',length(TableList.Strings[SelTableCount-1]))+')'+#13+#10);
        frmPDMod.mContent.Lines.Add('                 '+stringofchar(' ',length(TableList.Strings[SelTableCount-1]))+'VALUES('+#13+#10);

        for i:=0 to SelFieldCount -1 do
        begin
                if i= SelFieldCount-1 then
                        frmPDMod.mContent.Lines.Add('                 '+stringofchar(' ',length(TableList.Strings[SelTableCount-1]))+'@'+selFieldList[SelTableCount-1][i])
                else
                        frmPDMod.mContent.Lines.Add('                 '+stringofchar(' ',length(TableList.Strings[SelTableCount-1]))+'@'+selFieldList[SelTableCount-1][i]+',');
        end;
        frmPDMod.mContent.Lines.Add('                 '+stringofchar(' ',length(TableList.Strings[SelTableCount-1]))+')'+#13+#10);

        frmPDMod.mContent.Lines.Add('IF @@ERROR<>0'+#13+#10);
        frmPDMod.mContent.Lines.Add('BEGIN'+#13+#10);
        frmPDMod.mContent.Lines.Add('    ROLLBACK TRANSACTION'+#13+#10);
        frmPDMod.mContent.Lines.Add('    SELECT 1 ,''保存失败'''+#13+#10);
        frmPDMod.mContent.Lines.Add('END'+#13+#10);
        frmPDMod.mContent.Lines.Add('ELSE BEGIN'+#13+#10);
        frmPDMod.mContent.Lines.Add('         COMMIT TRANSACTION'+#13+#10);
        frmPDMod.mContent.Lines.Add('         SELECT 0 ,''保存成功'''+#13+#10);
        frmPDMod.mContent.Lines.Add('     END'+#13+#10);
        frmPDMod.mContent.Lines.Add('END'+#13+#10);
        if not assigned(frmPDMod) then
                frmPDMod:= tfrmPDMod.Create(self);
        try
                frmPDMod.show;//modal;
        except

        end;
        if frmPDMod.ModalResult = mrok then
        begin
                setlength(selFieldList,0);
                setlength(selFieldType,0);
                setlength(selFieldMemo,0);
        end;
end;

procedure TfrmPD.connOBJAfterConnect(Sender: TObject);
var
        mytables:tstringlist;
        intTableIndex,i,gridRowCount:integer;
        item:tlistitem;
begin
        mytables:= tstringlist.Create ;
        frmMain.DBConn.GetTableNames(mytables);
        if not Assigned(frmProcess) then
                frmProcess := tfrmProcess.Create(nil);
        frmProcess.Show;// .Show;
        frmProcess.Gauge1.Progress:=0;//进度条归0
        frmProcess.Gauge1.MaxValue:=mytables.Count;
        frmProcess.Label1.Caption :='正在打开数据库,请稍等.....';
        for intTableIndex:=0 to mytables.Count -1 do
        begin
                item:=lvTable.Items.Add ;
                item.SubItems.Add (myTables.Strings[intTableIndex]);
                application.ProcessMessages;
                frmProcess.Gauge1.AddProgress(1);
        end;
       // setlength(selFieldList,mytables.Count);
       TableList:=tstringlist.Create ;
        frmProcess.Close;
        frmProcess.Free;
        frmProcess:=nil;
end;

procedure TfrmPD.FormShow(Sender: TObject);
var
        mytables:tstringlist;
        intTableIndex,i,gridRowCount:integer;
        item:tlistitem;
begin
        mytables:= tstringlist.Create ;
        frmMain.DBConn.GetTableNames(mytables);
        if not Assigned(frmProcess) then
                frmProcess := tfrmProcess.Create(nil);
        frmProcess.Show;// .Show;
        frmProcess.Gauge1.Progress:=0;//进度条归0
        frmProcess.Gauge1.MaxValue:=mytables.Count;
        frmProcess.Label1.Caption :='正在打开数据库,请稍等.....';
        for intTableIndex:=0 to mytables.Count -1 do
        begin
                item:=lvTable.Items.Add ;
                item.SubItems.Add (myTables.Strings[intTableIndex]);
                application.ProcessMessages;
                frmProcess.Gauge1.AddProgress(1);
        end;
       // setlength(selFieldList,mytables.Count);
  //     TableList:=tstringlist.Create ;
        frmProcess.Close;
        frmProcess.Free;
        frmProcess:=nil;
end;

procedure TfrmPD.lvFieldSelectItem(Sender: TObject; Item: TListItem;
  Selected: Boolean);
begin
//showmessage(item.Caption );
{if lvField.Selected.Checked  then
        begin
                SelFieldCount:= SelFieldCount+1;
                setlength(selFieldList[SelTableCount-1],SelFieldCount);
                setlength(SelFieldType[SelTableCount-1],SelFieldCount);
                setlength(SelFieldMemo[SelTableCount-1],SelFieldCount);

                selFieldList[SelTableCount-1][SelFieldCount-1]:=lvField.Selected.SubItems[0];
                if (lvField.Selected.SubItems[1] = 'char') or (lvField.Selected.SubItems[1] = 'decimal') or (lvField.Selected.SubItems[1] = 'nchar') or
                   (lvField.Selected.SubItems[1] = 'ntext') or (lvField.Selected.SubItems[1] = 'numeric') or (lvField.Selected.SubItems[1] = 'nvarchar') or
                   (lvField.Selected.SubItems[1] = 'sql_variant') or (lvField.Selected.SubItems[1] = 'varchar') then
                SelFieldType[SelTableCount-1][SelFieldCount-1]:=lvField.Selected.SubItems[1]+'('+lvField.Selected.SubItems[2]+')'
                else
                        SelFieldType[SelTableCount-1][SelFieldCount-1]:=lvField.Selected.SubItems[1];
              try

                SelFieldMemo[SelTableCount-1][SelFieldCount-1]:='--'+lvField.Selected.SubItems[4];
              except

              end;
        end else
                SelFieldCount:= SelFieldCount-1;
                }
end;

end.