手动发布菜单操作

发布语句生成

-- 手动发布菜单语句生成
CREATE OR ALTER proc [dbo].[Proc_Insert] (
    @tablename varchar(256), -- 表名
    @where varchar(max) -- 条件
)
as
begin
    set nocount on
    declare @sqlstr varchar(MAX)
    declare @sqlstr1 varchar(MAX)
    declare @sqlstr2 varchar(MAX)
    select @sqlstr='select ''INSERT '+@tablename
    select @sqlstr1=''
    select @sqlstr2=' ('
    select @sqlstr1= ' VALUES ( ''+'
    select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case 
    -- when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
    when a.xtype =36 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    when a.xtype =127 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(20),'+a.name +')'+' end'
    when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'
    when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    when a.xtype =61 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
    when a.xtype =106 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
    when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
    when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'
    when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
    when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    when a.xtype =108 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
    when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
    when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
    when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'
    when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
    when a.xtype =48 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'
    -- when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
    when a.xtype =167 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    else '''NULL'''
    end as col,a.colid,a.name
    from syscolumns a
    where a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 -- and a.xtype <>36
    AND COLUMNPROPERTY(a.id, a.name, 'IsIdentity') <> 1
    )t order by colid
    select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename + ' where '+@where
    -- print @sqlstr
    
    exec(@sqlstr)

    set nocount off
end
GO

执行语句

if object_id('tempdb..#insertSQL') is not null  
    drop table #insertSQL
create table #insertSQL(seq int IDENTITY(1,1),sqlstr nvarchar(max))

insert into #insertSQL(sqlstr) values('-- 页面');
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Page]', 'Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8''';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Page_Trl]', 'ID in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PageColumn]', 'Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PageColumn_Trl]', 'ID IN (select ID from UBF_Assemble_PageColumn where Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Part]', 'Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Part_Trl]', 'ID in (select ID from UBF_Assemble_Part where Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_ColumnPart]', 'PageColumn IN (select ID from UBF_Assemble_PageColumn where Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_ParamBinding]', 'PageParameter IN (select ID from UBF_Assemble_PageParameter where Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PageParameter]', 'Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PartRelation]', 'Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8'')';

insert into #insertSQL(sqlstr) values('-- 页面');
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Page]', 'Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08''';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Page_Trl]', 'ID in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PageColumn]', 'Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PageColumn_Trl]', 'ID IN (select ID from UBF_Assemble_PageColumn where Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Part]', 'Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Part_Trl]', 'ID in (select ID from UBF_Assemble_Part where Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_ColumnPart]', 'PageColumn IN (select ID from UBF_Assemble_PageColumn where Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_ParamBinding]', 'PageParameter IN (select ID from UBF_Assemble_PageParameter where Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PageParameter]', 'Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PartRelation]', 'Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08'')';

insert into #insertSQL(sqlstr) values('-- DLL');
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_AppComponents]', 'Component = ''UFIDA.U9.SH.PubUI.WebPart.dll''';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_AppComponents]', 'Component = ''UFIDA.U9.SH.PubQryListUI.WebPart.dll''';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_AppComponents]', 'Component = ''UFIDA.U9.SH.PubBE.dll''';

insert into #insertSQL(sqlstr) values('-- 菜单[客开]');
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Menu]', 'Code = ''a6dcc019-7877-4c5a-9a2f-e50653104337''';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Menu_Trl]', 'ID IN (select ID from UBF_Assemble_Menu where Code = ''a6dcc019-7877-4c5a-9a2f-e50653104337'')';

insert into #insertSQL(sqlstr) values('-- 菜单[BOM子项替代关系]');
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Menu]', 'Code = ''25f8fca6-3ce5-425d-9956-1d108ebc84f3''';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Menu_Trl]', 'ID IN (select ID from UBF_Assemble_Menu where Code = ''25f8fca6-3ce5-425d-9956-1d108ebc84f3'')';

select * from #insertSQL order by seq;

删除菜单

delete from UBF_Assemble_Menu_Trl where ID in (select ID from UBF_Assemble_Menu where Code = 'a6dcc019-7877-4c5a-9a2f-e50653104337');
delete from UBF_Assemble_Menu where Code = 'a6dcc019-7877-4c5a-9a2f-e50653104337';

删除发布的DLL

DELETE FROM UBF_Assemble_AppComponents WHERE Component = 'UFIDA.U9.SH.PubUI.WebPart.dll';

删除发布的页面

DELETE FROM UBF_Assemble_PartRelation WHERE Page in (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8');
DELETE FROM UBF_Assemble_PageParameter WHERE Page in (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8');

with
    PageID as
    (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8'),
    PageParameterID as
    (select ID from UBF_Assemble_PageParameter where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_ParamBinding WHERE PageParameter in (select ID from PageParameterID);

with
    PageID as
    (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8'),
    PagePartID as
    (select ID from UBF_Assemble_Part where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_Part_Trl WHERE ID in (select ID from PagePartID);

DELETE FROM UBF_Assemble_Part WHERE Page in (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8');

with
    PageID as
    (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8'),
    PageColumnID as
    (select ID from UBF_Assemble_PageColumn where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_ColumnPart WHERE PageColumn in (select ID from PageColumnID);

with
    PageID as
    (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8'),
    PageColumnID as
    (select ID from UBF_Assemble_PageColumn where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_PageColumn_Trl WHERE ID in (select ID from PageColumnID);

DELETE FROM UBF_Assemble_PageColumn WHERE Page in (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8');
DELETE FROM UBF_Assemble_Page_Trl WHERE ID in (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8');
DELETE FROM UBF_Assemble_Page WHERE Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8';