Use Call Assemblies for Data Lookup
As of version 2.3, Call Assembly nodes are deprecated in favor of Custom Nodes. Custom Nodes offer numerous benefits over Call Assembly and should be the standard going forward for any implementation. The steps outlined here may be leveraged by the SQL Custom Node, instead of the Call Assembly equivalent.
Create a Database Lookup using a Simple SQL Query
One of the most common uses the Call Assembly Node is performing a database lookup. For this example, create a database lookup in your GlobalCapture Workflow using a general assembly called "SQLSelectQuery" that accepts two properties, a SQL connection string, and a simple SQL Query and then returns the first result of the SQL query as a third property. Any time a user wants to update one or more GlobalCapture Fields based on a key value, they can use the SQLSelectQuery assembly.
The SQLSelectQuery assembly makes use of a
PropertyMapping.xml file which contains elements for
ReturnValue. The data in these elements must match the keys in the
Dictionary(string, string) object passed in.
The Workflow requirements to use SQLSelectQuery are as follows:
PropertyMapping.xml.ConnectionStringmust match the
Dictionarykey for the SQL Connection String. An example of the
Dictionaryvalue might be:
Data Source=(local)\GETSMART;Initial Catalog=Employees;Integrated Security=SSPI;MultipleActiveResultSets=true;
PropertyMapping.xml.SqlStatementmust match the
Dictionarykey for the SQL
SELECTQuery. An example of the
Dictionaryvalue might be:
SELECT [Employee_ID] FROM [Employees].[dbo].[Employee_Data] WHERE [Employee_Name] = 'Robin Bird'
PropertyMapping.xml.ReturnValuemust match the
Dictionarykey for the expected return property ID. The returning
Dictionaryvalue is initialized (set to a value [can be empty] with “Set Property”) and is of the correct data type to accept the assembly’s returned value.
The properties can be given any name; the only attribute which must be consistent is each variable having the ID mentioned above.
If the SQL Query returns no results, the
Propertyvalue remains as what it was last set to.
Additional properties may be initialized in your Workflow before using this Assembly; however, the SQLSelectQuery assembly will only use properties matching
Create an Employee Data Lookup
Another way to perform a database lookup with a Call Assembly Node would be to hard-code the SQL connection string and query into the assembly. In the
EmployeeDataLookup.dll demonstration assembly, the properties that are passed to the assembly are the different column values that you want returned in the Workflow. For example, this DLL looks into a table called “
Employee_Data” within an “
Employees” database. The table consists of four columns:
Votes; any of these columns can be passed as a property to the assembly. The data within the specified column(s) from the returned row of SQL data (from the query) will be updated as that new property’s value.
The Workflow requirements to use EmployeeDataLookupare as follows:
Property values must be the column name for the database table. (
The demo SQL database table structure must exist (
Employee_Datatable in an
Employeesdatabase). Use the query below to create the database and table, then just fill in the table with some sample data.
CREATE DATABASE Employees
CREATE TABLE [dbo].[Employee_Data](
[Employee_Name] [varchar](50) NULL,
[Employee_ID] [int] NULL,
[Department] [varchar](50) NULL,
[Votes] [int] NULL
) ON [PRIMARY]
SQLSelectQuery.dllgives more user flexibility, but the SQL
Query Propertymust be changed and the assembly re-run for each field that needs to be updated.
EmployeeDataLookup.dllis more strict in what it is searching, but is more user-friendly, and can update multiple fields in one call.