Save Implementations

Save implementations inform the platform about how to insert or update form field values in database tables when a form is saved. Save implementations can be defined through a CREATE PROCEDURE statement declared inline with the spec XML or common language runtime code in classes referenced by the spec. Save implementations are required for Add Data Forms and Edit Data Forms.

Stored Procedure Save Implementations

The save implementation for a stored procedure data form spec is defined within the SPDataForm element in a SaveImplementation child element. SaveImplementation must have an SPName attribute that defines the stored procedure name. Although the SPSchema attribute appears as an IntelliSense option, SPSchema is not applicable to save implementations for data forms.

The names for custom stored procedures for load implementations and save implementations should begin with USR_USP_DATAFORMTEMPLATE_. The USR_USP_DATAFORMTEMPLATE_ prefix indicates by convention that the stored procedure is a custom stored procedure for a Blackbaud Infinity data form instance. For Add Data Forms, USR_USP_DATAFORMTEMPLATE_ should be followed by ADD_. For Edit Data Forms, USR_USP_DATAFORMTEMPLATE_ should be followed by EDIT_. All of that should be followed by a string to indicate the record type and possibly the data form instance. For example:

USR_USP_DATAFORMTEMPLATE_ADD_CUSTOMRECORD USR_USP_DATAFORMTEMPLATE_EDIT_CUSTOMRECORD

Within the SaveImplementation element, a CreateProcedureSQL element with the bb_appfx_commontypes namespace should wrap a CDATA that wraps the Transact-SQL for the CREATE PROCEDURE statement. For specs created with the SDK wizard, the namespace prefix established in the root element for bb_appfx_commontypes is common. The convention for Transact-SQL used by Blackbaud Infinity is to use lower-case for keywords. The name for the procedure is repeated by the CREATE PROCEDURE statement and preceded by the schema used by the transactional database for Blackbaud Infinity applications. For more information about create procedure statements, see the MSDN topic CREATE PROCEDURE (Transact-SQL).

		<SaveImplementation SPName="USR_USP_DATAFORMTEMPLATE_ADD_EXAMPLESP">
			<common:CreateProcedureSQL>
				<![CDATA[
create procedure dbo.USR_USP_DATAFORMTEMPLATE_ADD_EXAMPLESP
..				
				] ]>
			</common:CreateProcedureSQL>
		</SaveImplementation>
	</SPDataForm>

Parameters can be passed to stored procedures for save implementations. A spec created by the wizard includes six parameters for the purposes of illustration.

create procedure dbo.USR_USP_DATAFORMTEMPLATE_ADD_EXAMPLESP
(
  @ID uniqueidentifier = null output,
  @CONTEXTID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @FIELD1 nvarchar(10) = '',
  @FIELD2 nvarchar(20) = '',
  @FIELD3 nvarchar(max) = ''
)

The parameter declarations indicate a type such as uniqueidentifier or nvarchar(10) in the example. A default may be assigned to a parameter. For @ID in the example, a default null is assigned and the parameter is declared to be an output parameter to be returned by the stored procedure.

The @ID parameter represents the record ID for the record to be loaded. When a table is created in an Infinity application, the platform creates this column. The @CHANGEAGENTID parameter also corresponds to a column created by the platform. The ADDEDBYID and CHANGEDBYID columns store uniqueidentifiers that represent the ID for application users or a process changing something in the database. The @ID and @CHANGEAGENTID parameters and ID, ADDEDBYID, and CHANGEDBYID are type uniqueidentifier. Values pass between the @ID and @CHANGEDBYID parameters and the ID, ADDEDBYID, and CHANGEDBYID in an INSERT or UPDATE statement within the stored procedure.

Some forms require context, an ID for a parent record. For example a record for a car model may require a context record ID for car make. If the form expects context, one of the stored procedure parameters may be referenced elsewhere in the spec in a Context element. A parameter used for context does not have to be called @CONTEXT. The name is referenced in the RecordIDParameter attribute in the Context element.

This line indicates to not return the count of records:

set nocount on;

For more information about set nocount, see the MSDN topic SET NOCOUNT (Transact-SQL). This is suggested as a possible best practice to improve performance in the MSDN topic CREATE PROCEDURE (Transact-SQL).

For Edit Data Form save implementations, the first conditional in the following snippet is not necessary because a record already exists. But for Add Data Form save implementations, a new uniqueidentifier is required for the record to be added. A uniqueidentifier is returned by newid(), a system function in SQL Server databases. For more information about newid(), see the MSDN topic NEWID (Transact-SQL).

The rest of the snippet is applicable to Add Data Form and Edit Data Form save implementations. The stored procedure dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT is part of the Blackbaud Infinity platform. The dbo.CHANGEAGENT table stores the IDs and other information for change agents. The dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT stored procedure in this case returns ID to @CHANGEAGENTID since it is passed to the @ID input/output parameter for the stored procedure.The function getdate() is a built-in date function. For more information about getdate(), see the MSDN topic GETDATE (Transact-SQL).

if @ID is null
    set @ID = newid()

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

Edit Data Form save implementations include some variation of an update statement. For more information about update statements, see the MSDN topic UPDATE (Transact-SQL). Fields in a database table row are assigned values such as those passed to the stored procedure as parameters as with FIELD1, FIELD2, and FIELD3 in the example or with values retrieved from a procedure or function call such as with CHANGEDBYID and DATECHANGED. Other logic can be used within the stored procedure to determine values to update.

	begin try
		-- handle updating the data
		update dbo.TABLE1 set
			FIELD1 = @FIELD1,
			FIELD2 = @FIELD2,
			FIELD2 = @FIELD3,
			CHANGEDBYID = @CHANGEAGENTID,
			DATECHANGED = @CURRENTDATE
		where ID = @ID
	end try
	begin catch
		exec dbo.USP_RAISE_ERROR
		return 1
	end catch

Add Data Form save implementations include some variation of an insert statement. For more information about insert statements, see the MSDN topic INSERT (Transact-SQL). Fields in a new database table row are assigned values such as those passed to the stored procedure as parameters as with FIELD1, FIELD2, and FIELD3 in the example or with values retrieved from a procedure or function call such as with CHANGEDBYID and DATECHANGED. Other logic can be used within the stored procedure to determine values to update.

begin try
	-- handle inserting the data
	insert into dbo.TABLE1
		(ID, FIELD1, FIELD2, FIELD3, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    values
		(@ID, @FIELD1, @FIELD2, @FIELD3, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
	
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

A Transact-SQL RETURN statement is an exit statement that returns a status. The following stops execution of the query and returns a status of 0. If an error is encountered in the preceding try-catch statement, the returned value is 1, indicating an error. For more information about return statements, see the MSDN topic RETURN (Transact-SQL).

return 0;

Common Language Runtime Save Implementations

CLR (Common Language Runtime) data form specs enable business logic beyond reading and writing to the application's transactional database. Two scenarios where this may be helpful are when reading data from an external source such as a web service and to create dynamic Transact-SQL with which to read or write to the application's transactional database.

When a CLR data form spec is created with the SDK wizard, the initial class file for the implementations contains overridden functions for the load and save logic. These functions are contained in a base class designed for to the spec type, in this case Blackbaud.AppFx.Server.AppCatalog.AppEditDataForm.

Imports Blackbaud.AppFx.Server

Public NotInheritable Class ExampleClrEditDataForm
    Inherits AppCatalog.AppEditDataForm

    Public Overrides Function Load() As Blackbaud.AppFx.Server.AppCatalog.AppEditDataFormLoadResult

    End Function

    Public Overrides Function Save() As Blackbaud.AppFx.Server.AppCatalog.AppEditDataFormSaveResult

    End Function

End Class

There are two properties inherited from the base class to help access information about the request and context: RequestArgs, and ProcessContext.

RequestArgs contains information about the original request that initiated the invocation of the CLR implementation. This includes ClientAppInfo and UsesDatabaseConnection. ProcessContext contains other useful information.

RequestContext is a member of Blackbaud.AppFx.Server.dll and encapsulates information about a request and provides access to server utility classes and properties.

To read and write to the database, Transact-SQL code can be created within the Load() or Save() functions and executed with methods in a library such as SqlClient. Accesses to other data sources, whether outside of the application or the application services, can be made using the API for those services.

To replicate the behavior of the illustrative stored procedure created by the SDK wizards Transact-SQL can be executed with SqlClient.

Closing Connections

Sometimes your code requires an unmanaged resource, such as a SQL connection. If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. You may also consider a Using block which guarantees the disposal of one or more such resources when your code is finished with them. For more information see http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.close.aspx and http://msdn.microsoft.com/en-us/library/htd05whh.aspx.

For example, the following code would require the explicit closing of the connection:

Dim conn As SqlConnection = Me.RequestContext.OpenAppDBConnection
		‘code to perform work with the sql connection
conn.Close

Whereas utilizing a Using block guarantees the disposal of the SQLConnection:

using conn As SqlConnection = Me.RequestContext.OpenAppDBConnection
        ‘code to perform work with the sql connection
end using

Imports Blackbaud.AppFx.Server

Public NotInheritable Class Ex3EditDataForm
    Inherits AppCatalog.AppEditDataForm

    Public FIELD1 As String
    Public FIELD2 As String

    Public Overrides Function Load() As Blackbaud.AppFx.Server.AppCatalog.AppEditDataFormLoadResult
        Dim dataFormLoadResult As New AppCatalog.AppEditDataFormLoadResult(False)

        Using con As SqlClient.SqlConnection = Me.RequestContext.OpenAppDBConnection()
            Using command As SqlClient.SqlCommand = con.CreateCommand()
                command.CommandText = _
                "select " & " FIELD1, FIELD2 " & _
                "from dbo.USR_EX " & _
                "where dbo.USR_EX.ID = @ID;"

                command.Parameters.AddWithValue("@ID", New Guid(Me.ProcessContext.RecordID))

                Using reader As SqlClient.SqlDataReader = command.ExecuteReader()
                    reader.Read()
                    FIELD1 = reader.GetString(reader.GetOrdinal("FIELD1"))
                    FIELD2 = reader.GetString(reader.GetOrdinal("FIELD2"))
                    dataFormLoadResult.DataLoaded = True
                    reader.Close()
                End Using
                command.Dispose()
            End Using
        End Using

        Return dataFormLoadResult
    End Function

    Public Overrides Function Save() As Blackbaud.AppFx.Server.AppCatalog.AppEditDataFormSaveResult
        Dim dataFormSaveResult As New AppCatalog.AppEditDataFormSaveResult()

        Try
            Using con As SqlClient.SqlConnection = Me.RequestContext.OpenAppDBConnection()
                Using command As SqlClient.SqlCommand = con.CreateCommand()
                    command.CommandText = _
                    "update " & " dbo.USR_EX set " & _
                    "FIELD1 = @FIELD1, " & _
                    "FIELD2 = @FIELD2, " & _
                    "DATECHANGED = getdate(), " & _
                    "CHANGEDBYID = @CHANGEAGENTID " & _
                    "where ID = @ID;"

                    command.Parameters.AddWithValue("@ID", New Guid(Me.ProcessContext.RecordID))
                    command.Parameters.AddWithValue("@FIELD1", FIELD1)
                    command.Parameters.AddWithValue("@FIELD2", FIELD2)
                    Dim changeAgent As Nullable(Of Guid) = Me.RequestContext.GetChangeAgentID()
                    command.Parameters.AddWithValue("@CHANGEAGENTID", changeAgent)
                    command.ExecuteNonQuery()
                    command.Dispose()
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try

        Return dataFormSaveResult
    End Function

End Class