Web Shell Smart Client

CLR DataList

This code sample can be seen in action by selecting the Business processes task within the Administration functional area within Blackbaud CRM version 2.9.1001. You can view all business process activity executed through the program on the Business Processes page. The Business Processes page houses all business process activity executed in the program. For example, if a user generates a KPI instance value, a record of this execution appears in Business Processes; if a user makes a general ledger post, a record of the posting appears in Business Processes; if a user processes acknowledgment letters, a record of the process appears in Business Processes.

The <CLRDataList > tag (green highlight in the XML spec sample code below) indicates that the business logic layer for the data list should be implemented with .NET CLR code that resides within a class named BusinessProcessStatusDatalist within a DLL assembly file named "Blackbaud.AppFx.Platform.Catalog.dll."

Within the <CLRDataList> tag, the <c:Param ID="ApplyParametersIDFilter"> tag provides a value for a public memory variable with the same name (blue highlights in the XML spec and CLR code). Within the <Parameters> tag, each filter form field is represented by a public memory variable with the same names (yellow highlights in the XML spec and the CLR code). In this way, the parameter values are passed to the CLR code when the data list displays or when the filter is applied to the data list.

Looking at the CLR class, we see the class inherits from Blackbaud.AppFx.Server.AppCatalog.AppDataList. Looking at AppDataList, we see that we must override its GetListResults() function. Therefore, we see the function declared as Public Overrides Function GetListResults() within the BusinessProcessStatusDatalist class (gray highlights). It is within the GetListResults function where the main business logic layer processing happens. The GetListResults function builds a Transact-SQL SELECT statement, opens a database connection, executes the query, closes the connection as soon as possible, loops through the query results, builds a list of rows, and returns an AppFx.Server.AppCatalog.AppDataListResult object to the platform which represents the rows for the data list.

XML Spec Sample Code

<DataListSpec
	xmlns="bb_appfx_datalist"
	xmlns:c="bb_appfx_commontypes"
	ID="DB78E7EB-D0B2-4851-ADE7-406BDC7F2206"
	Name="Business Process Status List"
	Description="Returns a status list for all business processes."
	Author="Blackbaud Product Development"
	c:SecurityUIFolder="Business Process"\>
<c:DependencyList>
	<c:Dependency CatalogAssembly="Blackbaud.AppFx.Platform.Catalog.dll" CatalogItem="Blackbaud.AppFx.Platform.Catalog.BusinessProcessInstance.SimpleList.xml" />
</c:DependencyList>
 
<ResourceFile AssemblyName="Blackbaud.AppFx.Platform.Catalog.dll" ClassName="Blackbaud.AppFx.Platform.Catalog.BusinessProcessStatus.Datalist" />
 
<RSSFeed>
	<Channel>
		<Title Caption="Business Processes" ResourceKey="$$rss_title" />
	</Channel>
	<Item>
		<IDField>ID</IDField>
		<Title Caption="{0}">
			<FieldArguments>
				<Field>Process</Field>
			</FieldArguments>
		</Title>
		<PublicationDateField>Ended</PublicationDateField>
	</Item>
</RSSFeed>
 
<CLRDataList AssemblyName="Blackbaud.AppFx.Platform.Catalog"  ClassName="Blackbaud.AppFx.Platform.Catalog.BusinessProcessStatusDatalist">
 
	<c:GrantSelectList>
		<c:GrantSelect>BUSINESSPROCESSSTATUS</c:GrantSelect>
		<c:GrantSelect>BUSINESSPROCESSCATALOG</c:GrantSelect>
		<c:GrantSelect>APPUSER</c:GrantSelect>
	</c:GrantSelectList>
	<c:StaticParameters>
		<c:ParameterList>
			<c:Param ID="ApplyParametersIDFilter">
				<c:Value>false</c:Value>
			</c:Param>
		</c:ParameterList>
	</c:StaticParameters>
</CLRDataList>
 
<Parameters>
	<c:FormMetaData>
		<c:FormFields>
			<c:FormField FieldID="FilterBusinessProcessInstanceID" Caption="Process type" DataType="Guid" Hidden="false" CaptionResourceKey="$$process_type">
				<c:SimpleDataList SimpleDataListID="E199683C-BFEF-40C0-99AB-B3B37986BE49" />
			</c:FormField>
 
			<c:FormField FieldID="StatusCode" Caption="Status" CaptionResourceKey="$$status">
				<c:ValueList>
					<c:Items>
						<c:Item>
							<c:Value>0</c:Value>
							<c:Label>Completed</c:Label>
						</c:Item>
						<c:Item>
							<c:Value>1</c:Value>
							<c:Label>Running</c:Label>
						</c:Item>
						<c:Item>
							<c:Value>2</c:Value>
							<c:Label>Did not finish</c:Label>
						</c:Item>
					</c:Items>
				</c:ValueList>
			</c:FormField>
			<c:FormField FieldID="RowsToReturn" Caption="Rows to return" Required="true" DataType="Integer" DefaultValueText="100" CaptionResourceKey="$$rows_to_return">
				<c:ValueList>
					<c:Items>
						<c:Item>
							<c:Value>50</c:Value>
						</c:Item>
						<c:Item>
							<c:Value>100</c:Value>
						</c:Item>
						<c:Item>
							<c:Value>250</c:Value>
						</c:Item>
						<c:Item>
							<c:Value>500</c:Value>
						</c:Item>
						<c:Item>
							<c:Value>1000</c:Value>
						</c:Item>
					</c:Items>
				</c:ValueList>
			</c:FormField>
			<c:FormField FieldID="StartedDateRangeType" Caption="Date started" DefaultValueText="1" DataType="Integer" CaptionResourceKey="$$date_started">
				<c:ValueList>
					<c:Items>
						<c:Item>
							<c:Value>1</c:Value>
							<c:Label>All Dates</c:Label>
						</c:Item>
						<c:Item>
							<c:Value>0</c:Value>
							<c:Label>Specific Date</c:Label>
						</c:Item>
						<c:Item>
							<c:Value>2</c:Value>
							<c:Label>Today</c:Label>
						</c:Item>
						<c:Item>
							<c:Value>5</c:Value>
							<c:Label>This Week</c:Label>
						</c:Item>
						<c:Item>
							<c:Value>6</c:Value>
							<c:Label>This Month</c:Label>
						</c:Item>
						<c:Item>
							<c:Value>43</c:Value>
							<c:Label>This Quarter</c:Label>
						</c:Item>
						<c:Item>
							<c:Value>7</c:Value>
							<c:Label>This Year</c:Label>
						</c:Item>
					</c:Items>
				</c:ValueList>
			</c:FormField>
			<c:FormField FieldID="StartedDateSpecificDate" Caption="Specific date" DataType="Date" CaptionResourceKey="$$specific_date" />
		</c:FormFields>
 
		<c:FormUIComponent FormUIComponentType="CustomComponent">
			<c:CustomComponentID AssemblyName="Blackbaud.AppFx.Platform.ClientComponents.dll" ClassName="Blackbaud.AppFx.Platform.ClientComponents.BusinessProcessStatusDatalistFilter" />
		</c:FormUIComponent>
	</c:FormMetaData>
</Parameters>
 
	<Output>
		<OutputFields>
			<OutputField FieldID="ID" Caption="ID" DataType="Guid" IsHidden="true" CaptionResourceKey="$$id" DisplaySequence="1" />
			<OutputField FieldID="Process" Caption="Process" DataType="String" DisplaySequence="2" CaptionResourceKey="$$process" />
			<OutputField FieldID="Status" Caption="Status" DataType="String" DisplaySequence="4" CaptionResourceKey="$$status" />
			<OutputField FieldID="Status message" Caption="Status message" DataType="String" DisplaySequence="5" CaptionResourceKey="$$status_message" />
			<OutputField FieldID="Started by" Caption="Started by" DataType="String" DisplaySequence="6" CaptionResourceKey="$$started_by" />
			<OutputField FieldID="Started" Caption="Started" DataType="Date" DisplayDateAsDateTime="true" DisplaySequence="7" CaptionResourceKey="$$started" />
			<OutputField FieldID="Ended" Caption="Ended" DataType="Date" DisplayDateAsDateTime="true" DisplaySequence="8" CaptionResourceKey="$$ended" />
			<OutputField FieldID="Duration" Caption="Duration" DataType="String" DisplaySequence="9" CaptionResourceKey="$$duration" />
			<OutputField FieldID="Number of records processed" Caption="Number of records processed" DataType="String" DisplaySequence="10" CaptionResourceKey="$$number_of_records_processed" />
			<OutputField FieldID="Number of exception records" Caption="Number of exception records" DataType="String" DisplaySequence="11" CaptionResourceKey="$$number_of_exception_records" />
			<OutputField FieldID="Total count" Caption="Total count" DataType="String" DisplaySequence="12" CaptionResourceKey="$$total_count" />
			<OutputField FieldID="Server name" Caption="Server name" DataType="String" DisplaySequence="13" CaptionResourceKey="$$server_name" />
			<OutputField FieldID="ImageKey" Caption="ImageKey" DataType="String" IsImageKey="true" IsHidden="true" CaptionResourceKey="$$imagekey" DisplaySequence="14" />
			<OutputField FieldID="Completed" Caption="Completed" DataType="String" IsHidden="true" CaptionResourceKey="$$completed" DisplaySequence="15" />
			<OutputField FieldID="EnableDownload" Caption="EnableDownload" DataType="String" IsHidden="true" CaptionResourceKey="$$enabledownload" DisplaySequence="16" />
			<OutputField FieldID="EnableLetterMailMerge" Caption="Enable Letter Mail Merge" DataType="String" IsHidden="true" CaptionResourceKey="$$enable_mail_merge" DisplaySequence="17" />
			<OutputField FieldID="EnableLabelMailMerge" Caption="Enable Label Mail Merge" DataType="String" IsHidden="true" CaptionResourceKey="$$enable_mail_merge" DisplaySequence="18" />
			<OutputField FieldID="EnableMultipleLetterMailMerge" Caption="Enable Multiple Letter Mail Merge" DataType="String" IsHidden="true" CaptionResourceKey="$$enable_mail_merge" DisplaySequence="19" />
			<OutputField FieldID="ParameterSetName" Caption="Name" DataType="String" DisplaySequence="3" CaptionResourceKey="$$name" />
		</OutputFields>
	</Output>
</DataListSpec>
CLR Business Logic Sample Code
Imports Blackbaud.AppFx.Server
Imports System.Data.SqlClient

Public NotInheritable Class BusinessProcessStatusDatalist
    Inherits AppCatalog.AppDataList

    Public FilterBusinessProcessInstanceID As Guid = Guid.Empty 'this is passed from the filter panel
    Public BusinessProcessCatalogID As String = String.Empty
    Public StatusCode As Nullable(Of Integer)
    Public ApplyParametersIDFilter As Boolean = False
    Public RowsToReturn As Nullable(Of Integer)
    Public StartedDateRangeType As Nullable(Of Integer)
    Public StartedDateSpecificDate As Nullable(Of Date)

    Public Enum ProcessStatusImage
        check = 0
        businessprocessspec
        warning
        x_16
    End Enum

    'Private Const STATUSTABLESUFFIX As String = "STATUS"

    Private Enum FieldsList
        ID
        ProcessName
        Status
        StatusMessage
        StartedBy
        StartedOn
        EndedOn
        Duration
        NumberOfRecordsProcessed
        NumberOfExceptionRecords
        TotalCount
        ServerName
        ImageKey
        Completed
        EnableDownload
        EnableLetterMailMerge
        EnableLabelMailMerge
        'EnableMultipleLetterMailMerge deprecated
        ParameterSetName
        BusinessProcessCatalogID
        ParameterSetID
    End Enum

    Public Overrides Function GetListResults() As AppFx.Server.AppCatalog.AppDataListResult

        If BusinessProcessCatalogID = "=ContextID" Then
            BusinessProcessCatalogID = ProcessContext.ContextRecordID
        Else
            If FilterBusinessProcessInstanceID <> Guid.Empty Then
                BusinessProcessCatalogID = FilterBusinessProcessInstanceID.ToString()
            End If
        End If

        Dim builder As New Text.StringBuilder
        With builder
            .Append("select ID, ")
            .Append("ProcessName, ")
            .Append("Status, ")
            .Append("StatusMessage, ")
            .Append("StartedBy, ")
            .Append("StartedOn, ")
            .Append("EndedOn, ")
            .Append("Duration, ")
            .Append("NumberOfRecordsProcessed, ")
            .Append("NumberOfExceptionRecords, ")
            .Append("TotalCount, ")
            .Append("ServerName, ")
            .Append("ImageKey, ")
            .Append("Completed, ")
            .Append("EnableDownload, ")
            .Append("EnableLetterMailMerge, ")
            .Append("EnableLabelMailMerge, ")
            '.Append("EnableMultipleLetterMailMerge, ") deprecated
            .Append("coalesce(ParameterSetName,'') ")
            .Append("from dbo.UFN_BUSINESSPROCESSSTATUS_STANDARDDATALISTVALUES_ROWSTORETURNSTARTEDDATE(@BUSINESSPROCESSCATALOGID, @PARAMETERSETID, @STATUSCODE, @ROWSTORETURN, @STARTEDDATERANGETYPE, @STARTEDDATESPECIFICDATE) ")
            .Append("order by StartedOn desc")
        End With

        Dim command As New SqlCommand(builder.ToString)
        If ApplyParametersIDFilter Then

            Dim ctxID As String = Me.ProcessContext.ContextRecordID
            If String.IsNullOrEmpty(ctxID) Then
                Throw New ServiceException("The ContextRecordID must be specified for this BusinessProcessStatusDataList", ServiceErrorCode.InvalidAPIUse)
            End If

            command.Parameters.AddWithValue("@PARAMETERSETID", New Guid(ctxID))
        Else
            command.Parameters.AddWithValue("@PARAMETERSETID", DBNull.Value)
        End If

        If BusinessProcessCatalogID <> String.Empty Then
            command.Parameters.AddWithValue("@BUSINESSPROCESSCATALOGID", New Guid(BusinessProcessCatalogID))
        Else
            command.Parameters.AddWithValue("@BUSINESSPROCESSCATALOGID", DBNull.Value)
        End If

        AddNullableIntegerParameter(command, "@STATUSCODE", StatusCode)
        AddNullableIntegerParameter(command, "@ROWSTORETURN", RowsToReturn)
        AddNullableIntegerParameter(command, "@STARTEDDATERANGETYPE", StartedDateRangeType)

        AddNullableDateParameter(command, "@STARTEDDATESPECIFICDATE", StartedDateSpecificDate)

        Dim reader As SqlDataReader
        Dim resultList As New Generic.List(Of DataListResultRow)

        Using conn As SqlClient.SqlConnection = Me.RequestContext.OpenAppDBConnection
            SpWrap.USP_BUSINESSPROCESSSTATUS_VALIDATESTATUS.ExecuteNonQuery(conn)

            command.Connection = conn
            reader = command.ExecuteReader(CommandBehavior.CloseConnection)

            Do While reader.Read
                Dim result As New DataListResultRow

                Dim valueList As New Generic.List(Of String)
                valueList.Add(reader.GetGuid(FieldsList.ID).ToString)
                valueList.Add(reader.GetString(FieldsList.ProcessName))
                valueList.Add(reader.GetString(FieldsList.Status))
                valueList.Add(reader.GetString(FieldsList.StatusMessage))
                valueList.Add(reader.GetString(FieldsList.StartedBy))
                valueList.Add(FormatFieldForList(reader.GetDateTime(FieldsList.StartedOn)))

                If reader.IsDBNull(FieldsList.EndedOn) Then
                    valueList.Add(String.Empty)
                Else
                    valueList.Add(FormatFieldForList(reader.GetDateTime(FieldsList.EndedOn)))
                End If

                valueList.Add(reader.GetString(FieldsList.Duration))
                valueList.Add(reader.GetInt32(FieldsList.NumberOfRecordsProcessed).ToString)
                valueList.Add(reader.GetInt32(FieldsList.NumberOfExceptionRecords).ToString)
                valueList.Add(reader.GetInt32(FieldsList.TotalCount).ToString)
                valueList.Add(reader.GetString(FieldsList.ServerName))
                valueList.Add(reader.GetString(FieldsList.ImageKey))
                valueList.Add(reader.GetBoolean(FieldsList.Completed).ToString)
                valueList.Add(reader.GetBoolean(FieldsList.EnableDownload).ToString)
                valueList.Add(reader.GetBoolean(FieldsList.EnableLetterMailMerge).ToString)
                valueList.Add(reader.GetBoolean(FieldsList.EnableLabelMailMerge).ToString)
                valueList.Add(Boolean.FalseString) ' deprecated -- left in so as not to break dependent specs
                valueList.Add(reader.GetString(FieldsList.ParameterSetName).ToString)
                valueList.Add(BusinessProcessCatalogID)

                If ApplyParametersIDFilter Then
                    valueList.Add(Me.ProcessContext.ContextRecordID)
                End If

                result.Values = valueList.ToArray

                resultList.Add(result)

            Loop
        End Using

        Return New AppCatalog.AppDataListResult(resultList)

    End Function

    Private Sub AddNullableIntegerParameter(ByVal command As SqlClient.SqlCommand, ByVal parameterName As String, ByVal integerValue As Nullable(Of Integer))
        If Not integerValue.HasValue Then
            integerValue = -1
        End If

        command.Parameters.AddWithValue(parameterName, integerValue)
    End Sub

    Private Sub AddNullableDateParameter(ByVal command As SqlClient.SqlCommand, ByVal parameterName As String, ByVal dateValue As Nullable(Of Date))
        If dateValue.HasValue Then
            command.Parameters.AddWithValue(parameterName, dateValue.Value)
        Else
            command.Parameters.AddWithValue(parameterName, DBNull.Value)
        End If
    End Sub
End Class