Original article is posted here.
There has been far too many times that I’ve had to go back and recompile a SQL integration library, simply because I needed to compare a different column than the original, or filter out some unwanted results.
As it turns out, there’s a fairly straightforward method of supplying SQL integration components with a full condition string (or strings), immensely increasing the usability of a single component.
Typically, a SQL component is built thusly:
A typical SQL integration configuration. A specific column is defined in a condition argument, and the input is only comparable to that column.
But now, what if during further development, it turns out that not only does the CatalogItemName need to be referenced, but I also need to check for IsEnabled to only return enabled rows? I could always use a workflow Configurable Collection Filter to filter out the rows that IsEnabled = False; however, if I had filtered out the unwanted rows in the SQL query, further computing cycles doesn’t have to be afforded a filter model in Workflow.
Instead, I’m going to adjust the SQL query in the integration wizard.
Because the parameter entered doesn’t match a standard Db Parameter, the box is unchecked for that field. No quotes should be added around the input, and two dashes (SQL comment) are set as the Default Value so that the component will work even if left to its own devices.
In the conditions section of my SQL script, I’ve hardcoded where 1=1 so that any input can disregard whether a “where” or “and” is needed, and always use “and”.
After completing the wizard for this component and compiling/recompiling, I’ll put the column names into the “Usage” field (visible when the component is selected) so that when configuring the component in the Designer, I have a quick and easy column reference for my input.
A column reference guide for this component. Absolutely nothing is spelled correctly.
Now that the integration library is buttoned-up, I’ll load it into my project for use. Notice that the initial value for the condition string input is set to –, which should work fine as is.
The default value, being a simple comment value in SQL, means that this component will not apply any dynamic conditions to the query at runtime.
Also note the “Usage” field in the component’s Help menu:
The information that was entered in the integration wizard is visible here.
Now to supply the input merge for the component’s _querystring parameter.
By using the dynamic merge option, this component can be reused as many times as needed. In this example, we’re able to return any rows that partially match two different columns and that equal the value of a third column.
When using the “like” operator with data variables, it’s sometimes helpful to swap the “Not Found” value for a “%” so you can pass a blank value in and get results as if a “%” was passed in.
It should probably be noted as well that TextBox components are preconfigured to regex-scrape any user input. As always, processes should be vetted and tested for vulnerabilities, but this feature provides a helpful baseline for sanitizing inputs.
Using this method, I’m able to reuse a single query component for fetching data from the table.