Exercise: Build a Food Item Add Data Form Spec

We will now try a little hands-on exercise by creating an AddDataFormTemplateSpec to add records to our USR_FOODITEM table. This table was already loaded into your Infinity database by the FoodItem.Table.XML TableSpec created in Lab: Build the Food Bank Tables.

Instructions

Step 1 -  Open your Blackbaud.CustomFx.FoodBank.Catalog project. You can create the project with Lab: Build the Food Bank Tables.

Here is a link to the Food Bank Source Code.

Within your Microsoft Visual Studio project, open the existing project or solution by selecting File\Open\Project/Solution… in the main menu. The Open Project dialog box appears. Navigate to and select the solution file Blackbaud.CustomFx.FoodBank.Catalog.sln.

Step 2 -  Add the new Add Data Form Spec item into the Blackbaud.CustomFx.FoodBank.Catalog project.

Right-click the project in the Solution Explorer, and select Add\New Item… from the popup menu. Select Blackbaud AppFx Catalog as the category on the left side of the Add New Item dialog window, and select Add Data Form Template Spec (SP) as the template.

Enter FoodItem.Add.XML as the XML file name that will ultimately contain our new Add Data Form for food items.

Step 3 -  Enter the appropriate attributes for the AddDataFormTemplateSpec element.

• Name: Food Item Add Form

• Description: Used for adding a new Food Item

• Author: Technical Training

• RecordType: Food Item

• SecurityUIFolder: Constituent\Food Bank\Food Item

Step 4 -  Remove the load implementation.

After you add the spec to the project, you will notice both a LoadImplementation element and a SaveImplementation element. The load implementation is optional. It grabs default values from the database and populates the form fields in the user interface. We don't need the load implementation, so remove everything from the beginning <LoadImplementation SPName="USP_DATAFORMTEMPLATE_ADD_xxx_PRELOAD"> element to the ending </LoadImplementation> element.

Step 5 -  Create the save implementation.

The save implementation consists of a stored procedure that inserts a row into our food item table. At the heart of the stored procedure is a Transact-SQL INSERT INTO statement. When you created the spec, the SDK was kind enough to provide the beginnings of the stored procedure. You can modify this stored procedure in SQL Server Management Studio, or you can simply modify it within the spec.

Remember that the stored procedure is responsible for the following:

• Create a new ID for the row we insert (using the "newid()" function) and assign it to the output ID parameter.

• Assign the change agent ID parameter (or create one if it's NULL) to the "added by" and "changed by" columns.

• Assign the current date (using the "getdate()" function) to the "date added" and "date changed" columns.

When you create an Add Data Form Spec, the SDK provides an outline of the stored procedure along with some code in the stored procedure to set the values for the @ID, @CHANGEAGENTID, and @CURRENTDATE parameters.

You must modify the stored procedure's parameters and fields within the Transact-SQL INSERT statement to account for the following fields in the USR_FOODITEM table:

• Name

• Description

• CurrentCost

• LowInventoryThreshold

• Weight

When you are done, your stored procedure within the spec should look like this:

create procedure dbo.USR_USP_DATAFORMTEMPLATE_ADD_FOOD_ITEM
(
    @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

Example: The completed stored procedure to add a food item

Step 6 -  Create the form fields.

Next we have to describe the fields that appear on our Add Data Form. The names of our fields will correspond to the parameters in our stored procedure. The parameters such as @ID, @CONTEXTID, and @CURREENTAPPUSERID do not need to be listed as form fields.

When you are done, the form fields should look like this:

<c:FormMetaData FixedDialog="true">
        <c:FormFields>
<c:FormField FieldID="NAME" Required="true" MaxLength="100" Caption="Name"/>
            <c:FormField FieldID="DESCRIPTION" Caption="Description" />
<c:FormField FieldID="CURRENTCOST" DataType="Money" Caption="Current Cost" />
<c:FormField FieldID="LOWINVENTORYTHRESHOLD" DataType="SmallInt" Caption="Low Inventory Threshold" />
<c:FormField FieldID="WEIGHT" DataType="Decimal" Caption ="Weight" DefaultValueText="0.00" Description="The weight of a food item."/>
        </c:FormFields>
    </c:FormMetaData>

Step 7 -   Comment out the Context and FormUIComponent elements.

When you add a new data form to your project, you will notice two elements that are stubbed out for the developer: Context and FormUIComponent. XML comments have the exact same syntax as HTML comments. Below is an example of a notation comment that should be used when you need to leave a note to yourself or to someone who may view your XML. We will not go into the details around context at this time but, for now comment out the Context element as an XML comment.

The FormUIComponent element is used to reference a custom WinForm user interface instead of the Infinity-generated user interface. This WinForm UI only works in the ClickOnce shell; it does not work in the Web Shell. Custom user interfaces give the software developer a great deal of freedom on how to display the form and provides a mechanism to add code such as event handlers to the UI. If used by your data form, the spec calls upon a user interface component class file that resides within a .net client assembly. With the advent of the Web Shell, custom user interfaces are built using a UI Model for the UI code and HTML for the custom layout.

<!-- describe the context ID for this form (if applicable), which corresponds to a parameter in the SP. -->
 
<!-- <Context ContextRecordType="REPLACE_WITH_RECORDTYPE" RecordIDParameter="CONTEXTID"/>-->

Step 8 -  Review the spec and load it with LoadSpec.

At this point, your spec should look something like this. The GUIDs for the ID and DataFormInstanceID attributes are different because new GUIDs are generated by the SDK when you add the spec to the project. After you complete the spec, try to load it with LoadSpec.

<AddDataFormTemplateSpec 
    XMLns:c="bb_appfx_commontypes"
    ID="ee2cb812-cc6b-41c3-8165-59585f04a2ba"
    Name="Food Item Add Form"
    Description="Used for adding a new Food Item"
    Author="Technical Training"
    RecordType="Food Item"
    DataFormInstanceID="b911e219-42f2-4b64-9f02-d9e2ff899799"
    c:SecurityUIFolder="Constituent\Food Bank\Food Item"
    XMLns="bb_appfx_adddataformtemplate"
    >
    <SPDataForm>
        <SaveImplementation SPName="USR_USP_DATAFORMTEMPLATE_ADD_FOOD_ITEM">
            <c:CreateProcedureSQL>
<![CDATA[
create procedure dbo.USR_USP_DATAFORMTEMPLATE_ADD_FOOD_ITEM
(
    @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				

] ]>
</c:CreateProcedureSQL>		
        </SaveImplementation>
    </SPDataForm>

<!-- describe the context ID for this form (if applicable), which corresponds to a parameter in the SP. -->
<!--<Context ContextRecordType="REPLACE_WITH_RECORDTYPE" RecordIDParameter="CONTEXTID"/>-->

	<c:FormMetaData FixedDialog="true">
        <c:FormFields>
<c:FormField FieldID="NAME" Required="true" MaxLength="100" Caption="Name"/>
             <c:FormField FieldID="DESCRIPTION" Caption="Description" />
<c:FormField FieldID="CURRENTCOST" DataType="Money" Caption="Current Cost"/>
<c:FormField FieldID="LOWINVENTORYTHRESHOLD" DataType="SmallInt" Caption="Low Inventory Threshold" />
<c:FormField FieldID="WEIGHT" DataType="Decimal" Caption ="Weight" DefaultValueText="0.00" Description="The weight of a food item."/>
        </c:FormFields>
		
		<!-- indicate the client-side component for the user interface -->
		<!-- <common:FormUIComponent FormUIComponentType="CustomComponent">
<common:CustomComponentID AssemblyName="REPLACE_WITH_ASSEMBLYNAME" ClassName="REPLACE_WITH_FULLCLASSNAME" />
		</common:FormUIComponent>-->
		
    </c:FormMetaData>
</AddDataFormTemplateSpec>

Example: The completed spec

Here is a link to the Food Bank Source Code.