Exercise: Build the Processing Logic for the Food Bank Inventory Business Process

Before a business process starts, the parameter values are created and gathered into a parameter set THAT is saved to a custom parameter table. When the business process starts, the ID of the parameter set is passed to the processing logic. The processing logic for a business process occurs within a .NET CLR class that exists within an assembly that is deployed on the Infinity web server. This code runs within the web services layer. Within this CLR class file, the parameter set values are retrieved and the processing on the data begins in earnest. How the processing occurs is up to you and the output data format is completely open.

We will build a server side processing logic for a custom business process within this exercise. The logic will utilize four parameter values as defined by a configured parameter set.

Figure: A parameter set is defined and passed to a business process that creates an output such as a database table.

In this exercise, we will also utilize two QueryViewSpecs as choices for the Output Format parameter. We also create two ad-hoc queries to create two selections as options for the Selection parameter.

A QueryViewSpec can be used as a parameter to define the output format and help define the processing logic

A common practice is to use a QueryViewSpec to define the output format/columns. A reference to the QueryViewSpec/output format can be passed to the business process instance as a parameter within the parameter set. Different parameter sets can contain references to different QueryViewSpecs which in turn affects how the data is processed and which columns appear within the final output.

Step 1 - Review the business process logic.

Before building the code, read the sections on Business Process Overview, Business Process Parameters, and Building the Business Process Processing Logic.

Step 2 - Add the BusinessProcessSpec and associated Visual Basic class to the catalog project.

Un your Visual Studio catalog project, right-click the InventoryProcess folder and select Add\New Item… from the popup menu.
On the Add New Item dialog window, select the Blackbaud AppFx Catalog as the category and then select Business Process Spec as the item template.
Name the XML file "InventoryProcess.BusinessProcess.xml" and click the Add button.

Figure: Adding a new Business Process Spec
Two files are added to the project. One is a Visual Basic (.vb) file containing a class. The other is the spec file that contains the BusinessProcessSpec.

Figure: The Business Process Spec item template creates two files.

Step 3 - Inspect the ProcessorComponent XML element.

Open and inspect the BusinessProcessSpec. A BusinessProcessSpec is used to reference the .NETclass. The ProcessorComponenttag references the assembly name and a fully qualified class name that contains the processing logic. The fully qualified class name consists of the class name (InventoryProcessBusinessProcess) appended to the assembly name (Blackbaud.CustomFx.FoodBank.Catalog).

<BusinessProcessSpec
	xmlns="bb_appfx_businessprocess"
	xmlns:common="bb_appfx_commontypes"
	ID="069c24cc-1202-4f55-b701-f35e2f8b720c"
	Name="InventoryProcess Business Process"
	Description="REPLACE_WITH_DESCRIPTION"
	Author="SALESDEMO\TechTrainUser10"
	GeneratesOutput="false"
	ParameterTableName="REPLACE_WITH_PARAMETERTABLENAME"
	RecordType="REPLACE_WITH_RECORDTYPE"
	common:SecurityUIFolder="REPLACE_WITH_SECURITYUIFOLDER">
	
	<ProcessorComponent AssemblyName="Blackbaud.CustomFx.FoodBank.Catalog.dll" 
                      ClassName="Blackbaud.CustomFx.FoodBank.Catalog.InventoryProcessBusinessProcess" />
                                                                     
  <common:FormMetaData>
		<common:FormFields>
			<common:FormField Caption="REPLACE_WITH_CAPTION" 
                        DataType="REPLACE_WITH_DATATYPE" 
                        Description="REPLACE_WITH_DESCRIPTION" 
                        FieldID="REPLACE_WITH_FIELDID" />
		</common:FormFields>
	</common:FormMetaData>

</BusinessProcessSpec>

Figure: Sample BusinessProcessSpec

Your business process processing occurs within a .NET class that inherits from Blackbaud's AppCatalog.AppBusinessProcess class. Overriding the Validate() sub within this class enables you to retrieve the parameter set values for the instance. Overriding the StartFunctionBusinessProcess() function enables you to write code that processes the data and produce the output. StartFunctionBusinessProcess() will send back an object of type Blackbaud.AppFx.Server.AppCatalog.AppBusinessProcessResult which provides the Infinity platform with the number of records successfully process and the number of exceptions, if any.

For more information, see Business Process Spec and Business Process Logic.

Step 4 - Modify the BusinessProcessSpec XML element.

Modify the values for the following attributes within the BusinessProcessSpec XML element. Use the example below as a guide.

Name
Description
Author
GeneratesOutput
ParameterTableName
RecordType
SecurityUIFolder
ParameterLabelField

<BusinessProcessSpec
	xmlns="bb_appfx_businessprocess"
	xmlns:common="bb_appfx_commontypes"
	ID="a28d4f17-53fe-48d9-85bc-37e12884dc00"
	Name="Inventory Process Business Process"
	Description="Business process to get the inventory report for food banks."
	Author="Technical Training"
	GeneratesOutput="true"
	ParameterTableName="USR_INVENTORYPROCESS"
	RecordType="Inventory Process Parameter" 
	common:SecurityUIFolder="Constituent\Food Bank\Inventory

	ParameterLabelField="NAME">
   
	<ProcessorComponent AssemblyName="Blackbaud.CustomFx.FoodBank.Catalog" 
   ClassName="Blackbaud.CustomFx.FoodBank.Catalog.InventoryProcessBusinessProcess">

…

Note: When defining a parameter set table, a NAME column or some other column should be used to represent the label for the business process parameter set. This table column corresponds with the BusinessProcessSpec element's ParameterLabelField attribute which is used to identify the name of the field in the parameter table which represents the label for the parameter set. If the ParameterLabelField attribute for the BusinessProcessSpec element is not specified, NAME is used as the default. If the default is used and NAME does not exist on the parameter table, loadspec will throw an error. We recommend you explicitly identify the ParameterLabelField in your business process specs.

Step 5 - Add the USR_USP_INVENTORYPROCESS_GETPARAMETERS stored procedure.

When our business process logic starts, the parameters for the designated parameter set ID is retrieved from the custom business process parameter set table that we created in a previous exercise. In this step, we will add a stored procedure to accept a designated primary key value from the custom parameter set table (USR_INVENTORYPROCESS). This value represents a chosen parameter set that is utilized by the business processing logic. The stored procedure retrieves the IDSETREGISTERSETID, which represents a selection to limit the rows that require processing, and the OUTPUTVIEWID, which represents the output format or the columns created by the business process.

For more information on how the parameter set data is retrieved and utilized within the processing logic, see SQLStoredProcedureSpec to Retrieve the Parametes within the Processing Code and Business Process: StartBusinessProcess().

Let's add a new SQLStoredProcedureSpec to our food bank catalog project.

Right-click the InventoryProcess folder and select Add\New Item… from the context menu.
Select Blackbaud AppFx Catalog as the category on the Add New Item dialog window, and select SQL Stored Procedure Spec as the template item.
Name the XML spec file: USR_USP_INVENTORYPROCESS_GETPARAMETERS.xml.

The catalog item template SQL Stored Procedure Spec provides some default XML to help you define the stored procedure. Let's replace this XML with the XML below:

<SQLStoredProcedureSpec
	xmlns="bb_appfx_sqlstoredprocedure"
	xmlns:common="bb_appfx_commontypes" 
	ID="1d8dac58-3685-4216-b515-ac150d08def3"
	Name="USR_USP_INVENTORYPROCESS_GETPARAMETERS"
	Description="Defines a stored procedure that is used 
  within the business process .NET CLR code to retrieve the instance parameters 
  for a given id. See InventoryProcessBusinessProcess.vb"
	Author="Technical Training"
	SPName="USR_USP_INVENTORYPROCESS_GETPARAMETERS"> 
	
	<!--This is used in the business process to get the instance parameters.-->

	<CreateProcedureSQL>
		<![CDATA[
create procedure dbo.USR_USP_INVENTORYPROCESS_GETPARAMETERS(@ID uniqueidentifier)
as
begin
	select
		IP.IDSETREGISTERID,
		BPVIEW.ID as OUTPUTVIEWID
	from
		dbo.USR_INVENTORYPROCESS IP
	join
		dbo.BUSINESSPROCESSVIEW BPVIEW on IP.QUERYVIEWID = BPVIEW.QUERYVIEWCATALOGID
	where
		IP.ID = @ID
end
		] ]>
	</CreateProcedureSQL>
</SQLStoredProcedureSpec>

Save and load the SQLStoredProcedureSpec.

Step 6 - Modify the BusinessProcessSpec and grant permission to the database objects that the processing logic uses.

Let's go back and modify the BusinessProcessSpec to add some XML to grant permission to specific database object for our processing logic.

Modify the ProcessorComponent XML element such that it has a beginning and ending tag.

See the yellow highlight below.
Modify the BusinessProcessSpec to include the GrantExecuteList XML child element within the ProcessorComponent XML element.

In the green highlight below, notice the reference to the stored procedure we created in the previous step.
Modify the BusinessProcessSpec to include the GrantSelectList XML child element within the ProcessorComponent XML element.

UFN_IDSETREADER_GETRESULTS is a built-in table-valued user-defined function that retrieves a list of GUIDS for a given selection ID. Our processing logic leverages this function within a SQL JOIN clause to narrow the database table rows returned. Review the blue highlight below and add the highlighted select list to the Business Process Spec.

<BusinessProcessSpec
	xmlns="bb_appfx_businessprocess"
	xmlns:common="bb_appfx_commontypes"
	ID="a28d4f17-53fe-48d9-85bc-37e12884dc00"
	Name="Inventory Process Business Process"
	Description="Business process to get the inventory report for food banks."
	Author="Technical Training"
	GeneratesOutput="true"
	ParameterTableName="USR_INVENTORYPROCESS"
	RecordType="Inventory Process Parameter"
	common:SecurityUIFolder="Constituent\Food Bank\Inventory">

  <ProcessorComponent AssemblyName="Blackbaud.CustomFx.FoodBank.Catalog"
   ClassName="Blackbaud.CustomFx.FoodBank.Catalog.InventoryProcessBusinessProcess">

    <!-- Grant execution rights to the stored procedure to get parameters 
    and the UFN used to filter the selection results.
		UFN_IDSETREADER_GETRESULTS is a built-in table-value function that 
    returns a list of GUIDs based on a selection ID.-->
    <common:GrantExecuteList>
<common:GrantExecute>USR_USP_INVENTORYPROCESS_GETPARAMETERS</common:GrantExecute>
    </common:GrantExecuteList> 
    <common:GrantSelectList>
      <common:GrantSelect>USR_FOODBANK</common:GrantSelect>
		 <common:GrantSelect>UFN_IDSETREADER_GETRESULTS</common:GrantSelect>
      <common:GrantSelect>USR_UFN_FOODBANKSELECTION</common:GrantSelect>
    </common:GrantSelectList>
  </ProcessorComponent>

Step 7 - Remove the FormMetaData.

Remove the default FormMetaData element and child XML elements from the BusinessProcessSpec.

This element defines a pre-process Edit Data Form to use when the business process does not have parameters saved to a database table. This is a mechanism to display a form fields in the user interface after the business process starts but before processing shifts to the business processor code on the web server. We will not need the FormMetaData XML element, so we remove this tag. For more information about this element, see FormMetaData and Managing Parameter Sets and Starting a Business Process with a Pre-Process Edit Data Form.

Step 8 - Add the BusinessProcessOutputHeader XML element.

The BusinessProcessOutputHeader XML element describes how to designate a view to generate an output header file for a business process.

Add the following BusinessProcessOutputHeader XML element to the spec. See below.
Note the value of the OutputViewMetaTag attribute.

Do you see how the value is equal to "Inventory"?

Step 9 - Save and load the BusinessProcessSpec.

Loading the spec adds a row to the Business Process Spec catalog table.

Step 10 - Filter the Tables for our new parameter set table.

Open SQL Server Management Studio, navigate to the appropriate database, and filter the Tables folder to the name of our parameter set table: USR_INVENTORYPROCESS.
Note the tables that were created.

Besides the audit tables, you will notice the USR_INVENTORYPROCESS, USR_INVENTORYPROCESSEXPORTFORMAT, and USR_INVENTORYPROCESSSTATUS tables. USR_INVENTORYPROCESSEXPORTFORMAT is the custom export format table. The BUSINESSPROCESSEXPORTFORMAT table defines the format to display and export the data from a business process's output. The BusinessProcessSpec's GeneratesOuput attribute indicates whether the process generates output via this database table which can be exported later.

USR_INVENTORYPROCESSSTATUS is the custom status table. This table serves as a join table to the BUSINESSPROCESSSTATUS table for the business process parameter table (USR_INVENTORYPROCESS).

Step 11 - Review the ERD for the business process.

Take a few minutes to review the entity relationship diagram for the business process.

Step 12 - Add two QueryViewSpecs for the Output Format parameter.

A best practice is to use a QueryViewSpec to define the output format of a business process. Typically, before starting an instance, an end user may select a reference to a specific QueryViewSpec as one of the input parameters into a process. Different parameter sets can contain references to different QueryViewSpecs, which in turn affect how the data is processed and which columns appear within the final output. In short, a QueryViewSpec can be used to drive the columns of data created by a business process.

Note: The best practice to define the output columns is to utilize a reference to an Output Format QueryViewSpec as a parameter value into the business process instance. The QueryViewSpec defines a database view.

For more information, see Business Process Parameters and Using a QueryViewSpec as a Parameter for Output Formatting.

Open the InventoryProcess folder and ensure the existence of two Query XML files.

If these files do not exist, add them from the food bank source code. After adding the files, be sure to mark them as embedded resources by right-clicking each file, viewing the properties, and modifying the Build Action property value from "Content" to "Embedded Resource." These two QueryViewSpecs have a dependency on the custom Business Process Spec InventoryProcessCurrentInventoryOutput.Query.xml.
- InventoryProcessLowInventoryOutput.Query.xml
Open each QueryViewSpec and investigate its contents.

Notice the following XML elements and attributes within the specs:
- PrimaryKeyField attribute
- MetaTags element
- ViewImplementation element and its contents
- OutputFields element and child elements
- BusinessProcessOutput element
For more information on how a QueryViewSpec can be used to participate in the business process processing logic, see Using a QueryViewspec as a Parameter for Output Formatting and Typical Duties for your Sub Class.
Review the MetaTags element in both QueryViewSpecs.

Look closely at the value within the MetaTags XML element in both specs. Compare this value against the OutputViewMetaTag attribute within the BusinessProcessOutputHeader element near the bottom of the BusinessProcessSpec.

Did you notice the value for the BusinessProcessID attribute within the BusinessProcessOuput XML element? It has a dependency on the ID for the BusinessProcessSpec. Therefore, we can deduce that the BusinessProcessSpec needs to be loaded prior to loading these QueryViewSpecs.
Use LoadSpec to load each QueryViewSpec.

Step 13 - Add the code for the business process logic to the VB class file.

Copy the code below and replace the existing code within into the InventoryProcess.BusinessProcess.vb class.

Imports System.Text
Imports System.Data.SqlClient
Imports Blackbaud.AppFx.Server

Public NotInheritable Class InventoryProcessBusinessProcess
    Inherits AppCatalog.AppBusinessProcess
    Private _parameters As InventoryParameters = Nothing

    'Validate gets called first and if all goes well, it gets our parameters too.
    Public Overrides Sub Validate()
        MyBase.Validate()
        ' Get our business process parameters
        _parameters = New InventoryParameters(RequestArgs.ParameterSetID, Me.RequestContext)
        If _parameters Is Nothing Then
            Throw New Exception("No parameters found with the given parameter")
        End If


    End Sub

    ' This is a class used for the inventory process instance parameters.
    Private Class InventoryParameters
        Public ReadOnly OUTPUTVIEWID As Guid = Guid.Empty
        Public ReadOnly IDSETID As Guid = Guid.Empty

        Public Sub New(ByVal parameterSetID As Guid, ByRef requestContext As RequestContext)
            Using con As SqlConnection = New SqlConnection(requestContext.AppDBConnectionString)
                Using command As SqlCommand = con.CreateCommand()
                    Try
                        command.CommandText = "USR_USP_INVENTORYPROCESS_GETPARAMETERS"

                        command.CommandType = CommandType.StoredProcedure
                        command.Parameters.AddWithValue("@ID", parameterSetID)
                        con.Open()
                        Using reader As SqlDataReader = command.ExecuteReader()
                            reader.Read()
                            Me.IDSETID = reader.GetGuid(reader.GetOrdinal("IDSETREGISTERID"))
                            Me.OUTPUTVIEWID = reader.GetGuid(reader.GetOrdinal("OUTPUTVIEWID"))
                            reader.Close()
                        End Using
                        con.Close()
                    Catch
                        Throw New Exception("Unable to get parameter set found for the given Id")
                    End Try
                End Using
            End Using
        End Sub
    End Class

    ' The core of the buisness process
    Public Overrides Function StartBusinessProcess() As Blackbaud.AppFx.Server.AppCatalog.AppBusinessProcessResult
        ' Our return object 
        Dim result As New AppCatalog.AppBusinessProcessResult()

        ' Declare some constants for the output table 
        Const OUTPUT_TABLE_PREFIX As String = "USR_INVENTORYPROCESS"
        Const OUTPUT_TABLE_KEY As String = "OUTPUT"

        ' Declare local variables 
        Dim outputTableName As String = String.Empty
        Dim exportView As String = String.Empty
        Dim viewColumns() As AppCatalog.TableColumn = Nothing
        Dim inventoryIDField As String = String.Empty

        ' Get the export view's name and the field to join on 
        With Me.OutputView(_parameters.OUTPUTVIEWID)
            exportView = .ExportViewName
            inventoryIDField = .JoinField
        End With

        ' This will create the  output table called 
        'USR_INVENTORYPROCESS_<guid> and get the viewColumns
        outputTableName = Me.CreateOutputTableFromView(exportView, OUTPUT_TABLE_PREFIX, OUTPUT_TABLE_KEY, viewColumns)

        ' Now we need to populate the output table
        Dim sql As New StringBuilder()

        With sql
            .AppendFormat("insert into dbo.[{0}]", outputTableName)
            .AppendFormat("({0})", Me.CreateFieldsList(viewColumns))
            ' Now the select part of the query
            .AppendLine("select ")

            ' OUTPUTVIEW here is the table name field prefix i.e. TABLE.FIELD
            .AppendFormat("{0} ", Me.CreateFieldsList(viewColumns, "OUTPUTVIEW"))
            .AppendLine("from dbo.USR_FOODBANK as FB")
            .AppendFormat("join dbo.[{0}] as OUTPUTVIEW on FB.[ID] = OUTPUTVIEW.[{1}]", exportView, inventoryIDField)
            ' Now we add the join clause to filter based on our selection parameter
            ' Note: I used a standard product UFN to read the GUIDs from a IDSET given an ID
            .AppendFormat("join dbo.[UFN_IDSETREADER_GETRESULTS]('{0}') IDSET on OUTPUTVIEW.ID = IDSET.ID", _parameters.IDSETID.ToString())
        End With
     
        ' Execute the command to populate the table
        Using con As SqlConnection = New SqlConnection(Me.RequestContext.AppDBConnectionString)
            con.Open()
            Using command As SqlCommand = con.CreateCommand()
                With command
                    .CommandText = sql.ToString()
                    .CommandTimeout = Me.ProcessCommandTimeout
                    .ExecuteNonQuery()
                End With
            End Using
            ' Now get the record count for the BP status
            Using command As SqlCommand = con.CreateCommand()
                With command
                    .CommandText = String.Format("select count(*) from dbo.[{0}]", outputTableName)
                    .CommandTimeout = Me.ProcessCommandTimeout
                    result.NumberSuccessfullyProcessed = CType(.ExecuteScalar(), Integer)
                End With
            End Using
            con.Close()
        End Using
        Return result
    End Function
End Class

Tip:

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

Step 14 - Build and deploy the Blackbaud.CustomFx.FoodBank.Catalog assembly.

With Infinity development, a catalog is an assembly (.dll file) that contains embedded Catalog Items (specs) as resources. The catalog assembly also holds our VB file that contains our business process class.

Very Important: The VB class file's Build Action property should be marked as "Compile" while each XML spec file's Build Action property file should be marked as "Embedded Resource."
Copy the assembly (.dll file) to the Infinity application's ..\bbappfx\vroot\bin\custom folder.