Select Distinct Commerce Server Catalog SearchClause

 

In this case I needed to get a list of products from the Commerce Server catalog.

As you probably know, it is possible to get a catalog content from Commerce Server via BizTalk catalog adapter.

In this scenario I get a list of products which needs to be updated – but the client does not have all the mandatory fields.

To get all the product details I use the “CommerceServerCatalogQuery”  schema.

I came across this article which explains how to build “SeachClause” dynamically inside the BizTalk map.

   

The two main issues I found with the  article above are:

1.       Moving this function from map to map for each client could be a bit complicated (The xpath is messy and has to be manipulated for each scenario).

2.       The result “SearchClause” is not distinct.

  

Solution 

This is what I did to make it distinct (and maybe a bit more reusable).

1.       For each product in the source file – I call Inline C# function (“GetProductDistinctSearchClause”)

2.       I created two global variables (returnSearchClause,duplicateProdList).

3.       GetProductDistinctSearchClause decides if the product already exists in the list. Then it calls a private function (“GetSearchClause”)

4.       GetSearchClause returns an updated “SearchClause” string.

Breakdown Example

Source File example:

Source Example

 

Result File example:

Result File

 

 

Here is an example of a BizTalk map which builds a ““CommerceServerCatalogQuery”  message. 

How The Map Looks Like

As you can see there are 2 functoids in the map:

1.       Inline XSLT

2.       Inline C#

 

 

 

The Inline XSLT code:

 

Inline XSLT

 <xsl:for-each select=”/*[local-name()=’ExcelXLSWorkbook’ and namespace-uri()=’http://SIT.Schemas.ExcelRelationshipImport’%5D/*%5Blocal-name()=’Sheet1&#8242; and namespace-uri()=’http://SIT.Schemas.ExcelRelationshipImport’%5D%5B1%5D/*%5Blocal-name()=’Sheet1_Record&#8217; and namespace-uri()=’http://SIT.Schemas.ExcelRelationshipImport’%5D/*%5Blocal-name()=’ProductId&#8217; and namespace-uri()=’http://SIT.Schemas.ExcelRelationshipImport’%5D”&gt;
 <xsl:variable name=”varProdId” select=”.”/>
 <xsl:variable name=”varReturnValue” select=”userCSharp:GetProductDistinctSearchClause($varProdId)” />
 <xsl:attribute name=”SearchClause”><xsl:value-of select=”$varReturnValue” /></xsl:attribute>
</xsl:for-each>

 

The Inline C# code:

This is the inline C# code

 

Here is the code

public System.Collections.Generic.List<string> duplicateProdList = 

new System.Collections.Generic.List<string>();

public string returnSearchClause = string.Empty;

// public function which is being called from the inline XSLT script

public string GetProductDistinctSearchClause(string prodId)

{

    ProductExists productExists = ProductExists.ItemError;

    if(!duplicateProdList.Contains(prodId.Trim().ToLower()))

    {

        duplicateProdList.Add(prodId.Trim().ToLower());

        productExists = duplicateProdList.Count == 1 ? 

        ProductExists.ItemExistsFirstTime : ProductExists.ItemExistsRepeat; 

    }

    else

    {

        productExists = ProductExists.ItemAlreadyExists; // item is duplicate

    }

    returnSearchClause = GetSearchClause(prodId, productExists);

    return returnSearchClause;

}

private string GetSearchClause(string prodId,ProductExists productExists)

{

    if (productExists > ProductExists.ItemAlreadyExists)

    {

        if (productExists == ProductExists.ItemExistsFirstTime)

        {

            returnSearchClause = 

            string.Format("ProductId='{0}'", prodId.Trim());

        }

        else

        {

            returnSearchClause += 

            string.Format(" OR ProductId='{0}'", prodId.Trim());

        }

    }

    return returnSearchClause;

}

private enum ProductExists

{

    ItemError = -1,

    ItemAlreadyExists,

    ItemExistsFirstTime,

    ItemExistsRepeat

}

 

 Good luck, Omer