What If I Need to Lookup in the Database?
BizTalk users commonly need to extract values from the database to assist in transformations. A rich set of database functoids assists in doing just that. Consider an order-processing scenario wherein you generate an order directly to the supplier from an order request that you obtained from the requisition system. You would need to get all the supplier details, including the name, address, and contact information, from the database based on the supplier ID in the requisition system.
The value extractor functoid, in conjunction with the database lookup functoid, would help you achieve this. Both are in the database functoids category. The database lookup functoid actually connects to the database and queries using the lookup value. The lookup value is generally determined by the input parameter from the source schema, whereas the connection string can be stored in the BizTalk configuration file (btsntsvc.exe.config) and retrieved using advanced functoids. This allows the flexibility to change the data source without recompiling the orchestration.
The database lookup functoid requires four parameters in a specific order:
- The lookup value, which is normally taken from the source schema
- The database connection string with which the functoid will connect to the database
- The parameter that specifies the table / view name
- The parameter that determines the column name where the functoid will make the lookup for the value
Once connected, the database lookup functoid returns a recordset that holds the records matching the specified criteria (see Figure 5). A value extractor functoid must be used in conjunction with this to retrieve the values from the recordset. The value extractor functoid takes in two parameters: the first is the link from the database lookup functoid and the next is the column name from where the value is to be extracted. The value extractor returns the value for the specific column matching the criteria specified in the database lookup functoid.
|Figure 5. Database Functoids: Use the Database Lookup in conjunction with the Value Extractor functoid to extract values from the database.|
These Functoids Are No Help. I Need My Own.
Despite the flexibility and power provided by built-in functoids, some business scenarios demand more. Consider a scenario where you need to convert temperatures from Celsius to Fahrenheit or vice-versa based on parameters from a source schema. Doing this with the normal set of functoids would involve using advanced functoids to determine the type of conversion and then a series of mathematical functoids for the actual conversion process. This scenario provides a nice example in which to build a custom functoid.
A custom functoid builds on a .NET assembly, which inherits features of a functoid from the Microsoft.Biztalk.BaseFunctoids.BaseFunctoid class. For a functoid, you can just get down to the code without many preliminary steps (they just don't have much!). A custom functoid starts with a Class library project in the Visual studio environment. This project needs to reference the Microsoft.Biztalk.BaseFunctoids.dll file located in the BizTalk developer tools folder under the BizTalk installation folder.
The Class library compiles in an assembly that you need to register in the GAC. So be sure to generate the snk file using the "sn" utility and refer it in your AssemblyInfo.cs. Furthermore, you can have a resource file that defines attractive icons for your new functoid. So with all that done, you are ready to code the example:
public class ConvertTemperatureFunctoid: BaseFunctoid
//Functoid Unique Id. Start from 6000 for your own functoids
this.ID = 6003;
SetName("MY_FUNCTOID_NAME"); //Functoid Name from Resource
SetTooltip("MY_FUNCTOID_TOOLTIP"); //Functoid Tooltip from Resource
SetDescription("MY_FUNCTOID_DESCRIPTION"); //Functoid Description from Resource
SetBitmap("MY_FUNCTOID_BITMAP"); //Sets the Bitmap from resources
//Functoid will accept 2 parameters.
//One for Base type - Fahrenheit or Celsius
//The other for the actual value of temperature
this.SetMinParams(2); //Sets the Minimum number of parameters
this.SetMaxParams(2); //Sets the Maximum number of parameters
//Specify the function that will be called when the functoid executes
this.Category = FunctoidCategory.String;
this.OutputConnectionType = ConnectionType.AllExceptRecord;
// Parameter 1
// Parameter 2
public string ConvertTemperature(string val1, string val2)
string result = "";
//Here goes all the logic required for the conversion
In the above sample, note several things that differentiate the assembly as a functoid:
- The class is inherited from the BaseFunctoid class, which provides the required functionality for building over a functoid. Functionality and properties specific to your functoid need to be coded within the constructor.
- Within the constructor, the first thing you do is set the ID of the functoid. This ID must be unique to the functoid. Normally, you should start with an ID value of more than 6,000 so that the IDs do not conflict with those of the basic functoids.
- After setting the ID, you can set further properties of the functoid, including the name, tooltip text, description, and even the icon that is displayed in the toolbox.
setMinParams() methods allow you to define the maximum and minimum parameters for a functoid. These values will be used for the restrictions while compiling a map.
- Next to the parameters is the setting of the parameter types. This basically allows you to specify the type of values that each parameter will expect. In this case, you can allow everything (except the records) as input as well as output parameters because the functoid expects a single value and not a record, which contains multiple nodes.
- The next thing to do is set up the functoid category that determines where the functoid will fit in the toolbox.
- That leaves only the most important part: specifying the method that will do the actual job for the functoid. This is specified by the SetExternalFunctionName method. The method expects the complete path to the function, including the assembly name, the namespace and class, and the actual function name.
To use the functoid in the BizTalk Mapper, just GAC the compiled assembly and copy it to the Mapper extensions folder under the developer tools folder. This allows Visual Studio to "see" the assembly. After that, just add it to the toolbox by right clicking on toolbox and selecting add/remove items. With this, you should be able to see the functoid in the toolbox and use it just like any other.
In similar ways, a custom functoid can assist in various ways while doing the transformations. This can include Web service calls, logging functionality, database interactions, and several other things that would otherwise be quite complicated with the normal set of functoids.