Start the Business Process: StartBusinessProcess()

Prior to starting the business process, we should have the parameters retrieved and validated.  See Retrieve and Validate the Parameters: Validate() for details. 

Generally speaking the most typical use of a business process is to create some type of output that contains database records.  The format of the output depends on your requirements.  A common practice, although not required, is to product a database table containing your processing results.  No matter what format you choose for your business process output, there is a good chance you will need to define the columns for the output. 

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 is used to define a database view.  See Using a QueryViewSpec as a Parameter for Output Formatting for more information.

Grab the Output Format View Name and Join Field

So, our first major step within the StartBusinessProcess function is to take the ID of Output Format QueryViewSpec and retrieve the name of the view and its join field.  A helper function within the base class named OutputView is used to retrieve both the name of the view and the join field.  

See AppBusinessProcess base class' OutputView property for more details on retrieving the query view name and join field.

Create the Output Table Using the “Output Format” View Name

Often the result of a business process is the creation of a database table which contains the resulting data of your process.  CreateOutputTableFromView() creates an output table in the business processor database based on a view in the application database. The function will return the name of the created database table and a ByRef array of TableColumns. 

See Creating an Output Table via the CreateOutputTableFromView() function for more details. 

Build the SQL to Populate the Output Table: Build a Field List

Using the table name and the ByRef array of TableColumns returned from the base class’ CreateOutputTableFromView() function, begin the process of populating the output table by building an INSERT Transact-SQL statement.  Pass the table columns into the base class’ CreateFieldsList() function which will return a comma delimited list of columns.

See AppBusinessProcess base class’ CreateFieldsList Function and Creating an Output Table via the CreateOutputTableFromView() function for details. 

Build the SQL to Populate the Output Table: Join the “Output Format” and “Selection” Row Sets

After the field list has been built, the next step is to build the JOIN clauses that pulls in the records from Output Formatview parameter utilizing the join field and also restricts the row set using the Selection parameter.  The table valued UFN_IDSETREADER_GETRESULTS user defined function is leveraged to build a table of GUIDs represented by the Selection parameter (IDSETID).

Execute the SQL Command to Populate the Output Table and Return a Result

After the Transact-SQL has been dynamically built, we open a SQL connection to the database where we created the output table.  We execute the command, which is an INSERT Transact-SQL statement.  This populates the output table.  Finally, the StartBusinessProcess() function should return an object of type Blackbaud.AppFx.Server.AppCatalog.AppBusinessProcessResult which includes the number of records processed and the number of exceptions, if any. 

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