March 27, 2010

Sorting products by name in ASPDotNetStorefront with language support

Filed under: Tips — Tags: , — Webopius @ 1:38 pm

OK, not the most elegant article title but it seemed like the best way to describe the issue. If you saw our recent article about how to implement sorting product results by price in ASPDNSF you’ll see that we mentioned sorting results by name and how this was a harder problem to solve.

Multiple language product result sorting in ASPDotNetStorefront

Harder? You might be thinking that nothing is easier than sorting by Product name. In fact, the default ASPDNSF_GetProducts() Stored procedure sorts by product name if no other sort entity is specified.

The problem comes when your site supports multiple languages. An English ‘shirt’ is a German ‘hemd’ and this demands that product results are returned in a different order if sorting by name.

In a single language store, ASPDotNetStorefront stores the product name as you would expect as ‘Product Name’. In a multi-language store, each name field contains all language versions of the product name like this:


<ml><locale name="en-US">English language Name</locale><locale name="en-DE">German language Name</locale></ml>

In such an environment, a standard SQL ‘order by name’ will not work so something more advanced is required.

Use XML within SQL Server

The solution is to use SQL Server’s powerful XML and XPath processing to extract the correct version of the product name for sorting.

If you read the previous article, to add sorting by name, you need to add some additional sortentity values as follows:


if @sortEntity = 2000 /* Ascending name */
begin
  insert into @sort(ProductID)
  select p.ProductID from Product p with (NOLOCK) 
  join ProductCategory pc with (NOLOCK) on p.ProductID = pc.ProductID
  where p.Published = 1 and p.Deleted = 0 
  and pc.categoryID = @categoryID
  order by CAST(CAST(p.Name as xml).query('data(//locale[@name=sql:variable("@localeName")])')  as varchar(200))
end
else if @sortEntity = 2001 /* Descending name */
begin
  insert into @sort(ProductID)
  select p.ProductID from Product p with (NOLOCK)
  join ProductCategory pc with (NOLOCK) on p.ProductID = pc.ProductID 
  where p.Published = 1 and p.Deleted = 0 
  and pc.categoryID = @categoryID
  order by CAST(CAST(p.Name as xml).query('data(//locale[@name=sql:variable("@localeName")])')  as varchar(200)) desc
end

This code converts the ASPDotNetStorefront product name into XML and then performs an XPath query on it to extract the correct product name based on the Customer’s locale (@localeName is passed in as a parameter to the Stored Procedure you created previously).

If you need assistance with implementing a solution such as this within your own ASPDotNetStorefront store, then please get in touch. Based in the UK, Webopius have been building and extending ASPDotNetStorefront solutions since version 7 for a variety of global customers.

  • Tags