Beth's Chinese blog
The session I did last November at QCon in San Fransisco is online so check it out. In this talk I introduced VB 9's LINQ to XML syntax and XML literals, axis properties, and embedded expressions.
It's so weird watching myself but I think it was a pretty good presentation even though I was fighting a cough. I use my hands a lot (so unlike me <g>) but I think that helps express my points. That's my story and I'm sticking to it. ;-) I'll probably do even more hand waving at DevTeach in a couple weeks.
The code in the video is a bit hard to see, so if you prefer, I did a similar webcast earlier this month that you can view here.
Enjoy!
... and so are a bunch of other awesome speakers.
Join us all at DevTeach (first time in) Toronto, May 12th -16th. I've been speaking at DevTeach for a while where it originally started in Montreal. The organizers expanded last year to include Vancouver and now Toronto. I've never been there so it should be exciting (bonus: they have baseball).
If you're not familiar with DevTeach it's a rather intimate .NET/SQL developer conference that's pack full of world-renowned speakers. It's a lot of fun and packed with a lot of great content too. If you're not convinced, here's some more reasons to go.
Check out the sessions.
Register here.
Hope to see you there!
Often times we need to parse HTML for data. Sure in a perfect world everything would have a nice service or API wrapped around it but as we all know this is not always the case. Many times we're left with parsing files or "screen scraping" to get the data we need from other applications. Sure this is brittle, but sometimes it's the best we can do. And sometimes you're just trying to get the data once so "good enough" is really good enough.
I was faced with that challenge myself this week. Yes even here not all systems expose services or if they do, finding the documentation or person to consult would take longer than writing a simple program. ;-) At the core all I needed to do was query a couple pieces of data from a bunch of web pages. This seemed like the perfect opportunity to use LINQ to XML because the structure of the page was pretty well formed HTML. However there were a couple tricks to figure out mainly because LINQ to XML doesn't support HTML entities. It only supports character entities and the built in XML entities (< > " & ').
Working with simple HTML in an XElement is very straightforward, as long as it's well-formed and doesn't contain any HTML entity references:
Dim html = <html> <head> <title> Test Page </title> </head> <body> <a id="link1" href="http://mydownloads1.com">This is a link 1</a> <a id="link2" href="http://mydownloads2.com">This is a link 2</a> <a id="link3" href="http://mydownloads3.com">This is a link 3</a> <a id="link4" href="http://mydownloads4.com">This is a link 4</a> </body> </html> Dim links = From link In html...<a> For Each link In links Console.WriteLine(link.@href) Next
But as we all know HTML almost always contains entity references all over the place (like for the HTML space). Also if you end up with any querystring parameters in your hrefs, when you try to load the HTML into the XElement, you get the same problem. Additionally if you paste a literal into the VB editor it places a semicolon into the querystring because it automatically tries to interpret it as an entity and places a semicolon where you don't want it.
So to fix this you need to remove all the unsupported HTML entity references as well as replace the & characters with &. So in the pages I was loading luckily they were not that complicated and only contained and the problematic querystrings. This is an example of the page I was trying to load:
<html xmlns="http://www.w3.org/1999/xhtml"> <head> <title> Sample Page </title> <link href="css/page.css" rel="StyleSheet"/> </head> <body > <!--begin form --> <form name="form1" method="post" action="page.aspx?Product=Cool&Id=12345" id="form1"> <!--begin main table --> <table class="tblMain" cellspacing="0" cellpadding="0"> <!--Properties --> <tr> <td class="tdHead">Properties</td> </tr> <tr> <td class="tdGrid"> <div> <table class="grid" cellspacing="0" cellpadding="3" border="1" id="dgPage" style="border-collapse:collapse;"> <tr class="grid_row"> <td class="grid_item" style="font-weight:bold;width:100px;">ID</td> <td class="grid_item" style="width:480px;">12345</td> </tr> <tr class="grid_row"> <td class="grid_item" style="font-weight:bold;width:100px;">Published</td> <td class="grid_item" style="width:480px;">05/04/2007</td> </tr> </table> </div> </td> </tr> <!--Details --> <tr> <td id="tdHeadDetails" class="tdHead">Statistics</td> </tr> <tr> <td class="tdGrid"> <div> <table class="grid" cellspacing="0" cellpadding="3" rules="all" border="1" id="dgDetails" style="border-collapse:collapse;"> <tr class="grid_header"> <th scope="col">Rating :</th> <th scope="col">Raters :</th> <th scope="col">Pageviews :</th> <th scope="col">Printed :</th> <th scope="col">Saved :</th> <th scope="col">Emailed :</th> <th scope="col">Linked :</th> <th scope="col"></th> </tr> <tr class="grid_row"> <td class="grid_item" style="width:60px;">5.00</td> <td class="grid_item" style="width:60px;">100</td> <td class="grid_item" style="width:80px;">1000000</td> <td class="grid_item" style="width:60px;">150</td> <td class="grid_item" style="width:60px;">1000</td> <td class="grid_item" style="width:60px;">100</td> <td class="grid_item" style="width:280px;">40</td> <td class="grid_item"> <a href="http://www.somewhere.com/default.aspx?ID=12345&Name=Beth" target="_blank">View</a> </td> </tr> </table> </div> </td> </tr> </table> </form> </body> </html>
So here's what I did to load this programmatically and fix up the HTML. Also notice that I need to add an Imports statement in order to import the default xml namespace that is declared in the HTML document otherwise our query later will not return any results.
Imports <xmlns="http://www.w3.org/1999/xhtml"> Imports System.Net Imports System.IO Public Class SimpleScreenScrape Function GetHtmlPage(ByVal strURL As String) As String Try Dim strResult As String Dim objResponse As WebResponse Dim objRequest As WebRequest = HttpWebRequest.Create(strURL) objRequest.UseDefaultCredentials = True objResponse = objRequest.GetResponse() Using sr As New StreamReader(objResponse.GetResponseStream()) strResult = sr.ReadToEnd() sr.Close() End Using 'Replace HTML entity references so that we can load into XElement strResult = Replace(strResult, " ", "") strResult = Replace(strResult, "&", "&") Return strResult Catch ex As Exception Return "" End Try End Function Sub QueryData() Dim html As XElement Try Dim p = GetHtmlPage("http://www.somewhere.com/default.aspx") Using sr As New StringReader(p) html = XElement.Load(sr) End Using Catch ex As Exception MsgBox("Page could not be loaded.") Exit Sub End Try
.. 'Now we can write the queries... .
Now for the fun part, the actual querying! Now that the document is loaded into the XElement the querying of it becomes a snap. I needed to grab the publish date, and then all the statistics from the page. This is easily done with a couple LINQ to XML queries, one query for each of the HTML tables where the data is located:
'I'm using FirstOrDefault here because I know my page ' only has one of these tables Dim stats = (From stat In html...<table> _ Where stat.@id = "dgDetails" _ Select fields = stat.<tr>.<th>, values = stat.<tr>.<td>).FirstOrDefault() 'Same here. FirstOrDefault because there's only one "Published" ' html row (<tr>) on the page that I'm looking for. Dim lastPublished = (From prop In html...<tr> _ Where prop.<td>.Value = "Published" _ Select prop.<td>(1).Value).FirstOrDefault() Console.WriteLine(lastPublished) For i = 0 To stats.fields.Count - 1 Console.WriteLine(stats.fields(i).Value & " = " & stats.values(i).Value) Next
And that's it. For this simple utility this is good enough for me and took me about 15 minutes to program using LINQ. The trick to loading the HTML document into an XElement is to remove all the unsupported HTML entity references first.
The MVP summit was last week -- my first summit where I wasn't an MVP but instead an employee -- and what an enjoyable experience it was! It was awesome seeing MVPs from all over the world. Microsoft really throws a great summit, this year renting out the entire EMP like they did a couple years ago. The difference this year was live band karaoke and Rock Band on the XBox set up on a real stage. Remind me to pick songs I know the words to next time I get up and sing!
One of the highlights of the summit was Adam Cogan, a Visual Studio Team System MVP from Australia, who brought over some beer for me on a request from Chuck Sterling. (Chuck realized I loved beer after dinner at his place with Sara Ford.) I figured since Adam was bringing over some Australian beer he could also bring me a Victoria Bitter.... not a very good beer but I LOVE the logo. Chris Williams, Visual Basic MVP, took a picture of me holding it up in one of the VB sessions:
If you can't see it, here's a picture of the bottle:
But the beer they really wanted me to try is called Little Creatures and I highly recommend it to the hop heads out there! Unfortunately one of the bottles broke in Adam's luggage (alcohol abuse!) but he saved one. Thanks so much for the trouble Adam, REALLY! Anyone that goes that that great length to bring a girl beer around the world is #1 in my book!
Enjoy! (you know I did ;-))
In my previous posts this week I showed how to build a simple distributed application with a Windows client, a WCF hosted middle-tier and a data access layer that used LINQ to SQL:
LINQ to SQL N-Tier Smart Client - Part 1 Building the Middle-Tier
LINQ to SQL N-Tier Smart Client - Part 2 Building the Client
After sleeping on the design I realized that there's a scenario that we may want to handle. When we built the connected client-server version of the application (using the connected DataContext), because the DataContext is tracking all our changes (updates/inserts/and deletes) when we call SubmitChanges these updates are all processed in one single database transaction.
This may or may not be required for your application and in the case of Orders/OrderDetails it's okay to allow the updates and inserts and then the deletes to be processed in separate transactions. However what if we were working with drug interactions in a medical application or other data that needs to provide this level of integrity?
It's easy to make these modifications to our n-tier application we built. All we need to do is attach ALL the changes that we want processed in a single database transaction to one instance of the DataContext. To do this first we need to modify our service to accept all our changes. This can end up putting more data on the wire which we discussed in Part 1 so you need to evaluate your scenarios carefully. In our case I'm only pulling up open orders for a particular customer ID so the data set is relatively small.
First add the following interface on our WCF service:
<ServiceContract()> _ Public Interface IOMSService..<OperationContract()> _ Function SaveAllOrders(ByRef orders As OrderList, _ ByVal deletedOrders As IEnumerable(Of Order), _ ByVal deletedDetails As IEnumerable(Of OrderDetail)) As Boolean
End Interface
Next add the implementation to the OMSDataManager class in the data access layer to go ahead and attach all the changes to a single DataContext and submit all the changes at once. Note that the validation is performed exactly as before (when SubmitChanges is called).
Public Shared Function SaveAllOrders(ByRef orders As IEnumerable(Of Order), _ ByVal deletedOrders As IEnumerable(Of Order), _ ByVal deletedDetails As IEnumerable(Of OrderDetail)) As Boolean Dim hasOrders = (orders IsNot Nothing AndAlso orders.Count > 0) Dim hasDeletedOrders = (deletedOrders IsNot Nothing AndAlso deletedOrders.Count > 0) Dim hasDeletedDetails = (deletedDetails IsNot Nothing AndAlso deletedDetails.Count > 0) If (Not hasOrders) AndAlso (Not hasDeletedOrders) AndAlso (Not hasDeletedDetails) Then Return False 'nothing at all to save End If Dim db As New OMSDataContext For Each o In orders 'Insert/update orders and details If o.OrderID = 0 Then db.Orders.InsertOnSubmit(o) Else db.Orders.Attach(o, o.IsDirty) End If For Each d In o.OrderDetails If d.IsDirty Then If d.OrderDetailID = 0 Then db.OrderDetails.InsertOnSubmit(d) Else db.OrderDetails.Attach(d, True) End If End If Next Next If hasDeletedOrders Then 'Delete orders and related details db.Orders.AttachAll(deletedOrders, False) db.Orders.DeleteAllOnSubmit(deletedOrders) For Each o In deletedOrders For Each detail In o.OrderDetails db.OrderDetails.DeleteOnSubmit(detail) Next Next End If If hasDeletedDetails Then 'Now delete the order details that were passed in ' (these order parents were not deleted, just the details) db.OrderDetails.AttachAll(deletedDetails, False) db.OrderDetails.DeleteAllOnSubmit(deletedDetails) End If Try 'There's one database transaction for all records that are attached. 'Since we attached all updates/inserts/deletes ' they will all be processed in one transaction. db.SubmitChanges(ConflictMode.ContinueOnConflict) 'Reset the IsDirty flag For Each o In orders o.IsDirty = False For Each d In o.OrderDetails d.IsDirty = False Next Next Catch ex As ChangeConflictException 'TODO: Conflict Handling Throw Return False End Try Return True End Function
We can then modify our form to call this new operation. On the client form I just added a new method called SaveAll. Note that the same simple change tracking is being used.
Private Sub SaveAll() 'Push any pending edits on the BindingSources to the BindingList Me.Validate() Me.OrderBindingSource.EndEdit() Me.OrderDetailsBindingSource.EndEdit() Dim saved = False 'Only save changes if there are some and they are valid If Me.HasChanges AndAlso Me.ValidateOrders() Then Dim saveOrders = Me.Orders.ToArray() Dim delOrders = Me.DeletedOrders.ToArray() Dim delDetails = Me.DeletedDetails.ToArray() Try If saveOrders.Count > 0 OrElse delOrders.Count > 0 OrElse delDetails.Count > 0 Then 'Update/insert orders/details If proxy.SaveAllOrders(saveOrders, delOrders, delDetails) Then Me.DeletedDetails.Clear() Me.DeletedOrders.Clear() saved = True End If End If Catch ex As Exception MsgBox(ex.ToString) End Try 'Merges added keys and any validation errors Me.MergeOrdersList(saveOrders) End If If Me.HasErrors Then 'Display any errors if there are any Me.DisplayErrors() MsgBox("Please correct the errors on this form.") Else If saved Then MsgBox("Your data was saved.") Else MsgBox("Your data was not saved.") End If End If End Sub
So now when we make updates, inserts and deletes to our Orders and OrderDetails then we can save them all in a single database transaction.
I've uploaded the latest version of the application onto Code Gallery with the modifications.
In my last post we built the service and data access layer for our LINQ to SQL N-Tier application. In this post we'll walk through building a very simple Windows client form that works with our middle-tier.
Adding the Service Reference
Now that we have our middle-tier built it's time to add the service reference to the client project. Sine we have both .NET on the server and the client I'm going to use type sharing so that we can reuse the business objects (LINQ to SQL classes) on both ends. If you recall we we already added a project reference on the client to the OMSDataLayer project that defines these types.
Once you add that project reference we can add the service reference by right-clicking on the client and selecting "Add Service Reference" which opens up the Visual Studio 2008 Add Service Reference dialog. Hit the Discover button and it will pick up the OMSService in our solution. Click on the "Advanced" button and you'll notice some interesting settings here that I should mention.
Note here that the default is to "Reuse types in all referenced assemblies". This means that since we added the project reference to our LINQ to SQL business objects first, when the service proxy is generated it will not create new classes on the client, instead it will reference our business object types directly. Although this can make versioning more of a challenge it drastically cuts down the amount of code we have to write to maintain our business rules because now they are shared. However note that rules we call from the client cannot access the database directly. Our application here does not have any rules like that but it's something you may need to code for in your scenarios.
The other interesting settings I'll mention are the Collection type and Dictionary collection type settings since we're passing these types from our service. You can set these types to serialize differently if you need to. For instance, you can set the collection type to a BindingList if you are going to use all the collections from this service in typical data binding scenarios. Since this setting is for the entire service and we're only going to need a BindingList for just our GetOrdersByCustomerID result, I'm opting to keep the default Array type instead.
Loading the Data
Now we're ready to build our n-tier master-detail (one-to-many) form. Create a new form and then add a new data source (Menu, Data --> Add New Data Source) and select Object. Then expand the OMSDataLayer and choose the Order object and then do it again for Product.
Now we can build the master-detail form like I showed in this post (see the "Data Sources and Data Binding the Form" section) but this time against the objects in the shared assembly. The other main difference is that we don't need the Customer object because we're going to limit our data to just one customer.
Now we're ready to create an instance of our service reference and load the Orders from the middle-tier. Since the list will deserialize as an array, I'm going to place them into a BindingList that the form will manage. This will give us automatic add/delete support to the collection and a better data binding experience. I'm also going to set up a couple lists to track deletes of Order and OrderDetails. In a real application typically you create your own subclass of the BindingList and have it track these things but I'm trying to keep this example simple. We'll also load the products just like we did before but this time in our query we call the service instead.
Public Class NtierMasterDetailForm Dim customerID As Integer = 1 'should come from a search form Dim proxy As New OMSServiceReference.OMSServiceClient Dim Orders As New BindingList(Of Order) Dim DeletedOrders As New List(Of Order) Dim DeletedDetails As New List(Of OrderDetail) Private Sub Form1_Load() Handles MyBase.Load 'Load the orders from our service Dim orderList = proxy.GetOrdersByCustomerID(customerID) For Each o In orderList Me.Orders.Add(o) Next Me.OrderBindingSource.DataSource = Me.Orders Dim emptyProduct As Product() = _ {New Product With {.Name = "<Select a product>", .ProductID = 0}} Me.ProductBindingSource.DataSource = (From Empty In emptyProduct).Union( _ From Product In proxy.GetProductList _ Order By Product.Name) End Sub
Tracking Changes on the Objects
Now let's see how we're going to track all the changes made to the Orders and OrderDetails. First let's take another look at our BaseBusiness class. This is the class that we created in this post when we implemented our validation. When we built the middle-tier I mentioned that we needed to add this property but it's the client that needs to set it. Here's a look at the modifications we need to make to the BaseBusiness object including adding the DataMember attribute to the new IsDirty property as well as on the ValidationErrors dictionary.
<DataContract()> _ Public Class BaseBusiness Implements IDataErrorInfo Private m_isDirty As Boolean <DataMember()> _ Public Property IsDirty() As Boolean Get Return m_isDirty End Get Set(ByVal value As Boolean) m_isDirty = value End Set End Property 'This dictionary contains a list of our validation errors for each field Private m_validationErrors As New Dictionary(Of String, String) <DataMember()> _ Public Property ValidationErrors() As Dictionary(Of String, String) Get Return m_validationErrors End Get Set(ByVal value As Dictionary(Of String, String)) m_validationErrors = value End Set End Property...
Since LINQ to SQL classes implement IPropertyNotifyChanged we can handle this event to set the IsDirty flag. The easiest way to set this flag is to tell the business objects themselves to do it. In order to hook up this event handler again when the objects are deserialized from the WCF service we can attribute a method with the OnDeserializedAttribute and add an event handler to the PropertyChanged event on all our business objects.
Partial Class Order Inherits BaseBusiness <OnDeserialized()> _ Private Sub OnDeserialized(ByVal context As StreamingContext) AddHandler Me.PropertyChanged, AddressOf MyPropertyChanged End Sub Private Sub MyPropertyChanged(ByVal sender As Object, ByVal e As System.ComponentModel.PropertyChangedEventArgs) _ Handles Me.PropertyChanged If e.PropertyName <> "Customer" Then Me.IsDirty = True End If End Sub...
The trick in the handler is to set the IsDirty flag only if the entity reference (the parent reference) property is not being set because we want to only set this flag if the user is making changes, not when the collection reference is set by the system.
Tracking adds is really easy because when an object is added to the collection it will be sent to the middle-tier and we can use the primary keys to determine if the Order or OrderDetail is new. For instance, if the OrderID on the Order is equal to zero (OrderID = 0) then we know we have a new object in the collection.
Deletes are a bit trickier because when you delete an object from the collection it's gone. If you are implementing a custom BindingList then you can just override the RemoveItem method but in our simple form we're just going to add the Order or OrderDetail being deleted to our Deleted* lists when the delete buttons are clicked on the form.
Private Sub OrderNavigatorDeleteItem_Click() Handles BindingNavigatorDeleteItem.Click 'Track deletes of orders If Me.OrderBindingSource.Position > -1 Then Dim order As Order = CType(Me.OrderBindingSource.Current, Order) If order.OrderID > 0 Then 'Greater than 0 indicates that the object came from the database. 'If it's = 0 then we know the object was added here then deleted ' and we don't need to track that. Me.DeletedOrders.Add(order) End If End If End Sub Private Sub DetailNavigatorDeleteItem_Click() Handles DetailNavigatorDeleteItem.Click 'Track deletes of details If Me.OrderDetailsBindingSource.Position > -1 Then Dim detail As OrderDetail = CType(Me.OrderDetailsBindingSource.Current, OrderDetail) If detail.OrderDetailID > 0 Then Me.DeletedDetails.Add(detail) End If End If End Sub
Validating and Saving our Changes
Before we send the changes to the service on the middle-tier we should validate the business objects here to save a round-trip. When we were working with the LINQ to SQL DataContext in connected mode the objects were validated when we called SubmitChanges(). This still happens in our middle-tier code but we need to validate here on the client as well so I added a public Validate method to the LINQ to SQL partial classes that just simply call into the OnValidate private methods we wrote previously. In the case of Order we'll also validate any OrderDetails.
Partial Class Order Inherits BaseBusiness . . . Public Sub Validate() Me.OnValidate(System.Data.Linq.ChangeAction.None) 'Validate the OrderDetails if there are any For Each d In Me.OrderDetails d.Validate() Next End Sub...
Now we're ready to write our save code. If everything validates here on the client we first then send the deletes to the middle-tier, and if all goes well there then we clear the lists where we were tracking those objects. Then we can send the added and updated rows into the middle-tier. The middle-tier will then perform the validation there and then update and insert the business objects, and return the added primary/foreign keys. If we had any additional middle-tier business rules then those would also run and we could add additional validation messages that would be sent back in the ValidationErrors collection on each object.
The last thing left to do is dump the collection coming back from the middle-tier with our added keys back into the BindingList on our form. We just need to suspend the data binding first then we can copy the array back into the BindingList collection. Here's all the save code and supporting form methods.
Private Sub OrderBindingNavigatorSaveItem_Click() _ Handles OrderBindingNavigatorSaveItem.Click Me.Save() End Sub ''' <summary> ''' Saves all changes to the middle-tier ''' </summary> ''' <remarks></remarks> Private Sub Save() 'Push any pending edits on the BindingSources to the BindingList Me.Validate() Me.OrderBindingSource.EndEdit() Me.OrderDetailsBindingSource.EndEdit() Dim saved = True 'Only save changes if there are some and they are valid If Me.HasChanges AndAlso Me.ValidateOrders() Then Dim saveOrders = Me.Orders.ToArray Try If Me.DeletedDetails.Count > 0 OrElse Me.DeletedOrders.Count > 0 Then 'Delete any orders/details If proxy.DeleteOrders(Me.DeletedOrders.ToArray, _ Me.DeletedDetails.ToArray) Then Me.DeletedDetails.Clear() Me.DeletedOrders.Clear() Else saved = False End If End If If saved Then If saveOrders.Length > 0 Then 'Update/insert orders/details saved = proxy.SaveOrders(saveOrders) End If End If Catch ex As Exception MsgBox(ex.ToString) End Try 'merges added keys and any validation errors from the middle-tier Me.MergeOrdersList(saveOrders) End If If Me.HasErrors Then 'Display any errors if there are any (same technique as before) Me.DisplayErrors() MsgBox("Please correct the errors on this form.") Else If saved Then MsgBox("Your data was saved.") Else MsgBox("Your data was not saved.") End If End If End Sub ''' <summary> ''' Returns True if there are any validation errors on the business objects. ''' </summary> ''' <returns></returns> ''' <remarks></remarks> Private Function HasErrors() As Boolean For Each o In Me.Orders If o.HasErrors Then Return True Next Return False End Function ''' <summary> ''' Validates all the orders (order details are validated in the Order.Validate) ''' </summary> ''' <remarks></remarks> Private Function ValidateOrders() As Boolean Try For Each o In Me.Orders o.Validate() Next Catch ex As ValidationException Return False End Try Return True End Function ''' <summary> ''' Returns True if there are any changes to any of the orders/details. ''' </summary> ''' <returns></returns> ''' <remarks></remarks> Private Function HasChanges() As Boolean If Me.DeletedDetails.Count > 0 OrElse Me.DeletedOrders.Count > 0 Then Return True End If For Each o In Me.Orders If o.IsDirty Then Return True For Each d In o.OrderDetails If d.IsDirty Then Return True Next Next Return False End Function ''' <summary> ''' Copies from array to the BindingList while suspending data binding ''' </summary> ''' <param name="changes"></param> ''' <remarks></remarks> Private Sub MergeOrdersList(ByVal changes() As Order) Dim pos = Me.OrderBindingSource.Position Me.OrderBindingSource.SuspendBinding() Me.OrderBindingSource.RaiseListChangedEvents = False Me.Orders.Clear() For Each o In changes Me.Orders.Add(o) Next Me.OrderBindingSource.ResumeBinding() Me.OrderBindingSource.RaiseListChangedEvents = True Me.OrderBindingSource.Position = pos End Sub
And that's basically it. As you can see even in it's simplest implementation (that I could think of) writing n-tier applications with LINQ to SQL takes some work, especially as the relations between our object collections increase. LINQ to SQL is really just used as the data access technology in the middle-tier, everything on top of that is up to us to implement as we see fit for our particular scenarios.
You can download the sample application on CodeGallery here.
In my previous posts on LINQ to SQL I showed how to build LINQ to SQL classes and set up the data binding in your Windows applications. If you missed them:
Related Data Binding and ComboBoxes with LINQ to SQL
Creating Lookup Lists with LINQ to SQL
One-To-Many (Master-Detail) Forms with LINQ to SQL
Simple Validation with LINQ to SQL Classes
These articles focus on the binding and validation and use a connected model; meaning that the DataContext is alive and available tracking the changes we make to the collections of LINQ to SQL objects. Out of the box, LINQ to SQL is really easy to get working with a connected client-server architecture. As long as your objects are attached to the DataContext you get all the nice features like change tracking and automatic lazy loading of related collections. However, unlike the DataSet, once you disconnect from the data source you lose all automatic change tracking. So why use LINQ to SQL objects and not distributed DataSets?
The Great Debate - DataSets or objects?
It depends on your particular scenarios which approach you would take, and you are not limited to using just one approach in your application depending on your scenario. I always try to keep these guidelines in mind when I'm trying to decide which approach to take:
- DataSets are easy to define, serialize, and can now be easily separated from the data access logic in the TableAdapters. (Take a look at how to do this in VS 2008 in this video)
-DataSets have built-in change tracking, batched editing, viewing and filtering (also supports LINQ), and handles complex relationships automatically
-DataSets are easy to use with advanced DataBinding scenarios
-DataSets are easy to implement simple field and row validation but become challenging when complex behavior needs to be coded, they cannot inherit from a business class so complex business logic and data become separated
-DataSets should not be used if your clients are not all .NET clients because this type is a very complex type specific to .NET
-LINQ to SQL classes are plain old CLR objects (POCO) that only implement a couple property changed notification interfaces
-LINQ to SQL classes allow you full control over the validation, behavior, business logic, and can inherit from any class you provide so you can create a base business class
-LINQ to SQL object collections can be serialized as arrays to non-.NET clients
-LINQ to SQL objects, once detached from the DataContext, do not track changes or support any advanced DataBinding features out of the box, those must be implemented by you and the code is often non-trivial
Personally I like both approaches, it just depends on the scenario. Typically if I have a batch .NET data-entry form that I need to service with only a few field or column validations necessary, DataSets are my first choice, especially if there are more than a couple related tables. They are also a good choice for reports and simple view-only forms. If I have more complex rules and behavior or I need full control over my object hierarchy then POCO business objects are the better choice.
Going N-Tier with LINQ to SQL
If you're ready to jump off the cliff there's some really good information in the MSDN Library on how to set up LINQ to SQL in distributed applications. There are a variety of ways to handle LINQ to SQL in these scenarios as the library shows. In this post I'm going to walk through the most common scenarios to get this to work with a remote .NET Windows smart client (WPF coming soon!). This post will focus on the data access and service layer and I'll follow up with the client in the next post.
We're going to continue with the application that we built in the previous articles which was just a single Windows forms client built into one EXE. What we need to do is modify the design by adding a data access layer that communicates with our database as well as a service layer that the client will communicate with. Since our client and our server are both written in .NET I am also going to reuse the LINQ to SQL objects that contain our validation so we can keep those rules in one place. Please note that this is not SOA (Service Oriented Architecture). If you need to go that route then I highly recommend this book.
So here's a diagram of what we're going to implement.
The first thing to do is add a Class Library project called OMSDataLayer to contain the data access layer and the generated LINQ to SQL classes (our business objects) along with the partial class code and base business object class. Unfortunately the O/R designer doesn't let you separate the generated classes from the DataContext object (database access) like the DataSet designer does so if you really want to be a stickler and you have a large project you're developing you should create your own classes and then you can decorate them with the right attributes to get them to work with LINQ to SQL or use an external mapping file.
Next add a reference to System.Data.Linq to the class library and then move all the associated .dbml files as well as the BaseBusiness and ValidationException classes into the project. Then add a project reference to this OMSDataLayer to the client project. At this point if I want the client to compile and work exactly like before I can add a project level imports to the OMSDataLayer on the client, Import the OMSDataLayer namespace, and run it like before (we just have to go through and change the namespace on our objects on our form).
Finally, add a new WCF Service project to the solution and add the OMSDataLayer as a project reference. Now we've got the structure of our solution set up.
Contract First
Now it's time to think about the interaction our client will need to have with our service. It's always a good idea to think about how the endpoints in your distributed applications need to communicate with each other before you start developing them. For this application we only need interfaces for retrieval of the Orders and Products and saving and deleting Orders and OrderDetails. So I defined the following operations:
Imports OMSDataLayer <ServiceContract()> _ Public Interface IOMSService <OperationContract()> _ Function GetOrdersByCustomerID(ByVal customerID As Integer) As IEnumerable(Of Order) <OperationContract()> _ Function GetProductList() As IEnumerable(Of Product) <OperationContract()> _ Function SaveOrders(ByRef orders As OrderList) As Boolean <OperationContract()> _ Function DeleteOrders(ByVal orders As IEnumerable(Of Order), _ ByVal details As IEnumerable(Of OrderDetail)) As Boolean End Interface
I'm just returning a collection of our business objects in the Get* methods and I've defined Save and Delete methods that accept a collection of orders to either Save (update or insert) and Delete against the database. I'm sending Orders and OrderDetail collections in the case of Delete because I implemented a very simple change-tracking strategy and I want to be able to delete OrderDetails independently. OrderList is a simple Serializable class definition that inherits from List(Of Order) and has the KnownType attribute applied. This is needed so I can pass the Orders collection by reference so that primary keys and validation messages are sent back on the objects cleanly. (There are many ways to do this like passing messages and keys back separately. This is the easiest and cleanest in my opinion but you need to be aware that there is more data on the wire in this approach.)
<KnownType(GetType(Order))> _ <Serializable()> _ Public Class OrderList Inherits List(Of Order) End Class
The last step to defining our contract is to set up our business objects so that they can be serialized as a WCF DataContract. We can do this via the O/R designer. Just open up the OMS.dbml file and in the properties for the DataContext set the Serialization mode to "Unidirectional".
This adds the DataContract attribute to the classes and the DataMember attribute to the properties. If we're using a WCF service with the DataContractSerializer then all this will work just dandy. This is because this serializer supports bi-directional relationships (like we have with Order and OrderDetail). However if we were trying to serialize this with the XMLSerializer used with non-WCF services we'd have issues; you'd only be allowed to serialize objects with no cyclic relationships.
We also need to add the DataContract and DataMember attributes to our BaseBusiness class as well because our objects inherit from this class. Also add a property called IsDirty to the BaseBusiness class that will help us later when we need to implement change tracking on our client. In the next post I'll talk about my approach to doing that when we talk about the client.
Disconnected Retrieval
Now that we have our contracts and solution set up we're ready to start partying on the data and write our data access layer code. Let's start with retrieving our Products and Orders. To recap what I mentioned in previous articles, when working in connected mode with the DataContext, collections of objects are not retrieved until they are accessed. This means that if I write a LINQ query to select an Order from the database it will not execute any statements to retrieve the OrderDetails until I attempt to access that child collection. This is a good thing because it saves calls to the database if they are not needed.
However when we are disconnected like in a distributed application there is no automatic lazy loading available, you have to roll your own. You need to decide how chatty you want to be in favor of less data on the wire. If you expect the client to be scrolling through smaller child collections it's probably better to just bring down a larger chunk of data in one call instead of making the client call the service multiple times. (Just to be clear, the client can still perform LINQ queries on the collections on the client-side to do additional filtering but that will be performed with standard LINQ to Objects, LINQ to SQL is only used on our server in the middle-tier.)
In this example, we won't need any child collections filled on Products, we're just using it as a lookup list. However, with Orders I want to pull all the Orders that haven't shipped for a specific customer ID and I want to also pull down those OrderDetails all in one call.
So now create a OMSDataManager class in our data access layer which our service implementation can call into. It's really easy to retrieve the Products. Note that the objects are automatically detached from the DataContext when they are serialized to the client through the service so we don't have to explicitly detach them here.
Imports System.Data.Linq
Public Class OMSDataManager Public Shared Function GetProductsList() As IEnumerable(Of Product) 'Create the Datacontext Dim db As New OMSDataContext 'Return the list of products. 'Objects are detached from the DataContext ' automatically on serialization through the service Return db.Products.AsEnumerable() End Function
Now with Orders because we want load the OrderDetails as well we need to specify this in a DataLoadOptions object and pass them to the DataContext. The way we use this object is we call the LoadWith method to specify which data related to the main target should also be retrieved at the same time, resulting in one trip to the database. You pass it a Lambda expression to specify this. So to retrieve the Orders and OrderDetails in one LINQ to SQL query we can write:
Public Shared Function GetOrdersByCustomerID(ByVal customerID As Integer) As IEnumerable(Of Order) 'Create the Datacontext Dim db As New OMSDataContext 'We want to also retrieve the OrderDetails collection as well. Dim dlo As New DataLoadOptions() dlo.LoadWith(Of Order)(Function(o As Order) o.OrderDetails) db.LoadOptions = dlo Dim orders = From order In db.Orders _ Select order _ Where order.CustomerID = customerID AndAlso _ order.ShipDate Is Nothing Return orders.AsEnumerable() End Function
Disconnected Updates and Deletes
Disconnected updates are a bit more involved from a decision-making point of view. The way you update your data depends on the schema of your database and how you are performing concurrency checking. The documentation in the library goes into details on all the ways you can update your data but the simplest is using a timestamp field to track row modifications.
In our OMS database I have a "Modified" field specified on every table and I'm using that field to perform the concurrency checks. This makes it much easier to deal with updates on our middle-tier because that field's value is used to compare with the property value on the object and the update will only succeed if the values match. If you don't provide a RowVersion mechanism like a timestamp on your tables then you have to pass all the original values into the middle-tier as well. So I'm all for saving space on the wire and writing less code and timestamps are a common way to do row versioning and concurrency checking so I feel that this is the best approach for most applications.
The other issue you need to consider is what the best way is to work with your data. Is it better to update one entity at a time (for instance just sending one Order at a time to save) or is it better to accept a collection of updates and make just one chunky call to the middle-tier? Or a combination? I've almost always gone with the batch editing approach in the types of Windows applications I've written. Although there is more data on the wire, there's less calls to the middle-tier. So the client form caches all the updates, inserts and deletes to the collections and passes that around. You really need to watch how much data you're passing around, so in this example we're only pulling up open orders for a particular customer. Remember every scenario needs to be evaluated carefully. I took a simple approach, more data on the wire but 2 calls to the middle-tier, one to update/insert data and one call to delete data.
You can streamline this more by only sending the changes to the middle-tier from the client but then you need more code to figure out how to merge the middle-tier changes (validation messages and keys, for instance) back into the client. This is really easy to do with if we were using DataSets but for this example I took to simple route and am passing the entire collection -- but be warned -- this approach may not scale depending on your situation. As always, your mileage may vary.
So let's write our Save code for our Orders. Unlike when using DataSets with the TableAdapter, the DataContext in disconnected mode needs to be told explicitly what to do, insert, update or delete. An easy way to tell whether the objects in our collection are added or modified is by checking the primary key; if it's less then 1 then we know we have a new object to save. We also use the IsDirty flag we added on our BaseBusiness class to determine how to attach our objects to the DataContext.
Public Shared Function SaveOrders(ByVal orders As IEnumerable(Of Order)) As Boolean If orders Is Nothing OrElse orders.Count = 0 Then Return False End If Dim db As New OMSDataContext For Each o In orders If o.OrderID = 0 Then db.Orders.InsertOnSubmit(o) Else db.Orders.Attach(o, o.IsDirty) End If For Each d In o.OrderDetails If d.IsDirty Then If d.OrderDetailID = 0 Then db.OrderDetails.InsertOnSubmit(d) Else db.OrderDetails.Attach(d, True) End If End If Next Next Try 'This will continue to process the 'rest of the orders even if one fails db.SubmitChanges(ConflictMode.ContinueOnConflict) 'Reset the IsDirty flag For Each o In orders o.IsDirty = False For Each d In o.OrderDetails d.IsDirty = False Next Next Catch ex As ChangeConflictException 'TODO: Conflict Handling Throw Return False End Try Return TrueEnd Function
Disconnected deletes of Orders in our case are very easy because of the way we set up how deletes should work by manually setting setting the DeleteOnNull attribute to true (see the end of this post for info on that). We just need to attach the incoming Orders and OrderDetails to the DataContext and then we can delete them all.
Public Shared Function DeleteOrders(ByVal orders As IEnumerable(Of Order), _ ByVal details As IEnumerable(Of OrderDetail)) As Boolean Dim db As New OMSDataContext Dim submit = False If orders IsNot Nothing AndAlso orders.Count > 0 Then 'Delete orders and related details db.Orders.AttachAll(orders, False) db.Orders.DeleteAllOnSubmit(orders) For Each o In orders For Each detail In o.OrderDetails db.OrderDetails.DeleteOnSubmit(detail) Next Next submit = True End If If details IsNot Nothing AndAlso details.Count > 0 Then 'Now delete the order details that were passed in ' (these order parents were not deleted, just the details) db.OrderDetails.AttachAll(details, False) db.OrderDetails.DeleteAllOnSubmit(details) submit = True End If Try If submit Then db.SubmitChanges(ConflictMode.ContinueOnConflict) Return True End If Catch ex As ChangeConflictException 'TODO: Conflict Handling Return False End Try End Function
The last thing we need to do to our middle-tier is call the data access layer from our service implementation. Note that I'm catching the ValidationException that's thrown from our business objects if they are invalid when the call to db.SubmitChanges is made and just returning false, which keeps our service from entering the fault state. The client will also perform validation of our business objects before the data is submitted to save a round-trip but the validation is also run here on the middle-tier. Validation messages are collected in a dictionary and serialized back to the client. (Read this post on how we set up validation on our LINQ to SQL classes.)
Imports OMSDataLayer Public Class OMSService Implements IOMSService Public Sub New() End Sub Public Function GetOrdersByCustomerID(ByVal customerID As Integer) _ As IEnumerable(Of OMSDataLayer.Order) _ Implements IOMSService.GetOrdersByCustomerID Return OMSDataManager.GetOrdersByCustomerID(customerID) End Function Public Function GetProductList() As IEnumerable(Of OMSDataLayer.Product) _ Implements IOMSService.GetProductList Return OMSDataManager.GetProductsList() End Function Public Function DeleteOrders(ByVal orders As IEnumerable(Of OMSDataLayer.Order), _ ByVal details As IEnumerable(Of OMSDataLayer.OrderDetail)) _ As Boolean _ Implements IOMSService.DeleteOrders Return OMSDataManager.DeleteOrders(orders, details) End Function Public Function SaveOrders(ByRef orders As OrderList) As Boolean _ Implements IOMSService.SaveOrders Try Return OMSDataManager.SaveOrders(orders) Catch ex As ValidationException Return False End Try End Function End Class
So that's the meat of our middle-tier. In the next post we'll build our client form and implement a simple technique for change tracking.
UPDATE: Read how to create the client in this post.
Come on over to the warm side of the Bay as we kick off hard-core Visual Studio 2008 development with our local Launch event in Pleasanton. We've got pizza!!! :-) Check out our site for more information like registration info and directions.
I'll start at 5:45pm as usual for our FUNdamentals session where we continue to build upon our PizzaMania application we've been building since January. Tonight we'll build a couple WCF services.
Then the fun begins at 6:45 with an informal discussion with Dan Ledrick from MSDN to get feedback from developers on how the MSDN sites can be improved. Let-er-rip!
Next I'm up with a presentation I did at the SF Launch in March that shows some of the new client/service/data features of Visual Studio 2008 including a WPF client, exposing a Workflow as a WCF service, and also how to set up an occasionally connected client with the new ADO.NET sync services and local database cache. I'll throw in some LINQ as well ;-). Robin will clean it up with new ClickOnce deployment features.
Hope you can join us if you're in the Bay Area!
I've been getting a lot of mails lately from people trying to build n-tier applications with Visual Studio 2008. Luckily, VS now makes it much easier to build these kinds of applications which have pretty much become the "norm" these days. Here's a feed of items I'm building that should help you get started.
I've been really busy this week tagging items like this in an effort to revamp the Visual Basic Learn area of the Dev Center. The content there is still very popular but it's looking pretty dated at this point since the release of Visual Studio 2008. Look for changes to roll out there very soon. If you have some favorite pages to share please pass them along! Basically I'm trying to surface some of the best content from the Library, How-Do-I videos, Webcasts, books, learning plans, and blog posts on a variety of topics. Look for more topics like WPF, WCF, WF to be added to the Learn tab as well.
I was just informed that all the Hands On Labs from the Visual Studio/Windows Server/SQL Server 2008 launch events are available online here.
I took a brief look at the "Visual Basic 9 Language Enhancements" lab and in general it looks good although it completely omits XML Literals and XML properties, I'm assuming because this lab was created before the RTM release. I'd take a look here for a good starting point on XML in VB. I also will plug my XML in VB webcast I did this morning that you can download.
The lab does have a chapter on lambda expressions so if you want to dive deeper into that topic you can have a look. The lab doesn't really get into the expanded query expressions that VB supports (which helps me avoid most common lambdas ;-) so you can have a look here and here for a good starting point.
I also noticed that the link for additional information in the beginning is pretty outdated. The help has been updated since these labs were created with more complete information. I'd suggest starting here instead. Or if you're really gutsy, read the VB 9 Language spec.
Also I noticed a small syntax error for array initializers. If you're having trouble on page 13 getting the syntax to work, you just need to add a parentheses to the square variable:
Dim square() = { _New Point With { .X = 0, .Y = 5 }, _New Point With { .X = 5, .Y = 5 }, _New Point With { .X = 5, .Y = 0 }, _New Point With { .X = 0, .Y = 0 } _}
This will create an object array with 4 point objects inside and will only work if you have option strict off. It's much better to write:
Dim square As Point() = { _New Point With { .X = 0, .Y = 5 }, _New Point With { .X = 5, .Y = 5 }, _New Point With { .X = 5, .Y = 0 }, _New Point With { .X = 0, .Y = 0 } _}
The rest of the chapter explains this so I think it was just a typo.