Create a CLR-based Global Change
This tutorial/example will teach you the basics of building a CLR-based global change using a Blackbaud Infinity SDK Global Change Spec (CLR) catalog template. The template creates a spec for a CLR-implemented global change operation/definition.
Tip: The source code is included with this example and includes a Visual Studio 2010 solution and catalog project named Blackbaud.CustomFx.Address.Catalog containing the XML specs/catalog items and vb class file. The code for the global change can be found within a file named AddressCoordinateTimeZone.GlobalChange.vb. The spec file is named AddressCoordinateTimeZone.GlobalChange.xml. Grab the source from the repo in GitHub.
Prerequisites
Before you start, make sure you have a development instance of Blackbaud CRM installed along with a local install of the Blackbaud Enterprise SDK. For information about how to set up your development environment, check out the following guide here. I will develop on SDK version 2.94, which depends on Visual Studio 2010.
Tip: Blackbaud Enterprise SDK version 2.91 and higher takes a .NET 4.0 dependency for server side / catalog components that are created using the Infinity SDK Visual Studio catalog project template. However, Visual Studio 2008 only supports up to .NET 3.5. Visual Studio 2008 will not be able to target .NET 4.0, so you need to develop in Visual Studio 2010 for server side (Infinity SDK Catalog Projects) components for 2.91 and above.
You need to set up mapping credentials for your environment. The Mapping task within the Administration functional area allows you to set credentials for the mapping service. The out of the box "Address Geocode" global change definition depends on mapping credentials being set.
You need to configure and run an instance of the Address Geocode global change. That way our custom Address Coordinate TimeZone (Custom) global change will have address coordinate records to process.
Tip: For more information on global changes, see Global Change Definitions and Address Geocodes within our Administration Guide.
What You Will Build
You will implement a global change definition that supports editing and inserting custom time zone data for a constituent address's latitude and longitude known as an Address Geocode. You will author a GlobalChangeSpec that refers to a CLR class that implements the logic for the global change processing. CLR-based specs enable you to go beyond the capabilities of stored procedures by allowing you to create a CLR class to perform the business logic. We have chosen a CLR-based implementation due to the fact that we will make calls to a Google Time Zone API. For more information on the differences between SP- and CLR-based features, see CLR Versus SP Based Global Change.
The spec will also refer to form fields that define the user interface controls for the screens which allow the user to add and edit a global change instance.
After the global change is deployed, we will configure and start an instance of the global change. The goal of the global change is to populate rows within a custom table named USR_ADDRESSCOORDINATETIMEZONE. This table will hang off the ADDRESSCOORDINATE table via a 1 to 1 relationship. Below is a diagram of the custom table in relation to the relevant tables that ship with Blackbaud CRM.
Here is a sample of the data that is populated within the USR_ADDRESSCOORDINATETIMEZONE custom table by our global change code.
Skills You Will Learn
Here's what you'll learn:
-
How to create a new Blackbaud Enterprise SDK catalog project using the appropriate SDK Visual Studio project template.
-
How to add a Global Change Spec (CLR) catalog item to the catalog project.
-
How to create CLR implementation of a global change definition including the XML spec file and vb.net class file.
-
How to leverage the existing database connection available within the application context.
-
How form fields within the spec are used to define the user interface parameters.
-
How to build a dynamic T-SQL SELECT statement that restricts the rows processed using the form field parameter values.
-
How to call a web API from within the CLR class.
-
How to code a T-SQL MERGE statement to INSERT and UPDATE rows in a table.
-
How to report back the results of the global change instance run to the system including the number of inserted, updated, and deleted records.
-
How to decipher the metadata tables used to store global change and process run data.
Time Zones Depend on Address Geocodes
Out of the box, Blackbaud CRM will allow a user to track a Geocode (latitude and longitude) for an address within the AddressCoordinates table. We would like to store time zone data in addition to the Geocode. To populate the time zone data, we will create a custom global change definition to select the latitude and longitude records from the database for a given set of constituent address records. After we retrieve the Geocode records, we will pass the latitude and longitude to a web service which will return a time zone value. We will take the time zone value and store it within a custom table named USR_ADDRESSCOORDINATETIMEZONE which will have a 1 to 1 relationship with the constituent address's ADDRESSCOORDINATES table.
Geocodes allow addresses to be mapped. An address's Geocodes are stored within the AddressCoordinates table. You can assign Geocode when you create new records or import records into the system. Our custom global change will depend on the AddressCoordinates table being populated with Geocodes. We will utilize an out of the box "Address Geocode" global change definition on the appropriate constituent addresses prior to running our custom global change.
Mapping Credentials
You will need to setup mapping credentials for your environment. The Mapping task within the Administration functional area allows you to set credentials for the mapping service. The out of the box "Address Geocode" global change definition depends on mapping credentials being set.
Tip: For more information on Global Changes, see Global Change Definitions and Address Geocodes within our Administration Guide.
Restrict the Number of Rows Processed
Our global change will build a dynamic SQL SELECT statement that retrieves columns from the ADDRESSCOORDINATES table. Form fields defined within the GlobalChangeSpec will allow the user to pick a selection of constituent records to limit the number of rows processed. In addition, we can further restrict rows by allowing the user to select whether to process primary addresses and/or new or changed address geocodes. In this way, we can improve processing time by limiting the number of rows processed.
The Experimental Google Time Zone API
According to the Google Time Zone API website, "The Google Time Zone API provides a simple interface to request the time zone for a location on the earth... Requesting the time zone information for a specific Latitude/Longitude pair will return the name of that time zone." This is an experimental site with usage limits. The global change that we are about to build is for education purposes only and not meant to be used in production. In other words, I am trying to show you how to build a realistic CLR global change, and this example is not meant for production. If you want to use this API for any production purpose, Google recommends that you purchase a Maps API for Business license.
To use the API you formulate an HTTPS request that contains latitude and longitude data that represents an address on a map. The data returned in the reply can be either JSON or XML format. Here's an example request and XML reply:
At the heart of the request is the location parameter defined with a comma-separated latitude,longitude tuple (eg. location=-33.86,151.20) that represents the location to look up. Our latitude and longitude data is stored in the ADDRESSCOORDINATE table. In the XML reply in the figure above, note the time_zone_name element value of "Pacific Standard Time."
Now let's create a CLR global change...
Next Step: Adding the Project and Global Change Spec