Nor the out-of-the-box Database lookup functiod or the XRef functoid come with any caching capabilities, which might produce a tremendous amount database traffic, and eventually cause transformations to run poorly. If you are repeatedly executing similar queries you might gain on using these functoids instead.
The ExecuteQuery and ExtractData works pretty much like the corresponding functoids that ships with BizTalk. However these functoids caches the result, and will therefor prevent the same query from being executed more than once.
Blogical.Shared.Functoids.ExecuteQuery
This functoid can be used like the Database lookup functoid that ships with BizTalk. It will execute a query against a given database and cache the result using the System.Web.Caching.Cache with a sliding expiration of one minute. This enables the functoid to reuse the data across mapping boundaries, executed on the same host. In other words the same resultset can be used even from within different mapping transformations.
The result from the executed queries are stored in a HashTable, where the key is the concatenation of the database connection string and the actual SQL query. Its value is the System.Data.DataTable returned from executing the query or stored procedure. This makes it possible to query for more then one row, which you may later extract using the ExtractData functoid. You should use this approach with caution; -however this is where you get the best performance effect.
| parameters | Type | Optional | Description |
| 1 | String |
The connection string or a name of a connection string in the BTSNtSvc.exe.config file. Samples: Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
or
config://MyConnectionString
|
|
| 2 | Boolean | Indicates whether to search for cached data or not | |
| 3 | String | The name of the source that is providing the data (table or stored procedure) | |
| 4 | String | X | Column name or stored procedure parameter |
| 5 | String | X | Lookup value |
The functoid can take a maximum of 6 pairs of either [column name and value] or [sp parameter, parameter value].
Blogical.Shared.Functoids.ExtractData
This functiod is used together with the ExecuteQuery functoid, and functions similar to the Value Extractor functiod that ships with BizTalk. The ExtractData functiod gets the value from the result of the query executed by the ExecuteQuery functoid. The optional Column/Value pair can be used when the result from the ExecuteQuery functoid holds more than one row.
| Parameters | Type | Optional | Description |
| 1 | String | Link from the ExecuteQuery functoid | |
| 2 | String | The name of the column from which you like to extract its value | |
| 3 | String | X | Column name to filter * |
| 4 | String | X | Column value filter * |
The functoid can take a maximum of 6 pairs of either [column name and value] or [sp parameter, parameter value].
* When the optional parameters are used the Execute Query functoid is forced to have the whole query result cached.
I share any creds (or criticism) with Johan Hedberg and Niklas H%u00e4ggstr%u00f6m
All downloads are available through CodePlex.