One common task in a BizTalk map is performing some type of database lookup. While there are several database functoids available for this, quite often the easiest (and most appropriate) way is to create an external assembly to do the lookup.  An external assembly allows for the full power of the .NET framework and the flexibility to test and develop outside of the BizTalk platform.

An example of an external assembly for lookup is shown below.  This method accepts two input parameters:

• The first is the value to pass to the stored procedure
• The second is the connection string to the database.  The  connection string is added as an input so that the value can be passed to the map for testing. In production, a blank value can be passed and the connection string will be set through the BizTalk configuration file.

The method creates the necessary SQL objects to create a sqlcommand, add parameters, connect to the database and retrieve a scalar result.

Once the code is complete and tested, sign the assembly, build it and add a reference to it in the mapping project.  The code can then be called from a map using a custom Scripting  functoid.  Select External Assembly, the correct class name, and the appropriate method – as shown below:

The input parameters can be added as shown here (for the connection string either add the data source or add a blank input so that the setting in the BizTalk configuration file can be used).