Alternative to running DB Lookup functoid through a loop?

Question

I'm really not sure how to phrase this, so here's my best shot- I have a table which stores values by a 'TypeId' and the actual value. The 'TypeId' maps to a row in a lookup table which houses the actual plain-text description. When inserting data into my Database via BizTalk Server 2009, I need to perform a lookup on each value provided from the incoming file to get its correct TypeId, and then populate the original table.

The only way I see to do that is with the DB Lookup functoid, but as this function may be called literally hundreds of times per input file, I don't look forward to the performance hit I'd take from that. Does anyone know of another way to perform that lookup at run time, or perhaps a way do the lookup from SQL just once, and then have BizTalk refer to some in-memory representation of the table?

EDIT: I did have an idea, but would like someone in the SO community to tell me if it's a bad one and, if so, why. Here it is:

I could take the incoming document and convert straight to XML, then feed the resulting XMLDocument to an external .NET Assembly. That assembly could then pull in the lookup table as a Dictionary (or some other type of IEnumerable) and modify the original document by adding in the correct TypeId values in place of the original type descriptor in the original document. This would, in theory, allow me to avoid the overhead of a large number of DB Lookups by substituting the (theoretically) much lower overhead of calling an external assembly.

Can anyone think of a reason not to use this approach?

Answer 1

I am not familiar with BizTalk; but if you want to get the plain-text description back from a lookup table, you can use a SQL query like this:

Say your tables are: Values(TypeId, Value) TypeLookup(TypeId, TypeName)

Then you can do:

SELECT Values.Value, TypeLookup.TypeId, TypeLookup.TypeName
FROM Values
LEFT JOIN TypeLookup ON TypeLookup.TypeId = Values.TypeId

This gives you back a result for each Values record. If the Values.TypeId is not null, you will also get the TypeId and TypeName for that value.

HTH

Answer 2

You could write a custom static class that has a method in it that takes the id as a parameter. The method could then be called from a scripting functoid.

The class could look like this (Warning: This is just a stubbed out example):

public class Values
{
 public static Dictionary<Int, String> LookupDictionary;

 public static string GetDictionaryValue(Int idIn)
 {
  if (LookupDictionary is null) -> go out to the database and populate;

  return LookupDictionary[idIn].Value;
 }

}

Here are some considerations.

1. If you want to call your method from a scripting functoid, the class needs to be non-static class.
2. This solution may not work very well over multiple biztalk servers since the static variables will be held on a server by server basis.

Another alternative is this enhanced database functoid by Blogical. I have not tried this functoid personally, but it may be exactly what you are looking for.

BizTalk Database lookup functoid with caching feature

Answer 3

The approach you suggest, where you do the transform and then use a .NET assembly to replace IDs with descriptions, should work for relatively small messages but you'll have problems with larger messages.

Another option is to create a multi-part message that contains your original type, plus a new type. The new type is one you create that you load with the name-value pairs before you transform. So you now have two messages - Y (your message) and X (the message that contains the name-value pairs).

Take a copy of your existing map in case it breaks, then modify the transform shape so that it accepts both types (type Y and type X) - the order is not important.

In your map - at the places where you need to perform the lookup, add a scripting functoid and set it to Inline XSLT.

In the inline XSLT add your node name, followed by an xsl:value-of select... element. Put an xpath expression into the select statement that looks up the ID and selects the value. A simple expression could be something like /Root/Lookups[@id="12345"]/ - but of course your expression will be different. You can get an idea of how to address the message part by clicking the node that holds the name-value pairs and select the Instance XPath in the properties filed.

It's going to take some practice if you are not familiar with XPath or namespaces, etc - but when it works, it will be fast and will work seamlessly, and you'll have less code to maintain.

Answer 4

What I did in the past was to work with two functiods.

The first functiod would have some code similar to what Mike suggested. But instead of loading it to a static property, I was simply return an xml file (which had the key pair values, like:

<Types>
  <Type id="a" description="b" />
  <Type id="c" description="c" />
</Types>

The second functiod would have this XML string as an input and the value that you need to map as another input. Then within the code you could find out the Ids required by either:

• Using XPath
• Using regular expressions (I used regular expresions, so I didn't have to load the xml string into any kind of DOM).

My maps then would have The result of first functiod feeding as many copies of the second functiod as needed (in my case I had 10 or so lookups to perform).

You would still have to read the database once per mapping exercise, but that would be it.

I hope this helps.