Part 5: SQL Data Services – Querying


In the previous entry we reviewed how to create entities,  but what do we do now?

Before I go into detail on how to interact with SDS directly, let’s take some time to go over the fundamentals of querying with SDS.  This entry will focus on some of the techniques available for querying the entities.

Select Your Weapon of Choice!

SDS offers a number of potential vehicles for all your querying requirements.  We have:

  • SOAP Interface
  • REST Interface
  • SDS Explorer (uses REST), and,
  • Web browser!

The fastest to get up and running with is any standard web browser.  We’ll start by taking a look at what you can do with a simple web browser.

The SDS URI

As discussed in previous entries, there is a focus on creating a unique URI for your SQL Data Service.  This is made up of the name of an Authority and one or more Containers.

Our sample URI might look like this:

[Authority Only]
https://demo-movies.data.database.windows.net/v1/

[Authority + Container]
https://demo-movies.data.database.windows.net/v1/demo-movie

When we browse to these addresses, in a standard browser we are returned some XML formatted data, which will be discussed later in the entry (“Understanding Service Metrics”).

Security Model

In this release, every Authority has a single owner (user name and password). Once authenticated to an Authority, you will have full control over the Authority and the data stored within it.

Basic Querying With the Aptly Named Query String

Want to write a query on the fly?  A standard web browser can help you.  As above, we’ve defined the look of a valid SDS URI.
Syntactically, it looks like this:
https://<authority_id>.data.database.windows.net/v1/<container_id>?q='<your query>’

We can embed a query into the query string  of our URI underneath the appropriate container, for example:

https://demo-movies.data.database.windows.net/v1/demo-movie?q=’from e in entities select e’

Our query is a Linq style query ‘from e in entities select e’ which will select all entities in the container.  Navigating to this URI will return a whole lot of XML formatted data, all entities within the container.  Loading this in a browser will typically escape/encode our query like so:

https://demo-movies.data.database.windows.net/v1/demo-movie?q=’from%20e%20in%20entities%20select%20e’

Now that you have a handle on basic querying with a browser, you might want to take a look at the SDS Explorer tool which is part of the SDS SDK.

It provides you with a clean interface for practicing your queries in a friendly environment.  It’s a little more helpful for debugging purposes (note: I said a little more helpful!) .

For more help on REST and browser based queries refer to this MSDN page.
[http://msdn.microsoft.com/en-au/library/cc512431.aspx]

Better Querying

So now we’ve got the hang of some basic queries, let’s try some more useful syntax.  Below is the basic syntax for a query using SDS.

from e in entities [where condition] [orderby property 1 [,property 2, …]] select e

The where and order by clauses work as they do in a standard SQL statement.

Examining the Metadata Properties

Metadata properties are common to all entities and provide some basic information to help create granularity at (predominantly) the container level.  Refer to the table below for notes on each metadata property.

  Property   Comment
  Id   Identifies an entity – must be unique within a container
  Kind   A user defined property which identifies the type of entity
  Version   System assigned (and managed) version stamp (used for concurrency)
  Content   Applies only to Blob entities – stores attributes of the blob content

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>