Retrieving Your Data

When you use SDS, you can retrieve your data in two ways. You can retrieve authorities, containers, and entities directly by URI. Alternatively, you can scope queries at the service, authority, or container levels to retrieve direct descendants subject to the query criteria and conditions.

Direct Retrieval

Let's start by looking at how to retrieve authorities, containers, and entities directly. To retrieve an item, simply address an HTTP GET request to the appropriate URI. For example, to retrieve the coho authority, address your HTTP request to the authority URI:

https://coho.data.beta.mssds.com/v1/

The service responds with an XML payload that contains the authority metadata:

<s:Authority xmlns:s="http://schemas.microsoft.com/sitka/2008/03/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:x="http://www.w3.org/2001/XMLSchema">
<s:Id>coho</s:Id>
<s:Version>3593083</s:Version>
</s:Authority>

Remember that the Version property value is a non-incremental system-generated number. In other words, if you've just created an entity, don't assume that the version number will be 1.

To retrieve the CohoSauvignon05 entity, address your HTTP request to the entity URI:

https://coho.data.beta.mssds.com/v1/wines/cohosauvignon05

Note: In this URL, coho represents your authority, wines represents your container, and cohosauvignon05 represents your entity.

The service responds with an XML representation of the entity:

<RedWine xmlns:s='http://schemas.microsoft.com/sitka/2008/03/'
xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
xmlns:x='http://www.w3.org/2001/XMLSchema' >
<s:Id>CohoSauvignon05</s:Id>
<s:Version>210441</s:Version>
<vineyard xsi:type='x:string'>Coho Vineyard</vineyard>
<vintage xsi:type='x:string'>2005</vintage>
<grape xsi:type='x:string'>Cabernet Sauvignon</grape>
<region xsi:type='x:string'>New Zealand</region>
<description xsi:type='x:string'>Full-bodied and fruity</description>
<thumbnail xsi:type='x:base64Binary'>/9j/4AAQSkZJ… </thumbnail>
<price xsi:type='x:decimal'>16.95</price>
</RedWine>

You'll notice that this matches the XML payload we used earlier to create the entity, except that this also includes a Version element.

While these basic fetch operations are fine if you know exactly what you're looking for, in most cases you will want to specify queries to provide more sophisticated data retrieval.

Building a Query

SDS supports a simple, text-based query syntax, based on the C# Language Integrated Query (LINQ) pattern. Each query takes the following format:

from e in entities [where condition] order by [property] select e

The from e in entities format applies regardless of whether you are retrieving authorities, containers, or entities. In SDS, authorities, containers, and entities are all types of flexible entity. The scope of your query determines the type of flexible entity (authority, container, or entity) that your query will return. For example, if you scope a query to an authority, your query will return containers. Similarly, if you scope a query to a container, your query will return entities.

You can use comparison operators to add conditions to your queries. You can also use Boolean operators to concatenate or further manipulate your conditions. The current beta version of SDS supports the following operators.

Table 5: Supported comparison and Boolean operators

Let's look at a few examples. In each case, assume that we have scoped each query to the Wines container. We'll look at how to scope and submit a query in the next section.

First, suppose you want to retrieve all red wines from the Wines container. You would structure your query as follows:

from e in entities where e.Kind == "RedWine" select e

Because Kind is a strongly-typed metadata property, you use the dot notation in comparisons. In contrast, you use an indexer notation to query flexible properties. Suppose you want to refine your query to return only red wines from New Zealand:

 from e in entities where e.Kind == "RedWine" && e["region"] == "New Zealand" select e

You can use brackets to explicitly set the order of comparison for more complex queries. Suppose you want to broaden your query to return red wines from either New Zealand or South Africa:

from e in entities where e.Kind == "RedWine" && (e["region"] == "New Zealand" || e["region"] == "South Africa") select e

Finally, to show how you might use other comparison operators, suppose you want to return red wines where the grape is anything but Merlot and your price limit is $15:

from e in entities where e.Kind == "RedWine" && e["grape"] != "Merlot" && e["price"] <= 15 select e

Note: The public beta version of SSDS will also support join queries.

Now that you know how to build a query, let's look at how to scope and submit your queries to SDS.

Submitting a Query

To submit a query by using the REST protocol, you must:

1. Get the URI of the authority or container (or the root service URI) you want to scope your query to.
2. Append the query text to the scope URI as a query string.
3. Send an HTTP GET request to the resulting URI.

This makes the query process very straightforward--the URI specifies the scope, and the query string specifies the query.

For example, to submit a query to the Wines container, you would append your query to the container URI:

https://coho.data.beta.mssds.com/v1/wines/?='from e in entities…'

The service returns an XML payload that contains the collection of flexible entities that match your query conditions:

<s:EntitySet xmlns:s="http://schemas.microsoft.com/sitka/2008/03/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:x="http://www.w3.org/2001/XMLSchema">
<RedWine>
…
</RedWine>
<RedWine>
…
</RedWine>
</s:EntitySet>

This pattern applies regardless of the scope at which you submit your query. The service always returns an EntitySet element that contains all the child entities that match the query condition. These child entities could be authorities, containers, or entities, depending on the scope of the query. If no matching entities are found, the service returns an empty EntitySet element.

Finally, you can submit an empty query to return all the entities within a specified scope. For example, if you send an HTTP GET request to the following URI, the service will return an EntitySet that contains every entity in the Wines container:

https://coho.data.beta.mssds.com/v1/wines/?=''

Retrieving BLOB Data

To retrieve a BLOB entity, send an HTTP GET request to the entity URI. You cannot use queries to return BLOB entities, as the query results will include only the BLOB metadata.

In some applications, you may want to retrieve both the BLOB entity itself and its associated metadata. When you build the HTTP request, you can set the Accept header to specify whether the service should return the BLOB data itself or an XML document that represents the BLOB metadata:

• If you set the Accept header to the MIME (Multipurpose Internet Mail Extensions) type of your BLOB data (video/x-msvideo in the AVI example we used earlier), the service will return the BLOB data in the response body.
• If you set the Accept header to application/x-ssds+xml, the service will return the entity metadata in the response body, in the same format as any other entity.

Updating Your Data

So far we've covered how to provision your data source, add data, and retrieve data. Now let's look at how to update existing data on SDS.

Conceptually, to update an entity in SDS you simply retrieve your entity, make your changes, and send the updated entity back to SDS. When you use the REST interface, this process is very similar to creating an entity. However, whereas you use the HTTP POST method to create a new entity, you use the HTTP PUT method to replace an existing entity.

Suppose that you want to update the price property of the CohoSauvignon05 entity that we created earlier.

1. Send an HTTP GET request to https://coho.data.beta.mssds.com/v1/wines/cohosauvignon05.
2. Retrieve the XML payload returned by the GET request, and modify the value of the price element.
3. Send an HTTP PUT request to https://coho.data.beta.mssds.com/v1/wines/cohosauvignon05, and add the modified XML payload to the request stream.
4. Check the status code of the response to ensure that your entity was updated successfully.

When you update an entity, SDS will automatically update the Version metadata property of the entity. We'll look at how versioning can be used in conditional operations later in this document.

Updating BLOB Data

You can update BLOB entities in the same way that you update regular entities. Use the HTTP PUT method to stream your updated BLOB data to the entity URI. SDS will replace the entire BLOB entity with the new content.

Conditional Operations and Versioning

You can take advantage of the Version metadata property to perform conditional retrieve, update, and delete operations on entities. For example, you might want to:

• Retrieve an entity only if your local copy is out of date.
• Update an entity only if the server version matches the client version.
• Delete an entity only if the server version matches an expected value.

When you use the REST interface, you can make retrieve, update, and delete operations conditional by setting the If-Match or If-None-Match request headers to a specific version number. As you might expect, if you set the If-Match header, the operation will only complete if the version number of the target entity on SDS matches the value of the If-Match header. Similarly, if you set the If-None-Match header, the operation will only complete if the version number of the target entity on SDS does not match the value of the If-None-Match header.

The following C# code sample illustrates how to construct an HTTP request for a conditional GET operation. The service will only return the entity if the version number is not 210441:

WebRequest request = HttpWebRequest.Create(entityUri);
request.Credentials = new NetworkCredential(myUserName, myPassword);
request.Method = "GET";
request.Headers[HttpRequestHeader.IfNoneMatch] = "210441";
request.ContentLength = 0;
request.ContentType = "application/x-ssds+xml";

If the target entity does not match your versioning criteria, the service will instead return a Not Modified HTTP status code (HTTP 304).

Note: If you need to establish the version number of an entity on which you have performed an operation, retrieve the ETag response header returned by the service.

Deleting Your Data

We've now looked at how to provision your data source and how to add, retrieve, and update data. In this final section, we'll describe how you can delete entities from SDS.

To delete an entity in SDS, you set the scope to the entity you want to delete, and then you perform the delete operation. The process is exactly the same regardless of whether you want to delete a container or an entity (standard or BLOB). However, you should be aware that the current version of SDS will not allow you to delete an authority.

When you use the REST protocol, all you need to do is to send an HTTP DELETE request to the URI of the entity you want to delete. For example:

• To delete the CohoSauvignon05 entity, send an HTTP request that specifies the DELETE method to https://coho.data.beta.mssds.com/v1/wines/cohosauvignon05.
• To delete the entire Wines container, send an HTTP request that specifies the DELETE method to https://coho.data.beta.mssds.com/v1/wines.

The service will respond with an HTTP status code that indicates whether the delete operation was successful.

Current Limitations

The current beta version of SDS has various limitations that you should be aware of before you start developing your own applications. These limitations are likely to change as the service evolves.

When you create an authority, the authority ID can contain only lowercase letters, numbers, or dashes. Currently, you cannot delete authorities. Each authority can contain up to 1,000 containers.

In the current beta version, each container can store a maximum of 100MB of flexible entities and 1GB of BLOB entities. Flexible entities are limited to 2MB, and BLOB entities are currently limited to 100MB. You cannot update BLOB metadata properties; if you need to change BLOB metadata, you must delete and recreate the BLOB. Currently, you must use the REST interface, rather than SOAP, to handle BLOB entities.

Conclusion

In this paper, we've introduced SQL Data Services (SDS) as a cloud-based alternative to traditional onsite data infrastructures. We've examined some of the advantages that the service can offer in terms of flexibility and scalability, reliability and security, and developer agility, and we've explained the key features of data model and the application programming interface (API). Finally, we've described how to start using SDS and how you can create, retrieve, update, and delete your data.

For more information, you can also find links to videos, blogs, and other information on the SDS Web site.For end-to-end code examples in C#, Java, and Ruby, please visit the SDS Developer Center on MSDN. You can also download the SDS SDK.