Joining Returned SQL Data Services Responses

By James at February 05, 2009 05:08
Filed Under: SQL Data Services, SQL Services, Web Development

In preparing for my talk on SDS at last month’s Code Camp, I needed to figure out a way to join to SQL Data Services responses. I came up with this method using LINQ. I don’t know if its the best way to do it, but it works for me. I’d appreciate any feedback.


1. Master Page – Page_Load calls GetPodcasts()

2. GetPodcasts() gets two SDS responses, podCastDoc and guestDoc, does some LINQ, then returns a System.Linq.Enumerable

3. A ListView on the Master Page is bound to the podcast object.


Method GetPodcasts()

   1: public void GetPodcasts()
   2: {
   3:     AT_Podcasts at_Podcasts = new AT_Podcasts();
   4:     AT_Guests at_Guests = new AT_Guests();
   5:     XElement podCastDoc = at_Podcasts.SelectPodcasts();
   6:     XElement guestDoc = at_Guests.SelectGuests();
   8:     if (podCastDoc != null && guestDoc != null)
   9:     {
  10:         var podcasts = from podcast in podCastDoc.Descendants("at-podcastdata")
  11:                        join guest in guestDoc.Descendants("at-guest")
  12:                        on (string)podcast.Element("GuestID").Value
  13:                        equals (string)guest.Element("GuestName").Value
  14:                        orderby (DateTime)podcast.Element("PublishDate")
  15:                        select new
  16:                       {
  17:                           PodCastId = podcast.Element("PodCastDataId").Value,
  18:                           PodCastTitle = podcast.Element("PodCastTitle").Value,
  19:                           PodCastPublishDate = DateTime.Parse(podcast.Element("PublishDate").Value).ToLongDateString(),
  20:                           PodCastAbstract = podcast.Element("Abstract").Value,
  21:                           RunTime = podcast.Element("RunTime").Value,
  22:                           GuestID = podcast.Element("GuestID").Value,
  23:                           GuestName = guest.Element("FullGuestName").Value,
  24:                           GuestCompany = guest.Element("GuestCompany").Value
  25:                       };
  26:         lvPodCasts.DataSource = podcasts;
  27:         lvPodCasts.DataBind();
  28:     }
  29: }


Method at_Podcasts.SelectPodcasts()

   1: public XElement SelectPodcasts()
   2:     {
   3:         XElement atPodCasts;
   4:         AT_SDSConfig config = AT_SDSConfig.Create();
   5:         Uri container = config.GetPodCastDataContainer();
   6:         string uri = container.ToString() + "?q=";
   7:         var request = AT_SDSUtils.CreateRequest(config, new Uri(uri), "GET", string.Empty);
   8:         try
   9:         {
  10:             var response = (HttpWebResponse)request.GetResponse();
  11:             using (var stream = response.GetResponseStream())
  12:             using (var reader = new System.IO.StreamReader(stream))
  13:             {
  14:                 XmlTextReader r = new XmlTextReader(reader);
  15:                 atPodCasts = XElement.Load(r);
  16:                 return atPodCasts;
  17:             }
  18:         }
  19:         catch (WebException ex)
  20:         {
  21:             AT_SDSUtils.OutputException(ex);
  22:             return null;
  23:         }
  24:     }

NOTE: at_Guests.SelectGuests() is similar, just going into the Guest Container


Results of SelectPodcasts()

   1: <s:EntitySet xmlns:s="" xmlns:xsi="" xmlns:x="">  
   2:   <at-podcastdata>
   3:     <s:Id>getting-restful</s:Id>
   4:     <s:Version>539244</s:Version>
   5:     <PodCastDataId xsi:type="x:string">getting-restful</PodCastDataId>
   6:     <PodCastTitle xsi:type="x:string">Getting RESTful</PodCastTitle>
   7:     <RunTime xsi:type="x:string">48:25</RunTime>
   8:     <Description xsi:type="x:string">Volkan talks about how to work with REST and process the XML with LINQ</Description>
   9:     <AdminNotes xsi:type="x:string">Great episode</AdminNotes>
  10:     <Abstract xsi:type="x:string">REST, XML and LINQ</Abstract>
  11:     <IsPublished xsi:type="x:boolean">true</IsPublished>
  12:     <GuestID xsi:type="x:string">volkan-uzun</GuestID>
  13:     <CreateDate xsi:type="x:string">1/23/2009 12:00:00 AM</CreateDate>
  14:     <PublishDate xsi:type="x:string">2/20/2009 12:00:00 AM</PublishDate>
  15:   </at-podcastdata>
  16:   <at-podcastdata>
  17:     <s:Id>sql-data-services</s:Id>
  18:     <s:Version>539554</s:Version>
  19:     <PodCastDataId xsi:type="x:string">sql-data-services</PodCastDataId>
  20:     <PodCastTitle xsi:type="x:string">SQL Data Services</PodCastTitle>
  21:     <RunTime xsi:type="x:string">12:35</RunTime>
  22:     <Description xsi:type="x:string">Jennifer goes into detail about how to use SQL Data Services to run a web site.</Description>
  23:     <AdminNotes xsi:type="x:string">This is the first podcast</AdminNotes>
  24:     <Abstract xsi:type="x:string">Using SQL Data Services to run a website</Abstract>
  25:     <IsPublished xsi:type="x:boolean">true</IsPublished>
  26:     <GuestID xsi:type="x:string">jennifer-louie</GuestID>
  27:     <CreateDate xsi:type="x:string">1/24/2009 12:00:00 AM</CreateDate>
  28:     <PublishDate xsi:type="x:string">1/24/2009 12:00:00 AM</PublishDate>
  29:   </at-podcastdata>
  30:   <at-podcastdata>
  31:     <s:Id>working-with-office-2007</s:Id>
  32:     <s:Version>539227</s:Version>
  33:     <PodCastDataId xsi:type="x:string">working-with-office-2007</PodCastDataId>
  34:     <PodCastTitle xsi:type="x:string">Working with Office 2007</PodCastTitle>
  35:     <RunTime xsi:type="x:string">30:45</RunTime>
  36:     <Description xsi:type="x:string">In this episode, Joe talks about a cool macro he wrote that makes all the busy work his boss has him do, a lot more fun.</Description>
  37:     <AdminNotes xsi:type="x:string">Kinda flakey, but ok to publish</AdminNotes>
  38:     <Abstract xsi:type="x:string">Office 2007 Macros</Abstract>
  39:     <IsPublished xsi:type="x:boolean">true</IsPublished>
  40:     <GuestID xsi:type="x:string">joe-blow</GuestID>
  41:     <CreateDate xsi:type="x:string">1/23/2009 12:00:00 AM</CreateDate>
  42:     <PublishDate xsi:type="x:string">2/11/2009 12:00:00 AM</PublishDate>
  43:   </at-podcastdata>
  44: </s:EntitySet>

Results of SelectGuests()

   1: <s:EntitySet xmlns:s="" xmlns:xsi="" xmlns:x="">
   2:   <at-guest>
   3:     <s:Id>jennifer-louie</s:Id>
   4:     <s:Version>50343029</s:Version>
   5:     <GuestName xsi:type="x:string">jennifer-louie</GuestName>
   6:     <FullGuestName xsi:type="x:string">Jennifer Louie</FullGuestName>
   7:     <GuestEmail xsi:type="x:string"></GuestEmail>
   8:     <GuestCompany xsi:type="x:string">Louie Software</GuestCompany>
   9:     <GuestBio xsi:type="x:string">Jennifer is a .NET Rock Star. She's really cute and hot!</GuestBio>
  10:     <PublishEmail xsi:type="x:boolean">true</PublishEmail>
  11:   </at-guest>
  12:   <at-guest>
  13:     <s:Id>james-johnson</s:Id>
  14:     <s:Version>50404939</s:Version>
  15:     <GuestName xsi:type="x:string">james-johnson</GuestName>
  16:     <FullGuestName xsi:type="x:string">James Johnson</FullGuestName>
  17:     <GuestEmail xsi:type="x:string"></GuestEmail>
  18:     <GuestCompany xsi:type="x:string">Duringlunch</GuestCompany>
  19:     <GuestBio xsi:type="x:string">James is a .NET Developer who has been writing software since punch cards. He often remembers the time when his Computer Science PhD project got scattered to the wind, when he stumbled across a brand new orange screen monitor and his breath was taken away.</GuestBio>
  20:     <PublishEmail xsi:type="x:boolean">true</PublishEmail>
  21:   </at-guest>
  22:   <at-guest>
  23:     <s:Id>volkan-uzun</s:Id>
  24:     <s:Version>49928700</s:Version>
  25:     <GuestName xsi:type="x:string">volkan-uzun</GuestName>
  26:     <FullGuestName xsi:type="x:string">Volkan Uzun</FullGuestName>
  27:     <GuestEmail xsi:type="x:string"></GuestEmail>
  28:     <GuestCompany xsi:type="x:string">Uzun Enterprises</GuestCompany>
  29:     <GuestBio xsi:type="x:string">Inland Empire .NET User's Group Most Valuable Member - 2007/2008. LINQ Expert. Nice Guy.</GuestBio>
  30:     <PublishEmail xsi:type="x:boolean">true</PublishEmail>
  31:   </at-guest>
  32: </s:EntitySet>


Back to the LINQ which does the join

   1: var podcasts = from podcast in podCastDoc.Descendants("at-podcastdata")
   2:    join guest in guestDoc.Descendants("at-guest")
   3:    on (string)podcast.Element("GuestID").Value
   4:    equals (string)guest.Element("GuestName").Value
   5:    orderby (DateTime)podcast.Element("PublishDate")
   6:    select new
   7:   {
   8:       PodCastId = podcast.Element("PodCastDataId").Value,
   9:       PodCastTitle = podcast.Element("PodCastTitle").Value,
  10:       PodCastPublishDate = DateTime.Parse(podcast.Element("PublishDate").Value).ToLongDateString(),
  11:       PodCastAbstract = podcast.Element("Abstract").Value,
  12:       RunTime = podcast.Element("RunTime").Value,
  13:       GuestID = podcast.Element("GuestID").Value,
  14:       GuestName = guest.Element("FullGuestName").Value,
  15:       GuestCompany = guest.Element("GuestCompany").Value
  16:   };
  17: lvPodCasts.DataSource = podcasts;
  18: lvPodCasts.DataBind();




Again, like I said. I’d appreciate your comments.


Code Camp 2009 Presentation - Anonymous Types

By James at January 24, 2009 09:15
Filed Under: SQL Data Services

Today I presented "SQL Data Services - A real life SDS Application. Even though my laptop siezed just before the presentation, it went pretty well. Here is the code and slide deck for your enjoyment. (397.30 kb)


SQL Data Services Adventure – Day 3

By James at November 29, 2008 08:23
Filed Under: SQL Data Services

In the last post I talked about the structure of how SDS stores data. An Authority is geo-located and stores the containers. Multiple Containers can be stored in an Authority and each Container can hold multiple Entities.

I’m tired and need some REST, but after I wash with some SOAP

SQL Data Services support both REST (Representational State Transfer) and SOAP (Simple Object Access Protocol) interfaces which allows the use of practically any language and web development tools. For the rest of this series I will be focusing on interacting with SDS via REST.

Four kinds of URIs (Uniform Resource Identifier) are used when programming SDS with REST. Each of these URIs sets a scope for a set of operation to be performed. Each URI should look familiar by now:

Service – Used when creating and querying authorities

Authority – Used for creating and querying containers and to retrieve metadata regarding particular authorities

Container – Used for creating and querying entities, retrieving metadata about a particular container and for deleting a specific container

Entity – Used when retrieving, updating and deleting specific entities

Figuring out the pieces

Service URI

In the URI above, refers to the Service while v1 references the version of the service.

A query can be appended to the end of the URI to query for Authorities which have been created, like this’<some really cool query>’

Authority URI

To query against a particular Authority,  you prepend the URI with the name of the Authority you want to work with. For example:

returns a single entity which contains metadata about the referenced Authority. The entity returned is XML like this:

   1: <s:Authority 
   2:     xmlns:s="" 
   3:     xmlns:xsi="" 
   4:     xmlns:x="">
   5:       <s:Id>PizzaRecipes</s:Id>
   6:       <s:Version>2234</s:Version>
   7: </s:Authority>

As the same with Service URIs you can also append a query to an Authority URI to query against all the Containers in the Authority’<a query to find containers>’

Container URI

The Container URI returns a single entity which contains metadata about the particular Container and returns the following XML:

   1: <s:Container 
   2:     xmlns:s="" 
   3:     xmlns:xsi="" 
   4:     xmlns:x="">
   5:       <s:Id>LatinaSpecial</s:Id>
   6:       <s:Version>1234</s:Version>
   7: </s:Container>

As with Authority URIs appending a query to the end of a Container URI will return all entities which meet the query criteria for the particular Container.

Entity URI

An Entity URI is a Container URI followed by an Entity ID and returns a specific entity

   1: <LatinaSpecial
   2:  xmlns:s=""
   3:  xmlns:xsi=""
   4:  xmlns:x="">
   5:  <s:Id>LatinaSpecialPizza<s:Id>
   6:  <s:Version>13453</s:Version>
   7:  <s:DisplayName xsi:type="x:string">Latina Special Pizza</s:DisplayName>    
   8:  <Rating xsi:type="x:string">Great</Rating>
   9:  <NumberOfTimesMade xsi:type="x:decimal">250</NumberOfTimesMade>
  10:  <Description xsi:type="x:string">A mouth watering pizza with a Latina flair</Description>
  11:  <Pepperoni xsi:type="x:decimal">30</Pepperoni>
  12:  <Cheese xsi:type="x:decimal">24</Cheese>
  13:  <Crema xsi:type="x:decimal">5</Crema>
  14:  <QuesoFresco xsi:type="x:decimal">40</QuesoFresco>
  15: </LatinaSpecial> 

Security Model

Every Authority has a single user name and password (owner), and once authorized for a specific authority end point, full control over the Authority and the data stored within it is granted. Accounts are secured by SDS issued user credentials and only allows for communication by https.

Next…Query Support

SQL Data Services Adventure – Day 2

By James at November 24, 2008 15:40
Filed Under: SQL Data Services

One of the cool, and frustrating things, about dealing with cutting edge technology is how stuff changes so fast. Some of my links and bookmarks aren’t working until I start hacking the URL to find what it is I thought I had.

I’m going to make a minor change in plans and break my posts into two sub-categories; Hands-on learning and my experiences, and my interpretation of the online materials.

What are SQL Data Services (SDS)?

  1. Providing data and data access as a service.
  2. Highly scalable without having to manage the physical infrastructure.
  3. The capacity of the system is only limited by the amount of data you are putting into it.
  4. Supports REST and SOAP

The SDS data model uses concepts of authorities, containers and entities. The data model has a flexible schema, which make it very easy to create your data structures.

The “ACE” Model.

A is for Authority:

the authority is the topmost level of the hierarchy and is represented by a DNS name. For instance, in the URL,, “duringlunch” is the authority, with everything after being the reference to the service. What is cool about this is the authority maps to a specific data center. In other words an authority is a unit of geo-location.

C is for Container

Authorities contain containers. Each of these containers have a unique id associated to them, and store the data used in the application. As authorities are geo-located, so are the containers within them. Containers cannot be in a different geo-location than the authority holding them. Container can have either homogeneous or heterogeneous data.

E is for Entity

Entities are the actual bits of data stored in the container. Each entity stores any number of user-defined properties with the corresponding values. Entities can be retrieved, added, updated, deleted. However partial updates are not supported. When updating, the entire entity is replace in the container.

Homogeneous or Heterogeneous – everyone has a right to what they want.

As mentioned before containers can hold either homogeneous or heterogeneous data. Similar to a table in a database, homogeneous are entities which are all of the same kind. For instance a “Books” container stores all types of “book” entities. A heterogeneous container is more like an entire database with entities of all kinds. An example of a heterogeneous container would hold “book”, “office supply”, “furniture” entities.

Which is better? Hard to say, it depends on what your application needs. For the highly structured applications, use homogeneous containers. Heterogeneous containers allow for querying across all the entities stored in the parent container.

Flexible Entities

The key concept of SDS is the Flexible Entity. Flexible entities are XML fragments with one element per property value. Each property has a name and a value, with the value restricted to a simple scalar type of string, binary, boolean, decimal, or datetime. Each flexible entity has fixed metadata properties of “Id”, “Version”, and “Kind”, with “Version” acting as a timestamp. With each update, a new version is automatically assigned. “Kind” is user-defined, identifying the entity type. Entities can have additional user-defined flexible properties with any name and scalar type of the types listed above.

More soon,


SQL Data Services Adventure - Day 1

By James at November 24, 2008 14:36
Filed Under: SQL Data Services

Finally. I’ve been looking forward to this for a while now. Cracking open the VPC I created just for this, get VS2008 setup, re-familiarizing myself with SDS, and starting “Thriller”, I’m off on my adventure.

I decided the best way to get up to speed is to complete the Hands On Labs I started at PDC. You can get the same Azure Services Training kit at Unpacking everything and getting ready is pretty straight forward, and to be honest this is really good training, I’d suggest downloading and following along.

Jumping right into the Intro to SQL Data Services Lab things are fine until “unhandled exception (409) Conflict”. What the heck is that, double, triple, quadruple checking my code, yup, it’s fine. So, heading over to the SDS Forums a quick search returns the following post about how things have changed and Authorities are different now.

Now I’m up and running and got the first Exercise, “Using the REST Interface to Access SDS” complete. This is cool, can’t wait for the next one.


B-Side Players - Fire In The Youth - Unplug This Armageddon

The SQL Data Services adventure begins

By James at November 18, 2008 08:30
Filed Under: SQL Data Services

My great friend and mentor, Lynn Langit has asked me to present on SQL Data Services for geekSpeak on January 14th, 2009. Follow along as I learn as much as I can leading up to my presentation.

Wish me luck,


Post PDC 2008, onto SQL Data Services

By James at November 02, 2008 06:55
Filed Under: SQL Data Services

My first PDC was great with lots of new things to absorb and learn about. However this time, I’m going to go at this in a more structured way. Starting today, I am going to devote my learning time to SQL Data Services with several intentions.

1) I think this platform is just really bitchen.

2) I want to be the community expert when it comes to SDS (think Julia Lerman and Entity Framework, Jesse Liberty and Silverlight, Geoff Emery and Virtual Earth).

3) One of the coolest guys I know, and a great friend, David Robinson, is the Senior Program Manager on the SDS team, and has offered to help me in any way he can.

So, I’ve created a VPC image so I can install all the bits, and I will be writing up my experiences here as often as I can. Check back often. I’m off spelunking.

About the author

James James is a five time and current Microsoft MVP in Client App Development, a Telerik Insider, a past Director on the INETA North America Board, a husband and dad, and has been developing software since the early days of Laser Discs and HyperCard stacks. As the Founder and President of the Inland Empire .NET User's Group, he has fondly watched it grow from a twice-a-month, early Saturday morning group of five in 2003, to a robust and rambunctious gathering of all types and sizes of .NET developers.

James loves to dig deep into the latest cutting edge technologies - sometimes with spectacular disasters - and spread the word about the latest and greatest bits, getting people excited about developing web sites and applications on the .NET platform, and using the best tools for the job. He tries to blog as often as he can, but usually gets distracted by EF, LINQ, MVC, ASP, SQL, XML, and most other types of acronyms. To keep calm James plays a mean Djembe and tries to practice his violin. You can follow him on twitter at @latringo.

And as usual, the comments, suggestions, writings and rants are my own, and really shouldn't reflect the opinions of my employer. That is, unless it really does.

James Twitter Feed

Recent Comments

Comment RSS

Month List