Working with SDS

In my last two posts I showed you how to get setup to use SQL Data Services, and I described the SDS data model. Now it’s time to start actually working with SDS by using the SSDS Explorer tool.

SDS can be accessed in two ways, using a REST based protocol, or using SOAP. The SDS Explorer tool uses the REST method. At the top of the screen you will see the address (URI) that the requests will be sent to, and the buttons at the bottom represent the various actions that can be executed.

The first step will be to create an authority to work in. If you click the authority button a template for a new authority will be inserted into the text editor. It will look like this:

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

To create the authority you will first need to put an ID for the authority in the <s:ID> tag. A couple notes on creating the ID. First it must be globally unique over the entire SDS system, meaning that no two people can create the same authority ID. You may want to prefix your authority ID with the application name you created when you signed up for SDS. The ID also must only contain lower case letters, numbers and dashes. Finally, once you create an authority you currently cannot delete it, I assume this will change before the final release of SDS. Once you have entered an ID click the Post button. If everything is working ok you should get a green check mark next to the action buttons and you should receive no errors.

Once you have created the authority the address will automatically change to contain your authority ID. For example if you authority is called ‘testsds’ you will see this:

https://testsds.data.database.windows.net/v1/

If you now click Get you will see some information about the authority. Towards the top you will see the ID you just created in the <s:ID> tag. Below this you will see various statistics about the authority which we won’t get into here.

Now that we have setup an authority, we can create a container inside of it. Click on the Container button to get a template for adding a container. Just like with Authority you need to give the Container an ID. Container IDs can contain both upper and lower case and they ARE case sensitive so ‘Tasks’ would be a different container from ‘tasks’. Enter the ID within the <s:Id> tags and press the Post button to create the container. Here is the code for creating a container called ‘Tasks’:

<s:Container xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:s="http://schemas.microsoft.com/sitka/2008/03/"><br /> <s:Id>Tasks</s:Id><br /></s:Container><br />

Once again the Address will change to include the container ID, it will look something like this:

https://testsds.data.database.windows.net/v1/Tasks

Let’s take the “Tasks” off the end of the address so we can go back to the authority level and then click Query, this will query for the contents of the authority. The result will look something like this:

<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"><br /> <s:Container><br /> <s:Id>Tasks</s:Id><br /> <s:Version>50890052</s:Version><br /> </s:Container><br /></s:EntitySet><br />

Here you can see all the containers inside the authority. In this case there is only the Tasks container we just created. Change the address back to https://testsds.data.database.windows.net/v1/Tasks so we are working with the Tasks container once again.

We have created an authority and container, now we can add an entity to the container. Let’s create a simple entity to hold a task like you would have in a to-do list application Here is the code for creating the entity

<Entity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:s="http://schemas.microsoft.com/sitka/2008/03/"><br /> <s:Id>T1000</s:Id><br /> <Message xsi:type="x:string">Finish blog entry on SDS</Message><br /> <DateAdded xsi:type="x:dateTime">2/1/2009</DateAdded><br /> <DateDue xsi:type="x:dateTime">2/7/2009</DateDue><br /> <Completed xsi:type="x:boolean">false</Completed><br /></Entity><br />

The first thing we had to fill out in the entity template is the ID, remember that the ID must be unique within the container. We then have four flexible properties, one string property called Message, two datetime properties called DateAdded and DateDue and finally a Boolean property called Completed. You can see that in each property we specify the name of the property using the tag name, and also the data type in the xsi:type attribute. Within the tags we put the actual data. Once you have entered this click Post to add it to the database.

As always, the address will change again to include in the ID of the entity. You will notice that everything in the database, authorities, containers and entities have their own unique addresses. So if we use this address, https://testsds.data.database.windows.net/v1/Tasks/T1000, and then click Get, we will retrieve entity T1000 from the container Tasks in authority testsds.

That covers the basics of how to get data into SDS, next time I will talk about how to query the data.