Beth's Chinese blog
Bay.NET is organizing an Education Day in Cupertino this Saturday the 31st which you should check out if you live in the SF Bay Area. This is an awesome opportunity to learn WPF and the M-V-VM pattern from the experts Karl Shifflett and Jamie Rodriguez. Jamie is a Technical Evangelist who helps companies with the move to WPF. Karl is one of our featured Visual Basic bloggers on the Visual Basic Developer Center and is working on the WPF Designer team. He also is the creator of the Visual Studio visualizer, Mole and the XAML Power Toys for Visual Studio.
Register for the event here. Hope to see you there!
Enjoy!
Last few posts I've been building a WPF client against ADO.NET Data Services, if you missed them:
Today I want to show a simple Excel client that queries our service and allows us to edit the customers in Northwind. I'm going to use Visual Studio 2008 (VSTO) to build a document-level customization against an Excel 2007 Workbook. We'll also build an Actions Pane that allows us to query and save customers to our ADO.NET data service. Excel's tabular data format lends itself well to CRUD database operations when we Add, Delete and Update rows in a Sheet. (NOTE: You cannot create Office solutions in Visual Studio Express editions.)
Setting Permissions on Customers
The first thing we need to do on our data service is allow access to the Customers entity set on our data model. In the first article I showed how to set up our service and permissions. For this example we'll be building upon the same data service we used for the WPF client which works with Product and Categories so I'll just need to add access to Customers:
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) config.SetEntitySetAccessRule("Customers", EntitySetRights.All) End Sub...
Creating an Excel Client
Next we'll add the Excel 2007 project to the same solution we've been working on to make things simple for debugging. However, it's important to note that when we want to deploy the Excel (or WPF) client we should place the project into it's own solution. (For more information on deploying Office solutions, read this article.) You would also need to deploy the ADO.NET Data Service just like any other WCF Service to an IIS server first before deploying any clients.
Right-click on the solution and Add --> New Project then select the Office --> 2007 category on the left and choose the Excel 2007 Workbook project.
Next add the service reference to the data service by right-clicking on the Excel project and selecting Add Service Reference. This is exactly how we added the service reference to our WPF client which generates the client proxy and entity types for us.
Tracking Changes on Customer Entities
Our actions pane is going to control how we query and save the customers. But instead of interacting with the service client proxy directly I'm going to create a class that inherits from a BindingList(Of Customer) so that we can also encapsulate the tracking of adds, updates and deletes of the customers that we bring down from the service. This class will also have a method to execute a customer query filtered by a specified country as well as a method to save changes. Finally we'll need to override some BindingList methods that will allow us to tell the data service client whether to add, update or delete as the customer entities are added to, updated or removed from the list.
Imports ExcelClient.NorthwindService Imports System.ComponentModel Public Class MyCustomerList Inherits BindingList(Of Customer) Private DataServiceContext As New NorthwindEntities(New Uri("http://localhost:1234/Northwind.svc")) Private _isAdding As Boolean Private _isSaving As Boolean Private _hasChanges As Boolean Public ReadOnly Property HasChanges() As Boolean Get Return _hasChanges End Get End Property ''' <summary> ''' Queries the customers from the ADO.NET Data Service ''' and returns the number of customers returned in the results ''' </summary> ''' <param name="country">The country to use in the query filter</param> ''' <returns>number of customers returned from the service</returns> ''' <remarks></remarks> Public Function FillCustomersByCountry(ByVal country As String) As Integer Dim i = 0 Dim customers = From c In DataServiceContext.Customers _ Where c.Country = country _ Order By c.CustomerID For Each c In customers Me.Add(c) i += 1 Next Return i End Function ''' <summary> ''' Saves the customer changes back to the service. ''' </summary> ''' <returns>true if save was successful, otherwise an exception is thrown</returns> ''' <remarks></remarks> Public Function SaveChanges() As Boolean Dim saved As Boolean Try _isSaving = True Me.DataServiceContext.SaveChanges() _hasChanges = False saved = True Finally _isSaving = False End Try Return saved End Function Protected Overrides Function AddNewCore() As Object _isAdding = True Return MyBase.AddNewCore() End Function Protected Overrides Sub RemoveItem(ByVal index As Integer) Dim c = Me(index) Me.DataServiceContext.DeleteObject(c) _hasChanges = True MyBase.RemoveItem(index) End Sub Protected Overrides Sub OnListChanged(ByVal e As System.ComponentModel.ListChangedEventArgs) If Not _isSaving Then 'The Customer partial class will need to implement ' INotifyPropertyChanged for this will work If e.ListChangedType = ListChangedType.ItemChanged Then If _isAdding Then Me.DataServiceContext.AddToCustomers(Me(e.NewIndex)) _isAdding = False Else Me.DataServiceContext.UpdateObject(Me(e.NewIndex)) End If _hasChanges = True End If MyBase.OnListChanged(e) End If End Sub End Class
In order for the OnListChanged event to fire when a property on a customer entity is changed we will need to extend the partial class generated on the client. We'll need to Implement INotifyPropertyChanged and overwrite some partial methods just like we did to our product entity in the last post when we added validation. It's important to make sure the partial class is in the same namespace as the client service reference.
Imports ExcelClient.NorthwindService Imports System.ComponentModel Namespace NorthwindService Partial Public Class Customer Implements INotifyPropertyChanged Public Event PropertyChanged(ByVal sender As Object, ByVal e As PropertyChangedEventArgs) _ Implements INotifyPropertyChanged.PropertyChanged Private Sub FirePropertyChanged(ByVal propertyName As String) If propertyName <> "" Then RaiseEvent PropertyChanged(Me, New PropertyChangedEventArgs(propertyName)) End If End Sub Private Sub OnAddressChanged() FirePropertyChanged("Address") End Sub Private Sub OnCityChanged() FirePropertyChanged("City") End Sub Private Sub OnCompanyNameChanged() FirePropertyChanged("CompanyName") End Sub Private Sub OnContactNameChanged() FirePropertyChanged("ContactName") End Sub Private Sub OnContactTitleChanged() FirePropertyChanged("ContactTitle") End Sub Private Sub OnCountryChanged() FirePropertyChanged("Country") End Sub Private Sub OnCustomerIDChanged() FirePropertyChanged("CustomerID") End Sub Private Sub OnFaxChanged() FirePropertyChanged("Fax") End Sub Private Sub OnPhoneChanged() FirePropertyChanged("Phone") End Sub Private Sub OnPostalCodeChanged() FirePropertyChanged("PostalCode") End Sub Private Sub OnRegionChanged() FirePropertyChanged("Region") End Sub End Class End Namespace
Binding an Excel Sheet to the Customer List
Now that we have a way to track our customer entities we can work with them on an Excel Sheet by binding them to a ListObject control. First open up the data sources window (on the main menu select Data --> Show Data Sources) and then add a new data source and select the Object data source type. Then expand the ExcelClient.NorthwindService namespace and select the Customer object. Now open up Sheet1 in the project to open the Excel designer and drag the Customer onto the Sheet, this will create a ListObject control that is bound to a CustomerBindingSource.
Right-click on the sheet and View Code. All we need to do is set the CustomerBindingSource.DataSource property to an instance of our MyCustomerList. I'm going to make the list a public field of the Sheet so the Actions Pane can interact with it easily.
Public Class Sheet1 Public CustomerList As New MyCustomerList() Private Sub Sheet1_Startup() Handles Me.Startup Me.CustomerBindingSource.DataSource = Me.CustomerList End Sub End Class
Hooking Up the Actions Pane
In order to add an Actions Pane to an Excel Workbook we need to open up the Workbook code-behind so double-click on ThisWorkbook in the project and add the pane to the ActionPane.Controls collection like so:
Public Class ThisWorkbook Public CustomerActions As New CustomerActionsPane() Private Sub ThisWorkbook_Startup() Handles Me.Startup Me.ActionsPane.Controls.Add(Me.CustomerActions) End Sub End Class
Now back in the CustomerActionsPane we can write code to interact with the CustomerList on Sheet1. We'll just add some code to the click event handlers of our buttons. First the Find button click handler will attempt to grab a reference to the CustomerList on Sheet1 and then it will call the FillCustomersByCountry method we created:
Public Class CustomerActionsPane Private Sub cmdFind_Click() Handles cmdFind.Click Dim msg = "" Try Dim searchTerm = Me.txtSearchTerm.Text.Trim() If searchTerm <> "" Then Dim list = ExcelClient.Globals.Sheet1.CustomerList Dim count = list.FillCustomersByCountry(searchTerm) msg = String.Format("{0} customers returned from server.", count) Else msg = "Please enter a country filter." End If Catch ex As Exception msg = ex.ToString End Try Me.txtStatus.Text = msg End Sub...
Handling Exceptions from the Data Service
Next we need to write the code for our Save button click handler. Here we need to anticipate exceptions coming back from our database. By default we don't expose verbose exception details from our data service because this could give malicious hackers clues into the architecture of our service or worse our database structure. So what we get back from the ADO.NET data service is a DataServiceRequestException with a generic message. You can add additional information to this exception by overriding the HandleException method on the data service itself. For instance we could do something like this to return database exceptions:
Public Class Northwind Inherits DataService(Of NorthwindEntities) ' This method is called only once to initialize service-wide policies. Public Shared Sub InitializeService(...
Protected Overrides Sub HandleException(ByVal args As HandleExceptionArgs) If args.Exception.InnerException IsNot Nothing Then If TypeOf args.Exception.InnerException Is SqlClient.SqlException Then 'If we have config.UseVerboseErrors = True in the InitializeService ' then the InnerException (last parameter) will be serialized in detail ' including SQL message and full stack trace otherwise just the generic ' message below will be returned. args.Exception = New DataServiceException(args.ResponseStatusCode, _ TypeName(args.Exception.InnerException), _ "The database could not save the data. Check constraints.", _ "en-US", _ args.Exception.InnerException) End If End If 'TODO: Log the exception End Sub...
The message that is returned, however, is an XML document which contains different information depending on if we have config.UseVerboseErrors set to True in the InitializeService method or not. If we're not (the default) then the XML document returned from this handler would look like:
<?xml version="1.0" encoding="utf-8" standalone="yes"?> <error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"> <code>SqlException</code> <message xml:lang="en-US"> The database could not save the data. Check constraints. </message> </error>
If we did have config.UseVerboseErrors = True in the InitializeService then the XML document would be:
<?xml version="1.0" encoding="utf-8" standalone="yes"?> <error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"> <code>SqlException</code> <message xml:lang="en-US"> The database could not save the data. Check constraints. </message> <innererror xmlns="xmlns"> <message> The DELETE statement conflicted with the REFERENCE constraint "FK_Orders_Customers"... </message> <type>System.Data.SqlClient.SqlException</type> <stacktrace> at System.Data.SqlClient.SqlConnection.OnError(SqlException...full stack trace... </stacktrace> </innererror> </error>
It's definitely not a good idea to return database details in error messages so make sure your production code does not have UseVerboseErrors set to True.
So back on our actions pane we can hook up the Save button click handler and write a few lines of code to extract any message from the XML. First we need to add an XML namespace Import because the error document declares a default namespace as seen above.
Imports System.Data.Services.Client Imports <xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"> Public Class CustomerActionsPane Private Sub cmdFind_Click(... Private Sub cmdSave_Click() Handles cmdSave.Click Dim msg = "Customers could not be saved." & vbCrLf Try Dim list = ExcelClient.Globals.Sheet1.CustomerList If list.SaveChanges() Then msg = "Customers saved." End If Catch ex As DataServiceRequestException When ex.InnerException IsNot Nothing Try Dim xmlError = XElement.Parse(ex.InnerException.Message) msg &= xmlError.<message>.Value Catch ex1 As Exception 'message isn't xml, so display string msg &= ex.InnerException.ToString() End Try Catch ex As Exception msg &= ex.ToString End Try Me.txtStatus.Text = msg End Sub End Class
Running the Excel Client
Now that we've got all our code written let's open up Fiddler and SQL Profiler and run this baby. After the Actions Pane opens we can click the Find button and the Customer list is populated and we can see it on our Sheet. From here we can add a new row or modify rows and the changes will be tracked in our list. We can also delete rows as well by right-clicking on the row and selecting Delete. (click image to enlarge)
Slick! Note that we will trigger a constraint violation if we attempt to delete a Customer that has Orders and this will cause our ExceptionHandler to return a generic message that is displayed in the status message area. We're not doing it in this example but you can opt to continue processing the rest of the changes by indicating that option in the SaveChanges method. In that case you'll get a DataServiceRespons object back that you can inspect for errors on the offending submissions. I'll expand on that in a follow-up post.
I've added a new Code Gallery sample so you can download it and play with the Excel client. It also includes the WPF client we built in the previous posts.
Today I want to show you how we can add validation or any other extra processing when data is queried from the data service as well as when we attempt to make changes to the data.
Ways of Querying a ADO.NET Data Service
First let's recap how we can query a data service. In our WPF client we've been taking advantage of LINQ but that's not the only way to send queries to the service. Because we're using the data service client framework we can write LINQ queries against the service and the client handles translating the queries into HTTP GETs. But we could also easily specify the raw URI's to send to the service. For instance, when we want to fill a combobox of categories ordered by CategoryName we could do this instead:
Imports System.Data.Services.Client..'Use the untyped DataServiceContext and pass URIs. Dim ctx As New DataServiceContext(New Uri("http://localhost:1234/Northwind.svc")) 'Explicitly execute the the HTTP GET Dim cats = ctx.Execute(Of Category)(New Uri("Categories?$orderby=CategoryName", UriKind.Relative)) 'Display results in a combobox Me.cboCategoryLookup.ItemsSource = cats.ToList()
In the code above the query is explicitly executed on the second line. You can achieve the same response by typing http://localhost:1234/Northwind.svc/Categories?$orderby=CategoryName in the address bar of your browser.
However one of the benefits of adding a service reference to our client is that it generates a proxy that inherits from the DataServiceContext that allows typed access to the entity sets that we're exposing from our service. This makes our code cleaner so we can write a LINQ query to do the same job instead:
Imports WpfClient.NorthwindService..Dim ctx As New NorthwindEntities(New Uri("http://localhost:1234/Northwind.svc")) 'Use LINQ to query the service instead Dim cats = From c In ctx.Categories _ Order By c.CategoryName 'Display results in a combobox. 'The query is executed when we access the results (calling ToList()) Me.cboCategoryLookup.ItemsSource = cats.ToList()
Notice however that there is a subtle difference in this code that you should be aware of. When you write a LINQ query it never executes unless you access the results. In this case we're accessing the results when we call ToList() on the query. This is called deferred execution and it's something to be aware of. But if we look in Fiddler then we can see that the exact same HTTP GET is sent to the service: http://localhost:1234/Northwind.svc/Categories?$orderby=CategoryName
This also means that not every LINQ query can be translated into an HTTP GET. For instance, what if we just wanted to display a couple properties of the category in the combobox. You would think we'd be able to do something like this:
Dim cats = From c In ctx.Categories _ Order By c.CategoryName _ Select c.CategoryName, c.Description
Unfortunately if you try to do this you'll get a NotSupportedException thrown at you by the client framework because it can't translate the query into an HTTP GET. In this case you need to pull down the category entities you want first and then you can project over those to create a list of anonymous types with only the properties you specify. But remember that you need to "execute" the query against the service first and then query over the list that is returned. Here's a way to do this:
Dim cats = From c In ctx.Categories _ Order By c.CategoryName 'Now project only the properties we want. ' Call ToList() on the first query to execute the service call Dim results = From c In cats.ToList() _ Select c.CategoryName, c.Description
For more details on what is and isn't supported refer to this article.
Intercepting Queries
Now that we understand how queries work we can start messing with how they execute. :-) Say we want to query only the Products in Northwind that are not Discontinued. We could write the query against our data service that specified the filter like so:
Dim products = From p In ctx.Products _ Where p.Discontinued = False _ Order By p.ProductName
Or we could write the raw URI:
Dim productURI As New Uri("Products()?$filter=Discontinued%20eq%20false&$orderby=ProductName", _ UriKind.Relative) Dim products = ctx.Execute(Of Product)(productURI)
But what if it was a new requirement of the entire system that nowhere should we be displaying discontinued products? If this is the case we should be enforcing this on our data service instead. This can be done using query interceptors on the service. The way we create these is we annotate a method on our service with the QueryInterceptor attribute. The method you write must follow these rules:
The first two requirements are easy the third may be confusing if you've never played with lambda expressions. Basically what happens is you specify additional filtering to apply onto the incoming query via this lambda. So in order to append our condition that we should only be returning products that are not discontinued we can add this method to our data service:
Imports System.Data.Services Imports System.Linq Imports System.ServiceModel.Web Imports System.Linq.Expressions 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.All) End Sub
<QueryInterceptor("Products")> _ Public Function FilterProducts() As Expression(Of Func(Of Product, Boolean)) 'Only return products that are not discontinued Return Function(p) p.Discontinued = False End Function End Class
Now we can write our queries without specifying the additional filter on discontinued and this will not be sent to the service from our client in the HTTP GET but will be executed against our database. The query interceptor will execute regardless if we write a LINQ query or feed it the raw URI.
LINQ Query:
Dim products = From p In ctx.Products Order By p.ProductName Me.ListView1.ItemsSource = products.ToList()
URI:
Dim productURI As New Uri("Products()?$orderby=ProductName", UriKind.Relative) Dim products = ctx.Execute(Of Product)(productURI) Me.ListView1.ItemsSource = products.ToList()
Pretty slick. You could of course do other processing here first. And you can also specify your own additional service operations as well by attributing them with a <WebGet> attribute. More on those in a later post.
Validation with Change Interceptors
You can also add methods to your service that will execute when changes are submitted. This allows us to add validation or other processing onto the data being submitted to the database. You do this by attributing a method in the data service with the ChangeInterceptor attribute. A change interceptor will pass the entity being saved and a parameter that indicates what update operation is being performed. For instance, say we want to put a validation on our ProductName so that users cannot submit empty product names to the database. We could write a method in our data service like so:
<ChangeInterceptor("Products")> _ Public Sub OnChangeProducts(ByVal p As Product, ByVal ops As UpdateOperations) If ops = UpdateOperations.Add OrElse ops = UpdateOperations.Change Then 'Do not allow products with empty names If p.ProductName = "" Then Throw New DataServiceException(400, "Product name cannot be empty") End If End If End Sub
When we throw a DataServiceException we can specify the HTTP status code and the message to return to the client. 400 indicates "Bad Request" and we pass the message on what the problem was. So if we try to submit a new or existing product with no product name we will get an HTTP error as seen in Fiddler:
This is what's happening on the wire when we call SaveChanges and the exception is caught on the client. This prevents our data from being invalid no matter what client it's coming from. However this isn't that user-friendly to say the least. If we're building a smart client it's much better to put this type of validations on the client as well.
We can do this in our WPF client by extending the Product partial class and implementing IDataErrorInfo and adding our validation. On the WPF client create a new class called Product and place it in the same exact namespace as the NorthwindEntities data service client proxy that is generated for us when we add the service reference. It's called NorthwindService in our case. Then we can overwrite the partial method OnProductNameChanging to do the client-side validation. This method is called from the ProductName property setter in the generated entity on the client. Here's an example of how we can collect validation messages on the Product.
Imports WpfClient.NorthwindService Imports System.ComponentModel Namespace NorthwindService Partial Public Class Product Implements IDataErrorInfo Private Sub OnProductNameChanging(ByVal value As String) If value Is Nothing OrElse value.Trim = "" Then Me.AddError("ProductName", "Product name cannot be empty") Else Me.RemoveError("ProductName") End If End Sub #Region "IDataErrorInfo Members" Private m_validationErrors As New Dictionary(Of String, String) Private Sub AddError(ByVal columnName As String, ByVal msg As String) If Not m_validationErrors.ContainsKey(columnName) Then m_validationErrors.Add(columnName, msg) End If End Sub Private Sub RemoveError(ByVal columnName As String) If m_validationErrors.ContainsKey(columnName) Then m_validationErrors.Remove(columnName) End If End Sub Friend ReadOnly Property HasErrors() As Boolean Get Return (Me.Error IsNot Nothing) End Get End Property Friend ReadOnly Property [Error]() As String _ Implements System.ComponentModel.IDataErrorInfo.Error Get If m_validationErrors.Count > 0 Then Return "Product Data is invalid" Else Return Nothing End If End Get End Property Default Friend ReadOnly Property Item(ByVal columnName As String) As String _ Implements System.ComponentModel.IDataErrorInfo.Item Get If m_validationErrors.ContainsKey(columnName) Then Return m_validationErrors(columnName).ToString Else Return Nothing End If End Get End Property #End Region End Class End Namespace
Notice that the client-side IDataErrorInfo properties are declared as Friend (internal) so that they are not serialized back up to the server. Next we need to make sure the bindings in the XAML of our ProductDetail form is set up to display the error.
<TextBox Text="{Binding Path=ProductName, ValidatesOnDataErrors=True}" Height="25" Name="TextBox1" Width="180" Margin="3" HorizontalAlignment="Left" />
You can also add a validation ErrorTemplate if you like. I've shown this validation technique with WPF here before. So when we don't enter the ProductName on a product we can display the problem to the user right away without bothering our data service:
Check out the updated sample on Code Galley, in there I also implement IEditableObject so that users can cancel out of editing of the products.
However, the fact that we have to put rules in two locations in our code is a total drag. If we could type share the entity partial classes on the server and the client then we could write this code in one place and run it in both the client and the server. This is why if you have complex business rules you're probably better off creating your own DataContracts and implementing your own WCF services. However, applications like this that have simple validation and heavy CRUD requirements make it a perfect candidate to use ADO.NET Data Services.
Next post I'll show how we can query and edit tabular data inside of an Excel client and post changes back to the data service.
Last post I described one way to build a smart client in WPF against ADO.NET Data Services. In this example we are editing the products table and associating them to categories in the categories table from Northwind. In the Northwind database the CategoryID on the Products table allows nulls so that you can have products that do not necessarily belong to a category. More common, however, are associations that are required and enforced by not allowing nulls in the foreign keys in the database. If we make a change to our Northwind data model to enforce this we will encounter errors when we try to save the data back to the service.
Enforcing Foreign Key Constraints
Let me show you what I mean by changing the example to enforce that products belong to a category. Open up the Northwind data model in the designer and select the FK_Products_Categories association and change the multiplicity property on the category end to 1 (One) and then save the model and build the service. Then make sure to update the service reference on the client and rebuild again (under the client project expand Service References then right-click on the reference and choose Update Reference).
If we tried to use the original code sample against this new data model we would have problems. When we run the client and make a change to a product and associate a new category we would get the error: A relationship is being added or deleted from an AssociationSet 'FK_Products_Categories'. With cardinality constraints, a corresponding 'Products' must also be added or deleted. This is a problem because the Data Services client was trying to delete the link to the category on the product first and then update the product after that.
If we tried to add a new product it would have also failed: Entities in 'NorthwindEntities.Products' participate in the 'FK_Products_Categories' relationship. 0 related 'Categories' were found. 1 'Categories' is expected. This is a problem because the client data service is attempting to add a new product with no associated category (if you look in the request header you will not see the category reference). Now that we've changed the model these situations are not allowed so they fail.
In order to fix them I had to make a couple code changes. First we need to change how we were setting the category association. Instead of calling DeleteLink and AddLink on the Category entity we need to call SetLink on the Product entity instead. In fact, this will work regardless if we are enforcing the association exist or not so it's best to use this when setting 1..1 associations (and it makes a lot more sense to me as well). Because of this I updated the previous post and the code sample on code gallery with this new code. So when we edit our Product we need to change our code to this:
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 'changes were made 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) 'This will not work if we are enforcing the FK to ' Category from Product. Use SetLink instead '.DeleteLink(oldCategory, "Products", p) '.AddLink(newCategory, "Products", p) End If .UpdateObject(p) End With ...
When we add new Products we also need to call SetLink after we add the new Product:
'Add a new Product to the List Dim p As Product = CType(Me.ProductView.AddNew(), Product) p.ProductName = "New Product" '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) 'This will not work if we are enforcing the FK to ' Category from Product. Use SetLink instead '.AddLink(newCategory, "Products", p) End With...
The rule of thumb is AddLink is used when we are modifying the 1...many or many...many relationships on an entity and SetLink is used to modify a 1..1 relationship as in the case of our example. Check out this blog post which should help clear up when to use SetLink versus AddLink.
Deleting Entities Involved in an Association
You may have issues deleting entities that are involved in an association (regardless of whether they are enforced or not). This was a bug in ADO.NET Data Services described in KB958484 and is fixed with this update. (This should be flowing along with your regularly scheduled Windows Updates but you may not have gotten it yet so if you're experiencing this issue make sure you install this fix.) In our example I was forced to detach the category object from the client context before submitting the deletes.
Dim p As Product = CType(Me.ProductView.CurrentItem(), Product) If p IsNot Nothing Then With Me.DataServiceClient .Detach(p.Category) .DeleteObject(p) End With Me.ProductView.Remove(p) End If
Unfortunately this has side effects when working in batch like we've set up on our form. If I then added or edited another product but chose the same category that was on a product that I deleted, then I would get an InvalidOperationException on the client: The context is not currently tracking the entity. This is because I detached the category when I shouldn't have. But if I don't detach the category before deleting the product then I get another error, this time an XMLException: Root element is missing. Note, that if we didn't bring down the associated category for each product then this would work fine. It only happens when we have an association.
Once you install the update this bug will be fixed and the correct code should be to just call .DeleteObject:
With Me.DataServiceClient .DeleteObject(p) End With
This means that we can enable batching and add/update/delete products at will and submit them all at once in one call.
Try 'Batching will wrap all changes submitted into a transaction Me.DataServiceClient.SaveChanges(System.Data.Services.Client.SaveChangesOptions.Batch) MsgBox("Your data was saved") Catch ex As Exception MsgBox(ex.ToString()) End Try
Notice that when we batch save our changes we also get a database transaction. In your own applications you should determine if the set of data you are submitting to the server should be wrapped in a transaction or not (click image to enlarge).
Products aren't reliant on each other so in this example it's probably overkill. You should evaluate your sets of data carefully to ensure the right level of isolation is needed. I updated the sample on Code Gallery for you to play with.
You may be asking "Why doesn't the data services client do all this association linking for me automatically?" The reason I've gleaned from a few folks is that the team wanted the framework to be very simple and work with POCO (Plain Old CLR Objects) with no extra overhead. That said, there is a line of practicality here so they are looking into extending this in future versions. I'd like to also see the ability to extend the entities and add additional business functionality and properties not backed by database fields to them as well. That would make ADO.NET Data Services my data access layer of choice for general purpose n-tier smart client business applications.
Okay, so now that we have CRUD working properly in all our scenarios in the next post (soon) I'll show how we can intercept queries and change operations in order to do some additional processing -- I know I said that last post but this time I mean it ;-).
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:
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.
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.)
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.
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:
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:
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.
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....
I'm still catching up from holiday vacation so I just watched my dnrTV episode today that I filmed with Carl when I saw him at DevTeach in Montreal last month. I always enjoy interviewing with Carl. He has a way of making you feel comfortable and making you look maaaavelous in an interview.
In this episode I show a new feature of Visual Studio 2010 related to drag-drop data binding for WPF. I start by building a teeny WPF form from scratch in Visual Studio 2008 and hooking up the bindings manually (similar to how I do it in the WPF Forms over Data Videos). Then I show how to create a Master-Detail form in WPF using drag-drop data binding which saves a ton of time because all of the controls and bindings and data load code is generated for you, similar to what we have now in Winforms. It's one of the RAD features you would expect be in the Visual Studio box -- and it is in VS2010.
Here's some more resources on WPF Drag-Drop Data Binding and Visual Studio 2010 and the Program Manager, Milind Lele, in charge of the feature:
Here's how you can get the CTP and a fix for the VPC expiration issue:
If you can't wait for Visual Studio 2010 (who can!?) to start jumping into WPF for business applications, check out these resources:
Feel free to post a comment to this post if you find more WPF Business Application resources out there.
We just released a new How Do I Video onto the VB Dev Center on how to format controls on data entry forms using the IValueConverter like I showed in a previous post. The IValueConverter allows you full control over the display of the data as well as the editing of that data by users in the controls.
Check out all the WPF Forms over Data videos here.
Happy New Year everyone! I hope you all had a great holiday like I did. My family went on a cruise down the west coast of Mexico and it was awesome. I'm still wading through all my email ;-) It was so much fun to be on a ship for New Year's Eve celebrations but I think I ate my body weight in food and drink. My New Year's resolution will definitely be to get more exercise this year.
MSDN Magazine also made a New Year's resolution as stated in Howard Dierking's January 2009 Editor's Note. You may have already seen the news on the VB Team blog that all code samples for articles will be available on Code Gallery and provided in C# and Visual Basic. This decision was made based on the huge amount of feedback from the developer community.
Many thanks to Howard and his team for listening to us!