Create a Record Operation Spec

Video: Watch how to create a delete Record Operation (SP), an update Record Operation (SP), or a Record Operation (Wrapped SP).

To create a record operation spec, you simply add a new item to your Microsoft Visual Studio solution, select Blackbaud AppFX Catalog as the category of item, and then select Record Operation (SP), Record Operation (CLR), or Record Operation (Wrapped SP).

After you add a Record Operation (SP) spec to the project, you will notice the spec contains SPRecord, SPOperationImplementation, and CreateProcedureSQL elements. Within the CreateProcedureSQL element, a stored procedure is stubbed out for you. Even more interesting is the presumption to a call to a stored procedure (yellow highlight below).   

<SPRecord>
	<SPOperationImplementation SPName="USP_xxx_DELETE">
		<common:CreateProcedureSQL>
		<![CDATA[
create procedure dbo.USP_xxx_DELETE
(
		@ID uniqueidentifier,
		@CHANGEAGENTID uniqueidentifier
)
as begin
		--check deletion rules, if any

-- use the system generated delete routine to allow proper recording of the deleting agent
		exec USP_TABLE1_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID
		return 0;
	
end
				]]>
			</common:CreateProcedureSQL>
		</SPOperationImplementation>
	</SPRecord>

Example: The SDK presumes a call to a stored procedure.

Remember in the first lab when you created the USR_FOODITEM table via a TableSpec? Behind the scenes, the Infinity platform created two stored procedures for you: 

• USP_USR_FOODITEM_DELETEBYID_WITHCHANGEAGENTID

• USP_USR_FOODITEMAUDIT_SETDELETEDCHANGEAGENTID

When you create a new Record Operation (SP), the SDK tries to lead you in the right direction by providing a hint to a stored procedure that deletes a row. If we create a Record Operation Spec to delete a row from the USR_FOODITEM table, then we need to replace the call to the stubbed out stored procedure (highlighted above) with a call to the correct Infinity-generated delete stored procedure: 

exec USP_USR_FOODITEM_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID

Tip: After you load a spec type for the first time, it's a good practice to explore the database for new objects that were created. For example, when you load a TableSpec, the platform creates an audit table and stored procedures.

You can run the following query in SQL Server Management Studio to explore the Infinity tables you created with your TableSpecs. Replace the highlighted table name below with your Infinity database name before you run the query.

SELECT [TABLE_NAME]
FROM BBInfinityFoodBankTest.[
INFORMATION_SCHEMA].[TABLES]
WHERE TABLE_NAME LIKE 'USR_%' 

You can run the following query to view the stored procedures created for a specific table such as USR_FOODITEM:

SELECT TOP 1000 SPECIFIC_NAME
 FROM BBInfinityFoodBankTest.[INFORMATION_SCHEMA].[ROUTINES]
 WHERE SPECIFIC_NAME LIKE '%FOODITEM%'

You can run the following query to view table constraints:

SELECT TOP 1000 
 [CONSTRAINT_NAME]
,[TABLE_NAME]
,[CONSTRAINT_TYPE]
 FROM BBInfinityFoodBankTest.[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]
 WHERE TABLE_NAME LIKE '%FOODITEM%'