ADO.NET Data Services – Building a WPF Client

In my last post I introduced ADO.NET Data Services and how you can easily expose your data model via RESTful services that support the basic CRUD (Create,Retrieve,Update,Delete) operations. Basic CRUD database operations map well to the familiar HTTP verbs POST, GET, MERGE, DELETE and the framework takes care of the plumbing for us. In this post I’m going to build a simple WPF client that shows how to work with the client piece of the framework which resides in the System.Data.Service.Client namespace. 


The ADO.NET Data Service


Based on the previous example, our data service exposes the Northwind data model that I created as an Entity Data Model generated from the database. The only thing I’ve done to the Entity Model is I’ve changed the Categories navigation property on the Product to singular (since a product can only have one category) as well as the names of the entities themselves and the entity sets to plural like so:


AstoriaWPF1


We’re going to build a client that allows us to do CRUD operations on the Products data so I’m going to allow full access to that entity set. And since products must belong to a category in Northwind, we need to be able to associate them when we are editing the products. Therefore I’ll need to retrieve a list of categories for our lookup list so I’ve enabled read access on the Categories entity set. So here’s what our data service looks like in the Northwind.svc:

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

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)
‘ Return verbose errors to help in debugging
config.UseVerboseErrors = True
End Sub

End Class


Simple stuff. Next I’m going to add a new project to the solution and select WPF application. Then we need to add a Service Reference to the data service exactly how I showed in the previous post when I created the client console application in that example. This step will add a reference to the client framework (System.Data.Services.Client) as well as generate the proxy code for our model.


AstoriaWPF2


This is something to be aware of. At this time ADO.NET Data Services cannot type share the entities so you end up having client types and server types. Because of this, ADO.NET Data Services are not meant to replace a real business object layer (yet). So if you have complex business rules you want to share on the client and server you are better off writing your own WCF services and data contracts. However, if you have simple CRUD and validation requirements or are looking for a remote data access layer for applications where business rules and validations are processed predominantly on the server (like web or reporting or query-heavy applications) then ADO.NET Data Services are a great fit. And no one is stopping you from using both your own WCF services in addition to ADO.NET data services in your client applications.


Building the WPF Client


Now it’s time to build out some UI. We’re going to have two forms, one for displaying the list of products by category which will allow you to modify them and another form that will open when editing or adding the product details. First let’s build the ProductList form. I want to make the user pick a category before I pull down the products so I’ve got a combobox I’ll need to populate with the list of categories available and a search button to execute the query to the data service. Under that I have a ListBox with it’s View set to a GridView and I’ve defined the binding to a few of the product properties to show up in the columns. Under that is the buttons we’ll use to make changes to the data; Edit, Add, Delete and Save. Here’s the XAML

<Window x:Class=”ProductList”
xmlns=”http://schemas.microsoft.com/winfx/2006/xaml/presentation”
xmlns:x=”http://schemas.microsoft.com/winfx/2006/xaml”
Title=”Northwind Traders” Height=”385″ Width=”533″ Name=”ProductList”>
<
Grid>
<
Grid.RowDefinitions>
<
RowDefinition Height=”50*” />
<
RowDefinition Height=”198*” />
<
RowDefinition Height=”44*” />
</
Grid.RowDefinitions>
<
ListView
ItemsSource=”{Binding}”
IsSynchronizedWithCurrentItem=”True”
Grid.Row=”1″ Name=”ListView1″ Margin=”0,4,0,0″>
<
ListView.View>
<
GridView>
<
GridViewColumn Header=”Product Name” Width=”200″
DisplayMemberBinding=”{Binding Path=ProductName}” />
<
GridViewColumn Header=”Category” Width=”150″
DisplayMemberBinding=”{Binding Path=Category.CategoryName}” />
<
GridViewColumn Header=”Price” Width=”70″
DisplayMemberBinding=”{Binding Path=UnitPrice, StringFormat=’c2′}” />
<
GridViewColumn Header=”Units” Width=”70″
DisplayMemberBinding=”{Binding Path=UnitsInStock, StringFormat=’n0′}” />
</
GridView>
</
ListView.View>
</
ListView>
<
GroupBox Header=”Search Products” Margin=”0,0,3,0″ Name=”GroupBox1″ >
<
Grid>
<
ComboBox Margin=”90,6,199,0″ Height=”26″ VerticalAlignment=”Top”
Name=”cboCategoryLookup” DisplayMemberPath=”CategoryName”
IsSynchronizedWithCurrentItem=”True” />
<
Label HorizontalAlignment=”Left” HorizontalContentAlignment=”Right”
Margin=”6,6,0,0″ Name=”Label1″ Width=”78″ Height=”26″
VerticalAlignment=”Top”>Category:</Label>
<
Button HorizontalAlignment=”Right” Margin=”0,5.98,132,0″ Width=”64″ Height=”26″
VerticalAlignment=”Top”
Name=”btnSearch” >Search</Button>
</
Grid>
</
GroupBox>
<
Button Name=”btnAdd”
HorizontalAlignment=”Right” Margin=”0,0,143,12″
Width=”64″ Grid.Row=”2″ Height=”26″
VerticalAlignment=”Bottom” >Add</Button>
<
Button Name=”btnDelete”
HorizontalAlignment=”Right” Margin=”0,0,73,12″
Width=”64″ Grid.Row=”2″ Height=”26″
VerticalAlignment=”Bottom” >Delete</Button>
<
Button Name=”btnEdit”
HorizontalAlignment=”Right” Margin=”0,0,213,12″
Width=”64″ Grid.Row=”2″ Height=”26″
VerticalAlignment=”Bottom” >Edit</Button>
<
Button Name=”btnSave”
HorizontalAlignment=”Right” Margin=”0,0,3,12″
Width=”64″ Grid.Row=”2″ Height=”26″
VerticalAlignment=”Bottom” >Save</Button>
</
Grid>
</
Window>

Notice how we set up the binding to display the category for the product. Each product has a parent category that is accessed through the Category navigation property on the Product entity as defined in our Entity Data Model. This is how we traverse the association so that we can get at the CategoryName on the category entity that is associated with the product.


Before we can write our queries against our data service we will need to set up a few class-level variables to keep track of the data service client proxy, the list of products and categories and the products’ CollectionView. Note that you need to supply the URI to the service when you create the instance of the client proxy. (I’ve hard-coded it here for clarity but in a real app this should be in your My.Settings so that you can change it after deployment.)

Imports WpfClient.MyDataServiceReference

Class ProductList

Private DataServiceClient As New NorthwindEntities(New Uri(“http://localhost:1234/Northwind.svc”))
Private Products As List(Of Product)
Private CategoryLookup As List(Of Category)
Private ProductView As ListCollectionView


Querying the Data Service Using LINQ


Now we can write some code in our Loaded event handler to query the list of categories from our data service and populate the Category combobox. We can write a LINQ query over the DataServiceClient proxy and it will handle translating the call to the RESTful data service.

Private Sub Window1_Loaded() Handles MyBase.Loaded
    ‘Grab the list of categories and populate the combobox
Me.CategoryLookup = (From c In Me.DataServiceClient.Categories _
Order By c.CategoryName).ToList()

Me.cboCategoryLookup.ItemsSource = Me.CategoryLookup
Me.cboCategoryLookup.SelectedIndex = 0
End Sub


Let’s open up Fiddler and SQL Profiler and see what happens when we run it. (Note: to run localhost web calls through Fiddler I changed the URI to http://ipv4.fiddler:1234/Northwind.svc. See this page for details.)


AstoriaWPF3


What we’re looking at is our form with the categories ordered by their name. Then we have Fiddler showing the HTTP Get request header and the RSS Atom feed response containing the categories. Notice how the LINQ query is automatically translated to the GET /Northwind.svc/Categories()?$orderby=CategoryName and passed as a query against our IQueryable Entity Data Model. The Entity Framework handles the communication to SQL Server. You can see the SQL query in SQL Profiler.


It’s important to note that since LINQ queries on the client need to be translated to HTTP GETs by the framework not every extension method you see available in IntelliSense will work. It also may be impossible to write complex sub-queries. In those cases you may need to write a simpler queries, convert them to in-memory collections like a List and then write additional queries over the in-memory collections. Take a look at the middle of this article for a list of supported operations.


Now that we have the list of Categories to choose from we can handle the Search button’s click event and write the query to bring down the related Products. Since we want to be able to edit their details, including associating a parent Category, we need to explicitly load the Category property on the Product entity which is a reference to the parent Category entity. We then populate a simple List with the results and set up the binding on the form by setting the Window’s DataContext.

Private Sub btnSearch_Click() Handles btnSearch.Click
‘Get the selected category from the combobox
Dim category = CType(Me.cboCategoryLookup.SelectedItem, Category)

‘Return all the products for that category ordered by ProductName
Dim results = From p In Me.DataServiceClient.Products.Expand(“Category”) _
Order By p.ProductName _
Where p.Category.CategoryID = category.CategoryID

‘Populate the Products list
Me.Products = New List(Of Product)(results)
‘Set the DataContext of the Window so controls will bind to the data
Me.DataContext = Me.Products
‘Grab the CollectionView so that we can use it to add and remove items from the list
Me.ProductView = CType(CollectionViewSource.GetDefaultView(Me.DataContext), ListCollectionView)

End Sub

The .Expand(“Category”) syntax above is what loads the parent Category entity onto the Product. Now when we run the form and hit the Search button the list of Products is populated.


AstoriaWPF4


Creating the Product Detail Form


Now we need to create a form that will allow us to edit or add the details of a Product. We’re going to call this form up from the Edit and Add buttons at the bottom of the ProductList form. I’ve created a simple one that has a couple stack panels, one with labels and one with the data bound controls, and an OK and Cancel button. Here’s the XAML:

<Window x:Class=”ProductDetail”
xmlns=”http://schemas.microsoft.com/winfx/2006/xaml/presentation”
xmlns:x=”http://schemas.microsoft.com/winfx/2006/xaml”
Title=”Product Details” Height=”318″ Width=”353″>
<
Grid>
<
Grid.RowDefinitions>
<
RowDefinition Height=”243*” />
<
RowDefinition Height=”42*” />
</
Grid.RowDefinitions>
<
Grid.ColumnDefinitions>
<
ColumnDefinition Width=”114*” />
<
ColumnDefinition Width=”218*” />
</
Grid.ColumnDefinitions>
<
StackPanel Name=”StackPanel1″>
<
Label Height=”25″ Name=”Label1″ Width=”Auto”
HorizontalContentAlignment=”Right” Margin=”3″>Product Name:</Label>
<
Label Height=”25″ Name=”Label2″ Width=”Auto”
HorizontalContentAlignment=”Right” Margin=”3″>Category:</Label>
<
Label Height=”25″ Name=”Label3″ Width=”Auto”
HorizontalContentAlignment=”Right” Margin=”3″>Quantity per Unit:</Label>
<
Label Height=”25″ Name=”Label4″ Width=”Auto”
HorizontalContentAlignment=”Right” Margin=”3″>Unit Price:</Label>
<
Label Height=”25″ Name=”Label5″ Width=”Auto”
HorizontalContentAlignment=”Right” Margin=”3″>Units in Stock:</Label>
<
Label Height=”25″ Name=”Label6″ Width=”Auto”
HorizontalContentAlignment=”Right” Margin=”3″>Units on Order:</Label>
<
Label Height=”25″ Name=”Label7″ Width=”Auto”
HorizontalContentAlignment=”Right” Margin=”3″>Reorder Level:</Label>
</
StackPanel>
<
StackPanel Grid.Column=”1″ Name=”StackPanel2″>
<
TextBox
Text=”{Binding Path=ProductName}”
Height=”25″ Name=”TextBox1″ Width=”180″ Margin=”3″ HorizontalAlignment=”Left” />
<
ComboBox
Name=”cboCategoryLookup”
Height=”25″ Width=”180″ Margin=”3″ HorizontalAlignment=”Left” IsEditable=”False”
DisplayMemberPath=”CategoryName”
SelectedValuePath=”CategoryID”
SelectedValue=”{Binding Path=Category.CategoryID, Mode=OneWay}”/>
<
TextBox
Text=”{Binding Path=QuantityPerUnit}”
Height=”25″ Name=”TextBox2″ Width=”180″ Margin=”3″
HorizontalAlignment=”Left” />
<
TextBox
Text=”{Binding Path=UnitPrice}”
Height=”25″ Name=”TextBox3″ Width=”84″ Margin=”3″
HorizontalAlignment=”Left” HorizontalContentAlignment=”Right” />
<
TextBox
Text=”{Binding Path=UnitsInStock}”
Height=”25″ Name=”TextBox4″ Width=”84″ Margin=”3″
HorizontalAlignment=”Left” HorizontalContentAlignment=”Right” />
<
TextBox
Text=”{Binding Path=UnitsOnOrder}”
Height=”25″ Name=”TextBox5″ Width=”84″ Margin=”3″
HorizontalAlignment=”Left” HorizontalContentAlignment=”Right” />
<
TextBox
Text=”{Binding Path=ReorderLevel}”
Height=”25″ Name=”TextBox6″ Width=”84″ Margin=”3″
HorizontalAlignment=”Left” HorizontalContentAlignment=”Right” />
<
CheckBox
IsChecked=”{Binding Path=Discontinued}”
Height=”16″ Name=”CheckBox1″ Width=”120″
HorizontalAlignment=”Left” Margin=”3″>
Discontinued?
</CheckBox>
</
StackPanel>
<
Button Name=”btnOK” IsDefault=”True”
Grid.Column=”1″ Grid.Row=”1″
Width=”76″ Height=”26″ Margin=”0,0,81.627,4″
VerticalAlignment=”Bottom”
HorizontalAlignment=”Right” >OK</Button>
<
Button Name=”btnCancel” IsCancel=”True”
Grid.Column=”1″ Grid.Row=”1″
Width=”76″ Height=”26″ Margin=”0,0,0,4″
VerticalAlignment=”Bottom”
HorizontalAlignment=”Right”>Cancel</Button>
</
Grid>
</
Window>

Note the binding syntax on the category lookup in the XAML above. The DisplayMemberPath=”CategoryName” SelectedValuePath=”CategoryID” are fairly straight-forward. The DisplayMemberPath is set to the field on the items in the combobox that we want to display to the user. The SelectedValuePath is set to the field on the items in the combobox that is used to set the value on the Product. To set up the list of items to display in the combobox we will set the ItemsSource property to a List(Of Category) in code. It’s on these Category objects where we are indicating the properties to use for display and selection. If we were using DataSets or LINQ to SQL classes the SelectedValuePath would match up with the CategoryID foreign key field in the Product. However since the Entity Data Model uses object associations instead of ID properties, normal data binding won’t get us all the way there.


Therefore SelectedValue=”{Binding Path=Category.CategoryID, Mode=OneWay}” is specified to indicate to traverse the Category navigation property over to the Category entity hanging off the Product and to match that CategoryID to the CategoryID on the list of categories in the combobox. This gets the right category to display when we open the form. Notice however the Mode is set to OneWay. If we don’t specify this, then when we select a new Category in the combobox, only the CategoryID on the related entity would change and NOT the reference itself which is what we need. (I’m thinking this should be possible in WPF to set the Product.Category value to a Category object in XAML but it escapes me.) Therefore we need to set it in code when we close the form. The code is a lot shorter than my explanation of the code ;-):

Imports WpfClient.MyDataServiceReference

Partial Public Class ProductDetail

‘This is the Product we are editing and is
‘ set from the calling form.
Private _product As Product
Public Property Product() As Product
Get
Return
_product
End Get
Set
(ByVal value As Product)
_product = value
‘Binds the controls to this product
Me.DataContext = _product
End Set
End Property

‘This is the same list of categories
Private _categoryList As List(Of Category)
Public Property CategoryList() As List(Of Category)
Get
Return
_categoryList
End Get
Set
(ByVal value As List(Of Category))
_categoryList = value
Me.cboCategoryLookup.ItemsSource = _categoryList
End Set
End Property

Private Sub btnOK_Click() Handles btnOK.Click
‘Manually associate the selected Category with the Product.Category property
Me.Product.Category = CType(Me.cboCategoryLookup.SelectedItem, Category)
Me.DialogResult = True
Me
.Close()
End Sub
End Class


Adding New Products


Now that we have our forms designed and our data binding set up let’s get back to the good stuff. First we need to hook up the Add button back on our ProductList form. Since we are working with a single reference to the data service client proxy it’s already attached to the objects that we’ve retrieved. Working with a single reference also allows us to send batch update requests to the service (more on that in a minute). Here’s the code for our Add button’s click event handler:

Private Sub btnAdd_Click() Handles btnAdd.Click

‘Add a new Product to the List
Dim p As Product = CType(Me.ProductView.AddNew(), Product)
p.ProductName = “New Product”
Me.ListView1.ScrollIntoView(p)

‘Create our detail form and setup the data
Dim frm As New ProductDetail()
frm.Product = p
frm.CategoryList = Me.CategoryLookup

If frm.ShowDialog() Then ‘OK
Me.ProductView.CommitNew()
Dim newCategory = p.Category

‘Add a new product and set the association to the parent Category
With Me.DataServiceClient
.AddToProducts(p)
.SetLink(p, “Category”, newCategory)
End With

‘Refresh the grid
Me.DataContext = Nothing
Me
.DataContext = Me.Products
Else ‘Cancel – remove the new product from the list
Me.ProductView.CancelNew()
End If

End Sub


Now we can Add new products to the list:


AstoriaWPF5


Notice that we’re not actually saving anything yet in the code above — we won’t hit the data service again until the user clicks Save. So in order to see if this works and what the call to add a product looks like on the wire, let’s hook up our Save button — it’s very simple:

    Private Sub btnSave_Click() Handles btnSave.Click
Try
Me
.DataServiceClient.SaveChanges()
MsgBox(“Your data was saved”)
Catch ex As Exception
MsgBox(ex.ToString())
End Try

End Sub


All we need to do here is call SaveChanges on the client proxy. If we haven’t made any changes this will do nothing. But if we have then it will send all the changes to the data service in sequence. Depending on the data sets you are working with you may opt for a different strategy like sending the updates to the server immediately after each edit. This is chattier on the wire but reduces the possibility of someone else editing the data and running into database concurrency issues. As I mentioned you can also batch all the requests into a single chunky call to the data service by specifying this in the SaveChanges:

Me.DataServiceClient.SaveChanges(System.Data.Services.Client.SaveChangesOptions.Batch)

Deleting Products


To delete a product we can call DeleteObject on the proxy. Finally I remove the object itself from the Products List in which the form is bound through the CollectionView.

    Private Sub btnDelete_Click() Handles btnDelete.Click
If MessageBox.Show(“Are you sure you want to delete this item?”, _
Me.Title, MessageBoxButton.YesNo) = MessageBoxResult.Yes Then

Dim p As Product = CType(Me.ProductView.CurrentItem(), Product)
If p IsNot Nothing Then
With Me
.DataServiceClient

.DeleteObject(p)
End With

Me.ProductView.Remove(p)
End If
End If
End Sub


Editing Products


Last but not least we need to write the code to edit products in the list. Here we need to check if the category was changed and if so we need to delete the old link to the Category and add the new one.

Private Sub btnEdit_Click() Handles btnEdit.Click

Dim p As Product = CType(Me.ProductView.CurrentItem(), Product)
If p IsNot Nothing Then

Dim frm As New ProductDetail()
frm.Product = p
frm.CategoryList = Me.CategoryLookup
Dim oldCategory = p.Category

If frm.ShowDialog() Then
Dim newCategory = p.Category
‘If the category was changed, set the new link
‘ then set the product state to updated
With Me.DataServiceClient
If (newCategory IsNot oldCategory) Then
.SetLink(p, “Category”, newCategory)
End If
.UpdateObject(p)
End With

‘Refresh the grid to pick up change to category
Me.DataContext = Nothing
Me
.DataContext = Me.Products
End If
End If
End Sub


When we run the form and make some changes, they all are submitted to the data service. If we didn’t specify the Batch option in SaveChanges then the requests are sent in sequence to the data service. Here I’ve selected an update, HTTP MERGE, operation:


AstoriaWPF6


If we did set the Batch option in the save changes you would see only one large payload in Fiddler. I’ve uploaded the sample application onto Code Gallery so have a look.


In the next post I’ll show how we can intercept queries and change operations in order to do some additional processing as well as showing how to add simple validations.


UPDATE Jan 20-2009: I actually updated the code snippets above and updated the code sample because I uncovered some issues with deletes and enforcing FK associations. Check that post out here.


Enjoy!