Exercise: Build a Simple Parking Permit Table Spec

We will now try a little hands-on exercise to create two specs and load them into the Infinity platform catalog. We will create two new tables within the Infinity database to track parking permits that are provided to constituents. We will create one table with a TableSpec and the other with a CodeTableSpec.  

Step 1 -   Create the Exercise folder.

Create a sub-folder named Exercises on your local machine.

Example:  E:\Training Class Support Material\SDK\UIModel\My Code\Exercises

Step 2 -  Create a new Infinity catalog project named ParkingPermit.

In Microsoft Visual Studio, select File\New\Project… from the main menu. The New Project dialog box appears. Select Blackbaud AppFx as the project type, and select Catalog Project from the list of templates.  

• Name: Exercise01_ParkingPermit

• Location:  E:\Training Class Support Material\SDK\UIModel\My Code\Exercises

Click OK to create a Microsoft Visual Studio solution that contains a reference to a Visual Basic project.  Visual Studio creates a sub-folder with the project name in the C:\Training Class Support Material\SDK\UIModel\My Code\Exercises folder.

Example:  :\Training Class Support Material\SDK\UIModel\My Code\Exercises\Exercise01_ParkingPermit.

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

• Name: Parking Permit

• Description: Manages constituent parking permits

• Author: Technical Training

• Tablename: USR_CONSTITUENTPARKINGPERMIT

Step 4 -  Add simple fields.

Add the following fields for our parking permit. Be sure to read the comments sections for clues about how to create the fields.

Example: Simple parking permit columns

Step 5 -  Add a Code Table Spec to the project.

Next, let's extend the spec by adding a new Code Table Spec and a reference to the Code Table Spec within the parking permit TableSpec we created in the previous steps. Within Visual Studio, add a new CodeTableSpec to our Visual Studio project. See the section titled CodeTableSpec for more information on this spec type.

The CodeTableSpec XML file name should be PARKINGZONECOLORCODE.XML.

Within the PARKINGZONECOLORCODE.XML file, the CodeTableSpec element should have the following attributes:

• Name: Parking Zone Color

• Description: Parking Zone Color Codes for constituent parking passes

• Author: Technical Training

• DBTablename: USR_PARKINGZONECOLORCODE

• Category: Parking Permit

• SortMethod: Alpha

Step 6 -  Add the CodeTableField element to the Parking Permit TableSpec.

Now that we created our Code Table Spec, we need to reference the code table from our TableSpec. Within our project, open the parking permit TableSpec and add a CodeTableField element to the Fields element. The CodeTableField element should have the following attributes:

• Name: PARKINGZONECOLORCODEID

• CodeTable: USR_PARKINGZONECOLORCODE

• Description: Parking Zone Color

• Required: True

Example: Add the CodeTableField

Step 7 -  Deploy the specs with the LoadSpec utility.

Now that you created both the code table and the Table Spec, it's time to load each into the Infinity database. Specifications, not just for tables, are typically deployed in one of two ways:

• LoadSpec.exe – a custom tool included in the AdminTools of your source directory

• Catalog Browser in the shell

For now, let's use the LoadSpec utility. If you haven't already done so, go to Add LoadSpec to the Microsoft Visual Studio Development Environment for instructions on how to load the utility as an external tool to Visual Studio.

Make sure the code table file (PARKINGZONECOLORCODE.XML) is the active editor window, and select Tools\LoadSpec from the Visual Studio main menu. The Output window should display the following:

Connecting to database 'CHS-TOT-PS630\MSSQLSERVER2K8' on server 'BBInfinity2.0'.
Uploading CodeTableSpec 'Parking Zone Color' to catalog...
Uploading input file C:\files\Training Engineer\SDK Training\SDK Ott\Exercises\Exercise01_ParkingPermit\PARKINGZONECOLORCODE.XML
Upload complete.

Open the Parking Permit Table Spec file (ParkingPermit.Table.XML) in the editor and select the same command:

Connecting to database 'CHS-TOT-PS630\MSSQLSERVER2K8' on server 'BBInfinity2.0'.
Uploading TableSpec 'ConstituentParkingPermit' to catalog...
Uploading input file C:\files\Training Engineer\SDK Training\SDK Ott\Exercises\Exercise01_ParkingPermit\ParkingPermit.Table.XML
Upload complete.

Open SQL Server Management Studio, and navigate to your tables within the Infinity database using the Object Explorer. You should find your tables (dbo. USR_CONSTITUENTPARKINGPERMIT and dbo. USR_PARKINGZONECOLORCODE), as well as the audit tables (dbo. USR_CONSTITUENTPARKINGPERMITAUDIT and dbo. USR_PARKINGZONECOLORCODEAUDIT).