Sorting products by name in ASPDotNetStorefront with language support
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.
