We’ve always wondered why the standard ASPDotNetStorefront templates and database procedures don’t offer the Customer the chance to filter and sort their product results by price (low>high or high>low) or to show sale items first or even sort by product name.
In standard configuration, ASPDotNetStorefront supports sorting by the ‘display order’ field which you can configure in the Admin screens. However, many Customers and store Business owners ask for a much more flexible range of dynamic sort options for their ASPDNSF sites… and other e-commerce systems offer this capability out of the box.
In this article, we’ll show you the steps necessary to enhance you ASPDotNetStorefront system with price sorting. Sorting by name is a little more complex because of multi-language support so we’ll be writing another post about this later.
You can use the techniques here to add sorting by any field you wish. For example, we have used this method in ASPDotNetStorefront sites to add:
- Sort by Sale item products first
- Sort by Publication date (for books)
- Show Featured items first (using the product type attribute to flag particular items as featured)
- Sort by product price
- Sort by product name (with multi-language support – different sort results depending on the language)
- Sort by Author name (for books)
Step 1. Create a new aspdnsf_GetProducts() database procedure
The first part of making this enhancement is to create a new version of the standard aspdnsf_GetProducts() stored procedure. If you have ASPDotNetStorefront ML7.x, you can use SQL Server management studio to view the code of this procedure. If you are using ML8.x, you will need to look at the database install scripts (file Create AspDotNetStorefront database.sql).
We recommend creating a new procedure rather than editing the existing procedure because future ASPDotNetStorefront site upgrades are much simpler this way as you are not changing core source code.
So, create a new procedure called something like CUSTOM_GetProducts() and copy the original aspdnsf_GetProducts() procedure. Because this is part of the original ASPDNSF source code, we are unable to show you the full code here but will walk you through the process. If you need these changes made for you, please contact us and we can usually have the full price sorting solution implemented and tested on your ASPDotNetStorefront store.
Step 2. Change the sort code
If you look at your code, you will see a table called #DisplayOrder being declared and below this, some code that inserts specific values the #DisplayOrder table depending on the value of @sortEntity.
All of this code needs to be replaced with a new sort method. We will still use the @sortEntity field but will sort on price ascending if @sortEntity=1000 and on descending price if @sortEntity=1001. The #DisplayOrder table will be replaced with a new sort table called @sort.
First, remove the declarations for #DisplayOrder and create a new declaration for your new @sort table like this:
declare @sort table(DisplayOrder int not null identity primary key,ProductID int)
Basically this creates a table of ProductIDs. As each @sort row is inserted, SQL Server auto generates a primary key (DisplayOrder) that we will use in a join later to ensure product results come back in the correct order.
Now, replace the old #DisplayOrder logic with some new sort logic like this:
if @sortEntity = 1000 /* Ascending price */
begin
insert into @sort(ProductID)
select p.ProductID from Product p with (NOLOCK)
join ProductVariant pv with (NOLOCK) on p.ProductId = pv.ProductId
join ProductCategory pc with (NOLOCK) on p.ProductID = pc.ProductID
where p.Published = 1 and p.Deleted = 0 and pc.categoryID = @categoryID
and pv.IsDefault = 1 order by pv.Price
end
else if @sortEntity = 1001 /* Descending price */
begin
insert into @sort(ProductID)
select p.ProductID from Product p with (NOLOCK)
join ProductVariant pv with (NOLOCK) on p.ProductID = pv.ProductID
join ProductCategory pc with (NOLOCK) on p.ProductID = pc.ProductID
where p.Published = 1 and p.Deleted = 0 and pc.categoryID = @categoryID
and pv.IsDefault = 1 order by pv.Price DESC
end
As you can see, if @sortEntity=1000, the @sort table is populated with published products in ascending price order. If @sortEntity=1001, the table is populated with products in descending price order. Both use the default variant pricing – change this if you need to.
Note: the above sort code filters based on the current category. If you are using Manufacturer or Section (Department) based sorting, you’ll need to add these filters to your sort options.
Step 3. Use the new sort code
Having created a new sort table, you now need to modify the section of the procedure referring to @productfilter to use the @sort rather than #displayorder table.
Look for the INSERT @productfilter code and…
1. Replace the join on #displayorder line with:
join @sort sort on sort.ProductID = p.ProductID
2. Replace ‘order by do.displayorder’ with:
order by sort.DisplayOrder
You will need to make the above changes twice as the @productfilter table is created in two parts of an if clause depending on inventoryfilter value.
Step 4. Use your new sort within product category templates
Now you’ve written your new sort method, you can use it within your ASPDotNetStorefront category templates. Typically, we use a drop down menu control with the various sort options. We also pass &sort=1001 or &sort=1002 via the page URL because when paging between product results, your sort parameter will also need to be used.
As you can see, the procedure supports adding more sort options later, such as 2000 for ascending name, 2001 descending name, 3000 for sale items first and so on. In the next article, we’ll show you how to write a custom sort by name that takes account of the ASPDotNetStorefront locale so that products are sorted correctly as the store language changes.
If you would like a solution such as this implemented on your ASPDotNetStorefront site, or would like to discuss other ideas, please contact Webopius. We are one of the leading UK-based web development companies with extensive ASPDotNetStorefront experience.
