Delete Stored Procedure-based RecordOperationSpec with CLR Prompt

RecordOperationSpec Source Code

<RecordOperationSpec xmlns="bb_appfx_recordoperation" xmlns:common="bb_appfx_commontypes" ID="3B44D903-0D03-46C9-B95B-CAE92FD7DF5C" Name="Marketing Effort Segment: Delete" Description="Removes a segment from a marketing effort." Author="Blackbaud Product Development" RecordType="Marketing Effort Segment" OperationType="Delete" TimeOutSeconds="300" common:SecurityUIFolder="Marketing\Marketing Effort">
 
<ResourceFile AssemblyName="Blackbaud.AppFx.Marketing.Catalog.dll" ClassName="Blackbaud.AppFx.Marketing.Catalog.SegmentationSegmentDelete.RecordOperation" />
 
<SPRecord>
 
<SPOperationImplementation SPName="USP_MKTSEGMENTATIONSEGMENT_DELETE">
 
<common:CreateProcedureSQL>
 
 
 
create procedure dbo.[USP_MKTSEGMENTATIONSEGMENT_DELETE]
 
(
 
@ID uniqueidentifier,
 
@CHANGEAGENTID uniqueidentifier
 
)
 
as
 
set nocount on;
 
 
 
declare @ACTIVE bit;
 
declare @SEGMENTATIONID uniqueidentifier;
 
declare @SEGMENTATIONPACKAGEID uniqueidentifier;
 
declare @PACKAGEID uniqueidentifier;
 
declare @RECORDSOURCEID uniqueidentifier;
 
declare @SEQUENCE int;
 
declare @SEGMENTTYPECODE tinyint;
 
declare @ISVENDORMANAGED bit;
 
declare @DATATABLE nvarchar(128);
 
declare @SQL nvarchar(max);
 
begin try
 
/* Grab some info so we can remove the package and reorder the rest of the segments after the delete */
 
select
 
@SEGMENTATIONID = [SEG].[SEGMENTATIONID],
 
@SEGMENTATIONPACKAGEID = (select [PAK].[ID] from dbo.[MKTSEGMENTATIONPACKAGE] as [PAK] where [PAK].[SEGMENTATIONID] = [SEG].[SEGMENTATIONID] and [PAK].[PACKAGEID] = [SEG].[PACKAGEID]),
 
@PACKAGEID = [SEG].[PACKAGEID],
 
@SEQUENCE = [SEG].[SEQUENCE],
 
@RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
 
@SEGMENTTYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE],
 
@ISVENDORMANAGED = (case when [MKTSEGMENT].[SEGMENTTYPECODE] = 2 then dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([MKTSEGMENT].[ID]) else 0 end)
 
from dbo.[MKTSEGMENTATIONSEGMENT] as [SEG]
 
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [SEG].[SEGMENTID]
 
where [SEG].[ID] = @ID;
 
select @ACTIVE = [ACTIVE] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID;
 
 
 
if @ACTIVE = 0
 
--Check if the mailing is currently being activated...
 
exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;
 
/* Delete each test segment individually so the packages get adjusted correctly */
 
declare @TESTSEGMENTID uniqueidentifier;
 
declare TESTSEGMENTCURSOR cursor local fast_forward for
 
select [ID]
 
from dbo.[MKTSEGMENTATIONTESTSEGMENT]
 
where [SEGMENTID] = @ID
 
order by [SEQUENCE] desc;
 
open TESTSEGMENTCURSOR;
 
fetch next from TESTSEGMENTCURSOR into @TESTSEGMENTID;
 
while (@@FETCH_STATUS = 0)
 
begin
 
exec dbo.[USP_MKTSEGMENTATIONTESTSEGMENT_DELETE] @TESTSEGMENTID, @CHANGEAGENTID;
 
fetch next from TESTSEGMENTCURSOR into @TESTSEGMENTID;
 
end;
 
close TESTSEGMENTCURSOR;
 
deallocate TESTSEGMENTCURSOR;
 
/* Clear any segment cache */
 
if @SEGMENTTYPECODE in (1, 3, 4, 5) or (@SEGMENTTYPECODE = 2 and @ISVENDORMANAGED = 0)  --Constituent, revenue, membership, sponsorship, or imported list
 
exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE] @ID, 1, 0;
 
if @SEGMENTTYPECODE = 2 and @ISVENDORMANAGED = 1  --Vendor managed list
 
delete from dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO] where [SEGMENTID] = @ID;
 
/* Delete segmentation segment rows from the MKTSOURCECODEPART table. */
 
declare @MKTSOURCECODEPARTID uniqueidentifier;
 
declare MKTSOURCECODEPARTCURSOR cursor local fast_forward for
 
select [ID]
 
from dbo.[MKTSOURCECODEPART]
 
where [SEGMENTATIONSEGMENTID] = @ID;
 
open MKTSOURCECODEPARTCURSOR;
 
fetch next from MKTSOURCECODEPARTCURSOR into @MKTSOURCECODEPARTID;
 
while (@@FETCH_STATUS = 0)
 
begin
 
exec dbo.[USP_MKTSOURCECODEPART_DELETEBYID_WITHCHANGEAGENTID] @MKTSOURCECODEPARTID, @CHANGEAGENTID;
 
fetch next from MKTSOURCECODEPARTCURSOR into @MKTSOURCECODEPARTID;
 
end;
 
close MKTSOURCECODEPARTCURSOR;
 
deallocate MKTSOURCECODEPARTCURSOR;
 
/* delete the mailing data and source analysis rule data */
 
if @ACTIVE = 0
 
begin
 
set @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);
 
if exists(select top 1 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @DATATABLE)
 
begin
 
set @SQL = 'delete from dbo.[' + @DATATABLE + '] where [SEGMENTID] = @ID;';
 
 
 
exec sp_executesql @SQL, N'@ID uniqueidentifier', @ID = @ID;
 
end
 
 
 
set @DATATABLE = dbo.[UFN_MKTSOURCEANALYSISRULE_MAKETABLENAME](@RECORDSOURCEID);
 
 
 
if exists(select top 1 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @DATATABLE)
 
begin
 
set @SQL = 'delete from dbo.[' + @DATATABLE + '] where [SEGMENTID] = @ID;';
 
 
 
exec sp_executesql @SQL, N'@ID uniqueidentifier', @ID = @ID;
 
end
 
end
 
/* Delete segment from segmented house file import */
 
if dbo.[UFN_MKTCONSTITUENTFILEIMPORT_IMPORTTABLEEXISTS](@SEGMENTATIONID) = 1
 
begin
 
set @DATATABLE = dbo.[UFN_MKTCONSTITUENTFILEIMPORT_BUILDTABLENAME](@SEGMENTATIONID);
 
set @SQL = 'delete from dbo.[' + @DATATABLE + '] where [SEGMENTATIONSEGMENTID] = @ID;';
 
exec sp_executesql @SQL, N'@ID uniqueidentifier', @ID = @ID;
 
end
 
/* Delete the segment */
 
exec dbo.[USP_MKTSEGMENTATIONSEGMENT_DELETEBYID_WITHCHANGEAGENTID] @ID, @CHANGEAGENTID;
 
/* Remove the package, only if is not being used by any other segments */
 
if not exists(select [ID] from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID and [PACKAGEID] = @PACKAGEID)
 
exec dbo.[USP_MKTSEGMENTATIONPACKAGE_DELETE] @SEGMENTATIONPACKAGEID, @CHANGEAGENTID;
 
/* Reorder the rest of the segments */
 
update dbo.[MKTSEGMENTATIONSEGMENT] set
 
[SEQUENCE] = ([SEQUENCE] - 1),
 
[CHANGEDBYID] = @CHANGEAGENTID,
 
[DATECHANGED] = getdate()
 
where [SEGMENTATIONID] = @SEGMENTATIONID
 
and [SEQUENCE] &gt; @SEQUENCE;
 
end try
 
begin catch
 
exec dbo.[USP_RAISE_ERROR];
 
return 1;
 
end catch
 
return 0;
 
 
 
</common:CreateProcedureSQL>
 
</SPOperationImplementation>
 
</SPRecord>
 
 
 
 <Prompt>
 
  <CLRPrompt AssemblyName="Blackbaud.AppFx.Marketing.Catalog" ClassName="Blackbaud.AppFx.Marketing.Catalog.SegmentationSegmentDeletePrompt">
 
    <common:GrantSelectList>
 
      <common:GrantSelect>MKTSEGMENTATIONSEGMENT (ID, SEGMENTID)</common:GrantSelect>
 
      <common:GrantSelect>MKTSEGMENT (ID, SEGMENTTYPECODE)</common:GrantSelect>
 
    </common:GrantSelectList>
 
  </CLRPrompt>
 
</Prompt>
 
</RecordOperationSpec>

CLR Prompt Source Code

Imports Blackbaud.AppFx.Server
 
Public Class SegmentationSegmentDeletePrompt
 
	Inherits AppCatalog.AppRecordOperationGetPrompt
 
	Public Overrides Function GetPrompt() As Server.AppCatalog.AppRecordOperationGetPromptResult
 
		Dim segmentType As String = My.Resources.Content.Common_Segment
 
		' when this is called from the mailing segments grid, a record ID is not passed, which is OK because the prompt should
 
		' say "segment" then anyway
 
		If Not String.IsNullOrEmpty(Me.ProcessContext.RecordID) Then
 
			Using conn As SqlClient.SqlConnection = Me.RequestContext.OpenAppDBConnection()
 
				Dim command As New SqlClient.SqlCommand("select [MKTSEGMENT].[SEGMENTTYPECODE] from dbo.[MKTSEGMENTATIONSEGMENT] inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID] where [MKTSEGMENTATIONSEGMENT].[ID] = @ID", conn)
 
				With command
 
					.CommandTimeout = Me.RequestContext.ClientAppInfo.TimeOutSeconds
 
					.Parameters.AddWithValue("@ID", New Guid(Me.ProcessContext.RecordID))
 
 
 
					Dim reader As SqlClient.SqlDataReader = .ExecuteReader()
 
					If reader.Read Then
 
						Select Case CType(reader.GetByte(0), Segmentation.Segments.SegmentTypeCode)
 
						Case Segmentation.Segments.SegmentTypeCode.MediaOutlet
 
							segmentType = My.Resources.Content.Common_MediaOutlet
 
						Case Segmentation.Segments.SegmentTypeCode.TimeSlot
 
							segmentType = My.Resources.Content.Common_TimeSlot
 
						Case Segmentation.Segments.SegmentTypeCode.Location
 
							segmentType = My.Resources.Content.Common_Location
 
						End Select
 
					End If
 
					reader.Close()
 
				End With
 
			End Using
 
		End If
 
 
 
		Return New AppCatalog.AppRecordOperationGetPromptResult(String.Format(My.Resources.Content.SegmentationSegmentDeletePrompt_Prompt, segmentType))
 
	End Function
 
End Class

Closing Connections

Sometimes your code requires an unmanaged resource, such as a SQL connection. If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. You may also consider a Using block which guarantees the disposal of one or more such resources when your code is finished with them. For more information see http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.close.aspx and http://msdn.microsoft.com/en-us/library/htd05whh.aspx.

For example, the following code would require the explicit closing of the connection:

Dim conn As SqlConnection = Me.RequestContext.OpenAppDBConnection
		‘code to perform work with the sql connection
conn.Close

Whereas utilizing a Using block guarantees the disposal of the SQLConnection:

using conn As SqlConnection = Me.RequestContext.OpenAppDBConnection
        ‘code to perform work with the sql connection
end using