Purge all products on a Shared Server

[BBHartley]

Store is hosted with AC on a shared server so no direct access to the data files. DataPort does not give one the ability to purge & replace data. I am uploading a complete AC_Products file in XML format using DataPort.

This will be done regularly so I will need something easily repeatable. I must be missing something obvious, but search did not come up with anything I can apply.

Thanks.

[AbleMods]

DataPort does not give one the ability to purge & replace data.

That would be incorrect. Dataport will replace data so long as the ProductId value is supplied and matches an existing product record.

The bigger question is why you would break your store with every purge and upload. All of your orders, line items, wishlist items, discounts, coupons etc are all based on the ProductId value associated with each catalog item.

If you constantly delete and recreate these items, you'll break every product URL in your store. The ProductId value is part of each product URL. Your entire site SEO will drop through the floor since the spiders won't be able to keep up with your constantly changing product URLs.

Dataport can easily be used to UPDATE your store catalog. Simply download the store products, make whatever updates you require and upload it back.

[BBHartley]

I guess I should have been clearer. We are still testing and yes, I do need a way to delete all items. We are uploading multiple price levels via the AC_Specials table. We do not care about spiders. All access to the site is restricted to 400 predefined customers. The nature of the business requires frequent mass changes and it would be far more efficient to do a total replacement each time. This is for wholesale manufacturing not retail.

That said, is there a simple way to delete all AC_Products and all AC_Specials on a shared hosting server. I can do it with no problem on our self-hosted server.

[AbleMods]

That said, is there a simple way to delete all AC_Products and all AC_Specials on a shared hosting server. I can do it with no problem on our self-hosted server.

No, not in the default AC7 system. You would have to write a custom .Net db script to execute the T-SQL. I believe there a document in the Able Wiki on how to send custom SQL commands straight to the db.

[sohaib]

Its better not to delete products with direct SQL queries. You may fail to delete associated objects or take necessary cleanup actions. The best way to do this is to write a simple code like this

Code: Select all

ProductCollection products = Token.Instance.Store.Products;
foreach(Product prod in products)
{
   prod.Delete();
}

This will delete all products in the store.

[BBHartley]

Sohaib,

Thank you for the code. It works perfectly. Now I need to kill AC_Categories and AC_Specials.

I have tried to find the correct references in the CommerceBuilder API and Database Schema but have not come up with a workable solution yet.

Any additional guidance would be appreciated.

Bly Hartley

[mazhar]

AC_Categories

Code: Select all

 CommerceBuilder.Catalog.CategoryCollection categories = CommerceBuilder.Catalog.CategoryDataSource.LoadForStore();
        foreach (CommerceBuilder.Catalog.Category category in categories)
            category.Delete();

AC_Specials

Code: Select all

 ProductCollection products = ProductDataSource.LoadForStore();
        foreach (Product product in products)
        {
            product.Specials.DeleteAll();
            product.Save();
        }

[BBHartley]

Thanks for the very prompt reply. I will give it a try.

[BBHartley]

Mazhar,

With your assistance, I have been successful in deleting all Products, Categories and Specials. I thought that deleting the CatalogNodes and Parents would be a similar process, but I have been unsuccessful doing this on my own.

My XML upload is failing to upload Category data due to the presence of the old CatalogNodes and Parents.

I would greatly appreciate the proper syntax for these.

The last step in my "cleanup" process would be to reset the autoincrement to 0 for the ac_Products and ac_Categories. We have another self-hosted site and can handle this entire process with simple SQL commands. Without direct access to the server data for this particular store, this "purge" procedure is going to be an ongoing process.

I know I need a DBCC CHECKIDENT (ac_Products, reseed) but have not been able to derive the correct syntax here either.

I have gone back and read all posts for 7.0 with any references that might be of help, but I am finding myself having to ask for your help in finishing this up.

Thank you again for staying with me on this one!

[mazhar]

Try the following code for deleting catalog nodes

Code: Select all

protected void Page_Load(Object sender, EventArgs e)
    {
        CommerceBuilder.Catalog.CatalogNodeCollection catalogNodes = CommerceBuilder.Catalog.CatalogDataSource.LoadForCategory(0, false);
        foreach (CommerceBuilder.Catalog.CatalogNode catalogNode in catalogNodes)
            catalogNode.Delete();
    }

[BBHartley]

Mazhar,

Thank you once again. I was close on my own, but now it works.

Lastly can you point me in the right direction on the SQL DBCC CHECKIDENT programmaticly? The only reference I have found so far is the affilliate.cs in the Wiki.

Many thanks.