Database lookup functoid with caching feature

Listen with webReader
Published 28 April 08 12:11 PM | wmmihaa

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.




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;
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].




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äggström

All downloads are available through CodePlex.


No Comments

This Blog


    MVP - Microsoft Most Valuable Professional BizTalk User Group Sweden BizTalk blogdoc

    Follow me on Twitter Meet me at TechEd


    Locations of visitors to this page


    The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.