USP_DATAFORMTEMPLATE_CREATEORUPDATE_CLR

Adds or updates a row in the DATAFORMTEMPLATECATALOG table for CLR-based data forms.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@TEMPLATENAME	nvarchar(60)	IN
@DESCRIPTION	nvarchar(1000)	IN
@RECORDTYPE	nvarchar(50)	IN
@MODE	smallint	IN
@ASSEMBLYNAME	nvarchar(128)	IN
@CLASSNAME	nvarchar(128)	IN
@FORMDEFXML	xml	IN
@STATICPARAMETERDEFINITION	xml	IN
@SPEC	xml	IN
@CONTEXTPARAMETERNAME	nvarchar(128)	IN
@CONTEXTRECORDTYPE	nvarchar(50)	IN
@CHANGEAGENTID	uniqueidentifier	IN
@SPECUINAME	nvarchar(60)	IN
Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_CREATEORUPDATE_CLR
(
    @ID uniqueidentifier output, 
    @TEMPLATENAME nvarchar(60),
    @DESCRIPTION nvarchar(1000) = '',
    @RECORDTYPE nvarchar(50) = '',
    @MODE smallint,
    @ASSEMBLYNAME nvarchar(128) = '', 
    @CLASSNAME nvarchar(128) = '',
    @FORMDEFXML xml,
    @STATICPARAMETERDEFINITION xml = '',
    @SPEC xml,
    @CONTEXTPARAMETERNAME nvarchar(128) = '',
    @CONTEXTRECORDTYPE nvarchar(50) = '',
    @CHANGEAGENTID uniqueidentifier = null,
    @SPECUINAME nvarchar(60) = N''
)
as
set nocount on;

declare @RecTypeID uniqueidentifier;
declare @OBJID int;
declare @ExistingID uniqueidentifier;

if @SPECUINAME is null
    set @SPECUINAME = N'';

if @ID is null
    begin
      select @ExistingID = ID from dbo.DATAFORMTEMPLATECATALOG where TEMPLATENAME = @TEMPLATENAME;
      set @ID = @ExistingID;
    end
else
    select @ExistingID = ID from dbo.DATAFORMTEMPLATECATALOG where ID = @ID;

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

declare @CONTEXTRECORDTYPEID uniqueidentifier;

if coalesce(@CONTEXTRECORDTYPE, '') <> ''
    begin
        exec dbo.USP_RECORDTYPE_GETID_OR_CREATE_FROMNAME 
                @NAME=@CONTEXTRECORDTYPE,
                @CHANGEAGENTID=@CHANGEAGENTID,
                @RECORDTYPEID=@CONTEXTRECORDTYPEID output;                
    end

--Add record type if it does not exist.        

    set @RecTypeID = null;
    if coalesce(@RECORDTYPE,'') <> ''
        exec dbo.USP_RECORDTYPE_GETID_OR_CREATE_FROMNAME @NAME=@RECORDTYPE,@CHANGEAGENTID=@CHANGEAGENTID,@RECORDTYPEID=@RecTypeID output;    

declare @SECURITYUIFOLDER nvarchar(255);
declare @SECURITYUIDISPLAYFEATURE bit;

if @MODE = 0
    begin
        --view

        set @SECURITYUIFOLDER =
            coalesce(@SPEC.value(
                '
                declare namespace bbafx="bb_appfx_viewdataformtemplate";
                declare namespace c="bb_appfx_commontypes";
                /bbafx:ViewDataFormTemplateSpec[1]/@c:SecurityUIFolder
                '
                ,'nvarchar(255)')
            ,'');
        set @SECURITYUIDISPLAYFEATURE = cast(
            coalesce(@SPEC.value(
                '
                declare namespace bbfa="bb_appfx_viewdataformtemplate";
                /bbfa:ViewDataFormTemplateSpec[1]/@SecurityUIDisplayFeature
                ','bit')
            , 
                1
           ) as bit);
    end
else if @MODE = 1
    begin
        --edit

        set @SECURITYUIFOLDER=
            coalesce(@SPEC.value(
                '
                declare namespace bbafx="bb_appfx_editdataformtemplate";
                declare namespace c="bb_appfx_commontypes";
                /bbafx:EditDataFormTemplateSpec[1]/@c:SecurityUIFolder
                '
                ,'nvarchar(255)')
            ,'');
        set @SECURITYUIDISPLAYFEATURE = cast(
            coalesce(@SPEC.value(
                '
                declare namespace bbfa="bb_appfx_editdataformtemplate";
                /bbfa:EditDataFormTemplateSpec[1]/@SecurityUIDisplayFeature
                ','bit')
            , 
                1
           ) as bit);
    end
else if @MODE=2
    begin
        --add

        set @SECURITYUIFOLDER=
        coalesce(@SPEC.value(
                '
                declare namespace bbafx="bb_appfx_adddataformtemplate";
                declare namespace c="bb_appfx_commontypes";
                /bbafx:AddDataFormTemplateSpec[1]/@c:SecurityUIFolder
                '
                ,'nvarchar(255)')
        ,'');
        set @SECURITYUIDISPLAYFEATURE = cast(
            coalesce(@SPEC.value(
                '
                declare namespace bbfa="bb_appfx_adddataformtemplate";
                /bbfa:AddDataFormTemplateSpec[1]/@SecurityUIDisplayFeature
                ','bit')
            , 
                1
           ) as bit);
    end

declare @NOSECURITYREQUIRED bit=0;

if @MODE = 0
    set @NOSECURITYREQUIRED =
    coalesce(@SPEC.value(
        'declare namespace bbspec="bb_appfx_viewdataformtemplate";
         declare namespace c="bb_appfx_commontypes";
         /bbspec:ViewDataFormTemplateSpec[1]/@NoSecurityRequired'
        , 'bit')
    , 0);

if @MODE = 1
    set @NOSECURITYREQUIRED =
    coalesce(@SPEC.value(
        'declare namespace bbspec="bb_appfx_editdataformtemplate";
         declare namespace c="bb_appfx_commontypes";
         /bbspec:EditDataFormTemplateSpec[1]/@NoSecurityRequired'
        , 'bit')
    , 0);

if @MODE = 2
    set @NOSECURITYREQUIRED=
    coalesce(@SPEC.value(
        'declare namespace bbspec="bb_appfx_adddataformtemplate";
         declare namespace c="bb_appfx_commontypes";
         /bbspec:AddDataFormTemplateSpec[1]/@NoSecurityRequired'
        , 'bit')
    , 0);

if @ExistingID is null 
    begin
        if @ID is null set @ID = NewID();

        insert into dbo.DATAFORMTEMPLATECATALOG (ID, TEMPLATENAME, DESCRIPTION, RECORDTYPEID, MODE, IMPLEMENTATIONTYPE, ASSEMBLYNAME, CLASSNAME, FORMDEFINITIONXML, STATICPARAMETERDEFINITION, TEMPLATESPECXML, CONTEXTPARAMETERNAME,CONTEXTRECORDTYPEID,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SECURITYUIFOLDER, SPECUINAME, NOSECURITYREQUIRED, SECURITYUIDISPLAYFEATURE)
            values (@ID, @TEMPLATENAME, @DESCRIPTION, @RecTypeID, @MODE, 1, @ASSEMBLYNAME, @CLASSNAME, @FORMDEFXML, @STATICPARAMETERDEFINITION, @SPEC, @CONTEXTPARAMETERNAME,@CONTEXTRECORDTYPEID, @CHANGEAGENTID, @CHANGEAGENTID, GETDATE(), GETDATE(), @SECURITYUIFOLDER, @SPECUINAME, @NOSECURITYREQUIRED, @SECURITYUIDISPLAYFEATURE);
    end

else
    update dbo.DATAFORMTEMPLATECATALOG 
    set TEMPLATENAME = @TEMPLATENAME,
        DESCRIPTION = @DESCRIPTION,
        RECORDTYPEID = @RecTypeID,
        FORMDEFINITIONXML = @FORMDEFXML,
        STATICPARAMETERDEFINITION = @STATICPARAMETERDEFINITION,
        MODE = @MODE,
        IMPLEMENTATIONTYPE = 1,
        ASSEMBLYNAME = @ASSEMBLYNAME,
        CLASSNAME = @CLASSNAME,
        TEMPLATESPECXML = @SPEC,
        CONTEXTPARAMETERNAME = @CONTEXTPARAMETERNAME,
        CONTEXTRECORDTYPEID = @CONTEXTRECORDTYPEID,
        CHANGEDBYID = @CHANGEAGENTID,
        SECURITYUIFOLDER = @SECURITYUIFOLDER,
        DATECHANGED = GETDATE(),
        LOADPROCEDURE = '',
        SAVEPROCEDURE = '',
        SPECUINAME = @SPECUINAME,
        NOSECURITYREQUIRED = @NOSECURITYREQUIRED,
        SECURITYUIDISPLAYFEATURE = @SECURITYUIDISPLAYFEATURE
    where ID = @ID;

return 0;