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.

1

 

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

2

 

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.

3

I share any creds (or criticism) with Johan Hedberg and Niklas Häggström

All downloads are available through CodePlex.

Comments

# Polan said on May 23, 2008 05:37 AM:

How do you manage the configuration of the connection string when deployed to production?

# wmmihaa said on May 23, 2008 08:53 AM:

Polan,

you can define the connectionstring in the BizTalk config file (BTSNTSvc.exe.config), and later use it in the ExecuteQuery functoid with the "config://" moniker:

Sample:

1. Add the connectionstring at the end of the BTSNTSvc.exe.config:

********************************************

<?xml version="1.0" ?>

<configuration>

   ...

<connectionStrings>

<add name="lookUpServer" connectionString="Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;"/>

</connectionStrings>

</configuration>

********************************************

2. Use the conectionstring as the first parameter in ExecuteQuery functoid:

config://lookUpServer

# Tuan said on May 23, 2008 10:36 AM:

I have tried to use this functiod, but I get the following error:

Function 'ScriptNS1:GetData()' has failed. Exception has been thrown by the target of an invocation.

Can you help me?

# wmmihaa said on May 23, 2008 10:44 AM:

Tuan,

Do you get any errors in the eventlog?

# Tuan said on May 23, 2008 12:51 PM:

Hi Mikael,

I have ran the test in the Map designer by right click on the map and select Test Map. I do not see any error in the event viewer.

Or this won't work? I need to deploy the map and run the test with use of BizTalk server?

# wmmihaa said on May 23, 2008 01:51 PM:

Tuan,

What does your ExecuteQuery parameters set to?

I'm at a course this week, why I might not able to answer you quick enough. I you'd like, try contact me through msn messenger instead. There is an im icon on my blog page.

# wmmihaa said on May 23, 2008 02:43 PM:

Tuan,

the UDL reference is not supported Eg.

File Name=C:\MappingDB.udl

Try using a connectionstring instead, or use a config:// moniker to reference the configstring in the BTSNTSvc.exe.config (see Polan's post above)

# Tuan said on May 26, 2008 04:58 PM:

Hi Mikael,

Does config:// moniker also work when I use Test Map in the Map designer? Because I have added the connection string in BTSNTSvc.exe.config, but I get an error. Or do I need restart a certain service before the configuration is applied?

# wmmihaa said on May 27, 2008 10:25 AM:

Tuan,

It does work, however since you are running the transformation under the Visual Studio process, you need to add the same configuration to you Visual Studio configfile Eg:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe.config

hth

Mikael

# Bruce said on June 24, 2008 03:15 PM:

Hi Mikael,

What kind of connection strings/databases does this support?  I'm using Oracle and I'm getting similar error messages as above (Function 'ScriptNS1:GetData()' has failed. Exception has been thrown by the target of an invocation. DataWrapper.GetData)

My connection string is Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.10)(PORT = 5480))) (CONNECT_DATA = (SERVICE_NAME = IMIFSTST)));User Id=userid;Password=password;

The connection string as above works fine with the BizTalk Database Lookup functoid.

Thanks,

Bruce

# wmmihaa said on June 26, 2008 01:15 PM:

Bruce,

I'm sorry to say, it only works with MS SQL Server databases... :(

But if you need it, I'd be happy to fix it for you.

//Mikael

# Chung said on October 8, 2008 10:59 PM:

This is a great, thank you!  

I'm curious if there is a way could extend this into a looping mechanism, i.e. iterate for each row, rather than hard-code .  For example, I make a db call to get all addresses for an individual, based on id number.  Then, for each row, I would iterate (looping functoid or similar) to a record in the destination schema (Record_Addresses) and map to child elements using the extract mechanism (Address_1, Address_2, etc).

Suggestions would be greatly appreciated.

Thanks, Chung

# Johan Hedberg said on October 27, 2008 07:20 AM:

Chung,

If a record equals a row there is a couple of ways I would suggest solving this.

1. Call the Execute Query functoid once (that is, dont link in to it) and add a rowcount column to the data returned, use the rowcount column as a where clause in the Extract Data functoid in you iteration.

2. Adda a paging mechanism to the SQL Code or Stored procedure you are calling and call the Execute query functoid for every iteration.

The latter however more or less invalidates the reasoning behind using caching in the first place... So I'd recommed option 1.

# Tuan said on November 3, 2008 09:59 PM:

I want to call a stored procedure. Is this possible with this functoid? I see that the 3rd parameter can accept a stored procedure. Can you tell how it works?

# jock said on January 23, 2009 02:15 PM:

I'm trying to call a stored procedure with the ExecuteQuery functoid but I get the following error.

Function 'ScriptNS0:GetData()' has failed. Exception has been thrown by the target of an invocation. DataWrapper.GetData

I'm running the Test Map when this error occurs.

If I'm calling a table or a view it works fine but it won’t work when I call a SP.

Is there some special tricks I need to do to get it to work?

The first input param to the functoid is config://myDBConnection

Second is true

Third is GetAlarmError  -- Name of my SP

Forth @protocol -- param declared in the SP

Fifth a link from the source schema with protocol value

# jonnidip said on January 26, 2009 11:21 AM:

Un problema che mi ha sempre dato da fare &egrave; la chiamata di una query sql (o stored-procedure)

# Nick Wong said on May 13, 2009 12:09 AM:

If anyone is still struggling with calling a stored procedure using this functoid, you can try putting 'exec' in front of your stored procedure name. So the 3rd parameter would be 'exec [YourStoredProcedure]'.

Cheers

# Mark said on October 14, 2009 07:24 AM:

Is there a way to control how long a peice of data stays in the cache?

# wmmihaa said on October 14, 2009 08:02 AM:

Not through configuration. You'd have to update the code :(

# Dan said on April 27, 2010 03:36 PM:

Im having a problem with this functoid. If there is no matching record it puts a # into the target element. Is there anyway to stop this. I want it to put a value in if it finds one else leaves it blank. Thanks in advance

# Fernando Pires said on January 12, 2011 11:34 PM:

Hi

When calling a procedure use exec [procname]

parameters must be @[parametername]

Unfortunally if there does not exist a row the component trows an error instead of returning blank or null or something.

Other then that a very great functoid that we have choosen to use for all our mappings.

# Fernando Pires said on January 13, 2011 08:40 AM:

Sorry my bad! The GetData returns # if there is no match. Would be nice to be able to specify a default value for the GetData functoid. What uf the value to be returned IS #?

# wmmihaa said on January 13, 2011 08:51 AM:

I agree. Let me know if you make any updates. I'd be happy to post a new version in your name.

# Fernando Pires said on January 14, 2011 12:04 PM:

The cache=false option does not work for me! I repetedly get the same result when calling a stored procedure.

Can you give me any idea why this is!?

Its not a permission problem as the functoid gets the first increment of the counter without problem.

# Fernando Pires said on January 17, 2011 09:28 PM:

Hi

I made some changes to your functoid and if your interested you can download the file bellow.

cid-9c552982a1b470b6.office.live.com/.../.Public

Changes done

1) You can now put choose a  timeout by specifying a specific AppSetting.

2) Default value for ExtractData is enabled by specifying columnname=[default value]

I have done everything not to break any existing code.

3) I have added a new functoid that is used on its own that enables you to quickly retrive a single value from a table. I use it to retrieve counter values.

The reason i have created one more functoid is that i want to use one db lib for all by databse access.

You are free to use it if you want :-).

Thanks again for a great functoid.

# crux said on December 30, 2011 01:21 AM:

Hi

I am using the 1.1.0.0 version code in BizTalk 2010.

When testing in VS2010 gives error :

Could not load file or assembly 'Blogical.Shared.Functoids, Version=1.1.0.0, Culture=neutral, PublicKeyToken=c77920ec2c565075' or one of its dependencies. The system cannot find the file specified.

This Blog

News

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

    Follow me on Twitter Meet me at TechEd

    Visitors

    Locations of visitors to this page

    Disclaimer

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

Syndication