Edit a Row in Our Batch Extension Table

To edit a row, we must complete the EditRow element within our BatchTypeExtensionSpec. At the heart of this element are the LoadImplemenation and SaveImplementation elements that contain our stored procedures.

The LoadImplemenation is responsible for returning an XML document that contains the rows for our phone collection for a row within the batch. Notice the use of the TOITEMLISTXML user-defined function that was created for us when we loaded the SQLFunctionSpec that contains the CreateChildCollectionFunction element.

<EditRow DataFormInstanceID="6e44d1a1-a9c4-4871-bc34-9ee74d806777" DataFormTemplateID="888f2701-bf08-4574-991d-02ba1087e85b">
    <LoadImplementation SPName="USR_USP_DATAFORMTEMPLATE_EDITLOAD_USR_BATCHCONSTITUENTPHONE">
	<common:CreateProcedureSQL>
<![CDATA[   
create procedure dbo.USR_USP_DATAFORMTEMPLATE_EDITLOAD_USR_BATCHCONSTITUENTPHONE
(
	@ID uniqueidentifier,
  	@DATALOADED bit = 0 output,						
  	@EXTPHONES xml = null output,
	@TSLONG bigint = 0 output
)
as
	set nocount on;

	set @DATALOADED = 0;
	set @TSLONG = 0;

	-- populate the output parameters, which correspond to fields on the form.  Note that
	-- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
	-- will display a "no data loaded" message.  Also note that we fetch the TSLONG so that concurrency
	-- can be considered.
  	select
		@DATALOADED = 1
    , @EXTPHONES = dbo.USR_UFN_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_TOITEMLISTXML(@ID)
    --, @TSLONG = TSLONG
		   
return 0;
] ]>
	</common:CreateProcedureSQL>
    </LoadImplementation>
...

The SaveImplementation stored procedure accepts in the ID from our parent batch staging table, the XML containing our collection of phone data, and a change agent ID. Ultimately, the stored procedure will take the XML and update the data into the batch extension staging table. Fortunately, when we loaded the SQLFunctionSpec, the UPDATEFROMXML stored procedure was created for us named USR_USP_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_UPDATEFROMXML. We can wrap a call to this stored procedure within our SaveImplementation.

 <SaveImplementation SPName="USR_USP_DATAFORMTEMPLATE_EDITSAVE_USR_BATCHCONSTITUENTPHONE">
   <common:CreateProcedureSQL>
<![CDATA[
create procedure dbo.USR_USP_DATAFORMTEMPLATE_EDITSAVE_USR_BATCHCONSTITUENTPHONE
(	
  @ID uniqueidentifier,
	@CHANGEAGENTID uniqueidentifier = null,
  @EXTPHONES xml
)
as								 
	set nocount on;
  
	begin try
	  if @CHANGEAGENTID is null  
		  EXEC dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
				
	  DECLARE @CURRENTDATE datetime
	  set @CURRENTDATE = getdate()  
	
    DECLARE @INVALIDPHONESTARTDATE bit;
		
		if exists(
		  select top 1 ID 
		  from dbo.USR_UFN_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_FROMITEMLISTXML(@EXTPHONES)
		  where STARTDATE is not null and STARTDATE > dbo.UFN_DATE_GETLATESTTIME(getdate()))
			set @INVALIDPHONESTARTDATE = 1;
		
		if @INVALIDPHONESTARTDATE = 1
			RAISERROR('BBERR_CONSTITUENTBATCH_INVALIDPHONESTARTDATE',13,1);
  
	  --save parameters with correspond to fields on the form.
		exec dbo.USR_USP_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_UPDATEFROMXML @ID, @EXTPHONES, @CHANGEAGENTID;
    
	End try
	begin catch
		exec dbo.USP_RAISE_ERROR;
		return 1;
	end catch									 
] ]>
   </common:CreateProcedureSQL>
</SaveImplementation>