Part 4: SQL Data Services – Creating Data Entities

In the last entry, we established a way of programmatically creating an SDS Authority and Containers.

In this entry we will focus on creating data Entities using the SOAP interface.


In Part 1, we saw how the relationship is defined between objects in the SQL Data Services world.  An Authority is the sub domain and parent object.

An Authority can contain many Containers, and Containers can contain many Entities.  Now that we have been able to (hopefully) successfully create the latter (Authority/Container), we shall look at how to create and manipulate Entities.

Designed With Purpose

Often, I find it helpful to work with data which has some kind of meaning to me, personally.

It helps me mould some kind of user requirements since I’m actually able to take some advantage from the implementation (other than for the sake of example/demo code).

Those who read this blog regularly, will be familiar with the Mini Project I blogged about in September.  I’m going to be using that data schema with SDS.

Moreover, I will be uploading data about the DVDs I own into SDS where I will be able to craft applications to query and take advantage of this information.

Creating an Entity

As mentioned, I’m going to look at uploading data relating to the DVDs I own.  As a result, I’ve defined the following three key entity types:

  • Movie
  • Actor
  • Genre

It would be easy to expand out into other entities (e.g. Director) but I’m going to try and contain the scope of this example.

The original database schema looks like this


The reason I’ve chosen only three key types is I wish to also create relationships between them.  A movie often relates to multiple genres, and almost always has multiple actors and actresses.

As a result, I have two additional entity types, MovieActor and MovieGenre which act as join tables or junction tables (allowing the storage of many-to-many relationships).

The schema of each type is listed in the table below.  Note: I’ve dropped many properties from the Movie entity to keep it simple for now.

  <Title xsi:type="x:string"></Title>
  <Region xsi:type="x:decimal"></Region>
  <Case xsi:type="x:decimal"></Case>
  <Index xsi:type="x:string"></Index>
  <Criterion xsi:type="x:boolean"></Criterion>
  <ExRental xsi:type="x:boolean"></ExRental>
  <IsMovie xsi:type="x:boolean"></IsMovie>
  <Boxset xsi:type="x:boolean"></Boxset>
  <MovieId xsi:type="x:decimal"></MovieId>

  <Name xsi:type="x:string"></Name>
  <Aliases xsi:type="x:string"></Aliases>
  <ActorId xsi:type="x:decimal"></ActorId>

  <Name xsi:type="x:string"></Name>
  <GenreId xsi:type="x:decimal"></GenreId>

The Entity Object

The basic SDS Entity requires only two common properties (known as metadata properties) to be assigned values, .Id and .Kind (the third, .Version, is assigned and maintained by SDS for timestamp/row version purposes).  There is a fourth metadata property, which only applies to Blob entities, called Content – we will cover Blob entities in a later entry.

For additional entity data, a Dictionary (key/value pair) forms the basis of flexible properties.  In the examples above, metadata properties are all the properties without the namespace prefix (s:).

Design Notes

You may have noticed that I am storing record ID values with an extended property (“flexible properties”) instead of with the common (“metadata”) properties for example, Id.

This is for two reasons, the first being that the standard Id is a string (which affects the ‘order by’ query) and the second is that because the entities are being stored in the same container, their Id values must be unique (an obvious problem if migrating from a database).

Why am I storing all the entities in one container?  In the initial release, cross-container joins (in queries) are not supported and I want to be able to write multiple join queries.

BLOB (Binary Data) Entities

Blob data is treated differently than regular SDS entities. 

Each blob entity has .Id, .Version and .Kind metadata properties as above. In addition, each blob entity also has an additional property, Content, with following attributes

  • content-disposition
  • content-type
  • content-length

The .Kind property for all blob entities is "Entity" and cannot be changed at this time.

Flexible properties (key/value pairs) on blobs are not supported in this release.

Blobs are limited to 100MB.

Finally, in the initial release Blob entities are not supported via browser queries or the SOAP interface. 
That is to say, Blob entities can only be used via the REST interface.

Creating an SDS Entity

To create and upload an entity through code, it is fairly straight forward.  Using the established format (introduced in Part 3) it is purely a matter of defining your entity within the scope of a valid Authority and Container.

Sample Syntax

using (SitkaSoapServiceClient proxy = new  SitkaSoapServiceClient(“EndPointName”))
  proxy.ClientCredentials.UserName.UserName = “userName”;
  proxy.ClientCredentials.UserName.Password = “userPw”;

  Scope myContainerScope = new Scope();
  myContainerScope.AuthorityId = “YourAuthorityId”;
  myContainerScope.ContainerId = “YourContainerId”;

  // Now create a new entity
  Entity e1 = new Entity();
  e1.Id = "SomeId";
  e1.Kind = "UsedBookKind";
  e1.Properties = new Dictionary<string, object>();

  // Create
  proxy.Create(myContainerScope, e1);

As you may have noticed from the syntax above, once an entity is defined and populated, it is simply a matter of constructing the service and scope followed by a call to the Create method, passing in the scope object and the entity to be created.

For example, this is how I create a “Movie” entity:

Entity e = new Entity();

// Metadata Properties
e.Id = “<ID”>;
//Entity Type, user defined (except BLOB entities)
e.Kind = "Movie";

// Flexible Properties
e.Properties = new Dictionary<string, object>();
e.Properties["Title"] = “Movie Title”;
e.Properties["Region"] = 4;
e.Properties["Case"] = 1;
e.Properties["Index"] = “209”;
e.Properties["Criterion"] = false;
e.Properties["ExRental"] = false;
e.Properties["IsMovie"] = true;
e.Properties["Boxset"] = false;           
e.Properties["MovieId"] = <Id>;

Code Reuse

To make this process more transparent, I wrote a generic function called CreateEntity() since each SDS entity must conform to the same structure (with the exception of blob entities).

As a result, I can use this method (passing the configuration into the method) to create any of the five entities described above.

public static void CreateEntity(DataAccessConfiguration config, Entity newEntity)
  // Configuration Specifies Basic or Token based Authentication
  using (SitkaSoapServiceClient proxy = new SitkaSoapServiceClient(config.AuthenticationType))
    proxy.ClientCredentials.UserName.UserName = config.UserName;
    proxy.ClientCredentials.UserName.Password = config.Password;

    Scope myContainerScope = new Scope();
    myContainerScope.AuthorityId = config.AuthorityName;
    myContainerScope.ContainerId = config.ContainerName;

       proxy.Create(myContainerScope, newEntity);
    catch (FaultException<Error> e)
      if (e.Detail.StatusCode != ErrorCodes.EntityExists)
      Debug.WriteLine("Entity Exists");
      // Or log


In this entry, we defined our data structure, our purpose and reviewed how to programmatically add a new data entity.

The next entry will cover bulk data migration and some basic querying via the SDS Explorer tool and from the Web Browser.

About Rob Sanders

IT Professional and TOGAF 9 certified architect with nearly two decades of industry experience, 18 years in commercial software development and 11 years in IT consulting. Check out the "About Rob" page for more information.

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>