none
Should Commerce Server make queries to MS SQL Database?

    Question

  • I am new to Commerce server 2009. Currently I have a sql database that contains special shipping override on the product base on the supplier shipping rules. total shipping calculated by the total of each supplier's shipping rules. I am trying to understand how will the structure be applied in the commerce server?

    I come up with a couple of suggestions and want to know if I am on the right track. Should I use the commerce server to query ms sql server externally?

    Or can I create a new table within the commerce server call supplierShippingOverride?

    Is there a better solution?

    Thanks in advance

    Saturday, May 29, 2010 12:22 AM

Answers

  • Hi Jonathan,

    The challenge in your case is that Commerce Server by design expects all Shipping Methods (and Payment Methods) to be in the [siteName]_transactionconfig database - having them configured in Customer And Orders Manager. These tables are not extendable making it difficult having own business rules like custom prices, custom logic etc.

    When adding line items to the basket, you have to specify the ShippingMethodGroupId (Guid) on each line item:

    var lineItem = new CommerceEntity("LineItem");
    
    lineItem.SetPropertyValue("ProductId", "001-100-20");
    lineItem.SetPropertyValue("Quantity", 1);
    lineItem.SetPropertyValue("CatalogName", "Test");
    
    // Must be set in order to have Total pipeline executed
    lineItem.SetPropertyValue("ShippingMethodId", "54e5dc14-9a36-47a2-a659-47264aca0296"); // GroupId on Shipping Method
            
    basketUpdateQuery.RelatedOperations.Add(
    	new CommerceCreateRelatedItem<CommerceEntity>("LineItems", "LineItem") { Model = lineItem });

    Commerce Server 2009 Foundation API will not execute the Total and Checkout-pipeline if one of the following rules do not apply:

    • No Shipping Method found in database based on the ShippingMethodId specified on the line item
    • One or more line item have no Shipping Method specified
    • Status on Basket is not set to "ReadyForCheckout"
    • Basket pipeline execution did not return PipelineExecutionResult.Success

    The Total pipeline then have two pipeline components (Commerce.Splitter and Commerce.ShippingMethodRouter) that are responsible of creating one or more actual instance(s) of the Shipment class that gets added to the ShipmentCollection on the Basket/OrderForm. More info here: http://msdn.microsoft.com/en-us/library/bb219254(CS.70).aspx

    This means that you would properly have to create a custom pipeline component for calculating Shipping Costs - replacing the Commerce.StepwiseShipping component.

    So here is what I did on a Commerce Server 2007 project where we had Shipping Prices outside Commerce Server. I basically removed the two previously mentioned pipeline components (Commerce.Splitter and Commerce.ShippingMethodRouter) from the Total pipeline, and created the Shipment instance myself and added that to the ShipmentCollection on the Basket/OrderForm _before_ any pipelines were executed. I had to specify the ShipmentTotal manually, but this was also the whole purpose of the thing, as we get prices from an external system (ERP system in this case).

    The code for doing this in CS 2007 would look something like:

    var basket = CommerceContext.Current.OrderSystem.GetBasket(Guid.NewGuid());
    basket.OrderForms.Add(new OrderForm());
    
    var shipment = new Shipment();
    shipment.ShipmentTotal = 100m;
    
    basket.OrderForms[0].Shipments.Add(shipment);
    
    // run pipelines
    
    basket.Save();

    This works great in Commerce Server 2007 and we don't need to have any dummy shipping methods in the transactionconfig database, saving some database-roundtrips and extra complexity. And we were able to extend the Shipment class with new strongly typed columns/properties like any other types in the Orders System.

    In Commerce Server 2009 though, you need to do some plumbing getting this working as you never actually work with the "real" objects/types in the Orders System - you work with a query and ICommerceEntity objects having the Operation Sequence components doing the actual work on the underlying Orders System.

    You need to create a new Operation Sequence Component class that does the actual work with the Orders System for the Query, Delete and Update scenario. Also you need to create a new Translator class for Shipment that can translate from a ICommerceEntity object to a Shipment object. These classes needs to be registred in the ChannelConfiguration.config file.

    This will allow you to create a Commerce Server 2009 query that could look like the following:

    var shipment = new CommerceEntity("Shipment");
    shipment.SetPropertyValue("Total", 300m);
    
    var relatedShipment = new CommerceCreateRelatedItem<CommerceEntity>("Shipments")
    {
    	Model = shipment
    };
    
    basketUpdateQuery.RelatedOperations.Add(relatedShipment);

    I actually have a proof of concept working like this with Commerce Server 2009 supporting custom Shipment Methods - and I'm currently working on writing a series of articles with this as one of the topics on my blog (http://blog.brianh.dk/CategoryView,category,Commerce%2BServer.aspx).

    So to sum up; I would suggest having your prices/rules in the external database, remove the two pipeline components, and extend the Operation Sequence that updates the basket (CommerceUpdateOperation_Basket) with a new Operation Sequence Component class.

    With this solution you don't need to develop any new pipeline components - as working with Commerce Server pipeline is very low-level (no type-safetyness, lots of magic strings) and generally not exactly happy-land (it's COM).

    I hope you can use my input - otherwise feel free to ask :-)

    Best Regards,

    Brian Holmgård Kristensen
    http://blog.brianh.dk


    ---
    June 21st - 25th we are hosting a 5 day Commerce Server Deep Dive developer course in Luton, UK. The site is only 25 minutes outside London and 5 minutes from London Luton Airport. Read more on http://www.commerceservertraining.com/events/commerce-server-course-luton,-uk.aspx

    Saturday, May 29, 2010 11:56 AM

All replies

  • Welcome to Commerce Server development! It is a great eCommerce platform to use once you build up your knowledge.

    Shipping Override? I'm assuming that you're talking about the rates being applied to the shipment have special conditions that are outside of what Commerce Server's shipping rate structure (e.g. by subtotal, by item count) can handle.

    In this case, you can leverage to simply hold your shipping methods and then create a WCF based service that your NEW Pipeline shipping component will call. This service, its business logic for calculating shipping methods and underlying datastore are then under you're control. It simply returns the rate for the shipping to be applied to the order.

    It really is that simple. Therefore, create your service and then do some research on how to build custom pipeline components. Your new shipping component will be placed in the total pipeline.

    Enjoy.


    Mark McCrimmon - www.devwerx.com - Commerce Server Specialists
    • Proposed as answer by Mark McCrimmon Saturday, May 29, 2010 11:28 AM
    Saturday, May 29, 2010 11:28 AM
  • Hi Jonathan,

    The challenge in your case is that Commerce Server by design expects all Shipping Methods (and Payment Methods) to be in the [siteName]_transactionconfig database - having them configured in Customer And Orders Manager. These tables are not extendable making it difficult having own business rules like custom prices, custom logic etc.

    When adding line items to the basket, you have to specify the ShippingMethodGroupId (Guid) on each line item:

    var lineItem = new CommerceEntity("LineItem");
    
    lineItem.SetPropertyValue("ProductId", "001-100-20");
    lineItem.SetPropertyValue("Quantity", 1);
    lineItem.SetPropertyValue("CatalogName", "Test");
    
    // Must be set in order to have Total pipeline executed
    lineItem.SetPropertyValue("ShippingMethodId", "54e5dc14-9a36-47a2-a659-47264aca0296"); // GroupId on Shipping Method
            
    basketUpdateQuery.RelatedOperations.Add(
    	new CommerceCreateRelatedItem<CommerceEntity>("LineItems", "LineItem") { Model = lineItem });

    Commerce Server 2009 Foundation API will not execute the Total and Checkout-pipeline if one of the following rules do not apply:

    • No Shipping Method found in database based on the ShippingMethodId specified on the line item
    • One or more line item have no Shipping Method specified
    • Status on Basket is not set to "ReadyForCheckout"
    • Basket pipeline execution did not return PipelineExecutionResult.Success

    The Total pipeline then have two pipeline components (Commerce.Splitter and Commerce.ShippingMethodRouter) that are responsible of creating one or more actual instance(s) of the Shipment class that gets added to the ShipmentCollection on the Basket/OrderForm. More info here: http://msdn.microsoft.com/en-us/library/bb219254(CS.70).aspx

    This means that you would properly have to create a custom pipeline component for calculating Shipping Costs - replacing the Commerce.StepwiseShipping component.

    So here is what I did on a Commerce Server 2007 project where we had Shipping Prices outside Commerce Server. I basically removed the two previously mentioned pipeline components (Commerce.Splitter and Commerce.ShippingMethodRouter) from the Total pipeline, and created the Shipment instance myself and added that to the ShipmentCollection on the Basket/OrderForm _before_ any pipelines were executed. I had to specify the ShipmentTotal manually, but this was also the whole purpose of the thing, as we get prices from an external system (ERP system in this case).

    The code for doing this in CS 2007 would look something like:

    var basket = CommerceContext.Current.OrderSystem.GetBasket(Guid.NewGuid());
    basket.OrderForms.Add(new OrderForm());
    
    var shipment = new Shipment();
    shipment.ShipmentTotal = 100m;
    
    basket.OrderForms[0].Shipments.Add(shipment);
    
    // run pipelines
    
    basket.Save();

    This works great in Commerce Server 2007 and we don't need to have any dummy shipping methods in the transactionconfig database, saving some database-roundtrips and extra complexity. And we were able to extend the Shipment class with new strongly typed columns/properties like any other types in the Orders System.

    In Commerce Server 2009 though, you need to do some plumbing getting this working as you never actually work with the "real" objects/types in the Orders System - you work with a query and ICommerceEntity objects having the Operation Sequence components doing the actual work on the underlying Orders System.

    You need to create a new Operation Sequence Component class that does the actual work with the Orders System for the Query, Delete and Update scenario. Also you need to create a new Translator class for Shipment that can translate from a ICommerceEntity object to a Shipment object. These classes needs to be registred in the ChannelConfiguration.config file.

    This will allow you to create a Commerce Server 2009 query that could look like the following:

    var shipment = new CommerceEntity("Shipment");
    shipment.SetPropertyValue("Total", 300m);
    
    var relatedShipment = new CommerceCreateRelatedItem<CommerceEntity>("Shipments")
    {
    	Model = shipment
    };
    
    basketUpdateQuery.RelatedOperations.Add(relatedShipment);

    I actually have a proof of concept working like this with Commerce Server 2009 supporting custom Shipment Methods - and I'm currently working on writing a series of articles with this as one of the topics on my blog (http://blog.brianh.dk/CategoryView,category,Commerce%2BServer.aspx).

    So to sum up; I would suggest having your prices/rules in the external database, remove the two pipeline components, and extend the Operation Sequence that updates the basket (CommerceUpdateOperation_Basket) with a new Operation Sequence Component class.

    With this solution you don't need to develop any new pipeline components - as working with Commerce Server pipeline is very low-level (no type-safetyness, lots of magic strings) and generally not exactly happy-land (it's COM).

    I hope you can use my input - otherwise feel free to ask :-)

    Best Regards,

    Brian Holmgård Kristensen
    http://blog.brianh.dk


    ---
    June 21st - 25th we are hosting a 5 day Commerce Server Deep Dive developer course in Luton, UK. The site is only 25 minutes outside London and 5 minutes from London Luton Airport. Read more on http://www.commerceservertraining.com/events/commerce-server-course-luton,-uk.aspx

    Saturday, May 29, 2010 11:56 AM