Thread: MS SQL Server General Questions/MS Commerce server - Aged Basket Cleaner

MS Commerce server - Aged Basket Cleaner

In commerce server, a basket is created either when the user logs in to the application or when the user tries to add first product to the cart. In reality, in the background we are creating an entry about user basket in transaction database – OrderTemplatesAndBaskets table. All information related to basket such as products, quantity, addresses, etc are stored in a column named “marshaled_data” as Binary large object (BLOB).

Note: You can’t read this data from select query rather you have to use customer and order manager tool to view this binary information.

All baskets will not become purchase orders. So, in an enterprise application with more than a million customers, you can see many unnecessary baskets created in the system. It is good practice to remove unnecessary baskets periodically. We have few ways to do it.

1. Simple and Ugly way: Delete unwanted data directly from OrderTemplatesAndBaskets table.

Delete OrderTemplatesAndBaskets Where LastModified <=  DATEADD (d, -30, GetDAte())


2. Use PurgeCommerceData.exe tool that comes out of box from commerce server. The syntax is

PurgeCommerceData <site_name> flag [options] 

For example, the below command purges all baskets that are been unchanged for last 30 days.

PurgeCommerceData site_name -b -d 30

Note: This option only works on the system where commerce server installs

3. Some times, we may have to run this tool from remote system (not on the production server directly) and in those situations, we can use commerce server agent mode API to perform this task. The below code snipped can be used to delete the baskets. You can create a service to do this clean up job regularly or create command line utility and schedule to run that utility on specific intervals.

string orderWebServiceUrl = "http://server/OrdersWebService/OrdersWebService.asmx";

DateTime agingDate = DateTime.Now.AddDays(-30);

var serviceAgent = new OrderServiceAgent(orderWebServiceUrl);

OrderManagementContext context = OrderManagementContext.Create(serviceAgent);

BasketManager basketManager = context.BasketManager;

// Create a search clause.

DataSet searchableProperties = basketManager.GetSearchableProperties(CultureInfo.CurrentUICulture.ToString());

SearchClauseFactory searchClauseFactory = basketManager.GetSearchClauseFactory(searchableProperties, "Basket");

agingDate = agingDate + DateTime.Now.TimeOfDay;

SearchClause clause = searchClauseFactory.CreateClause(ExplicitComparisonOperator.OnOrBefore, "Created", agingDate);

// Delete baskets that match the conditions.

int recordsDeleted;

basketManager.DeleteBaskets(clause, out recordsDeleted