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.