Bulk Import From Third Party Systems Using The Ucommerce API

Ucommerce is designed to run in one of two scenarios: Standalone or integrated. When running in an integrated scenario you’ll typically need to import information from other systems like SKUs, preexisting accounts, etc..

If you need to import large amounts of data from external systems you’ll want to the stateless API Ucommerce provides for just this type of scenario.

You can skip the background portion if you’re just looking for the quickest way to import data into Ucommerce. If not read on and get the inside scoop on Ucommerce data access.

Background

Ucommerce uses NHibernate for data access a handy way of querying the underlying database and converting the relational data into the e-commerce objects you know and love.

To provide a more convenient way of working with the data access APIs NHibernate will build up a large map of every object ever loaded into memory, which is awesome for caching scenarios and for figuring out which objects require updates and in which order when a write command is issued to the data access engine, but not so much for bulk imports.

As it turns out this is not really what you want for bulk scenarios as the object map tends to slow down writes quite a bit after only a few database operations. This slows bulk import scenarios quite noticeably; even a few hundred write commands.

Luckily you have at your disposal an API optimized for bulk import scenarios called the stateless API.

The Stateless API

The stateless API is named so because it doesn’t maintain the memory map of objects, which is exactly what we want for bulk imports because generally we’re just inserting and updating each object once. We don’t really need L1 or L2 caching, nor identify map tracking and that good stuff.

Using the stateless API offers awesome performance for writes compared to the default one, but it does come with a couple of drawbacks as well, which means there are a couple of areas you’ll have to handle yourself:

• Lazy loading child collections is disabled
• ModifiedBy, ModifiedOn, CreatedBy, and CreatedOn properties are not automatically updated
• Cascades for saves and deletes are disabled
• Auto soft delete is disabled – everything is deleted permanently
• Save is disabled and replaced by manual Insert/Updates operations
• Fear not the rest of the article describes how to accomplish these operations in a straightforward manner.

First things first: To use the stateless API you need an implementation of ISessionProvider, which is obtained with the following code:

    ISessionProvider sessionProvider = ObjectFactory.Instance.Resolve<ISessionProvider>();
    IStatelessSession statelessSession = (sessionProvider as SessionProvider).GetStatelessSession();
    

With the session you’re ready to start issuing commands to the data access layer in a stateless fashion.

Eager Loading Child Collections

By default Ucommerce will automatically load any child collections you access, which is very convenient when you want something up and running rapidly. This capability however is not available when working with the stateless API, so you’ll have to initialize any collection you need manually.

QUICK TIP: This is actually the way to get the best level of performance from your queries too.

Here’s how you eager load order lines for a purchase order.

    var purchaseOrder = statelessSession
        // The Query method is an extension method found in the NHibernate.Linq namespace
        .Query<PurchaseOrder>()
                    // The fetch method receives a lambda which tells if which child collection to initialize
        .Fetch(x => x.OrderLines)
                    // You can use ThenFetch to initialize subsequent child collections on the same parent
        .ThenFetch(x => x.OrderAddress)
                    // Loads the first parent
        .SingleOrDefault(x => x.OrderNumber == "WEB-001");
    
    

The code above will issue one query batch to the database instead of opening and closing a connection for each round trip as is the case for lazy initialization, so much more efficient all around.

Updating ModifiedOn, ModifiedBy, CreatedBy, CreatedOn Properties

These properties are required by objects implementing the IAuditCreatedEntity and IAuditModifiedEntity. You just need to make sure that they are set for any objects you insert.

Handling Delete Cascades

When you delete an object which has relationships with other objects using the default API Ucommerce will handle removing or updating the related objects, e.g. PurchaseOrder.Delete() also deletes associated order lines, customer, addresses, etc..

With the stateless API, however, you have to delete the objects yourself in the correct order to avoid foreign key constraint errors in the database. It works exactly as if you’re deleting directly from the database using T-SQL.

This means that for our order from before you’ll have to delete the order lines and customer before deleting the parant order, which both the order lines and the customer a link to in the underlying data structure.

    using (var transaction = statelessSession.BeginTransaction())
    {
        var purchaseOrder = statelessSession.Query<PurchaseOrder>()
            .Fetch(x => x.OrderLines)
            .ThenFetch(x => x.OrderAddresses)
            .ThenFetch(x => x.Customer)
            .ThenFetch(x => x.Payments)
            .ThenFetch(x => x.Shipments)
            .SingleOrDefault(x => x.OrderNumber == "WEB-001");
    
        // Issue a delete for each shipment
        // The ForEach method is just for convenience
        purchaseOrder.Shipments.ForEach(x => statelessSession.Delete(x));
    
        // Issue a delete for all child objects of order until they're all deleted
    
        // Finally delete the order itself
        statelessSession.Delete(purchaseOrder);
    
        transaction.Commit();
    
    }
    
    

Handling Saves

With the default API Ucommerce will figure out whether to insert or update an object when you issue a save. This is not the case with the stateless API so you’ll have to issue an insert or update depending on whether you’re updating an existing object or adding a completely new one.

    using (var transaction = statelessSession.BeginTransaction())
    {
        // Update an existing order
        var purchaseOrder = statelessSession.Query<PurchaseOrder>()
            .SingleOrDefault(x => x.OrderNumber == "WEB-001");
    
        purchaseOrder.ModifiedBy = "Søren";
        purchaseOrder.ModifiedOn = DateTime.Now;
    
        // Save to the database
        // Notice the explicit use of Update for
        // the existing object
        statelessSession.Update(purchaseOrder);
    
        // Create a new order
        var newOrder = new PurchaseOrder();
        newOrder.CreatedOn = newOrder.ModifiedOn = DateTime.Now;
        newOrder.CreatedBy = newOrder.ModifiedBy = "Søren";
        newOrder.BillingCurrency = statelessSession.Query<Currency>()
            .Single(x => x.ISOCode == "EUR");
    
        // Save to the database
        // Notice the use of Insert for the new object
        statelessSession.Insert(newOrder);
    
        transaction.Commit();
    }
    
    

Handling Soft Deletes

Some entities are marked with the ISoftDeletable interface, which indicates that they never really disappear from the database. The stateless API will permanently delete data from the database. If you want to preserve soft delete behavior you just have to set the “Deleted” property to “true” on objects which have it and issue a save like above.

In Summary

While there are some aspects you have to manually handle when using the stateless Ucommerce API for bulk updates it’s still a convenient way to stay in the object oriented world while keeping decent write performance.

Of course all the Ucommerce information is stored in standard SQL tables so you can just as well use a tool like SQL Server Integration Services, which bypass the API completely and go straight to the database. This is by far the fastest way to import, but bear in mind that integrations done directly against the database might break when you upgrade to newer versions of Ucommerce.