Load Implementations

Load implementations inform the platform about how to load form field default values when a form is opened. Load 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. Load implementations are optional for Add Data Forms and required for Edit Data Forms. The implementation for a View Data Form is similar to a load implementation. But implementations for View Data Forms are discussed separately.

Stored Procedure Load Implementations

The load implementation for a stored procedure data form spec is defined within the SPDataForm element in a LoadImplementation child element. LoadImplementation 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 load 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. Finally the name should end with _PRELOAD. For example:

USR_USP_DATAFORMTEMPLATE_ADD_CUSTOMRECORD_PRELOAD USR_USP_DATAFORMTEMPLATE_EDIT_CUSTOMRECORD_PRELOAD

Within the LoadImplementation 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.

		<LoadImplementation SPName="USR_USP_DATAFORMTEMPLATE_ADD_CUSTOMRECORD_PRELOAD">
			<common:CreateProcedureSQL>
				<![CDATA[
create procedure dbo.USR_USP_DATAFORMTEMPLATE_ADD_CUSTOMRECORD_PRELOAD
..				
				] ]>
			</common:CreateProcedureSQL>
		</LoadImplementation>

MSDN: CREATE PROCEDURE (Transact-SQL)

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

create procedure dbo.USR_USP_DATAFORMTEMPLATE_ADD_CUSTOMRECORD_PRELOAD
(
	@CONTEXTID uniqueidentifier,
	@FIELD1 nvarchar(10) = null output
)

The parameter declarations indicate a type, respectively uniqueidentifier and nvarchar(10) in the example. A default may be assigned to a parameter. For @FIELD1 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.

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 returned:

set nocount on;

MSDN: SET NOCOUNT (Transact-SQL). This is suggested as a possible best practice to improve performance in MSDN: CREATE PROCEDURE (Transact-SQL).

The body of the CREATE PROCEDURE statement for a load implementation will usually and mostly be some variation of a SELECT statement to retrieve data from the database. In the illustrative example produced by the spec wizard, the set of records returned is narrowed to just those with ID equal to the @CONTEXTID parameter. The presumption with the example is that only one value for @FIELD1 and @CONTEXTID are necessary since the goal is to load data for a single form. The variable @FIELD1 is assigned the value of the FIELD1 field for the row returned by the query.

  select @FIELD1 = FIELD1
  from dbo.SOMETABLE
  where ID = @CONTEXTID

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.

return 0;

MSDN: RETURN (Transact-SQL)

		<LoadImplementation SPName="USR_USP_DATAFORMTEMPLATE_ADD_CUSTOMRECORD_PRELOAD">
			<common:CreateProcedureSQL>
				<![CDATA[
create procedure dbo.USR_USP_DATAFORMTEMPLATE_ADD_CUSTOMRECORD_PRELOAD
(
	@CONTEXTID uniqueidentifier,
	@FIELD1 nvarchar(10) = null output
)
as
  set nocount on;
		
	-- populate the fields that have default values that should be fetched from the database
  select @FIELD1 = FIELD1
  from dbo.SOMETABLE
  where ID = @CONTEXTID
	
  return 0;				
				] ]>
			</common:CreateProcedureSQL>
		</LoadImplementation>

When the spec is loaded from the catalog into the application, the CREATE PROCEDURE is run and the stored procedure is added to the database for use by the data form's load implementation. Subsequent loadings of the spec are handled by the platform as changes to the procedure and the platform modifies the CREATE PROCEDURE statement to be an ALTER PROCEDURE statement.

Warning: For Edit Data Form load implementations, @DATALOADED must be explicitly set to TRUE for the form to save.

Common Language Runtime Load 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, two parameters can be passed to the Load() function and a query of the database can be made with SqlClient.

Warning: For Edit Data Form load implementations, DataLoaded for the AppCatalog.AppEditDataFormLoadResult must be explicitly set to TRUE for the form to save.

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