Exercise 5: Commit Spec a.k.a. "Batch Commit Add"

This AddDataFormTemplateSpec contains all of the logic that runs when the batch is committed. The spec inserts the information from the batch staging table into its permanent location – the USR_FOODITEM table. In addition to the ability of adding rows, your batch type could also include an Edit Commit Spec that updates table rows with the information specified in the batch table. The Food Item Add Batch that we are describing here does not allow the updating of records and therefore an Edit Commit Spec is not needed. For our Food Item Add Batch, the stored procedure below will be executed for each row within the batch. At its heart the spec wraps a call to the USR_USP_DATAFORMTEMPLATE_ADD_FOOD_ITEMBATCH stored procedure. The technique of wrapping the stored procedure is not mandatory but does help set the stage for potential future maintenance of the logic.

<AddDataFormTemplateSpec 
    xmlns:common="bb_appfx_commontypes"
    ID="96d20428-a73d-44a7-ac74-44a75bfada37"
    Name="Food Item Add Batch Row Commit Add Form"
    Description="Adds a food item record to the system from a given batch."
    Author="Technical Training"
    RecordType="Food Item"
    DataFormInstanceID="4b79233b-cbb3-4ab8-a78f-01f25a11b322"
    common:SecurityUIFolder="Constituent\Food Bank\Food Item\Batch"
    SecurityUIDisplayFeature="false"
    xmlns="bb_appfx_adddataformtemplate"
    >
	<common:DependencyList>
		<common:Dependency CatalogAssembly="Blackbaud.CustomFx.FoodBank.Catalog.dll" 
                       CatalogItem="Blackbaud.CustomFx.FoodBank.Catalog.USR_USP_DATAFORMTEMPLATE_ADD_FOOD_ITEMBATCH.xml" />
	</common:DependencyList>
	<SPDataForm>
		<SaveImplementation SPName="USR_USP_DATAFORMTEMPLATE_ADD_BATCHFOODITEMADDBATCHCOMMIT">
			<common:CreateProcedureSQL>
        <![CDATA[
CREATE procedure dbo.USR_USP_DATAFORMTEMPLATE_ADD_BATCHFOODITEMADDBATCHCOMMIT
(	@ID uniqueidentifier = null output,
	@VALIDATEONLY bit = 0,
	@CHANGEAGENTID uniqueidentifier,
	@NAME nvarchar(100) = '',
	@DESCRIPTION nvarchar(max) = '',
	@CURRENTCOST money = 0.00,
	@LOWINVENTORYTHRESHOLD smallint = 0,
	@WEIGHT decimal(10,2) = 0.00)
as
set nocount on;
declare @CURRENTDATE datetime;
if @ID is null
	set @ID = newid();
if @CHANGEAGENTID is null
	exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();

begin try
	DECLARE @ERRORMSG nvarchar(100)
	
  IF @VALIDATEONLY = 1 
  BEGIN
	  IF (@NAME = @DESCRIPTION) or (LEN(@DESCRIPTION) = 0)
	  BEGIN
	    SET @ERRORMSG = 'The description is not descriptive and @VALIDATEONLY = ' + 
		  CONVERT(nvarchar(1),@VALIDATEONLY)
		  RAISERROR (@ERRORMSG, 13, 1)
	  END
	  IF EXISTS(Select NAME FROM USR_FOODITEM WHERE NAME = @NAME)
	  BEGIN
	   SET @ERRORMSG = 'The Name "' + @NAME + '" is currently being used' +
	    ' in the food item table and @VALIDATEONLY = ' + 
		  CONVERT(nvarchar(1),@VALIDATEONLY)
		  RAISERROR (@ERRORMSG, 13, 1)
	  END
  END
  ELSE
  BEGIN
	  exec dbo.USR_USP_DATAFORMTEMPLATE_ADD_FOOD_ITEMBATCH @ID = @ID output, @CHANGEAGENTID = @CHANGEAGENTID, @NAME = @NAME, @DESCRIPTION = @DESCRIPTION, @CURRENTCOST = @CURRENTCOST, @LOWINVENTORYTHRESHOLD = @LOWINVENTORYTHRESHOLD, @WEIGHT = @WEIGHT;
  END
end try

begin catch
	exec.dbo.USP_RAISE_ERROR;
	return 1;
end catch
return 0;


]]></common:CreateProcedureSQL>
<common:ExpectedDBExceptions>
	<common:Constraints>
		<common:Constraint Name="UC_USR_FOODITEM_NAME" Field="NAME" Type="Unique" />
		<common:Constraint Name="CK_USR_FOODITEM_NAME" Field="NAME" Type="Required" />
	</common:Constraints>
</common:ExpectedDBExceptions>
	</SaveImplementation>
</SPDataForm>
  
<common:FormMetaData>
	<common:FormFields>
	<common:FormField FieldID="VALIDATEONLY" DataType="Boolean" Caption="Validate only" />
	<common:FormField FieldID="NAME" Required="true" MaxLength="100" Caption="Name" />
      	<common:FormField FieldID="DESCRIPTION" Caption="Description" DefaultValueText="" />
	<common:FormField FieldID="CURRENTCOST" Required="true" DataType="Money" Caption="Current Cost" DefaultValueText="0.00" />
	<common:FormField FieldID="LOWINVENTORYTHRESHOLD" DataType="SmallInt" Caption="Low Inventory Threshold" DefaultValueText="0" />
	<common:FormField FieldID="WEIGHT" DataType="Decimal" Caption="Weight" Description="The weight of a food item." Precision="10" Scale="2" DefaultValueText="0.00" />
	</common:FormFields>
	</common:FormMetaData>
</AddDataFormTemplateSpec>
  

Below is the Transact-SQL StoredProcedureSpec that creates the stored procedure that is wrapped by within the Commit Spec above. This spec does the actual work of committing a single row from the staging area into the production table.

<SQLStoredProcedureSpec
	xmlns="bb_appfx_sqlstoredprocedure"
	xmlns:common="bb_appfx_commontypes" 
	ID="352cb180-aac7-481c-919c-3c6fe24f38ff"
	Name="USR_USP_DATAFORMTEMPLATE_ADD_FOOD_ITEMBATCH"
	Description="Wrapped by Commit Add Spec for Food Item Add Batch"
	Author="Technical Training"
	SPName="USR_USP_DATAFORMTEMPLATE_ADD_FOOD_ITEMBATCH"
	>

	<CreateProcedureSQL>
		<![CDATA[
CREATE procedure [dbo].[USR_USP_DATAFORMTEMPLATE_ADD_FOOD_ITEMBATCH]
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @NAME nvarchar(100),
    @DESCRIPTION nvarchar(max) = '',
    @CURRENTCOST money = 0.00,
    @LOWINVENTORYTHRESHOLD smallint = 0, 
	@WEIGHT Decimal(10,2) = 0.00
)
as

set nocount on;

if @ID is null
    set @ID = newid()

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

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

begin try
	-- handle inserting the data
	insert into dbo.USR_FOODITEM
		(ID, NAME, DESCRIPTION, CURRENTCOST, LOWINVENTORYTHRESHOLD, WEIGHT, 
		ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    values
		(@ID, @NAME, @DESCRIPTION, @CURRENTCOST, @LOWINVENTORYTHRESHOLD, @WEIGHT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0]]>
	</CreateProcedureSQL>
</SQLStoredProcedureSpec>

Step 1 -  Review and load the SQLStoredProcedureSpec.

Step 2 -  Review and load the Commit Spec.

Review and load the Commit Spec that wraps the stored procedure created with the SQLStoredProcedureSpec.