Select your user interface:

Exercise 14: Validate a Simple Batch

You can validate a batch from two areas within the application. To validate within the batch user interface screen, select Edit\Validate menu option. The validation runs on each row in the batch.

You can validate a batch from two areas within the application. To validate within the batch user interface screen, click Validate under Processes. The validation runs on each row in the batch.

Figure: Validate batch menu option

Step 1 -  Modify the food items within the batch and validate at commit.

In the example below, I have modified one of the food item names so it is unique and have left the "Coffee" and "Orange Juice" rows the same as to invoke errors when the batch is validated.

Step 2 -  Review the Commit Add data form.

Looking at the Commit Add data form below, review the commit logic which is called by the Infinity platform for each row in the batch.

<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.BatchTraining.Catalog.dll" CatalogItem="Blackbaud.CustomFx.BatchTraining.Catalog.CopyOfFoodItemBatch.Add.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

	exec dbo.USR_USP_DATAFORMTEMPLATE_ADD_FOOD_ITEMBATCH @ID = @ID output, @CHANGEAGENTID = @CHANGEAGENTID, @NAME = @NAME, @DESCRIPTION = @DESCRIPTION, @CURRENTCOST = @CURRENTCOST, @LOWINVENTORYTHRESHOLD = @LOWINVENTORYTHRESHOLD, @WEIGHT = @WEIGHT;

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>

At the heart of the commit spec is the USR_USP_DATAFORMTEMPLATE_ADD_BATCHFOODITEMADDBATCHCOMMIT stored procedure that wraps a call to USR_USP_DATAFORMTEMPLATE_ADD_FOOD_ITEMBATCH which attempts to insert a row into the USR_FOODITEM table. 

exec dbo.USR_USP_DATAFORMTEMPLATE_ADD_FOOD_ITEMBATCH @ID = @ID output, @CHANGEAGENTID = @CHANGEAGENTID, @NAME = @NAME, @DESCRIPTION = @DESCRIPTION, @CURRENTCOST = @CURRENTCOST, @LOWINVENTORYTHRESHOLD = @LOWINVENTORYTHRESHOLD, @WEIGHT = @WEIGHT;

@ValidateOnly Flag

Look closely at the commit spec, do you notice anything interesting with the form fields and the parameters for the wrapping USR_USP_DATAFORMTEMPLATE_ADD_BATCHFOODITEMADDBATCHCOMMIT stored procedure? Did you notice the FormField element with a FieldID attribute equal to "VALIDATEONLY" and the corresponding parameter? What is this purpose of VALIDATEONLY? The Infinity platform passes a value of 1 to the @VALIDATEONLY parameter whenever the user validates the batch. The purpose of the @VALIDATEONLY flag is to provide a way for the database developer to determine if the user opts to validate the batch. If the value is 1, then you can write code within your save implementation that respects the @VALIDATEONLY flag. How you respect (or not respect) the @VALIDATEONLY flag is up to you. For example, if the flag is true then you could only perform checks that inspect incoming data against the production data via Transact-SQL SELECT statements and completely bypass the commit logic. Or, you can provide additional logic within your stored procedure in addition to the commit logic which would include the native database constraint checks that naturally occur when a row is saved to the table.

Keep in mind that if you run validation and choose not to bypass the commit logic, then the commit data logic is executed within a transaction that is rolled back. Until the transaction is rolled back, the logic has a hold of database resources referenced by the logic's Transact-SQL, such as database tables. Whenever you deal with a large number of rows in the batch or your commit logic is complicated and involves many tables, then you may run across resource contention issues. Resource contention issues arise when one or more queries consume excessive resources on the SQL Server, resulting in several different (sometimes unrelated) queries apparently blocking each other. The net effect can be a system that seems slow, certain operations time out, etc. You get the picture. The problem of resource contention is magnified the more rows you attempt to validate and commit within your batches and the more complicated your commit and validation logic.

Step 3 -  Add @VALIDATEONLY logic.

Figure: Add the validation logic

Step 4 -  Test the validation logic.