Deep inserts using SAP Mobile Platform SDK

[miltonc]

Deep insert using SMP SDK

Most business applications require some sort of deep insert when creating hierarchical data.  For example, a Sales Order and Sales Order Item(s) can only be created together and at the same time.  Notice that there can be a single Sales Order Item or multiple Sales Order Items.  Deep insert provides the possibility of creating the parent entity and the child entities in a single activity.

In our deep insert example, we will use the universally available OData Service found in http://services.odata.org.  Since this involves updating the backend data, use the link to create a full access (Read-Write) service.

Deep insert 1:1 (Offline and Online)

Let’s start with the easier option of inserting 1 parent entity and 1 child entity.  We can then expand on this to see how we can insert 1 parent entity and multiple child entities.

The first step is to create the parent entity.  PropertyCreationMode.All indicates that you will be supplying all the values for the properties.  If you do not supply values for any of the optional properties, the default value will be used.  Note that you also want to call the AllocateNavigationProperties method to establish the navigation properties in the parent entity.  The following code snippet creates a local parent entity and sets the values for the properties.

var parentEntity = new SAP.Data.OData.Online.ODataEntity("ODataDemo.Category");

parentEntity.SetResourcePath("Categories", "Categories");

Store.AllocateProperties(parentEntity, PropertyCreationMode.All);

SharedContext.Context.OnlineStore.AllocateNavigationProperties(parentEntity);

parentEntity.Properties["ID"].Value = 3;

parentEntity.Properties["Name"].Value = "Home Goods";

The next step is to create the child entity.  The following code snippet creates a local child entity and sets the values for the properties.

var childEntity = new SAP.Data.OData.Online.ODataEntity("ODataDemo.Product");

childEntity.SetResourcePath("Products", "Products");

Store.AllocateProperties(childEntity, PropertyCreationMode.All);

childEntity.Properties["ID"].Value = 9;

childEntity.Properties["Name"].Value = "Microwave Oven";

childEntity.Properties["Description"].Value = "1000 W Samsung Microwave Oven";

childEntity.Properties["ReleaseDate"].Value = DateTime.Now;

childEntity.Properties["Rating"].Value = 3;

childEntity.Properties["Price"].Value = 1430.38m;

The next step is to add the association between the parent entity and the child entity.  To do this, you must create a navigation property of the parent entity.  Then set the value of the navigation content property to the child entity.  The following snippet of code adds the association between the parent entity and the child entity.

var navigationProperty = parentEntity.GetNavigationProperty("Products");

var childEntities = new SAP.Data.OData.ODataEntitySet();

childEntities.Add(childEntity);

navigationProperty.NavigationContent = childEntities;

parentEntity.SetNavigationProperty("Products", navigationProperty);

The last step is to actually submit the POST request.  The following snippet of code submits the POST request.

var requestParam = new ODataRequestParametersSingle("Categories", RequestMode.Create, parentEntity);

try

{

   var execution = SharedContext.Context.OnlineStore.ScheduleRequest(requestParam);

   await execution.Response;

}

catch (ODataException ex)

{

   var errorInfo = new

   {

      Message = ex.Error != null ? ex.Error.Message : ex.Message,

      ErrorCode = ex.Error != null ? ex.Error.Code : ex.ErrorCode.ToString()

   };

}

catch (Exception ex)

{

   System.Diagnostics.Debug.WriteLine(ex.Message);

}

Deep insert 1:N (Online)

Deep insert 1:N for Online applications pretty much follows the same paradigm as the previous section. The only difference is that you will create more than 1 child entity.  Notice that this section is meant only for Online applications.  This is because there is a limitation in OData specifications that does not allow deep inserts 1:N for Offline applications. In the next section, we will look at possible workarounds to perform 1:N deep inserts in Offline applications.

The following snippet of code creates 2 child entities – should be pretty straightforward.

var childEntity1 = new SAP.Data.OData.Online.ODataEntity("ODataDemo.Product");

childEntity1.SetResourcePath("Products", "Products");

SharedContext.Context.OnlineStore.AllocateProperties(childEntity1, SAP.Data.OData.Store.PropertyCreationMode.All);

childEntity1.Properties["ID"].Value = 201;

childEntity1.Properties["Name"].Value = "Tennis racket";

childEntity1.Properties["Description"].Value = "Prince O-Shark Tennis racket";

childEntity1.Properties["ReleaseDate"].Value = DateTime.Now;

childEntity1.Properties["Rating"].Value = 5;

childEntity1.Properties["Price"].Value = 174.99m;

var childEntity2 = new SAP.Data.OData.Online.ODataEntity("ODataDemo.Product");

childEntity1.SetResourcePath("Products", "Products");

SharedContext.Context.OnlineStore.AllocateProperties(childEntity2, SAP.Data.OData.Store.PropertyCreationMode.All);

childEntity2.Properties["ID"].Value = 202;

childEntity2.Properties["Name"].Value = "Tennis shoes";

childEntity2.Properties["Description"].Value = "Prince Invisible Shoes";

childEntity2.Properties["ReleaseDate"].Value = DateTime.Now;

childEntity2.Properties["Rating"].Value = 5;

childEntity2.Properties["Price"].Value = 124.99m;

You must also add the association between the parent entity and the 2 child entities.  The following code snippet adds the association between parent entity and the 2 child entities – almost identical to the code snippet from the previous section, except that you are now adding 2 child entities.

var navigationProperty = parentEntity.GetNavigationProperty("Products");

var childEntities = new SAP.Data.OData.ODataEntitySet();

childEntities.Add(childEntity1);

childEntities.Add(childEntity2);

navigationProperty.NavigationContent = childEntities;

parentEntity.SetNavigationProperty("Products", navigationProperty);

The last step is to submit the POST request.  This code is identical to the code snippet from the previous section.

Note:  There is a bug in the Windows SMP SDK that throws the following exception.  A relative URI cannot be created because the 'uriString' parameter represents an absolute URI.  This is fixed in SMP SDK SP11.

Deep insert 1:N (Offline) – work around

As mentioned earlier, there is a limitation in OData specification that does not allow deep inserts 1:N for Offline applications.  In Offline applications, there is no way to accurately resolve local keys to real keys when performing 1:N deep insert.  Since we cannot accurately resolve local keys to real keys – the SMP SDK does not allow this operation.

However, we could work around this limitation by persisting the property values of the parent entity and child entities in the datavault when the device is offline.  Later when the device is online, we can perform a deep insert 1:N followed by a refresh of the Offline Store.

The first step in performing deep insert 1:N in Offline mode is to persist the property values of the parent entity and child entities in the datavault when the device is offline. The following code snippet persists the property values of the parent entity and multiple child entities in the datavault.

var childEntity1 = new ChildEntity(301, "Tennis racket", "Prince O-Shark Tennis racket", DateTime.Now, 5, 174.99m);

var childEntity2 = new ChildEntity(302, "Tennis shoes", "Prince Invisible Tennis shoes", DateTime.Now, 5, 124.99m);

var childEntities = new List<ChildEntity>();

childEntities.Add(childEntity1);

childEntities.Add(childEntity2);

var parentEntity = new ParentEntity(11, "Sporting Goods", childEntities);

var serializeParentEntity = JsonConvert.SerializeObject(parentEntity);

await Globals.LogonCore.SetObjectInSecureStoreAsync(serializeParentEntity, "DeepInsert");

public class ParentEntity

{

   public ParentEntity(int Id, string name, List<ChildEntity> childEntities)

   {

      this.ID = Id;

      this.Name = name;

      this.ChildEntities = childEntities;

   }

   public int ID { get; set; }

   public string Name { get; set; }

   public List<ChildEntity> ChildEntities { get; set; }

}

public class ChildEntity

{

   public ChildEntity(int Id, string name, string description, DateTime releaseDate, int rating, decimal price)

   {

      this.ID = Id;

      this.Name = name;

      this.Description = description;

      this.ReleaseDate = releaseDate;

      this.Rating = rating;

      this.Price = price;

   }

   public int ID { get; set; }

   public string Name { get; set; }

   public string Description { get; set; }

   public DateTime ReleaseDate  { get; set; }

   public int Rating { get; set; }

   public Decimal Price { get; set; }

}

When the device is Online (AND) when you are ready to call Flush, simply read from the datavault and submit the POST request using the Online Store.

var jContent = JObject.Parse((await Globals.LogonCore.GetObjectFromSecureStoreAsync("DeepInsert")).ToString());

var parentEntity = new SAP.Data.OData.Online.ODataEntity("ODataDemo.Category");

parentEntity.SetResourcePath("Categories", "Categories");

SharedContext.Context.OnlineStore.AllocateProperties(parentEntity, SAP.Data.OData.Store.PropertyCreationMode.All);

SharedContext.Context.OnlineStore.AllocateNavigationProperties(parentEntity);

parentEntity.Properties["ID"].Value = (int)jContent["ID"];

parentEntity.Properties["Name"].Value = (string)jContent["Name"];

var children = jContent["ChildEntities"];

var childEntities = new SAP.Data.OData.ODataEntitySet();

for (int i = 0; i < children.Count(); i++)

{

   var childEntity = new SAP.Data.OData.Online.ODataEntity("ODataDemo.Product");

   childEntity.SetResourcePath("Products", "Products");

   SharedContext.Context.OnlineStore.AllocateProperties(childEntity, SAP.Data.OData.Store.PropertyCreationMode.All);

   childEntity.Properties["ID"].Value = (int)children[i]["ID"];

   childEntity.Properties["Name"].Value = (string)children[i]["Name"];

   childEntity.Properties["Description"].Value = (string)children[i]["Description"];

   childEntity.Properties["ReleaseDate"].Value = (DateTime)children[i]["ReleaseDate"];

   childEntity.Properties["Rating"].Value = (int)children[i]["Rating"];

   childEntity.Properties["Price"].Value = (decimal)children[i]["Price"];

   childEntities.Add(childEntity);

}

var navigationProperty = parentEntity.GetNavigationProperty("Products");

navigationProperty.NavigationContent = childEntities;

parentEntity.SetNavigationProperty("Products", navigationProperty);

var requestParam = new ODataRequestParametersSingle("Categories", RequestMode.Create, parentEntity);

try

{

   var execution = SharedContext.Context.OnlineStore.ScheduleRequest(requestParam);

   await execution.Response;

}

The last step would be to call Refresh on the Offline Store.  This would bring the newly inserted row in the backend to the Offline Store.

try

{

   await SharedContext.Context.OfflineStore.ScheduleRefreshAsync();

}

There are a few limitations with this work around.  The limitations with this work around are as follows.

• GET request to Offline Store will not fetch the temporary deep insert record (before submitting the POST request)
• Additional updates to the temporary deep insert record is difficult
• Proper naming scheme required to persist the temporary deep insert records

However, these limitations can be easily overcome with some custom code.

Please find attached the source code for a sample Windows application that performs 1:N deep inserts in an online scenario.  The workaround for 1:N deep insert in an offline scenario is also included.

In the next blog, I will talk about how we can use Content-ID referencing in a batch request to insert parent and child entities in a single transaction.  Note:  The OData Service must support Content-ID referencing for this to work.