Bulk updating Commerce catalog entries
While working on a new site, I needed to do a batch update on all my catalog entries (products and variants). Seems simple, but there is one pitfall you might end up in.
In my case I had to do some simple string replacement in the Code column in the CatalogEntry table. Simple enough, some basic SQL - we can all do that. The weird thing is, after executing my SQL and recycling the application pool to clear caches, I was still looking at old data in EPiServer. The reason for this is the SerializedData column, which holds all data for that entry in a serialized form.
A simple fix for this, was simply to UPDATE dbo.CatalogEntry SET SerializedData = NULL. When decompiling the source from Commerce it seems that SerializedData is inspected before the normal columns for caching the catalog entry objects. If it's empty, however, it uses the raw columns instead. And as far as I've experienced, it's just as fast. And also, the first time you're saving the catalog entry after doing this, the SerializedData column will be repopulated. In the end, no harm done.
I've been working with the site for a good few months after I did this without having any side effects, so to me it looks safe. However, I do not take any responsibility for data loss you experience. Backup your database before trying and code fearlessly.
EPiServer: Maybe you should look into removing this column completely? I'm guessing it remains from the old Mediachase system, but now that everything is included in the cache that you talk to via IContentLoader - is this column really necessary? Also, it always falls back to the raw column values in the database. Removing it would make batch updates far, easier - and far more intuitive to work with as a developer. Plus it could save some bytes of space when the database grows bigger.