Introduction
When we were working on one of the BizTalk projects, we were facing difficulties because of not having a built in functoid that will have more than one return value. To overcome this issue, we came up with the following solution.
Problem Statement
Retrieve two field’s values from the database based on the input values. And the retrieved two field’s values should be assigned to the three different fields in the destination schema of the map. And all these activities should happen in the map.
Solution
1. Create a Method for Retrieving the Value from the Database
Given below is the sample code:
public static string GetSuppRecordForAP(string input1, string input2, string input3)
{
Database db = null;
IDataReader dr = null;
DbCommand cmd = null;
string suppQuery = string.Empty;
string SPSTID = string.Empty;
string outString = string.Empty;
String query = String.Empty;
Int32 orgId = 0;
try
{
db = DatabaseHelper.Instance.CreateDatabase("database1");
cmd = db.GetSqlStringCommand
("select out1,out2 from table where field1='" + input1 +
"' AND field2='" + input2 + "' AND field3='" + input3 + "'");
cmd.CommandType = CommandType.Text;
dr = db.ExecuteReader(cmd);
if (dr.Read())
{
outString = "out1~out2#";
outString += Convert.ToString(dr["out1"]) + "~" + Convert.ToString(dr["out2"]);
}
else
{
throw;
}
return outString;
}
catch
{
throw;
}
finally
{
if (dr != null)
{
dr.Close();
}
}
}
The output of the above method will be like out1~out2#value1~value2.
Note: Make sure that the special characters used will not be part of the data.
2. Create a Custom Functoid that will Accept 2 Input String Parameters
The custom functoid should accept 2 input parameters, and the first parameter is for accepting the string in the format out1~out2#value1~value2 and the second parameter is for receiving the name of the field whose value needs to be retrieved.
3. Sample Code for Main Function that will Help to Retrieve the Value in the Custom Functoid
public string ColumnValueExtractor(string fieldsAndValues, string columnName)
{
ResourceManager resmgr = new ResourceManager
("Sample.Functoid.KeyValueExtractor.KeyValueExtractorResource",
Assembly.GetExecutingAssembly());
string fields = string.Empty;
string values = string.Empty;
string[] fieldsArray = null;
string[] valueArray = null;
string result = string.Empty;
try
{
fields = fieldsAndValues.Split('#')[0];
values = fieldsAndValues.Split('#')[1];
fieldsArray = fields.Split('~');
valueArray = values.Split('~');
for (int i = 0; i < fieldsArray.Length; i++)
{
if (fieldsArray[i] == columnName)
{
result = valueArray[i];
return result;
}
}
return result;
}
catch
{
throw;
}
}
Note
Use this link for help with creating a custom functoid.
4. Use the Custom Functoid in the Map
You can find more details below:
Use a scripting functoid that will call the .NET method that is created in step 1. The figure is as shown below:
Figure 1.
Use the custom functoid that is created in step 2 & 3 and give the output of the above scripting functoid as the first parameter and the name of the field whose value needs to be retrieved as the second parameter. Find the figure as follows:
Figure 2.
Map the output of the custom functoid to the respective fields in the destination schema. Find the figure below:
Figure 3.
Note: The blue colored functoids are the new custom functoids created in steps 2 & 3. And based on your requirements, customize the methods used in the .NET component to retrieve the value and the customer functoid method.
History
