Using ADO.NET Data Services

This week I worked through a good article on getting started with ADO.NET Data Services (a.k.a Astoria) called Using ADO.NET Data Services. If you're not familiar with the technology it's basically a framework for exposing your data models via RESTful web services. So if you are building a remote CRUD data access layer then this is a technology that you'll probably want to look into. It should save you some time especially if you're trying to expose your data over REST.

(Note: We're in the process of adding Visual Basic code snippets to the article but you can also download the VB sample projects here on Code Gallery.)

I'm building a few different Windows clients (WPF and Office) to see how we could use ADO.NET Data Services in our n-tier business applications and I'll blog about that soon. But for this post I'd like to highlight the getting started parts of the article that I found most helpful when trying to understand how to work with our database over the web.

File > New Project > ASP.NET Web Application....

Creating an ADO.NET Data Service is easy if you are using Visual Studio 2008 SP1. First create a new ASP.NET Web Application:

This will create a new ASP.NET project with a default.aspx page. If you're just building the service then you can delete this file right away. Next you need to add a new item to the project and select ADO.NET Data Service.

For this example I want to expose data in the Northwind sample database so I'm naming the service Northwind.svc. This will add the System.Data.Services and System.Data.Services.Client (the server and client pieces of the framework) to your project. Right-click on the Northwind.svc in the solution explorer and select Set as Start Page.

This process spits out a code template that sets up your data service by creating a class called Northwind that inherits from DataService.

Imports System.Data.Services
Imports System.Linq
Imports System.ServiceModel.Web

Public Class Northwind
    ' TODO: replace [[class name]] with your data class name
    Inherits DataService(Of [[class name]])

    ' This method is called only once to initialize service-wide policies.
    Public Shared Sub InitializeService(...

End Class

Notice however that you have some work to do. The DataService acts upon a data model, more specifically, a data model that exposes IQueryable properties. (Take a look at the CustomDataService example that is explained in the MSDN article if you're interested in creating a compatible data source yourself.) If you're lazy like me then you can use an Entity Framework data model which implements IQueryable by default.

Adding an Entity Data Model

So in order to fill out the [[class name]] of our IQueryable data source we need to create one first. Add a new item to the project and select ADO.NET Entity Data Model. I named the model NorthwindModel.

\

Now you can run through the wizard to create the model. I'll just generate it from the database, select all the tables and then hit Finish. This process creates an IQueryable data source called NorthwindEntities. Now we can go back into the data service and fill out the [[class name]].

Public Class Northwind
    Inherits DataService(Of NorthwindEntities)

Setting Access to Tables in the Data Model

We also want to control access to the tables in our data model. It's probably a really bad idea to allow read/write access to every table in our database but it's possible by configuring an EntitySetAccessRule on the DataServiceConfiguration that is passed into the InitializeService method. Note that this is a Shared method so it will only run once no matter how many instances of the Northwind DataService are created. You may need to keep that in mind when debugging via Visual Studio and force some rebuilds if you change the code in there.

Public Class Northwind
    Inherits DataService(Of NorthwindEntities)

    ' This method is called only once to initialize service-wide policies.
    Public Shared Sub InitializeService(ByVal config As IDataServiceConfiguration)
        ' Everyone can party on my data!
        config.SetEntitySetAccessRule("*", EntitySetRights.All)
    End Sub

End Class

This allows anyone access to update, insert, delete and query the tables in the model. Probably a bad idea. So you can allow just read-only:

config.SetEntitySetAccessRule("*", EntitySetRights.AllRead)

You can also write additional web methods for querying and updating the data with some additional processing. I'll get to that in a follow-up post or you can jump into that section of the article. First let's see how we query our data via an HTTP GET in IE.

Querying the Data via HTTP GET

Hitting F5 on the project will now automatically start the service and open the browser to the Northwind.svc which lists all the entities in which we're allowing access in our data model.

When we want to query our data, say all the customers, then we can do that via an HTTP GET by typing in the browser address bar: http://localhost:1234/Northwind.svc/Customers and a feed of all the customers in Northwind will be returned. If you're browsing using IE then it will attempt to put a style sheet on the feed, just right-click and view source to see the actual data returned (I'm just displaying the first two in the feed for clarity):

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<feed xml:base="http://localhost:1308/Northwind.svc/" 
      xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" 
      xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" 
      xmlns="http://www.w3.org/2005/Atom">
  <title type="text">Customers</title>
  <id>http://localhost:1308/Northwind.svc/Customers</id>
  <updated>2009-01-10T02:17:37Z</updated>
  <link rel="self" title="Customers" href="Customers" />
  <entry>
    <id>http://localhost:1308/Northwind.svc/Customers('ALFKI')</id>
    <title type="text"></title>
    <updated>2009-01-10T02:17:37Z</updated>
    <author>
      <name />
    </author>
    <link rel="edit" 
          title="Customers" 
          href="Customers('ALFKI')" />
    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Orders" 
          type="application/atom+xml;type=feed" 
          title="Orders" 
          href="Customers('ALFKI')/Orders" />
    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/CustomerDemographics" 
          type="application/atom+xml;type=feed" 
          title="CustomerDemographics" 
          href="Customers('ALFKI')/CustomerDemographics" />
    <category term="NorthwindModel.Customers" 
              scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <content type="application/xml">
      <m:properties>
        <d:CustomerID>ALFKI</d:CustomerID>
        <d:CompanyName>Alfreds Futterkiste</d:CompanyName>
        <d:ContactName>Maria Anders</d:ContactName>
        <d:ContactTitle>Sales Representative</d:ContactTitle>
        <d:Address>Obere Str. 57</d:Address>
        <d:City>Berlin</d:City>
        <d:Region m:null="true" />
        <d:PostalCode>12209</d:PostalCode>
        <d:Country>Germany</d:Country>
        <d:Phone>030-0074321</d:Phone>
        <d:Fax>030-0076545</d:Fax>
      </m:properties>
    </content>
  </entry>
  <entry>
    <id>http://localhost:1308/Northwind.svc/Customers('ANATR')</id>
    <title type="text"></title>
    <updated>2009-01-10T02:17:37Z</updated>
    <author>
      <name />
    </author>
    <link rel="edit" 
          title="Customers" 
          href="Customers('ANATR')" />
    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Orders" 
          type="application/atom+xml;type=feed" 
          title="Orders" 
          href="Customers('ANATR')/Orders" />
    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/CustomerDemographics" 
          type="application/atom+xml;type=feed" 
          title="CustomerDemographics" 
          href="Customers('ANATR')/CustomerDemographics" />
    <category term="NorthwindModel.Customers" 
              scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <content type="application/xml">
      <m:properties>
        <d:CustomerID>ANATR</d:CustomerID>
        <d:CompanyName>Ana Trujillo Emparedados y helados</d:CompanyName>
        <d:ContactName>Ana Trujillo</d:ContactName>
        <d:ContactTitle>Owner</d:ContactTitle>
        <d:Address>Avda. de la Constitución 2222</d:Address>
        <d:City>México D.F.</d:City>
        <d:Region m:null="true" />
        <d:PostalCode>05021</d:PostalCode>
        <d:Country>Mexico</d:Country>
        <d:Phone>(5) 555-4729</d:Phone>
        <d:Fax>(5) 555-3745</d:Fax>
      </m:properties>
    </content>
  </entry>
.
.
.

Notice that this also returns the relationship information from the customer to the other tables in the model. So if we wanted to return all the orders for customer ALFKI then we would use the relative URL in this feed like so:

http://localhost:1234/Northwind.svc/Customers('ALFKI')/Orders

BTW, if you're going to get serious about developing RESTful web services I'd highly suggest you download Fiddler which will allow you to inspect your HTTP traffic and payloads. It's a lot easier to view this data in Fiddler than in IE.

Building a Simple Client

This stuff is all fun and games until we want to write a real client against our service. I don't think anyone would be too happy with IE as their client against this service, no matter how cool we wrote it. So let's take a look at a simple client that queries a product, makes a change, and submits it back to the service. This is an example that is illustrated in the article that helped me understand how to make changes to the data.

First thing we need to do is configure our service so we can query and update the product table. I'm also going to open up the Categories table for read access:

Public Class Northwind
    Inherits DataService(Of NorthwindEntities)

    ' This method is called only once to initialize service-wide policies.
    Public Shared Sub InitializeService(ByVal config As IDataServiceConfiguration)
        config.SetEntitySetAccessRule("Products", EntitySetRights.All)
        config.SetEntitySetAccessRule("Categories", EntitySetRights.AllRead)
    End Sub

End Class

Now let's add a simple console application (real clients soon, I promise!) to illustrate the client-side code it takes to query and update a product in our database. Once you add a new console application to the solution we need to add the service reference to our Northwind Data Service. Right click on the client console application and select "Add Service Reference". When the dialog opens click the Discover button and it should find the data service.

Once we add this service reference to our client this will automatically generate the client-side proxy that inherits from DataServiceContext and the necessary entities to work with our data service. It also adds a reference to the System.Data.Services.Client assembly which has the client-side framework where the DataServiceContext object lives. This is another added benefit than you creating a remote data access layer yourself. You tell the DataServiceContext how to track changes to the entities and it can can batch up requests and send them at once to the service.

One of cool things about having an IQueryable data source exposed through a data service is that you can use LINQ over the entities generated on the client-side and the framework will handle translating that to HTTP requests. You can dive deeper into the article on the exact query syntax it can support but it's pretty full featured.  For instance back on the IE client, if we just want to return the top 2 customers we could type: http://localhost:1234/Northwind.svc/Customers?$top=2

But when we're working with a managed client we can write a LINQ query against the data service instead. Here I'm returning all the products in the Beverages category:

Imports MyDataServiceClient.ServiceReference1

Module Module1
    Private ServiceURI As Uri = New Uri("http://localhost:1308/Northwind.svc/")

    Sub Main()
        Dim ctx As New NorthwindEntities(ServiceURI)

        Dim results = From p In ctx.Products _
                      Where p.Categories.CategoryName = "Beverages" 

        For Each p In results
            Console.WriteLine(p.ProductName)
        Next
        Console.ReadLine()
    End Sub

End Module

Pretty slick. Notice that since I'm using the Categories in the query, I have to enable access to that on the service side otherwise I'll get an error when running the client.

Saving Changes through the ADO.NET Data Service

In order to update the database we tell the DataServiceContext that we updated the object and then tell it to pass the change to the server by calling SaveChanges.

Imports MyDataServiceClient.ServiceReference1

Module Module1
    Private ServiceURI As Uri = New Uri("http://localhost:1308/Northwind.svc/")

    Sub Main()
        Dim ctx As New NorthwindEntities(ServiceURI)

        Dim product = (From p In ctx.Products _
                      Where p.ProductName = "Chai").FirstOrDefault()

        If product IsNot Nothing Then

            product.ProductName = "Chai Tea"
            ctx.UpdateObject(product)

            Try
                'You can batch up changes on the DataServiceContext
                ' before calling SaveChanges. This will hit the service.
                ctx.SaveChanges()
                Console.WriteLine("Saved Product")
            Catch ex As Exception
                Console.WriteLine(ex.ToString())
            End Try
        End If
    End Sub

End Module

Nice. In the next post I'll dig a little deeper into how we can intercept queries so that we can do some server-side processing first as well as how we can add additional processing methods to our service in order to perform custom querying and validation. I'm also building a WPF client for doing batch editing that I'll introduce. For now, please read the article on MSDN for more information. Until next time....

Enjoy!