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 ConnectionString, SqlStatement, and 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.ConnectionString must match the Dictionary key for the SQL Connection String. An example of the Dictionary value might be:
       Data Source=(local)\GETSMART;Initial Catalog=Employees;Integrated Security=SSPI;MultipleActiveResultSets=true;



• PropertyMapping.xml.SqlStatement must match the Dictionary key for the SQL SELECT Query. An example of the Dictionary value might be:
       SELECT [Employee_ID] FROM [Employees].[dbo].[Employee_Data] WHERE [Employee_Name] = 'Robin Bird'



• PropertyMapping.xml.ReturnValue must match the Dictionary key for the expected return property ID. The returning Dictionary value 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.
  
  

Additional Notes:

• 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 Property value 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 PropertyMapping.xml.

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: Employee_Name, Employee_ID, Department, and 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. (Employee_Name, Employee_ID, Department, Votes).
  
  

• The demo SQL database table structure must exist (Employee_Data table in an Employees database). Use the query below to create the database and table, then just fill in the table with some sample data.

CREATE DATABASE Employees

GO

USE [Employees]

GO

CREATE TABLE [dbo].[Employee_Data](

    [Employee_Name] [varchar](50) NULL,

    [Employee_ID] [int] NULL,

    [Department] [varchar](50) NULL,

    [Votes] [int] NULL

) ON [PRIMARY]

GO

Additional Notes:

• SQLSelectQuery.dll gives more user flexibility, but the SQL Query Property must be changed and the assembly re-run for each field that needs to be updated. EmployeeDataLookup.dll is more strict in what it is searching, but is more user-friendly, and can update multiple fields in one call.