Part IV: WiE reaches for the Cloud...Implementing the SQL Server Data Services (SSDS) data model for the WiE Mobile Client.
The Implementation of WiERemoteModelSSDSForMobile
The WiERemoteModel expects to store and retrieve data from SQL Server Data Services (SSDS). I won’t include the entire implementation of the WiERemoteModelSSDSForMobile in-line as we plan to eventually provide the entire project for download but I will highlight some of the key methods to explain the implementation.
As we discussed in the previous articles, the data layer for the WiE Mobile Client is made up of “providers” that each implement the IWiEModel interface and work together to implement support for the occasionally disconnected mobile scenario. In last week’s article we covered the implementation of the local data store: IWiELocalModel.
As a refresher, the IWiEModel interface is shown below:
interface IWiEModel { event EventHandler<LocationHistoryChangedArgs> RaiseLocationHistoryChangedEvent; event EventHandler<DeviceChangedArgs> RaiseDeviceChangedEvent; event EventHandler<MemberChangedArgs> RaiseMemberChangedEvent; WiELocationDataObject GetLocation(System.Guid p_guidLocationID); void RemoveLocation(System.Guid p_guidLocationID); List<WiELocationDataObject> GetLocationHistoryBetween(DateTime p_dtStartTimeUTC, DateTime p_dtStopTimeUTC); List<WiELocationDataObject> GetLocationHistoryBefore(DateTime p_dtEndTimeUTC); void SaveLocation(WiELocationDataObject p_locationDataObject); void SaveMember(WiEMemberDataObject p_memberDataObject); WiEMemberDataObject GetMember(Guid p_guidMemberID); WiEMemberDataObject GetMemberByUserName(string p_strUserName); WiEMemberDataObject GetMemberByPhoneNumber(string p_strPhoneNumber); void SaveDevice(WiEDeviceDataObject p_deviceDataObject); WiEDeviceDataObject GetDevice(System.Guid p_guidDeviceID); List<WiEDeviceDataObject> GetDevicesByMember(System.Guid p_guidMemberID); void Initialize(); void Terminate(); }
|
What is SSDS?
Per the MSDN web site: SQL Server Data Services (SSDS) are highly scalable, on-demand data storage and query processing utility services. Built on robust SQL Server technologies and Windows Server, SSDS provides highly available, secure, standards based web services that are easy to program and provision
Signing up for an SSDS Beta Account:
Currently access to SSDS is by invitation only. You can request an invitation to register at this location. Once you register you will be given a username and password that will let you create containers and entities within your Authority.
Core Concepts:
SSDS has 3 key concepts to keep in mind:
· Authority – Conceptually similar to a database. Its responsibility is to be the “logical root” for containers. Authorities provide for partitioning of data at a geo level (i.e. across data centers).
· Containers –Conceptually similar to a “table” in RDBMS terms. Containers are however not limited to storing a single kind of entity, they can house entities of multiple types and shapes. Entities expose a well known property called Kind that can be used to select or filter entities by type. Containers provide for partitioning of data across servers (useful to keep in mind for scaling purposes).
· Entities – Similar in concept to a row in a table, but more flexible. An entity can have any number of properties (a property bag of sorts) with a few well known properties. These well known properties include Id and Kind. These are useful to uniquely identify single entities and to filter entities by type (since a container can hold entities of different kinds).
In the case of the WiE Community we will start with a simple approach with a single container hosting several types of entities (Location, Member and Device). As we look to scale-out the application we may spread the entities that make up a WiE Community across multiple containers and possibly as we grow WiE globally we would look at using multiple Authorities to define regional communities (possibly by continent).
Accessing SSDS: Add a Web Reference to SSDS
I chose to use the SOAP interface as the mechanism for interaction with SSDS. SSDS also supports a REST based approach. Both approaches have their own set of advantages and disadvantages, in my case I was more comfortable and familiar with the SOAP based web service approach so it was primarily driven from the perspective of minimizing my time to adoption curve. Add a Web Service reference to your windows mobile project, by right clicking the Web References project folder, and selecting Add Web Reference… Enter https://data.beta.mssds.com/soap/v1 as the URL for the web service.
The SSDS Web Service
This process will result in a new SitkaClient proxy class that you can use to make calls against the SSDS services. The SSDS web service exposes a fairly small set of methods (shown above) and a few helper classes:
· Create(Scope, Entity)
· Delete(Scope)
· Query(Scope, string)
· Update(Scope, Entity)
· Delete(Scope)
Note: The proxy generator for a windows mobile project will generate the internal collection of properties for the Entity as an array of name value pairs(rather than a dictionary) and will implement the Query method so that it returns an array (rather than a List). The .net compact framework does not implement the full WCF framework so you will experience a different result if you generate a proxy for a rich client (WPF) or full web application that can make use of WCF and let you specify your preferred method of dealing with collections (Lists, arrays, etc.) |
Conceptually, if you can picture SSDS as a hard drive with a directory hierarchy, the root has folders (authorities), each authority has subfolders (containers) and each container holds a bunch of files of different types (entities). The Scope is about specifying where in the directory hierarchy you want the action to take place.
The Scope object provides the context for an operation, specifying if a call applies to an authority, a container or an entity. For example, to create a new container within an existing authority:
Scope serviceScope = new Scope(); serviceScope.AuthorityId = AUTHORITY_NAME; Container container = new Container(); container.Id = CONTAINER_NAME; try { // Call the web service to create the container m_sitkaSoapService.Create(serviceScope, container); } catch (System.Web.Services.Protocols.SoapException) { . . . }
|
SSDS queries are specified using a LINQ like syntax in the form of:
from e in entities where <some expression> select e
For example, to retrieve a list of all the containers for a given authority:
Scope authorityScope = new Scope(); authorityScope.AuthorityId = AUTHORITY_NAME; string strQuery = @"from e in entities select e"; // try to query SSDS for the matching containers in the authority Entity[] entities = m_sitkaSoapService.Query(authorityScope, strQuery);
|
You can also enter this query directly into a web browser (a great debugging aid) using the following URL format
https://<authority-id>.data.beta.mssds.com/v1/?q='from e in entities select e'
Sorting & Limiting Query Results
Something to keep in mind as you approach building queries with SSDS (learned the hard way):
- Current version of the service does not offer the ability to specify a sort as part of the query.
- Current version of the service does not have TOP clause.
In the case of WiE this would be extremely useful to retrieve the "latest" known location for a member for example. The current WiE implementation retrieves the "last 5 minutes" of data for a member by specifying a WHERE clause on the date and time collected and then sorts it in descending order on the client to pick the newest item.
// Build the querystring string queryString = @"from e in entities where (e.Kind == """ + LOCATION_KIND + @""") && (e[""" + WiELocationDataObject.KEY_DATECOLLECTED + @"""] >= DateTime(""" + p_dtStartTimeUTC.ToString() + @""")) && (e[""" + WiELocationDataObject.KEY_DATECOLLECTED + @"""] <= DateTime(""" + p_dtStopTimeUTC.ToString() + @""")) && (e[""" + WiELocationDataObject.KEY_DEVICEID + @"""] == """ + p_guidDeviceID.ToString() + @""") select e"; |
You can learn more about SSDS in much more detail on the MSDN web site.
Implementing the WiERemoteModelSSDSForMobile
Now that we have the background for SSDS let’s look at how the model was implemented by looking at a few of the key methods the Model provides. These will show examples of using each of the SSDS web service methods. As with the WiELocalModel discussion I will not show the entire implementation as we plan to post all the code with the last article in the series.
class WiEModelRemoteSSDSForMobile : IWiEModel { // Sitka Credentials private const string LOGIN_NAME = "<YOUR SSDS LOGIN>"; private const string LOGIN_PASSWORD = "<YOUR SSDS PASSWORD>"; private const string AUTHORITY_NAME = "wie"; // Container private const string CONTAINER_NAME = "wie"; // Entity Types private const string LOCATION_KIND = "location"; private const string MEMBER_KIND = "member"; private const string DEVICE_KIND = "device"; // SOAP Web Service wrapper / proxy to Sitka SSDS Data service SitkaSoapService m_sitkaSoapService; . . . } |
Initialize()
This method is responsible for initializing the data store. In the case of WiELocalModel we used this method to connect to the SQL Server Compact database and to prepare our prepared statements. For SSDS we leverage this call to ensure that the WiE authority and container exist and to create them if they do not. The Initialize() method makes use of a couple helper methods. The IsInitialized() function checks to see if the container and authority already exist by issuing a simple query against them and the IntializeAuthority() and InitializeContainer() create the authority and the container in the event they do not already exist.
Note: There is a restriction on authority and container names, they cannot use upper case letters, so we used “wie” for our container and authority names.
public void Initialize() { // Initialize the web service proxy m_sitkaSoapService = new SitkaSoapService(); m_sitkaSoapService.Credentials = new System.Net.NetworkCredential(LOGIN_NAME,LOGIN_PASSWORD); // Check to see if the authority and containers exist, if not create them if (!IsInitialized()) { // Make sure we have an Authority and Container to store our information in SSDS InitializeAuthority(); InitializeContainer(); } } . . . private bool IsInitialized() { bool bReturnValue = false; try { Scope authorityScope = new Scope(); authorityScope.AuthorityId = AUTHORITY_NAME; string strQuery = @"from e in entities where e.Id==""" + CONTAINER_NAME + @""" select e"; // try to query SSDS for the member Entity[] entities = m_sitkaSoapService.Query(authorityScope, strQuery); bReturnValue = (entities.Length > 0); } catch (Exception ex) { // There was a problem querying Console.WriteLine("There was a problem querying for the authority's list of containers: " + ex.ToString()); } return bReturnValue; } . . . private bool InitializeAuthority() { bool bReturnValue = true; Scope serviceScope = new Scope(); Authority authority = new Authority(); authority.Id = AUTHORITY_NAME; try { // Call the web service to create the authority m_sitkaSoapService.Create(serviceScope, authority); } catch (System.Web.Services.Protocols.SoapException createAuthoritySoapException) { // Look at the exception to see what the fault was for, it is likely be that we // have already created the authority, so this is not an issue. Console.WriteLine("Problem creating the authority. Exception: " + createAuthoritySoapException.ToString()); } catch (Exception createAuthorityException) { // There was an issue trying to create the authority bReturnValue = false; Console.WriteLine("There was an issue creating / initializing Authority: " + createAuthorityException.ToString()); } return bReturnValue; } |
SaveLocation()
The SaveLocation method takes a WiELocationDataObject and saves it as an Entity within the wie container. The method makes use of the DataObjectToEntity() helper method that helps convert the WiEDataObjects to Entity objects and the SaveEntity() method that attempts to create or update the entity if it already exists.
public void SaveLocation(WiELocationDataObject p_locationDataObject) { // Create a new entity object and initialize it Entity locationHistoryEntity = DataObjectToEntity(p_locationDataObject); locationHistoryEntity.Kind = LOCATION_KIND; locationHistoryEntity.Id = p_locationDataObject.LocationID.ToString(); // Attempt to save the location history if (false == SaveEntity(locationHistoryEntity)) { // We were unable to save the location to the remote data store... throw new Exception("Unable to save location information."); } else { // We saved the record, inform anyone that cares that we posted a new location if (RaiseLocationHistoryChangedEvent != null) RaiseLocationHistoryChangedEvent(this, new LocationHistoryChangedArgs(p_locationDataObject)); } } . . . |
DataObjectToEntity()
This helper method takes a data object which exposes its properties as a Dictionary and converts it into a array of name-value pairs expected by the SSDS Web Service Entity type. This represents the mapping between the storage specific data type and the application data objects used by the Model.
private Entity DataObjectToEntity(WiEDataObject p_dataObject) { // Retrieve the data object in a property bag form Dictionary<string, object> dataObjectProperties = p_dataObject.ToPropertyDictionary(); // Create a new entity object and initialize it Entity entity = new Entity(); // Now create properties on the Entity to contain the values from the Data Object properties entity.Properties = new ArrayOfKeyValueOfstringanyTypeKeyValueOfstringanyType[dataObjectProperties.Count]; int i = 0; foreach (KeyValuePair<string, object> kvp in dataObjectProperties) { entity.Properties[i] = new ArrayOfKeyValueOfstringanyTypeKeyValueOfstringanyType(); entity.Properties[i].Key = kvp.Key; entity.Properties[i].Value = kvp.Value; i++; } // Return the entity with populate properties... still needs to have its Type and ID set. return entity; } |
SaveEntity()
The SaveEntity helper method is responsible for saving an entity which can mean either creating a new entity or updating an existing entity. Unfortunately this can mean up to 2 roundtrips in the current implementation. Hopefully soon SSDS will have a method that combines both operations (something like SQL Server 2008 MERGE statement that combines an update and an insert-if-it-doesn’t-exist).
private bool SaveEntity(Entity p_entity) { bool bReturnValue = true; try { Scope containerScope = new Scope(); containerScope.AuthorityId = AUTHORITY_NAME; containerScope.ContainerId = CONTAINER_NAME; // Call the web service to create the entity m_sitkaSoapService.Create(containerScope, p_entity); } catch (System.Web.Services.Protocols.SoapException soapEx) { // Look at the exception to see what the fault was for, it could likely be that we // have already created the Entity, so it is really an update (rather than a create). Console.WriteLine("SaveEntity() Insert failed, assuming need an update. Exception:" + soapEx.Detail.ToString()); bReturnValue = false; } if (false == bReturnValue) { // Assume the failure was that the item already exists and we need to do an update // rather than an insert. We will need to revisit this and possibly allow passing in // a hint for those cases where we know we are doing one versus the other to save on // one roundtrip. try { Scope entityScope = new Scope(); entityScope.AuthorityId = AUTHORITY_NAME; entityScope.ContainerId = CONTAINER_NAME; entityScope.EntityId = p_entity.Id; m_sitkaSoapService.Update(entityScope, p_entity); bReturnValue = true; } catch (System.Web.Services.Protocols.SoapException soapEx2) { // Ok Update failed too... Console.WriteLine("There was a problem updating the Entity with SSDS. Exception: " + soapEx2.Detail.ToString()); } } return bReturnValue; } |
GetMember(MemberID)
The GetMember method retrieves the member whose ID matches MemberID.
public WiEMemberDataObject GetMember(Guid p_guidMemberID) { WiEMemberDataObject matchingMember = null; string queryString = @"from e in entities where (e.Kind == """ + MEMBER_KIND + @""") && (e[""" + WiEMemberDataObject.KEY_MEMBERID + @"""] == """ + p_guidMemberID.ToString() + @""") select e"; List<WiEDataObject> matchingMembers = GetQueryResults(queryString); if ((null != matchingMembers) && (matchingMembers.Count > 0)) matchingMember = (WiEMemberDataObject) matchingMembers[0]; // Return the results return (matchingMember); } |
GetQueryResults(query)
The GetQueryResults helper method returns lists of WiEDataObjects based on the results of an SSDS query.
private List<WiEDataObject> GetQueryResults(string p_strQuery) { Entity[] entities = null; List<WiEDataObject> queryResults = null; Scope containerScope = new Scope(); containerScope.AuthorityId = AUTHORITY_NAME; containerScope.ContainerId = CONTAINER_NAME; try { // try to query SSDS for the member entities = m_sitkaSoapService.Query(containerScope, p_strQuery); if (entities.Length > 0) queryResults = new List<WiEDataObject>(); foreach (Entity currentEntity in entities) { switch (currentEntity.Kind) { case LOCATION_KIND: WiELocationDataObject newLocationDataObject = new WiELocationDataObject(EntityToPropertyBag(currentEntity)); queryResults.Add(newLocationDataObject); break; case MEMBER_KIND: WiEMemberDataObject newMemberDataObject = new WiEMemberDataObject(EntityToPropertyBag(currentEntity)); queryResults.Add(newMemberDataObject); break; case DEVICE_KIND: WiEDeviceDataObject newDeviceDataObject = new WiEDeviceDataObject(EntityToPropertyBag(currentEntity)); break; default: Console.WriteLine("Unsupported or deprecated entity was returned: {0}.", currentEntity.Kind); break; } } } catch (Exception ex) { // There was a problem querying for location history. Console.WriteLine("There was a problem querying data object(s): " + ex.ToString()); } return queryResults; }
|
EntityToPropertyBag(Entity)
This helper method converts the property array returned as part of an SSDS Entity into a Dictionary (or property bag).
private Dictionary<string, object> EntityToPropertyBag(Entity p_entity) { Dictionary<string,object> propertyBag = new Dictionary<string,object>(); foreach(ArrayOfKeyValueOfstringanyTypeKeyValueOfstringanyType kvp in p_entity.Properties) { propertyBag.Add(kvp.Key,kvp.Value); } return (propertyBag); } |
Wrapping the WiE Mobile Client up with Sync & Sync Framework
Next week we'll be completing the WiE Mobile Client by wrapping the WiELocalModel, WiERemoteModel together with a Sync provider to implement the WiEModelWithLocalCache which provides the WiE Mobile Client with the "best of both worlds", local storage when disconnected and synchronization with the centralized SSDS storage when the mobile device is within wireless data connectivity.
Have a great week,
Olivier