Permanently drop prices of all products

This is an unusual post – as I usually don’t post sample code – that should be the job of the documentation. However, I jumped upon this question, and found it to be an interesting case to demo.

It’s worth noting that as a customer, I’d like price drops, not the way around, so in this example, we will see how to cut prices of all products to 5%, instead of making them 5% more. Of course, it’s just simple mathematics, so you can change to the formula however you want.

Let’s go with the fast method first. The prices are stored in two systems, as we already know, IPriceDetailService and IPriceService. The unit prices are saved in PriceDetail, and PriceValue tables, respectively. Luckily for us, changing the unit prices does not involve reference tables and such, therefore it’s a simple query

UPDATE PriceDetail SET UnitPrice = 0.95 * UnitPrice WHERE 1=1

UPDATE PriceValue SET UnitPrice = 0.95 * UnitPrice WHERE 1=1

This should be very fast – but as always, this is something you should avoid. For starter, the prices are cached by default, those these changes are not visible in the application layer, unless you do a cache cleanup (read, IISRESET) for your site. Also, in next version of Commerce, the database schema might change, render that queries above invalid, or even harmful. This is an example of “Because you can, does not mean you should”. You should know about the options, but don’t use it until absolutely necessary. Even then, proceed with cautions.

Now let’s go with the recommended way. Using official APIs is better in almost every way – it’s supported, it is backward compatible, it takes care of the cache and any database schema changes, so you don’t have to. It’s also much more flexible than the above approach, so for example if you only want to update prices for products from specific provider, you can easily do so. It’s, in most of the cases, fast enough.

To update prices of all products, we should go with recursive approach – given a starting point, we will get all children. If a children is a product, update its prices. If it is a node, then continue until the end.

Some interesting notes:

  • Here we are caring only products, and ignore packages and standalone SKUs. However updating their prices should be easy enough – I will leave it as homework.
  • By default, getting prices of a product will return all prices of its variants. (Product itself should have no prices), so we only have to update once, even if the product has 20 or more variants.
  • Even more interesting, if you call IPriceDetailService.List on a category, it will load all prices of all variants, packages in that category. However we want to avoid that, because it does not work recursively (the children categories will be ignore). Also, there are cases when the category is very big – contains thousand or tens of thousands products, and doing that might result in a timeout. We should make sure that our code work in any case. It’s true that a product can have multiple variants, but it’s very unlikely to have more than 100 variants, so it should not be a concern.

Now with this code, we can simply pass the starting point to UpdatePrices method and let it does the job. You can pass the ReferenceConverter.GetRootLink() to update all prices, or a catalog/category ContentReference to update prices on that catalog/category only.

This approach of recursive traverse the catalog tree can be used for other purposes, not just updating prices.

*Code is provided as-is without any guarantee. You have to test yourself. Use with your own risks.*

It's only fair to share...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInShare on Reddit

2 thoughts on “Permanently drop prices of all products”

Leave a Reply

Your email address will not be published. Required fields are marked *