Commerce Server

Changing the underlying transaction database tables

Simon — Fri, 22 Jun 2007 09:21:00 GMT

One common error Ihave found which took ages to fix was:

Microsoft.CommerceServer.ServerFaultException: Table PurchaseOrders contains 26 columns, expected 25 columns according to mapping definition

The numbers might change depending on how many columns you have or a expecting in the table you are accessing. This problem is not to do with the mapping xml files or any caching issues.
The problem is that existing orders are summarises in the MarshalledData column and when you change the number of columns the existing orders wont work any more.
Luckily we have only needed to change the column lengths during development and can delete all the orders we have so next time it works.

I dont know what you would do if the live data was already populated and youneeded to extend the system. There might be a way to rewrite the correct marshalled data for the existing orders. Any ideas, feel free to post here.

Changing values on a Purchase order once it has been created

Simon — Tue, 08 May 2007 10:27:00 GMT

Once a purchase order has been created, it is sometimes useful to change the values of that purchase order, values on the lineitems or the shipping address for each lineitem.
An example of this might be where the delivery address changes at the point of delivery after the customer has placed the order.

To change the values on an order, you must first get an OrderManagementContext object by either creating an OrderServiceAgent or an OrderSiteAgent.

OrderSiteAgent ordersAgent = new OrderSiteAgent("mySiteId");
OrderManagementContext oContext = OrderManagementContext.Create(ordersAgent);

Using the context it is possible to get a dataset which contains many tables of information relating to the order, the addresses and the lineitems.

You can change the values of the datarows to reflect your changes and then update Commerce Server using the PurchaseOrderManager.UpdatePurchaseOrder method.

This method accepts the dataset you have edited and an applicationId.

The applicationId is one of the values found in the transactionConfig.StatusManager database table. The default values are Site and LOB Adapter.

If the purchase order is a new order, you can use the value "Site" in yor method. If the order is not, then you need to check the status of the purchase order. This will be one of the values in the table transactionConfig.AllowedStatus table.
The ApplicationId you use in your method, must be one of the applicationId's in the StatusManager tables and that ApplicationId must has permission to update the status that the order form is currently in.

If you are having problems updateing the dataset, this is most likely the reason. Add permissions for you applicationId to modify the Purchase order for the state the orderform is currently in.

OrderServiceAgent agent = new OrderServiceAgent(ConfigurationManager.AppSettings["ShopOrdersWebServiceURL"]);

OrderManagementContext oContext = OrderManagementContext.Create(agent);

PurchaseOrderManager poManager = oContext.PurchaseOrderManager;

DataSet purchaseOrder = poManager.GetPurchaseOrderAsDataSet(new Guid(ViewState["orderid"].ToString()));

DataTable lineItems = purchaseOrder.Tables["LineItems"];

foreach (DataRow drLineItem in lineItems.Rows)
{

   if (ViewState["lineitemid"].ToString().ToLower() == drLineItem["LineItemId"].ToString().ToLower())
   {
      string shippingAddressId = drLineItem["ShippingAddressId"].ToString();
      DataTable addresses = purchaseOrder.Tables["Addresses"];
      foreach (DataRow drAddress in addresses.Rows)
      {
            if (drAddress["orderAddressId"].ToString() == shippingAddressId)
            {
               //update the dataset
               drAddress["firstname"] = "Hello";
               poManager.UpdatePurchaseOrder(purchaseOrder, "Site");
               break;
            }
      }
      break;
   }
}

Dynamically Loading Product property definitions including their data type information.

Simon — Tue, 10 Apr 2007 14:14:00 GMT

Adding product definitions to Commerce Server is easy through the business tools. You can then add products that fit the product definitions. For example, if you create a product definition for a pair of jeans, then the product will have properties such as waist size, color and length. However, you would not want a wardrobe to have the same properties. You would create different definitions for each type of product and then match the product to the definition.

If you need to get the defintions out of CS, you can use the method

cContext.GetDefinitions()

This method is called on the Catalog Context and returns a typed dataset of product defintion attributes.

By using this method you can iterate through your product defintion attributes so you know what the attributes are called.

I am building an administration area where clients will be able to choose a category to load their products and choose the correct defintion automatically based upon the category. This means that HQ can define the catalog and category structure of Commerce Server, decide what products can go into each category, and workers can add products to only certain categories and catalogs and can be restricted by other business logic through a custom website.
This makes managing the site for large organisations much easier and more secure.

I have created a .NET user control that changes what .NET form control the user uses based upon the data type of the Product defintion property. For example a date time data type can display the calendar control, a text data type can display a text box etc.
This allows me to show the correct fields for data entry for each product and product defintion.

Unfortunately, the information returned in the dataset using the above method does not include the datatype of the properties. This means it is impossible to determine which type of control to display for each different data type.

I have therefore created a Data Access Layer that gets the information directly from the _productCatalog database.

I create the following stored proc:

--------------------

CREATE PROCEDURE [dbo].[getPropertyDefinitionsForSpecificProductDefinition]

@definitionName nvarchar(128)

BEGIN

select

ca.PropertyName,ca.DataType,ca.DisplayOnSite,ca.DisplayName,cdp.DefinitionId,cdp.PropertyOrder

from

CatalogAttributes ca

join

CatalogDefinitionProperties cdp

ca.PropertyName = cdp.PropertyName

join

catalogDefinitions cd

cd.DefinitionId = cdp.DefinitionId

where

cd.DefinitionName = @definitionName

------------------------

You can then retrieve the information you need from this and load the correct controls.

I have linked a specific product definition to a category by a custom property in the category defintion. I only need to be able to define one product defintion per category so this works very well.

The user chooses the category of product, the product defintion is loaded by using its name.
The name is obtained from the custom property of the category the user has chosen.
I then iterate through the dataset returned from this stored procedure and display the appropriate control.

Uploading Images into Commerce Server

Simon — Wed, 04 Apr 2007 09:28:00 GMT

I was quite suprised to find that CS2007 does not support uploading images for products through the business tools. This is a basic requirement for all E-Commerce sites to display images of the products they are selling. I have thought about some possible solutions to this common problem and have briefly outlined some of them here:

The solution seems to be to one of the following:

Use a custom property on the product to specify the image path and then upload the image via FTP. You could then get the image path for the product when it is loaded, and use this path to diaplay the appropriate image.

This has major drawbacks in terms of scalability because you have to know how many images for each product you will need and extending the number means adding more custom properties and coding on the website.

It also has the drawback of allowing the client FTP access to the server and making them use a seperate FTP client to load their images.

Another drawback of this approach is that if the client types in the filename wrong then the image will not be loaded.

When a site contains many thousands of products, the directory structure will become extremely untidy and images are not automatically removed when the product is sold or deleted.

Database structure is also not optimised at their will be a lot of redundancy with regards to space as some of the custom properties will remain empty as not every product will have the maximum number of allowed images.

This solution might work for small sites with less then a 100 products and a client who does not mind using FTP.

Create a bespoke web page upload using HTTP File Upload that adds a single path to an image folder for each product.

This solution mitigates against the problem of clients typing in the wrong filename and also allows better management of file structure.

It also allows a random filename to be applied to the image which helps with Spoofing attacks and helps complience with PCI guidelines.

By storing one path to a specific folder into commerce server, it also stops redundency in the database and allows scalability to allow as many images as is needed. Each product can have its own set of images in its own folder which will help with directory management.

Another benefit of using this approach is that you can automatically resize the images using a .NET Bitmap object so that all images are the same size and quality.

To manage multimple images for a single product with different sizes and different descriptions and alt tags for each one, it is possible to implemented an XML file for each folder (which in turn means each product) and store this in the folder with the images or in a custom property in CS.
In this way we can keep track of all the images for each product and what size should be displayed where, in a relatively space friendly way.

The images would still need to be manually deleted though when a product is removed from Commerce Server. This would be easier though because each set of images is in its own folder which could have the ProductId as its name.

The ultimate solution is to alter the business tools to allow image uploading and link in the products to the images so that when the product is removed, the image is also removed.
This is by far the best solution for most projects, but does required quite an overhead in development time and means altering the underlying database structure of Commerce Server and the busienss tool UI.
However, there is an excellent step by step approach given by Max in his blog here:
http://blogs.msdn.com/maxakbar/archive/2006/09/20/764026.aspx of how to do this.

I have not followed his article yet, but intend to just to see how its done! I'll post any comments to this soluton here and on his blog when I get round to implementing the solution.
Please note, there are several parts to his step by step so make sure you follow them all.

Extending Profiles in Commerce Server 2007

Simon — Fri, 30 Mar 2007 13:43:00 GMT

We have been working on extending the profiles in CS 2007 to include some custom properties and have come across some interesting issues. I thought I'd add those here for anyone else who is extending the profile system.

To add a custom profile property, you need to follow these steps:

(Make sure you close the Customer and Profile Manager first as we found sometimes this locks records and you can find your profile properties disapearing after you add them!)

1. Add the column to the appropriate object table in the profiles database

2. Open CS Manager

3. Extend the Profiles node > Profile Catalog > DataSources node and all the way to the objects

4. Right click on the object you are extending and 'New Data Member'

5. Add the new member name and the display name and any description and map to the column in the database

6. Extend the Profiles node > Profile Catalog > Profile Definitions and click on the object type you are extending

7. Click onto a definition above where you want your new property to appear and click the 'Add' button at the bottom of the screen.

8. Complete at least the Name, Display name and Type and open the Advanced Attributes tab.

9. Click on the 'Map to Data' field and map to the column you added in step 5.

10. You can set the max value to the length of the field if using a variable length data type in the Custom Attributes Tab.

11. Click Apply

12. Click onto another object in the Profile Definitions tree. This is important as CS tools dont seem to save you changes until you do this. A box should now appear, asking if you wish to save changes.

13. If you checked the searchable field in Advanced Attributes, you should now see the new field appear in the drop down box in the search field in the Customer and Profiles Manager.

14. Do an iisreset and hopefully you should now be able to program against your new proprties.

Commerce Server 2007 Profiles

Simon — Tue, 27 Mar 2007 10:53:00 GMT

Retrieving profiles from the database

We are creating a profile system whereby each UserObject profile belongs to an Organisation Profile object. I wanted to return all UserObjects for a specific OrganisationId but have found that you can only search profiles using the web services and not directly with the APIs.
This is because the search object ProfileManagementContext, which is used for searching, can only be created with a web service url or reference in its constructor.
Therefore I am having to use part API calls and part webservice calls to achieve my goal.

The only other way to search using the CS Library API is to use the CommerceContext.Current.ProfileSystem.CommerceOleDbProvider and access the Sql tables through this.
However, the provider does not allow 'and' or 'or' in the where clauses and does not allow joins. So returning UserObjects by GeneralInfo.UserId from the UserObject and GeneralInfo.Name from the Organisation object is not possible.

The only way to implement this functionality is to create a new Data Access Layer (DAL) to access the database directly, which is not recommended by Microsoft for obvious reasons.

See: http://blogs.msdn.com/maxakbar/archive/2006/11/03/free-commerce-server-2007-sql-2005-visual-studio-web-and-c-express.aspx
----------------------------------------

PUP Viewer

Simon — Mon, 12 Mar 2007 14:48:00 GMT

A useful PUP viewer that works with CS2007 - see attachments.

Resources for Commerce Server 2007

Simon — Tue, 06 Mar 2007 15:42:00 GMT

Please add any useful URLS that you find for Commerce Server as comments to this post.

Installation of Commerce Server 2007

Simon — Mon, 05 Mar 2007 15:29:00 GMT

------------------------

This is the list of steps that I took when installing commerce server, I have included the errors I got and how I overcame them.

-----------------------

1. install windows 2003 web server edition

1a. install service packs and patches

2. configure for terminal services (optional)

3. add to iis asp.net 2.0 - intall the framework

4. add to iis - check web server extension 2.0 is activated

5. install sqlserver 2005

6. install patches etc

7. configure sqlserver 2005 for remote connections

8. install commerce server 2007

8a. Configure Commerce Server wizard - use Windows Authentication for database connections.

9. install commerce server business parts (web services) This is done by re-rerunning the Commerce Server SetUp exe and installing Business Applications

10. install the starter site application (Adventure Works)

10a Unzip the test site
10b Copy the starter.pup file into C://Program Files/MS Commerce Server/PUP Packages
10c Open Commerce Server Manager
10d Right Click on the Commerce Sites Node in the tree and unpack the starter.pup file

(at this point site not found 404 or permissions 403)

11. follow all of the steps in the installation guide for security http://download.microsoft.com/download/d/b/e/dbe333fd-6cbe-4850-b24c-8da3704d6190/Commerce%20Server%202007%20Starter%20Site%20Installation%20Guide.htm

12. biz talk error

The module 'CommerceProfileModule' depends on the site resource 'Biz Data Service' which does not exist in the Commerce Server administration database.

http://groups.google.co.uk/group/microsoft.public.commerceserver.general/msg/bdf65887c9081811?dmode=source&hl=en

delete global resouce for profile and reload PUP for test site

13. cannot create file in C://temp - check permissions according to above document on the Temporary folder.

14 any db permission errors check above document - this is mostly seen as the web services not running with 404 or 403 errors. They are actually security log in errors.

15. check event log for other errors

16. test each web service in browser

17. run cmd line test site creator - if red errors cant create class - check permissions on C://windows/temp - allow all web service users write access

This inputs all of the test data into the project - the exe is sampleDataImport.exe

18. You should now be able to run the admin and testsite to configure the adventureworks site.

---

Any other problems encountered, please add below...

Getting Started with Commerce Server 2007

Simon — Mon, 05 Mar 2007 12:00:00 GMT

At the time of writing there are not many message boards or blogs about Commerce Server 2007 and other than the official documentation help can be hard to find.

So we have created this blog which covers some of the problems I and other developers on our team have come across and how we overcame them.

The trial version of commerce server can be downloaded from Microsoft's Commerce Server website: http://www.microsoft.com/commerceserver/default.mspx

Please do not post any company specific information here as this blog is available as read only to the public