Beth Massi - Sharing the goodness that is VB : Article, LINQ

Merging Text & Photos into a Word Document using Open XML SDK

Beth Massi — Fri, 25 Sep 2009 00:55:10 GMT

Yesterday I posted about how we could create a letter generator (mail merge) that took data from a database to create a Word 2007 document using the System.IO.Packaging class. I showed how to take data from Northwind using a single LINQ query to create XDocument objects representing letters to customers and then create a single document of all the data using XML Literals.

Today I want to show how we can embed images into the documents as well as text. Since we’re using Northwind for this example, we’ll use the Employee.Photo field from that database.

The Letter Template with Photo

The first thing to do is create a letter template with our field placeholders as well as an image placeholder. We’ll also need to insert a page break at the end because we’re going to merge all the letters into a single document for easy printing:

The next step is to grab the document.xml from this docx file. Because it’s an Open XML package, as I showed in yesterday’s post, we can just rename this file with a .zip extension temporarily and drill into the word\document.xml. However, there’s an easier way to work with these packages by installing the Open XML package editor Visual Studio add-in from the VSTO Power Tools download.

Once you install the VSTO Power tools, save this template in your project. Then you can just double-click on the .docx file in the Solution Explorer and it will open the package editor (it also works with .pptx and .xslx files). Now you can drill into the document.xml directly in Visual Studio and inspect all the parts of the package easily.

The difference between this template and the template we used yesterday is there is an image imbedded into the document. So you will see an image embedded into the package in the media folder. This is referred to as an “image part”. This part is linked to the “main document part” which is the word\document.xml file. The Open XML package editor shows the links (relationships) to all the parts. When you look at the relationship properties you’ll see that it has a unique ID. This is the same ID that you will see in the main document part at the point where we embedded the image, in our case it’s set to rId4. (I’ve omitted all the WordProccessingML for clarity):

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<w:document ...
  <w:body> ...

        <w:drawing> ...

              <a:graphic xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
              <a:graphicData uri="http://schemas.openxmlformats.org/drawingml/2006/picture">
                <pic:pic xmlns:pic="http://schemas.openxmlformats.org/drawingml/2006/picture">
                  <pic:nvPicPr>
                    <pic:cNvPr id="0" name="image1.png"/>
                    <pic:cNvPicPr/>
                  </pic:nvPicPr>
                  <pic:blipFill>
                    <a:blip r:embed="rId4" cstate="print"/>
                    <a:stretch>
                      <a:fillRect/>
                    </a:stretch>
                  </pic:blipFill>
                  <pic:spPr>
                    <a:xfrm>
                      <a:off x="0" y="0"/>
                      <a:ext cx="1829055" cy="2124372"/>
                    </a:xfrm>
                    <a:prstGeom prst="rect">
                      <a:avLst/>
                    </a:prstGeom>
                  </pic:spPr>
                </pic:pic>
              </a:graphicData>
            </a:graphic> ...
          
        </w:drawing> ...

        <w:p w:rsidR="00622A50" w:rsidRDefault="00622A50">
            <w:r>
                <w:br w:type="page"/>
            </w:r>
        </w:p> ...

  </w:body>
</w:document>

Installing the Open XML SDK

As I mentioned yesterday, manipulating the package directly with the System.IO.Packaging classes gets a little bit tricky when you have to start adding embedded images and objects and linking them together. Instead, it’s easier to use the Open XML SDK to do this because it will handle linking new image parts we add to the package automatically for us. The SDK provides a strongly-typed way of accessing the parts in a package so it cuts down on the amount of code you have to write yourself. Version 1.0 requires only .NET 2.0, Version 2 requires .NET 3.5 and uses LINQ to provide more functionality when querying the parts. This version is currently a CTP. For what we’re doing you can use either version but I have Version 2 installed.

The assembly that is installed in the GAC when you install the SDK is called DocumentFormat.OpenXml.dll and you’ll need to add a project reference to this. When you distribute your application you can simply xcopy the assembly with your application for easy deployment. (After you add the reference, select it in the Server Explorer and then in the Properties window set Copy Local to True in order to have it outputted to the \bin folder when you build your application.)

Now we can add the namespace imports we’ll be working with at the top of our program, including the DocumentFormat.OpenXml.Packaging namespace:

Imports <xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main">
Imports System.Xml
Imports System.IO
Imports DocumentFormat.OpenXml.Packaging

Modifying the Letter Class and LINQ Query to Get the Photo

In order to create letters for our customers in the database, we want to end up with a collection of XDocuments with our data merged into them. I created a Letter class that has two properties, CustomerID As String and Document As XDocument. We’re also going to add a new property for the photo byte array called EmployeePhoto As Byte() and OrderID as Integer. Next we need to modify the query so that it selects the photo as well as add the above WordProcessingML into our XML Literal to specify where the image should appear in the document. Because the relationship IDs must be unique, I’m going to use the OrderID as the suffix for the relationship ID for each of the images we want to embed. Like before, we can use embedded expressions to merge the data into the right places in the XDocument.

(I’ve omitted all the XML with the embedded expressions for clarity, take a loot at the code sample for the whole listing.) The bolded areas show the modified sections of the query:

Dim letters = _
  From Order In db.Orders _
  Join Employee In db.Employees _
    On Order.EmployeeID Equals Employee.EmployeeID _
  Where Order.OrderDate IsNot Nothing AndAlso _
    Order.ShippedDate IsNot Nothing AndAlso _
    Order.ShippedDate.Value.Date >= #1/1/1998# _
  Let DateOrder = Order.OrderDate.Value.ToShortDateString _
  Let DateShip = Order.ShippedDate.Value.ToShortDateString _
  Select New Letter With { _
    .CustomerID = Order.CustomerID, _
    .OrderID = Order.OrderID, _



    .EmployeePhoto = If(Employee.Photo IsNot Nothing, Employee.Photo.ToArray, Nothing), _
    .Document = <?xml version="1.0" encoding="utf-8" standalone="yes"?>
        <w:document ... 
            <w:body> ...
                <w:p w:rsidR="00A9498E" w:rsidRDefault="00A9498E" w:rsidP="00AA2EC6">
                    <w:r>
                        <w:t xml:space="preserve">If you have any issues please contact </w:t>
                    </w:r>
                    <w:proofErr w:type="spellStart"/>
                    <w:r>
                        <w:t><%= Employee.FirstName & " " & Employee.LastName %></w:t>
                    </w:r>
                    <w:proofErr w:type="spellEnd"/>
                        <w:r>
                            <w:t xml:space="preserve"> at</w:t>
                        </w:r>
                        <w:r w:rsidR="005F35D6">
                            <w:t xml:space="preserve"> ext.</w:t>
                        </w:r>
                        <w:r>
                            <w:t xml:space="preserve"></w:t>
                        </w:r>
                        <w:r w:rsidR="005F35D6">
                            <w:t><%= Employee.Extension %></w:t>
                        </w:r>
                        </w:p> ...
                        <w:drawing> ...
                            <pic:blipFill> ...
                                <a:blip r:embed=<%= "rId" & Order.OrderID %>/> ...
                        </w:drawing> ...
                        <w:p w:rsidR="00622A50" w:rsidRDefault="00622A50">
                            <w:r>
                                <w:br w:type="page"/>
                            </w:r>
                        </w:p> ...
                    </w:body>
                </w:document>}

The query returns an IEnumerable(Of Letter) objects with the Document property set to the document.xml data for that customer and the EmployeePhoto set to the photo byte array. If we wanted to create separate documents (which is exactly what I demonstrated in my DevProConnections article) it’s actually pretty simple because all we need to do is copy the template and then replace the document.xml and the image data, we don’t have to mess with the relationship ID at all. However if we want to create a single document with all our letters we need to delete the placeholder image part and add new unique image parts for the Letter.EmployeePhoto.

Adding and Linking the Images into a Single Word Document

Because relationship IDs must be unique in a document I used the OrderID as the embedded expression for the r:embed attribute in the XML Literal above. You can choose to use any unique ID you want but it must start with an alpha character, that’s why I prefixed it with “rId”. When you use the Open XML SDK to add the new image parts we have to specify the relationship ID to use. To update the code we wrote yesterday to work with the Open XML SDK and also work with images we first need to add a call to open the Word document template, grab the main document part and then delete the image part by calling DeletePart:

'Place the letters in a separate directory
Dim sourceFile = CurDir() & "\LetterTemplate.docx"
Dim letterDir = CurDir() & "\Letters\"
My.Computer.FileSystem.CreateDirectory(letterDir)

Dim mergeFile = letterDir & "AllLetters.docx"
My.Computer.FileSystem.CopyFile(sourceFile, mergeFile, True)

'Open the Document template using the Open XML SDK
Using wordDoc = WordprocessingDocument.Open(mergeFile, True)

    'get the main document part (document.xml)
    Dim mainPart = wordDoc.MainDocumentPart()

    'Delete the placeholder image from the template so we can replace it with the real photos
    mainPart.DeletePart("rId4")

This opens the Word document as read/write and then deletes the placeholder image. You should also notice at this point that the code is a bit simpler to open the package than yesterday because we don’t have to declare the relative URI’s or content types. The Open XML SDK helps us here. Next we need to load the document.xml into an XElement object and grab the body:

    Dim mainDocumentXML As XElement
    Using sr As New StreamReader(mainPart.GetStream)
        mainDocumentXML = XElement.Load(sr)
    End Using

    Dim mainBody = mainDocumentXML...<w:body>.First()
    Dim i = 0

Now we can loop through all the letters, grab the body of those XDocuments and then add an image part. We need to specify the content type and the relationship ID that must match what we embedded in the XML Literal in our query. Then we can either replace the main document body with the first letter (overwrite our placeholder data) otherwise we append the rest of the letter contents into the document:

    For Each letter In letters
        Dim nextBody = letter.Document...<w:body>.First()
        'Add the new imagePart. The SDK takes care of linking this properly.
        Dim imagePart = mainPart.AddImagePart("image/png", "rId" & letter.OrderID)

        If i = 0 Then
           'Replace the first body contents in the template
           mainBody.ReplaceNodes(nextBody.Elements())
        Else
           'Otherwise append the next letter contents
           mainBody.Add(nextBody.Elements())
        End If

Next we need to feed the byte array data into the ImagePart. Note that since I’m using Northwind for this example there is an OLE header on those images that we must strip off that is 78 bytes. If you are using your own images stored in SQL Server you won’t have to do that:

        'Now feed the byte array into the ImagePart
        If letter.EmployeePhoto IsNot Nothing Then
            'Using ms As New System.IO.MemoryStream(letter.EmployeePhoto.ToArray)
            'NOTE: Northwind Photos have an OLE header on them that we must strip off.
            ' You don't have to do this if you are using your own SQL-server stored images
            Using ms As New System.IO.MemoryStream(letter.EmployeePhoto, 78, _
                                                   letter.EmployeePhoto.Length - 78)
                imagePart.FeedData(ms)
            End Using
        End If

        i += 1
    Next

Now that we’ve built up the document XML, added our image parts and linked them properly, the final step is to replace the document part with our XML just like before:

    'Replace the document part with our XML
    Using sw As New StreamWriter(mainPart.GetStream(FileMode.Create))
        mainDocumentXML.Save(sw)
    End Using

End Using

Now when we run the application you will see a single document with all the letters, including photos, that’s much easier to print than multiple files.

There’s Always Room for Improvement

LINQ & Northwind savvy folks may notice that I’m actually creating duplicates of the employee photos. Since I’m flattening out the data in my query to have 1 photo per order there could be an unnecessary amount of duplicate images. This can cause the document to get bloated. To be more efficient we should reuse the relationship ID’s to the images if they are the same on multiple orders. One approach would be to modify the LINQ query and Letter object to select the EmployeePhoto and then have a collection of order & document data under that. I’ll leave that experiment up to you :-)

Take a look at the full code listing for this example as well as the complete project with other Open XML examples here on Code Gallery.

Enjoy!

Mail Merging Into a Single Word Document Using XML Literals

Beth Massi — Thu, 24 Sep 2009 02:18:00 GMT

With the release of Microsoft Office 2007 we can work with a much simpler, standard, XML format called Open XML which opens the door for many types of applications that cannot work via COM. What if you needed to build a scalable web service that processes many documents in high volume? What if you wanted to quickly read or write to these formats from a client application but wanted to have minimal dependencies on other applications? These types of programs do not want to require Microsoft Office be installed to run. The cool thing is you have the tools already with Visual Basic 2008. XML Literals are an easy way to manipulate any kind of XML, including Open XML.

I’ve written before on how to use XML Literals to manipulate Word documents, if you missed them:

If you aren’t familiar with XML Literals or Open XML then I’d suggest reading the article in the first bullet above first. It’s a great intro into XML Literals, Open XML and it also demonstrates a couple practical ways of reading and writing to Word document formats directly.

Recently I got a great question on how to change the program outlined in the last bullet above to merge letters we were creating on the fly from a database into a single document instead of multiple documents like we’re doing. This would make it much easier for an end user to print them. That’s such an obvious thing I can’t believe I didn’t think of that! For those of you that aren’t familiar with what we did I’ll give a quick recap of that application.

Creating Word Documents with XML Literals

What I wanted to do is take all the Customers in the Northwind database who had some orders shipping today and send them thank you letters. To get started I created a new Word 2007 document with the letter text and some placeholder field names to indicate where I want the data:

Save the document (I named it Doc1.docx) and then rename the .docx extension to .zip and you can drill into the package and see a bunch of XML documents inside it. If we drill down through the zip file we'll see that the text we just typed is located in the \word\document.xml file. Copy this xml and paste it into the Visual Basic editor, it will infer it as an XDocument object, and then you can use embedded expressions to replace the placeholders. (If I’ve lost you, read this article which explains it step-by-step.)

In order to create letters for our customers in the database, we want to end up with a collection of XDocuments with our data merged into them. I created a simple class called Letter that has two properties, CustomerID As String and Document As XDocument. Then I wrote a LINQ query to select the data and embed it into the document (I’ve omitted all the WordProcessingML for clarity, take a loot at the code sample for the whole listing.)

Dim letters = _
    From Order In db.Orders _
    Join Employee In db.Employees _
        On Order.EmployeeID Equals Employee.EmployeeID _
    Where Order.OrderDate IsNot Nothing AndAlso _
        Order.ShippedDate IsNot Nothing AndAlso _
        Order.ShippedDate.Value.Date >= #1/1/2007# _
    Let DateOrder = Order.OrderDate.Value.ToShortDateString _
    Let DateShip = Order.ShippedDate.Value.ToShortDateString _
    Select New Letter With { _
        .CustomerID = Order.Customer.CustomerID, _
        .Document = <?xml version="1.0" encoding="utf-8" standalone="yes"?>
    <w:document ...
    <w:p w:rsidR="00705CFF" w:rsidRDefault="00112228" w:rsidP="00AA2EC6">
        <w:r>
            <w:br/>
        </w:r>
        <w:r w:rsidR="007A5236">
            <w:t xml:space="preserve">Dear </w:t>
        </w:r>
            <w:proofErr w:type="spellStart"/>
        <w:r>
            <w:t><%= Order.Customer.ContactName %></w:t>
        </w:r>
        <w:proofErr w:type="spellEnd"/>
            <w:r w:rsidR="00AA2EC6">
                <w:t>,</w:t>
            </w:r>
     </w:p>
     <w:p w:rsidR="00E04FB0" w:rsidRDefault="00AA2EC6" w:rsidP="00AA2EC6">
        <w:r>
            <w:t xml:space="preserve">We’d like to inform you that the order you placed on </w:t>
        </w:r>
        <w:proofErr w:type="spellStart"/>
            <w:r w:rsidR="00112228">
                <w:t><%= DateOrder %></w:t>
            </w:r>
        <w:proofErr w:type="spellEnd"/>
            <w:r w:rsidR="00806521">
                <w:t xml:space="preserve"> has shipped on </w:t>
            </w:r>
        <w:proofErr w:type="spellStart"/>
            <w:r w:rsidR="00112228">
                <w:t><%= DateShip %></w:t>
            </w:r>
... </w:document>}

The query returns an IEnumerable(Of Letter) objects with the Document property set to the document.xml data for that customer. The next step involved taking the template Doc1.docx file, making a copy of it and then simply replacing the document.xml part inside the package with the one here in the Letter class for each letter. However, what I did was create separate letters for each customer. Instead we want to create one .docx file with ALL the letter data merged inside.

Merging into a Single Word Document

The first thing we need to do is insert a page break between all our letters. The easiest thing to do is to open back up your template Doc1.docx file in Word and insert a page break, save it, and then look at the document.xml again in the package. You should see this WordProccessingML element inserted near the end:

<w:p w:rsidR="00622A50" w:rsidRDefault="00622A50">
    <w:r>
        <w:br w:type="page"/>
    </w:r>
</w:p>

Paste this into the query above in the document exactly where you pulled it out of the document.xml.

Next we have a couple options on how to manipulate the package (docx file). I’ve shown how to use the Open XML SDK before to manipulate documents but in this simple case we can use the System.IO.Packaging classes directly. This is because all we’re doing is working with text in the document. If we were working with images or embedded objects it would be easier to use the SDK. In the next post we’ll add photos of the employees on each order into the document, but for this post let’s just focus on how to merge the text.

So the first thing we need to do is Import some namespaces including an XML namespace that we’re going to use:

Imports <xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main">
Imports System.IO
Imports System.IO.Packaging

Next we need to set up some variables and copy the Doc1.docx template to a new file I’m calling AllLetters.docx.

Dim uri As New Uri("/word/document.xml", UriKind.Relative)
Dim contentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document.main+xml"
Dim sourceFile = CurDir() & "\Doc1.docx"
Dim letterDir = CurDir() & "\Letters\"
My.Computer.FileSystem.CreateDirectory(letterDir)

Dim mergeFile = letterDir & "AllLetters.docx"
My.Computer.FileSystem.CopyFile(sourceFile, mergeFile, True)

Now we can open the template package and load the main document part as an XElement:

Using p As Package = Package.Open(mergeFile)

    'get the main document part (document.xml)
    Dim mainPart = p.GetPart(uri)
    Dim mainDocumentXML As XElement




    Using sr As New StreamReader(mainPart.GetStream)
        mainDocumentXML = XElement.Load(sr)
    End Using

The next part is where it gets fun. A word document has a top element structure like this:

<w:document>
    <w:body>
       ...
    </w:body>
</w:document>

So what we need to do is grab the body of the template, replace it with the first customer’s document body we have and then append the rest of the elements inside the bodies of the rest of the documents. A word document can only have one <w:body> element for it to be legal. The way we get the <w:body> XElement from the document is using the descendants syntax with the three dot notation (…):

    Dim mainBody = mainDocumentXML...<w:body>.First()
    Dim i = 0

    For Each letter In letters
        Dim nextBody = letter.Document...<w:body>.First()
        If i = 0 Then
            'Replace the first body contents in the template
            mainBody.ReplaceNodes(nextBody.Elements())
        Else
            'Append the new contents for the rest of the customers
            mainBody.Add(nextBody.Elements())
        End If
        i += 1
    Next

Now that we’ve built up the right body with all our letter data in it, we can replace the main document.xml part in the package and close it.

    'Delete the current document.xml file in the template
    p.DeletePart(uri)
    'Replace that part with our new merged XDocument
    Dim replace As PackagePart = p.CreatePart(uri, contentType)

    Using sw As New StreamWriter(replace.GetStream())
        mainDocumentXML.Save(sw)
        sw.Close()
    End Using

    p.Close()
End Using

Now when we run this, all the letters will be mail merged into the same document for easy printing:

As I mentioned if we have embedded images or objects that we need to replace it gets a little trickier. In the DevProConnections Article I have an example of how to create multiple documents with embedded pictures of the employee’s photo. In the next post I’ll show you how we can use the Open XML SDK to create a single document with embedded pictures as well.

Until then, have a look at the complete code I put up on Code Gallery.

[UPDATE: Merging Text & Photos into a Word Document using Open XML SDK]

Enjoy!

DevProConnections Article: Taking Advantage of LINQ and XML in Microsoft Office 2007

Beth Massi — Mon, 17 Aug 2009 20:05:00 GMT

This month I have an article on DevProConnections:

Taking Advantage of LINQ and XML in Microsoft Office 2007

In this article, I talk about how a lot of applications that need to take advantage of Microsoft Office can do so without going through COM. Many programs that need to process documents often require manipulation of the file formats directly and doing that through the Office component object model won’t scale very well. It also requires that Microsoft Office be installed to run. A better route in a lot of these cases is to use the Open XML SDK. With the release of Office 2007, Word documents, Excel spreadsheets and PowerPoint presentations are based on an open standard for packaging XML files called Open XML. Using Visual Basic’s powerful and simple implementation of LINQ to XML you can work with these new document formats much easier than ever before.

I show a couple practical examples of reading data from Word documents and updating a database as well as reading data from a database to create Word documents – all without COM. No Office applications need to be installed in order to read and write to these formats directly.

This article is based on the two episodes I did on DnrTV:

[UPDATE: Here's code samples (also includes a presentation pptx)]

Working with Open XML in Visual Basic

More resources:

Open XML Format SDK 2.0

Open XML Resource Center

Microsoft Visual Studio Tools for the Office System Power Tools v1.0.0.0

Word 2007 Content Control Toolkit

VSTO Developer Center

Visual Basic Developer Center

Enjoy!

Filtering Entity Framework Collections in Master-Detail Forms

Beth Massi — Fri, 17 Jul 2009 03:18:17 GMT

Last post I talked about how to get WPF data binding to work with master-detail entity collections. I had a couple readers ask me how they could filter the child collection instead of bringing them all down so in this post I’d like to show how you could do that. It’s actually pretty easy.

Customer (Master) –< Orders (Detail)

I created a very simple Entity Data Model (EDM) that demonstrates a Master-Detail relationship. The Orders table is defined in the database with a non-nullable foreign key CustomerID, meaning that no Order can exist without a Customer. This relationship is inferred by Entity Framework (EF) to set up the navigation properties giving us an Orders EntityCollection on Customer.

To recap, I’ve created my own ObservableCollection(of Customer) called CustomerCollection so that when the UI modifies the collection through AddItem/RemoveItem, we can instruct the ObjectContext to track these changes so that it will insert and delete entities to the database when we call SaveChanges.

The constructor takes an IEnumerable(Of Customer) which allows us to pass a LINQ to Entities query into our CustomerCollection. This executes the query against the database when we call the base class’s constructor which iterates over the query results and adds the Customer entities to our collection.

Take a look at the first chunk of code in the last post for the full code listing for the CustomerCollection class. I don’t have to make any changes to it to support filtering the children.

Loading All Detail Entities

If you want to bring down all the Orders for the selected Customer you can use the .Include extension like I’ve shown before using explicit loading.

'EF ObjectContext connects to database and tracks changes
Dim db As New OMSEntities
'inherits from ObservableCollection(Of Customer)
Dim CustomerData As CustomerCollection

'Include all Orders for Customer #1
Dim customers = From c In db.Customers.Include("Orders") _
                Where c.CustomerID = 1

CustomerData = New CustomerCollection(customers, db)

This ends up shooting one query to SQL Server to return all the Orders for the selected Customer when we pass the query into our collection. But what if we also wanted to filter the Orders? There’s a couple ways we can do this because of a cool feature of the Entity Framework.

Filtering Detail Entities with a Sub-Query

When the entities are being created in memory on the client from the query (materialized), the object state manager will attempt to hook up the entity references and collections automatically. This means that all we have to do is specify a filtered query and make sure we execute it by enumerating over the results (or calling ToList).

So say we wanted to only grab the most recent Orders where the order was placed on or after January 1st, 2009:

Dim query = From c In db.Customers _
            Where c.CustomerID = 1 _
            Select Customer = c, _
                   Orders = From o In c.Orders _
                            Where o.OrderDate >= #1/1/2009#

Dim customers = From item In query.ToList Select item.Customer

CustomerData = New CustomerCollection(customers, db)

Here I’m specifying a sub-query to only pull the Orders I want on the selected Customer. The first query creates a collection of anonymous types that have a Customer object and a filtered Orders collection. In the following query, I need to call query.ToList in order to execute the query against the database and materialize the objects. This sends one statement to SQL Server like before, but this time it’s filtered on Orders as well.

At this point the Customers and Orders are in memory and the object state manager has hooked them up for us. Meaning that the Customer now has an Orders collection like we want. As long as we Select the entire Customer and Order types in our queries this will work (as opposed to just selecting specific fields – the entity types have to match up for it to work). The second query is just a LINQ to Objects query (in memory) that we’re using so that we can pass an IEnumerable(Of Customer) to our ObservableCollection.

Filtering Detail Entities with Separate Queries

There are other ways we could write this. We could write the queries separately:

Dim customers = (From c In db.Customers _
                 Where c.CustomerID = 1).ToList()

Dim orders = (From o In db.Orders _
              Where o.Customer.CustomerID = 1 AndAlso _
                    o.OrderDate > #1/1/2009#).ToList()

CustomerData = New CustomerCollection(customers, db)

Notice that I’m calling ToList to force the queries to execute so that the objects will materialize (come into memory), then I’m just passing the Customers to the constructor because at this point the object state manager has hooked up our entities (Customer has an EntityCollection of Orders based on the filtered query). The results on the client are the same but the major difference here is that two statements are sent to SQL Server, so keep that in mind. I like the first approach better with a sub-query because of the single SQL statement that’s generated as well as the clearer looking LINQ query.

I’ve updated this sample application that also shows a variety of other data binding techniques with EF and WPF so have a look. The filter example above is in the SimpleMasterDetailBinding form.

Enjoy!

Using the WPF ObservableCollection with EF Entities

Beth Massi — Sat, 09 May 2009 01:29:00 GMT

The ObservableCollection is a special WPF collection that provides proper notifications to the UI when items are added, removed, or the list is refreshed because it implements INotifyCollectionChanged. It’s common to use this collection (or inherit from it) to contain your business objects you want to bind to in WPF.

Class Window1
    Private CustomerData As ObservableCollection(Of Customer)

You can then set up a CollectionViewSource and use it’s View property to get a reference to the ListCollectionView in order to add and remove items instead of working with the source collection directly. This decouples your data source (and therefore any collection logic) from the form itself making it much easier to change sources later. I’ve showed how to use CollectionViewSources before but basically you just declare them in the Window.Resources section and bind to them in XAML:

<Window x:Class="Window1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    Title="Window1" Height="282" Width="440" Name="Window1">
    <Window.Resources>
        <CollectionViewSource x:Key="CustomerSource" />
    </Window.Resources>
    <Grid DataContext="{Binding Source={StaticResource CustomerSource}}">

And then you can set the Source property in code to your collection and obtain the ListCollectionView.

Dim customerSource = CType(Me.Resources("CustomerSource"), CollectionViewSource)
customerSource.Source = Me.CustomerData

Me.View = CType(customerSource.View, ListCollectionView)

Then you use the View to add and remove items from the collection and the UI will update properly:

Private Sub btnDelete_Click() Handles btnDelete.Click
       If Me.View.CurrentPosition > -1 Then
           'removes the currently selected customer from the underlying collection 
           Me.View.RemoveAt(Me.View.CurrentPosition)
       End If
   End Sub

   Private Sub btnAdd_Click() Handles btnAdd.Click
       'adds a new customer to the underlying collection 
       Dim customer = CType(Me.View.AddNew, Customer)
       'do something with customer if needed...
       Me.View.CommitNew()
   End Sub

Calling these methods on the ListCollectionView will execute the InsertItem and RemoveItem methods on the ObservableCollection.

Now if you are using an Entity Data Model (EDM) the designer in Visual Studio 2008 SP1 will generate entity classes for you that you can also bind to in your UI. Access to these entities is done through the ObjectContext and the designer also creates a class for you that inherits from this when you create the EDM. It is named something like xxxEntites. (For instance, in Visual Studio 2008 SP1 “Add New Item” and select ADO.NET Entity Data Model and name it Northwind.edmx. Generate from Database and select Northwind. Select all the tables and then the designer will generate an ObjectContext called NorthwindEntities and entity classes based on the tables in the database.)

Because the ObjectContext is what tracks changes on entities you can place entities inside an ObservableCollection but in order for the ObjectContext to be notified that adds and deletes need to be tracked you need to write a bit of code. The easiest thing to do is to create your own class that inherits from ObservableCollection and override the InsertItem and RemoveItem methods so that you can tell the ObjectContext to either add or delete the entity which will ultimately execute against the database. In the constructor pass a reference to the ObjectContext. You can also pass in any collection of entities, say from a LINQ query, and then add them to the ObservableCollection. For example:

Imports NorthwindDAL
Imports System.Collections.ObjectModel

Public Class CustomerCollection
    Inherits ObservableCollection(Of Customer)

    Private _context As NorthwindEntities
    Public ReadOnly Property Context() As NorthwindEntities
        Get
            Return _context
        End Get
    End Property

    Sub New(ByVal customers As IEnumerable(Of Customer), ByVal context As NorthwindEntities)
        MyBase.New(customers)
        _context = context
    End Sub

    Protected Overrides Sub InsertItem(ByVal index As Integer, ByVal item As Customer)
        Me.Context.AddToCustomers(item)
        MyBase.InsertItem(index, item)
    End Sub

    Protected Overrides Sub RemoveItem(ByVal index As Integer)
        Me.Context.DeleteObject(Me(index))
        MyBase.RemoveItem(index)
    End Sub

End Class

Then you can use the collection on your WPF form instead like so:

Imports NorthwindDAL

Class Window1
    Private db As New NorthwindEntities
    Private CustomerData As CustomerCollection
    Private View As ListCollectionView

    Private Sub Window1_Loaded() Handles MyBase.Loaded

        Dim results = From c In db.Customers _
                      Where c.City.ToLower = "seattle" _
                      Order By c.LastName, c.FirstName _
                      Select c

        Me.CustomerData = New CustomerCollection(results, db)

        Dim customerSource = CType(Me.Resources("CustomerSource"), CollectionViewSource)
        customerSource.Source = Me.CustomerData
        Me.View = CType(customerSource.View, ListCollectionView)
    End Sub

    Private Sub btnSave_Click() Handles btnSave.Click
        Try
            db.SaveChanges()
            MsgBox("Customer data was saved.")
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try
    End Sub

    Private Sub btnDelete_Click() Handles btnDelete.Click
        If Me.View.CurrentPosition > -1 Then
            Me.View.RemoveAt(Me.View.CurrentPosition)
        End If
    End Sub

    Private Sub btnAdd_Click() Handles btnAdd.Click
        Dim customer = CType(Me.View.AddNew, Customer)
        'do something with customer if needed...
        Me.View.CommitNew()
    End Sub

End Class

Now any updates, adds or deletes you make in the UI will be propagated to the database through the Entity Framework.

Enjoy!

Data Binding WPF Lookup Combobox Values to EF Entities

Beth Massi — Thu, 30 Apr 2009 17:57:00 GMT

It’s extremely common to have to hook up lookup tables on your data entry forms in order to populate foreign keys in a database. I’ve talked about how to do this in Winforms and WPF with Datasets and LINQ to SQL before:

The common theme between all of these is that the data sources, either the LINQ to SQL classes or the DataTables that we bind to, uses a navigation path based on the foreign key and that foreign key is exposed as a property (or DataColumn). For instance if we have a Customer related to Orders we would have a CustomerID property on Orders.

DataSets (like databases) rely on this type of navigation. So when you want to find the parent Customer of an Order you have to know the relation. Using typed datasets helps you more but you still end up having to know the details of relationships and foreign keys of the DataSet. That’s why people who are familiar with databases are usually comfortable with working with DataSets.

LINQ to SQL classes also include navigation properties as direct object references and collections. So they have both the foreign key and the navigation properties. Customer will have a collection of Orders and Order will have a reference back to Customer but the classes also contain the CustomerID property. This isn’t “pure” I suppose but it does make data binding and subsequent saves back to the database pretty much a no brainer.

For instance say I want to hook up a lookup combobox using a LINQ to SQL class on an Order with a reference to Customer. I want to display a list of Customers the user can pick from and that Customer should be associated with that Order. It’s pretty straight forward and works the same with DataTables.

XAML:

<Window.Resources>
    <CollectionViewSource x:Key="OrdersSource" />
    <CollectionViewSource x:Key="CustomerLookup" />
</Window.Resources>

<Grid Grid.Row="1" Name="Grid1" DataContext="{Binding Source={StaticResource OrdersSource}}">
<ComboBox Height="23" Name="ComboBox1" Width="177" Margin="2"  HorizontalAlignment="Left" 
          IsEditable="False"
          ItemsSource="{Binding Source={StaticResource CustomerLookup}}"
          DisplayMemberPath="LastName"
          SelectedValuePath="CustomerID" 




          SelectedValue="{Binding Path=CustomerID}" 
          />
...

Code-behind:

Class Window1
    Private db As New MyDataObjectContext
    Private OrderData As IEnumerable(Of Order)

    Private Sub Window1_Loaded(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Me.Loaded
        'Load all the orders from the database
        Me.OrderData = db.Orders
        'Get the customer lookup list (this is the Combobox ItemsSource)
        Dim customerList = From c In db.Customers _
                           Where c.Orders.Count > 0 _
                           Order By c.LastName, c.FirstName

        Dim ordersSource = CType(Me.FindResource("OrdersSource"), CollectionViewSource)
        ordersSource.Source = Me.OrderData
        Dim custSource = CType(Me.FindResource("CustomerLookup"), CollectionViewSource)
        custSource.Source = customerList.ToList()

    End Sub
...

Almost all of this code is just to set up the context of this discussion, you can watch this video for details on building a complete example. Here I’m using CollectionViewSources in the XAML and setting their Source property in code. This technique is handy especially if you are using nested DataTemplates. The important piece to note are the four properties on the Combobox. ItemsSource, DisplayMemberPath, SelectedValue, and SelectedValuePath. To set up your combobox:

Set the ItemsSource to the list of Customers you want to display in the Combobox.
Next set the DisplayMemberPath to the property name on this list that you want to use to display in the list, here I used LastName.
Then set the SelectedValuePath to the property name on this list that will be used to populate the foreign key value on the Order.
Finally you set the SelectedValue to the property binding on the Order that is foreign key. LINQ to SQL (and DataSets) will happily save your data with this binding in place.

This is how you always bind DataTables and it also works well for LINQ to SQL classes. But since LINQ to SQL classes also include the navigation properties (the Orders collection on Customer and the Customer object reference on Order) you can use a different technique by binding directly to the Customer reference. This is the only choice we have with Entity Framework entities in .NET 3.5 SP1.

What’s unique with Entity Framework is that the associations between other entities use only navigation properties -- so there isn’t a CustomerID foreign key property value on the Order at all.

What you have to do instead is bind directly to the Customer object reference. The change in the above example is the Combobox binding in XAML:

<ComboBox Height="23" Name="ComboBox1" Width="177" Margin="2"  HorizontalAlignment="Left" 
          IsEditable="False"
          ItemsSource="{Binding Source={StaticResource CustomerLookup}}"
          DisplayMemberPath="LastName"          
          SelectedItem="{Binding Path=Customer}" 
          />

The important pieces in this case are the three properties on the Combobox. ItemsSource, DisplayMemberPath, and SelectedItem.

Set the ItemsSource to the list of Customers you want to display in the Combobox -- same as before.
Next set the DisplayMemberPath to the property name on this list that you want to use to display in the list, here I used LastName – same as before.
Now set the SelectedItem to the property binding on the Order that is the navigation property to Customer.

This will work properly with LINQ to SQL classes and Entity Framework entities but there is one caveat. You need to make sure that you pull the entire Customer entity into in the lookup list from the same ObjectContext you used to query the Orders. This is because the same ObjectContext (DataContext in LINQ to SQL) needs to resolve the entity references between the queries. Entity Framework does this based on the EntityKeys. The neat side effect of this with EF is that you don’t have to pull down the Customers with the Orders query, they will automatically become references when the Customer lookup list is queried through the same context. (See my post on explicit load for more details on how to bring down related EF entities when you only make one call.)

This behavior may be desired in a lot of scenarios but if we do not need to modify the Customer, like in our example, this can be overkill especially if the Customer has a lot of large fields you aren’t using. In the first example we could have only pulled a subset of fields from the Customer table – the only required ones would be the ones used in the data binding, CustomerID and LastName. So with DataTables and LINQ to SQL classes that bind on the values we could have optimized our lookup list query to:

Dim customerList = From c In db.Customers _
                   Where c.Orders.Count > 0 _
                   Order By c.LastName, c.FirstName _
                   Select c.CustomerID, c.LastName

This will not work if we bind directly to the navigation properties because the customerList is now a list of anonymous types and not a list of Customer entities. So binding to the values gives you greater flexibility with your lookup list queries. Unfortunately in the current version of EF you cannot bind this way but they are planning to enable this in the next version. In .NET 4.0 the EF team will add support for a new type of association called "FK Associations". Read more about that here.

And I’m working on the next set of WPF Forms over Data How Do I videos, this time with Entity Framework, that will hopefully explain how to use EF in practical way by building WPF data applications. Stay tuned!

Enjoy!

Tally Rows in a DataSet that Match a Condition

Beth Massi — Tue, 28 Apr 2009 05:24:21 GMT

Today I got a question that comes up often in data application programming about how to count rows in a DataSet that matched a condition. The DataSet may be bound to a DataGridView or other list control and it’s tempting to start looking at the control to see if you can coax it into returning what you need but usually there is a much better way.

For instance, say we have a table in our database called “Inbox” that has varchar fields Subject, From, and Status and we’d like to tally all the rows where the Status = “Unread”. Suppose we’ve also created a typed DataSet that contains this Inbox table. You create a data-bound Windows Form with a DataGridView on it by dragging the table from the Data sources window onto the form (like I showed in this video). When you do this, Visual Studio generates code that hooks up your DataGridView’s DataSource property to a BindingSource object which in turn has it’s DataSource set to your DataSet. This is a good thing. The BindingSource is a simple controller that provides currency between your DataSet and the DataGidView UI. (In WPF this is similar to the CollectionView object). You write code against the BindingSource instead so that it doesn’t matter what kind of control is being used to display the data.

BindingSource to the Rescue

The BindingSource in this case is actually working with a DataView, not the actual DataTable, which may seem confusing. This is needed for related data binding to work (see this video and this one). Since the BindingSource manages the currency (current row position) that is being displayed by the controls, in this example the BindingSource.List will return the DataView and the BindingSource.Current property will always return the DataRowView. You access your typed DataRow by casting the DataRowView.Row property. So to get a count of items in the DataView we could just simply ask the BindingSource for a count of it’s rows no matter what kind of control is being used for the display:

Dim count = Me.InboxBindingSource.Count

Or we could grab the DataView and ask for its count:

Dim dv = CType(Me.InboxBindingSource.List, DataView)
Dim count = dv.Count

Once you have the DataView you can apply more filtering on it directly or we can loop through it to tally the rows where Status = “Unread”.

Dim dv = CType(Me.InboxBindingSource.List, DataView)
Dim count = 0
For Each drv As DataRowView In dv
    Dim inboxRow = CType(drv.Row, EmailDataSet.InboxRow)
    If inboxRow.Status = "Unread" Then
        count += 1
    End If
Next

But the neat thing about using the BindingSource is that you always know the current row being displayed. So if we want to tally the rows based on a condition in the current row we go through the BindingSource. You can easily write the code that gets the current row as your typed data row by using a code snippet. Right-click in the editor, select Data – LINQ, XML, Designer, ADO.NET > Designer Features and ADO.NET > Converts BindingSource.Current to a specific row in a DataTable:

This code returns a tally of rows in the current view that have the same status as the selected row. Instead of looping through the data manually with For Each, this code sets the Filter property of the DataView instead:

Dim currentRow As EmailDataSet.InboxRow
currentRow = CType(CType(Me.InboxBindingSource.Current, DataRowView).Row, EmailDataSet.InboxRow)

Dim dv = CType(Me.InboxBindingSource.List, DataView)
dv.RowFilter = "Status ='" & currentRow.Status & "'"
Dim count = dv.Count

This technique causes any bound controls to update to the new filter. To remove the filter, set the Filter property to Nothing or the empty string. Although this code may work for a lot of scenarios, we may have a situation where we do not want to affect any of the bound controls by changing the filter. And even though this is a simple condition it would be nice not to have to manually write the loop ourselves. This is a perfect place to use LINQ instead.

LINQ to the Rescue

If we want to tally the entire DataTable for rows matching a condition we can use LINQ to DataSets with Visual Basic’s Aggregate clause. This doesn’t involve a BindingSource at all but be aware that the DataView that a BindingSource may be displaying data from may be filtered. In the case of this example the DataView contains the same rows as the DataTable (no filter) so we can write a simple Aggregate query to count the rows:

Dim count As Integer = Aggregate row In Me.EmailDataSet.Inbox _
                       Where row.Status = "Unread" _
                       Into UnreadCount = Count()

This will return an integer indicating the number of rows in the entire DataTable that matched the Where clause. But what if we do want to use the BindingSource and take into account any filters that are being applied to the DataView in which controls are bound? In this case we can still use an Aggregate LINQ query it will just be against the DataView so we will need to cast a few things:

Dim dv = CType(Me.InboxBindingSource.List, DataView)
Dim count As Integer = Aggregate row In dv.Cast(Of DataRowView).AsQueryable _
                       Where CType(row.Row, EmailDataSet.InboxRow).Status = "Unread" _
                       Into UnreadCount = Count()

Now what would be really nice is to automatically tally this information from the DataSet and display it in a label on the Form anytime the user made any changes to the rows. We can do this easily by handling the BindingSource’s ListChanged event and putting our Aggregate LINQ query in there:

Private Sub InboxBindingSource_ListChanged() Handles InboxBindingSource.ListChanged
    Dim count As Integer = Aggregate row In Me.EmailDataSet.Inbox _
                           Where row.Status = "Unread" _
                           Into UnreadCount = Count()

    Me.lblStatus.Text = count.ToString() & " unread email."
End Sub

Now this will give us a “live” running tally and we’re not bound to the UI controls at all. Nice. For more information on Aggregate queries and DataSets check out this video.

More resources:

Enjoy!

ADO.NET Data Services - Intercepting Queries and Adding Validation

Beth Massi — Thu, 22 Jan 2009 07:53:00 GMT

Last few posts I've been building a WPF client against ADO.NET Data Services, if you missed them:

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 method must have public scope and be annotated with the QueryInterceptorAttribute, taking the name of a entity set as a parameter.
The method must accept no parameters.
The method must return an expression of type System.Linq.Expressions.Expression(Of Func(Of T, Boolean)) that is the filter to be composed for the entity set.

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.

Enjoy!

Community Article: Scaling ADO.NET DataTables

Beth Massi — Wed, 17 Dec 2008 23:43:52 GMT

We just released a new community article onto the Visual Basic Developer Center by one of our MVPs, Jeff Certain, called Scaling ADO.NET DataTables. In this article Jeff shows shows us how to query and aggregate data using the built in DataTable methods as well as LINQ to DataSets. He compares the performance on indexed and non indexed DataTables in a variety of scenarios. See for yourself what Jeff recommends on large sets of data.

Enjoy!

Editing Data from Two Tables in a Single DataGridView

Beth Massi — Thu, 16 Oct 2008 04:31:00 GMT

I've had a lot of questions lately on how to display data from two separate tables in the database into a single DataGridView for editing. It sure would be nice if all our data was in a single table, but in reality most of the time it's not. Basically the problem is that we want one single table (entity) representation on the client even though we have two physical tables in the database holding the information... thus we need to "split" the data in our entity on the client side into two or more physical tables on the server. There are many ways you can do this depending on the relations in the database and also depending on what your client-side data source happens to be. I'll present a simple, common database table scenario and then attempt to explain how we can work with it using DataTables, LINQ to SQL classes, and then an Entity Data Model --- three different approaches to working with data in Visual Studio.

So let's take a very simple example. In my database I have two tables with a one-to-one relationship, Customer and CustomerContactInfo, one storing basic information about a customer and another that stores contact information:

Entity Splitting DataTables

If we're using DataSets in our application already we probably want to represent this as a single Customer client-side DataTable. To do this, we need to specify some stored procedures in our database for Select, Insert, Update, and Delete so that when ADO.NET retrieves our data or sends back the updated, inserted and deleted rows to the database, it calls our stored procedures that do the work of splitting the data into the proper physical tables. You can easily map DataTables in the DataSet designer to stored procedures. In our example these stored procedures are going to be very simple:

CREATE PROCEDURE [dbo].[GetCustomers] AS
SELECT  cust.CustomerID, 
        cust.Title, 
        cust.FirstName, 
        cust.MiddleName, 
        cust.LastName, 
        cust.Suffix, 
        cust.CompanyName, 
        cust.SalesPerson, 
        contact.EmailAddress, 
        contact.Phone
FROM    [dbo].[Customer] AS cust
JOIN    [dbo].[CustomerContactInfo] 
        AS contact ON cust.CustomerID = contact.CustomerID

CREATE PROCEDURE [dbo].[DeleteCustomer](
    @CustomerID [int]
) AS
BEGIN

    DELETE   [dbo].[CustomerContactInfo]
    WHERE    [CustomerID] = @CustomerID
    
    DELETE   [dbo].[Customer]
    WHERE    [CustomerID] = @CustomerID
    
END

CREATE PROCEDURE [dbo].[UpdateCustomer](
    @CustomerID [int],
    @Title [nvarchar](8),
    @FirstName [nvarchar](50),
    @MiddleName [nvarchar](50),
    @LastName [nvarchar](50),
    @Suffix [nvarchar](10),
    @CompanyName [nvarchar](128),
    @SalesPerson [nvarchar](256),
    @EmailAddress [nvarchar](50),
    @Phone [nvarchar](25)
) AS
BEGIN

    UPDATE  [dbo].[Customer]
    SET     [Title] = @Title,
            [FirstName] = @FirstName,
            [MiddleName] = @MiddleName,
            [LastName] = @LastName,
            [Suffix] = @Suffix,
            [CompanyName] = @CompanyName,
            [SalesPerson] = @SalesPerson
    WHERE   [CustomerID] = @CustomerID

    UPDATE  [dbo].[CustomerContactInfo]
    SET     [EmailAddress] = @EmailAddress,
            [Phone] = @Phone
    WHERE   [CustomerID] = @CustomerID
    
END

CREATE PROCEDURE [dbo].[InsertCustomer](
    @Title [nvarchar](8),
    @FirstName [nvarchar](50),
    @MiddleName [nvarchar](50),
    @LastName [nvarchar](50),
    @Suffix [nvarchar](10),
    @CompanyName [nvarchar](128),
    @SalesPerson [nvarchar](256),
    @EmailAddress [nvarchar](50),
    @Phone [nvarchar](25),
    @CustomerID int OUTPUT
) AS
BEGIN

    INSERT INTO [dbo].[Customer]
    VALUES (
        @Title,
        @FirstName,
        @MiddleName,
        @LastName,
        @Suffix,
        @CompanyName,
        @SalesPerson )
    
    -- Get back the customer ID 
    SELECT @customerID = CustomerID
    FROM [dbo].[Customer]
    WHERE @@ROWCOUNT > 0 AND [CustomerID] = scope_identity()

    INSERT INTO [dbo].[CustomerContactInfo]
    VALUES (
        @customerID,
        @EmailAddress,
        @Phone)
    
END

Now that we have that set up, open up the Data Source Window in Visual Studio and add a new data source, select the Database and then select just the GetCustomers stored procedure:

Click Finish and then open up the DataSet designer by double-clicking on the CustomerDataset.xsd in the Solution Explorer. You'll notice that the name of the DataTable is GetCustomers so change that to just "Customer". Next we need to configure the DataTable so that it will use our stored procedures so right click on the DataTable and select "Configure...". The TableAdapter Configuration Wizard should open and this will allow you to map your stored procedures to the Update, Insert and Delete commands. By default the fields will match up by name so you shouldn't have to do anything but drop down the combo boxes and select the right procedures for each action.

To test this out you can drag the Customer table from the Data Sources window on to a Windows Form as a DataGrid and immediately run it.

You will see the data from both tables displayed in the grid and they will be editable. As you update, insert and delete rows here, the DataSet will keep track of these changes for you. So when it is time to save the data via a call to the TableAdapterManager.UpdateAll method, the proper stored procedure will be called for each row that was modified.

Entity Splitting LINQ to SQL Classes

So with a Dataset, the DataTables do not have to map one-to-one with your database tables, but you need to specify how the data should be saved back to the database via stored procedures. This is also true if using LINQ to SQL classes. In that case though, you drag methods onto the method pane first and then map them to the class in the designer by right-clicking on the class and selecting "Configure Behavior...".

However with LINQ to SQL classes you can't map the Select behavior unfortunately. What you do instead is map the result of the GetCustomers method to a result type of Customer (the class we created on the design surface). Then when accessing the customers data you need to remember to call the GetCustomers method and not access the Customers directly in the DataContext, otherwise you'll get a SQLException that it cant find the additional columns when retrieving the data (in our case EmailAddress and Phone).

Private db As New MyDataContext

Private SubForm1_Load() Handles MyBase.Load     
    'This will properly populate a collection of our     
    ' Customer entities.    
    Me.CustomerBindingSource.DataSource = db.GetCustomers
End Sub

To save the data in a connected state like this we can simply call SubmitChanges on the DataContext. The LINQ to SQL DataContext tracks the state of each modification, deletion or insertion into the collection of Customers and will call the corresponding stored procedures that we configured.

Private Sub CustomerBindingNavigatorSaveItem_Click() _
   Handles CustomerBindingNavigatorSaveItem.Click
   Try
       db.SubmitChanges()

       MsgBox("saved")
   Catch ex As Exception
       MsgBox(ex.ToString)
   End Try
End Sub

(Note: In order to get get drag-drop data binding in Windows Forms to work with LINQ to SQL objects you need to select Data --> "Add New Data Source" on the main menu, then select Object (not database). Then select the Customer object and Finish. This will populate your Data Sources window so you can drag the grid onto a Windows form.)

Entity Splitting Using the Entity Data Model

This scenario becomes pretty straight-forward and automatic using the Entity Framework (EF) and you're not required to write any stored procedures to get it to work. This is because EF provides more complex mappings out of the box than LINQ to SQL or DataSets. And EF separates your database schema from your object model by providing a mapping layer. If you have Visual Studio 2008 Service Pack 1 you can create what's called an Entity Data Model which provides an ObjectContext, similar in theory to the LINQ to SQL DataContext above, but it provides many more mapping features.

When you add a new Entity Data Model to your project you can choose to generate it from the database or you can create an empty model. For this example I'll choose to generate it from the database and I'll select just the Customer and CustomerContactInfo tables and no stored procedures this time. (Note that I can still map stored procs to the update, insert and delete behaviors if I need to though.)

This looks just like our database model except you can see the navigation properties displayed that let us navigate from one entity to related ones. Notice the association is also shown as one-to-(none or)one. To set up the mapping so that it will automatically split the entity for us first we need to adjust the Customer by adding the EmailAddress and Phone. You can select the EmailAddress and Phone properties of the CustomerContactInfo and cut then paste them into the Customer. Then you can delete the CustomerContactInfo class from the design surface.

Now select the Customer and look at the Mapping Details window. Below the column mappings you will see <Add a Table or View>. Drop that down and select CustomerContactInfo and it will automatically map the columns in that table to the properties that we added.

Save the model and rebuild the project. Now you can add Customer as a data source for drag-drop Winforms data binding the same way you do for LINQ to SQL classes or your own objects. And the code for loading and saving of the Customers is similar to the LINQ to SQL code above except loading the Customers is much more intuitive. And the ObjectContext tracks changes for you and generates the proper insert, update, and delete statements automatically without you having to define stored procedures to do the splitting.

Private db As New MyDatabaseEntities

Private Sub Form1_Load() Handles MyBase.Load

    Me.CustomerBindingSource.DataSource = db.Customer
End Sub

Private Sub CustomerBindingNavigatorSaveItem_Click() _
    Handles CustomerBindingNavigatorSaveItem.Click

    Try
        db.SaveChanges()

        MsgBox("saved")
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

This is just the tip of the iceberg using the Entity Data Model. I'd recommend reading the documentation in the MSDN library, visiting the forums and the FAQ, as well as visiting Julie Lerman's site (she's been living EF since the early Betas). I'm just learning EF myself but as you can see it allows you to model more complex data scenarios.

Enjoy!

MSDN Magazine - Dynamic Data Entry With XML Literals

Beth Massi — Thu, 02 Oct 2008 04:49:38 GMT

Check out the latest news on the VB Dev Center. I'm finally headlining myself ;-)

I'm in the October issue of MSDN Magazine. I've been managing the content for the Basic Instincts column for a little over a year now where we rotate writers from the VB Team. This month is my birthday month so I decided to take this one on personally. :-)

Enjoy!

Accessing Open XML Document Parts with the Open XML SDK

Beth Massi — Thu, 31 Jul 2008 05:30:00 GMT

About a month ago the Open XML SDK 1.0 (June 08 update) was released. The SDK provides strongly typed document part access to Word 2007, Excel 2007 and PowerPoint 2007 documents. The SDK has been a CTP for a while, but last month version 1.0 was finally released. So I installed this baby last week and started playing around with it and found it really easy to use after briefly looking at the documentation. The How Do I section is a great place to start.

Upgrading the Letter Generator

I decided to upgrade my Word 2007 letter generator program to use the SDK to manipulate the packages. Remember that Office 2007 documents are really just archive files, so if you rename them to .ZIP you can take a look at the contents of the package. The Open XML Package spec defines a set of XML files that contain the content and define the relationships for all of the document parts stored in a single package. To programmatically manipulate them you can use the raw System.IO.Packaging namespace, but the SDK's DocumentFormat.OpenXml.Packaging namespace is much easier to work with.

My mail merge program uses XML literals to construct XML for the document part of a Word 2007 file based on data in the Northwind database. The LINQ query was a piece of cake compared to figuring out how to manipulate the .docx package in order to replace the document.xml (called the MainDocument) part. Not that the final code is particularly long, it was just a pain to figure it out. The SDK not only saved me a few lines of code, it made the code much more readable and took only a few minutes to write. (I updated the code for the WordMailMerge program on Code Gallery).

Getting Started with the Open XML SDK

Let's take another simple example that constructs a MainDocument part using XML literals and then replaces it in a .docx package using the SDK. This time I'll focus on the code that manipulates the Open XML package with the SDK not on the particulars of XML Literals. The first thing I recommend is to install the VSTO Power Tools so you can open Office 2007 documents and manipulate the parts directly in the Visual Studio IDE like I showed in my last post using the Open XML Package Editor.

Of course you'll need to also install the SDK which places the DocumentFormat.OpenXML.dll assembly into your GAC. Add a reference to this assembly in your project. As an aside, when x-copy deploying to a machine with the .NET Framework on it already just make sure you deploy the DocumentFormat.OpenXML.dll assembly alongside your application to avoid having to install the SDK on the target machine. The easiest thing to do is select "Show All Files" in the Solution Explorer, expand the References, and on the Properties for the DocumentFormat.OpenXML reference set "Copy Local" = True. This will place a private copy of the assembly next to your application when it's built.

Now create a new Word 2007 document with some simple text in it, for instance, type: "This is my document" then save it and add the .docx file to your Visual Basic project. Double-click on it and that opens the Open XML Package Editor:

We can manipulate the parts through this editor if we want to but what I really want to do is replace the document.xml with our own we create using XML literals and embedded expressions. Double-click on the document.xml to open the MainDocument part in the XML Editor (if the XML editor opens and the XML is all on one line with no breaks then just select all the contents and cut then paste it back into the editor and it will put the proper line breaks in there for you : Ctrl + A,X,V).

For this simple example, let's place the executing user's name into the document. Create the XML Literal and an embedded expression by pasting the document.xml into the VB Editor and adding an expression to print out the executing user's name:

Dim myDoc = <?xml version="1.0" encoding="utf-8" standalone="yes"?>
            <w:document xmlns:ve="http://schemas.openxmlformats.org/markup-compatibility/2006"
               xmlns:o="urn:schemas-microsoft-com:office:office"
               xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
               xmlns:m="http://schemas.openxmlformats.org/officeDocument/2006/math"
               xmlns:v="urn:schemas-microsoft-com:vml"
               xmlns:wp="http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing"
               xmlns:w10="urn:schemas-microsoft-com:office:word"
               xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"
               xmlns:wne="http://schemas.microsoft.com/office/word/2006/wordml">
               <w:body>
                   <w:p w:rsidR="00DD17EB" w:rsidRDefault="00361264">
                       <w:r>
                           <w:t>This is <%= Environment.UserName %>'s document</w:t>
                       </w:r>
                   </w:p>
                   <w:sectPr w:rsidR="00DD17EB" w:rsidSect="00DD17EB">
                       <w:pgSz w:w="12240" w:h="15840"/>
                       <w:pgMar w:top="1440" w:right="1440" w:bottom="1440"
                           w:left="1440" w:header="720" w:footer="720" w:gutter="0"/>
                       <w:cols w:space="720"/>
                       <w:docGrid w:linePitch="360"/>
                   </w:sectPr>
               </w:body>
           </w:document>

Replacing the MainDocument Part

Before the SDK, replacing the MainDocument part in the package we had to figure out the right content type and write the code that deleted then added the new part. We also needed to add a reference to WindowsBase (a 3.0 assembly) in order to access the System.IO.Packaging namespace.

Imports System.IO.Packaging
Imports System.IO

...

'**** Without OpenXML SDK
Dim uri As New Uri("/word/document.xml", UriKind.Relative)
Dim contentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document.main+xml"
Dim docFile = CurDir() & "\MyDocument.docx"

Using p As Package = Package.Open(docFile)
    'Delete the current document.xml file
    p.DeletePart(uri)

    'Replace that part with our XDocument
    Dim replace As PackagePart = p.CreatePart(uri, contentType)
    Using sw As New StreamWriter(replace.GetStream())
        myDoc.Document.Save(sw)
     End Using
End Using

For this example it's pretty easy, however if you add/remove parts it's up to you to update the relations in the package and this isn't an easy task using this raw API. Enter the Open XML SDK. Now we don't need to add a reference to WindowsBase, only to DocumentFormat.OpenXML and import the Packaging namespace contained within. Then our code can access the parts of the document in a strongly-typed way:

Imports DocumentFormat.OpenXml.Packaging
Imports System.IO

...

'***** Use the OpenXML SDK for easier access to parts
Dim docFile = CurDir() & "\MyDocument.docx"

Dim wordDoc = WordprocessingDocument.Open(docFile, True)
Using wordDoc
    'Replace the document part with our XML
    Using sw As New StreamWriter(wordDoc.MainDocumentPart.GetStream(FileMode.Create))
        myDoc.Document.Save(sw)
    End Using
End Using

After we run this code you'll see that the MainDocument part now has the user name in the document body as described by our XML literal.

Using LINQ with the Open XML SDK

Using the SDK we can also write LINQ queries over the part collections. For instance if we want to select all the top level parts and any of their sub-parts, we can write a query like so:

Using wordDoc

  Dim parts = From part In wordDoc.Parts _
            Select part.OpenXmlPart, _
                   part.RelationshipId, _
                   part.OpenXmlPart.RelationshipType, _
                   SubParts = _
                   ( _
                    From subPart In part.OpenXmlPart.Parts _
                    Select subPart.OpenXmlPart, _
                           subPart.RelationshipId, _
                           subPart.OpenXmlPart.RelationshipType _
                   ).ToList

End Using

This query returns similar information to what you get with the Open XML Package Editor if we look at the same document. If we display the query results in two related DataGridViews we'll see that the MainDocument part contains additional parts for things like themes, styles and settings.

If we want to access the actual XML content for each of the OpenXmlParts we can call the GetStream method on the OpenXmlPart we want and pass it a StreamReader which we can use to load an XDocument object.

Using wordDoc

 Dim parts = From part In wordDoc.Parts _
             Select Doc = XDocument.Load(New StreamReader(part.OpenXmlPart.GetStream())), _
                    part.OpenXmlPart, _
                    part.RelationshipId, _
                    part.OpenXmlPart.RelationshipType, _
                    SubParts = _
                    ( _
                     From subPart In part.OpenXmlPart.Parts _
                     Select Doc = XDocument.Load(New StreamReader(subPart.OpenXmlPart.GetStream())), _
                            subPart.OpenXmlPart, _
                            subPart.RelationshipId, _
                            subPart.OpenXmlPart.RelationshipType _
                    ).ToList
End Using

Loading and Querying the XDocument from the Package

Let's say we have a case where we can't use XML Literals and embedded expressions, instead we want to pull out the MainDocument part and find and replace text inside. We can do this using XML Axis properties. This can get pretty tricky because there may be a lot of formatting information in the document. An easier way may be to use content controls which you can alias so that it's easier to query those instead, but for this example it's a pretty simple query to find our body text and replace the word "my" with the user name.

Imports <xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main">

...

Dim docFile = CurDir() & "\MyDocument.docx"
Dim wordDoc = WordprocessingDocument.Open(docFile, True)
Dim myDoc As XDocument

Using wordDoc
    Using xr As New StreamReader(wordDoc.MainDocumentPart.GetStream())
        'Load the MainDocument part's XML
        myDoc = XDocument.Load(xr)
    End Using

    'Find the only line of text in this document
    Dim element = (From item In myDoc...<w:t>)(0)

    'Replace the value of the element
    element.Value = <s>This is <%= Environment.UserName %>'s document</s>.Value

    Using sw As New StreamWriter(wordDoc.MainDocumentPart.GetStream(FileMode.Create))
        'Save the modified XML back to the MainDocument part
        myDoc.Save(sw)
    End Using

End Using

One of the cool things about using the Open XML SDK is that you don't have to have Office installed to run any of this code. So it's a great alternative instead of using slow COM automation to manipulate documents.

As I explore Open XML in Office 2007 more and more I'll post more realistic business examples using LINQ to XML and Visual Basic. For now, you may want to sink your teeth into Ken Getz's Advanced Basics March 2008 article in MSDN Magazine: Office 2007 Files and LINQ. This article also shows off some important XML namespace features of Visual Basic.

Enjoy!

Article: Sharpening Your Axis with Visual Basic 9

Beth Massi — Tue, 01 Jul 2008 00:12:00 GMT

In the July/August issue of CoDe Magazine there's an article I wrote with Avner Aharoni (the Program Manager on XML literals) called Sharpening Your Axis with Visual Basic 9. (They just opened up online access to even non-subscribers today!) I highly recommend this article which shows you from the beginning how XML literals, embedded expressions and axis properties work in Visual Basic.

I start with the basic syntax and definitions and then walk through the XML to Schema tool (which has been added to VS2008 SP1) to enable XML IntelliSense, as well as show some practical examples of using these features with LINQ. Avner describes how IntelliSense works and discusses advanced scenarios with XML namespaces. We end with tips-and-tricks which get you thinking about using XML literals in other text generation/manipulation scenarios.

If you like this topic, so do I! Check out the XML topic feed from this blog for more resources.

Enjoy!

Dynamic Data Entry with WPF and LINQ

Beth Massi — Wed, 25 Jun 2008 01:29:22 GMT

In my last post on this subject I explored creating WPF UI's dynamically using XML literals. The one part that bugged me a bit was that even though the UI was dynamic, we were using a fixed object model of our customer (using LINQ to SQL classes). I wanted to augment this code a bit more so that we could not only dynamically generate the WPF UI but also dynamically edit any maintenance table in our database -- not just customer. What I really wanted to enable is if we modify the database schema of any of our maintenance tables then we don't have to update our object model and recompile our code.

To achieve this I decided to explore loading and editing a simple untyped DataTable at runtime. This worked well using XML literals again -- this time to generate the SELECT and UPDATE statements for the SqlDataAdapter. One thing to note, this technique relies on the database to enforce any validation rules. This is why I would only use this type of dynamic form to edit very simple tables (aka. maintenance tables).

For this example I decided to create a typed DataTable for my TableSchema instead as well, eliminating the need for LINQ to SQL classes in the project. If you recall this is used to hold the column metadata (ColumnName, DataType, etc.) for the table we want to edit. I just right-clicked on my project to add a new item and selected DataSet. I named it TableSchemaDataSet and then just simply dragged the GetTableSchema stored procedure (which we added to the Northwind database in the last post) onto the design surface. This automatically creates a typed DataTable for us with no fuss. I renamed the DataTable to TableSchema and saved it.

Now for the fun part. We need to load an untyped DataTable into our dynamically generated WPF form because we don't want to make any assumptions about the schema of the table we're editing (except that there is a primary key of some sort). DataTables and DataSets work well with WPF but there are a couple things we need to set up manually since we're loading this all at runtime. (Writing the code to load and save the DataTable makes me REALLY appreciate the DataSet designer and the code that it generates for you).

First I set up a Public property on the form to hold the name of the table we want to edit called TableName and set the default to "Shippers" this time. Then I created some private class level variables to reference the ADO.NET objects we'll need. (Take a look at the last post for the XAML markup of the Window, it's exactly the same for this example.)

Imports <xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation">
Imports <xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">
Imports System.Windows.Markup
Imports System.Data.SqlClient
Imports System.Data

Partial Public Class Window2
    'This is the metadata table we created in the DataSet Designer
    Private TableSchema As New TableSchemaDataSet.TableSchemaDataTable
    'ADO.NET objects used to load and save the table we're editing
    Private TableDataAdapter As New SqlDataAdapter
    Private TableConnection As New SqlConnection(My.Settings.NorthwindConnectionString)
    Private Table As DataTable
    'This is the key field used in searching for a row in this example
    Private PKField As TableSchemaDataSet.TableSchemaRow

    'This property can be set before the Form.Show() to edit any table
    Private m_tableName As String = "Shippers"
    Public Property TableName() As String
        Get
            Return m_tableName
        End Get
        Set(ByVal value As String)
            m_tableName = value
        End Set
    End Property

In the Loaded event handler we can now load the metadata, create and load our XAML to display our UI just like before, and then set our UpdateCommand on the TableDataAdapter.

Private Sub Window1_Loaded() Handles MyBase.Loaded
    Try
        'Get the schema of the database table we want to edit
        Dim taSchema As New TableSchemaDataSetTableAdapters.TableSchemaTableAdapter
        taSchema.Fill(Me.TableSchema, Me.TableName)

        'Create the DataTable that will hold the record we're editing
        Me.Table = New DataTable(Me.TableName)

        Me.Title = Me.TableName
        Me.LoadUI() 
        Me.SetPrimaryKey()
        Me.SetUpdateCommand()        

    Catch ex As Exception
        MsgBox(ex.ToString)
        Me.Close()
    End Try
End Sub

Private Sub LoadUI()

    Dim UI = <Grid xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
                 xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
                 Name="Grid1">
                 <Grid.ColumnDefinitions>
                     <ColumnDefinition Width="100*"/>
                     <ColumnDefinition Width="200*"/>
                 </Grid.ColumnDefinitions>
                 <StackPanel Name="StackLabels" Margin="3">
                     <%= From column In Me.TableSchema _
                         Where column.IsPrimaryKey = 0 AndAlso column.DataType <> "timestamp" _
                         Select <Label
                                    Height="28"
                                    Name=<%= column.ColumnName & "Label" %>
                                    HorizontalContentAlignment="Right">
                                    <%= column.ColumnName %>:</Label> %>
                 </StackPanel>
                 <StackPanel Grid.Column="1" Name="StackFields" Margin="3">
                     <%= From column In Me.TableSchema _
                         Where column.IsPrimaryKey = 0 AndAlso column.DataType <> "timestamp" _
                         Select GetUIElement(column) %>
                 </StackPanel>
             </Grid>

     Me.DynamicContent.Content = XamlReader.Load(UI.CreateReader())

End Sub

Private Function GetUIElement(ByVal columnInfo As TableSchemaDataSet.TableSchemaRow) As XElement
    Select Case columnInfo.DataType.ToLower
        Case "datetime", "int", "smallint", "money"
            Return <TextBox
                       Height="28"
                       Name=<%= "txt" & columnInfo.ColumnName %>
                       Text=<%= "{Binding Path=" & columnInfo.ColumnName & "}" %>/>
        Case "bit"
            Return <CheckBox
                       HorizontalContentAlignment="Left"
                       Name=<%= "chk" & columnInfo.ColumnName %>
                       IsChecked=<%= "{Binding Path=" & columnInfo.ColumnName & "}" %>>
                       <%= columnInfo.ColumnName %>
                   </CheckBox>
        Case Else
            Return <TextBox
                       Height="28"
                       Name=<%= "txt" & columnInfo.ColumnName %>
                       MaxLength=<%= columnInfo.MaxLength %>
                       Text=<%= "{Binding Path=" & columnInfo.ColumnName & "}" %>/>
    End Select
End Function

Now that we've got the UI defined I'll set the primary key field (which is a TableSchemaDataRow object) so that we can use this in our UPDATE statement as well as in the SELECT query when the user clicks the Find button on the form. Generally primary keys are surrogate keys (like auto-incrementing integers) and mean nothing to the user, so instead you may want to create another Public property that captures the search field name. Since there is only one primary key field on a table, I use the FirstOrDefault() extension method which returns the first of the sequence.

Private Sub SetPrimaryKey()
    'Grab the Primary Key column of the table we want to edit so we can use it in the search
    Me.PKField = (From column In Me.TableSchema Where column.IsPrimaryKey = 1).FirstOrDefault()
End Sub

To create the SELECT statement, notice that I'm once again using XML literals but this time I'm not creating XML. Instead I'm creating a string by calling the XElement's .Value property. We then can create the SqlCommand and fill our untyped DataTable with the results. Setting the WPF form's DataContext sets up the data binding to the fields we specified when we generated the XAML above.

Private Sub btnFind_Click() Handles btnFind.Click
    If Me.txtSearch.Text <> "" Then
        Try
            'Create the SELECT command
            Dim cmdText = <s>
                          SELECT * FROM <%= Me.TableName %> 
                          WHERE <%= Me.PKField.ColumnName %> = 
                                <%= If(Me.PKField.DataType.Contains("char"), _
                                    "'" & Me.txtSearch.Text & "'", _
                                    Me.txtSearch.Text) %>
                          </s>.Value

            Dim cmd As New SqlCommand(cmdText, Me.TableConnection)
            Me.Table.Clear()
            Me.TableDataAdapter.SelectCommand = cmd
            Me.TableDataAdapter.Fill(Me.Table)

            Me.DataContext = Me.Table
            Dim view = CollectionViewSource.GetDefaultView(Me.Table)
            view.MoveCurrentToFirst()

        Catch ex As Exception
            MsgBox(ex.ToString)
            Me.DataContext = Nothing
        End Try
    Else
        Me.DataContext = Nothing
    End If
End Sub

We want to be able to edit and save this record so we need to also generate an UPDATE command. For this example I'm only setting up the UpdateCommand on the SqlDataAdapter because we're creating a form that just edits records, but it's easy enough to create Delete and Insert commands as well. Here I'm using XML Literals again (of course! ;-)).

Private Sub SetUpdateCommand()
    'Set the UpdateCommand so that we can save edited records in the table
    Dim cmdText = <s>
                  UPDATE <%= Me.TableName %> 
                  SET <%= From column In Me.TableSchema _
                          Where column.IsPrimaryKey = 0 AndAlso column.DataType <> "timestamp" _
                          Select <c>
                                     <%= column.ColumnName %> = @<%= column.ColumnName %>
                                     <%= If(Me.TableSchema.Rows.IndexOf(column) < _
                                            Me.TableSchema.Rows.Count - 1, ", ", "") %>
                                 </c>.Value %>
                  WHERE <%= Me.PKField.ColumnName %> = @<%= Me.PKField.ColumnName %>
                        <%= From column In Me.TableSchema _
                            Where column.IsPrimaryKey = 0 AndAlso column.DataType = "timestamp" _
                            Select <c>
                                     AND <%= column.ColumnName %> = @<%= column.ColumnName %>
                                   </c>.Value %>
                  </s>.Value

    Dim cmd As New SqlCommand(cmdText, Me.TableConnection)
    Dim p As SqlParameter

    For Each column In Me.TableSchema
        If column.IsPrimaryKey = 0 AndAlso column.DataType = "timestamp" Then
            'Note: It's recommended to use a TimeStamp column in your tables for concurrency checking
            p = New SqlParameter("@" & column.ColumnName, SqlDbType.Timestamp)
            p.SourceVersion = DataRowVersion.Original
            p.SourceColumn = column.ColumnName
            cmd.Parameters.Add(p)
        Else
            p = New SqlParameter("@" & column.ColumnName, _
                                 CType([Enum].Parse(GetType(SqlDbType), column.DataType, True), SqlDbType))
            p.SourceColumn = column.ColumnName
            p.SourceVersion = DataRowVersion.Current
            cmd.Parameters.Add(p)
        End If
    Next

    Me.TableDataAdapter.UpdateCommand = cmd
End Sub

One important note here is that when I create the UpdateCommand, I'm assuming that concurrency checking is being done with a TimeStamp field (which I've added to my copy of Northwind) but you can also create the "long version" where it checks original against current values as well.

So now we can simply set the TableName property of this form and it will dynamically generate the UI, load the data when we click the Find button, and allow us to save our changes back to the database. Try modifying your database table's schema and running it again without recompiling. Slick.

I've uploaded this project onto CodeGallery with both forms (this one and the one we did in the last post) so have a look. You'll need to download the Northwind database here first.

Enjoy!

Dynamic UI with WPF and LINQ

Beth Massi — Fri, 13 Jun 2008 02:51:00 GMT

Lately I've been getting my hands deep into WPF with my line-of-business (LOB)/data-based application mind set. I'm taking a different approach to the technology resisting the urge to put on my amateur-designer hat and instead purely focus on data, data-binding, formatting controls, and some basic layout. (Yes before you ask, I have started producing the WPF forms over data videos!)

Today I wanted to play with dynamically creating XAML and loading it at runtime. This was really easy using XML literals and XML namespace imports. Let me show you what I mean. You can load and save XAML at runtime using the System.Windows.Markup.XamlReader and System.Windows.Markup.XamlWriter classes. Create a new WPF project and in the Window1 code-behind you can do this:

Imports <xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation">
Imports <xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">
Imports System.Windows.Markup

Class Window1

    Private Sub Window1_Loaded() Handles MyBase.Loaded

        Dim UI = <Label Name="Label1">This is COOL!</Label>

        Me.Content = XamlReader.Load(UI.CreateReader())

    End Sub
End Class

When we run it:

There are a lot of possibilities here. For instance, wouldn't it be nice to automatically generate all your maintenance screens in your business apps? When I say "maintenance" I mean all those simpler lookup data tables or contact tables. Even a customer contact form is usually pretty basic. Instead of handing those screens to the newbie or the junior on the team why not just generate them all at runtime based on your object model or database schema?

Admittedly this isn't something that is only unique to WPF. You could do this in Winforms as well but it was an exercise in coding the layout by hand. WPF makes this a breeze because we can define one piece of XAML and we can construct it from a single LINQ query. You can use reflection to look at your object model but if it's really simple and maps one-to-one to your database table anyway you can just create a table (or a stored proc) that contains the schema (or meta-data) of all of your maintenance tables. For instance to generate a simple UI we would probably want to obtain at minimum the following properties for each column in a table:

ColumnName
DataType
MaxLength
IsPrimaryKey

You can either create a meta-data table in your database or a stored proc that returns the info from the information schema (check permissions for the stored proc if you go that route). I was playing with the stored proc idea because then if I make a change to my database, I don't need to update my code necessarily. So for this example I created a stored proc in the Northwind database called GetTableSchema:

CREATE PROCEDURE dbo.GetTableSchema
    (
    @table varchar(50)
    )    
AS
SELECT 
c.table_name As TableName, 
c.column_name As ColumnName, 
c.data_type As DataType, 
c.character_maximum_length As MaxLength,
    COALESCE (
    ( SELECT 
        CASE cu.column_name
            WHEN null THEN 0
            ELSE 1
        END
    FROM information_schema.constraint_column_usage cu
    INNER join information_schema.table_constraints ct
    ON ct.constraint_name = cu.constraint_name
    WHERE 
    ct.constraint_type = 'PRIMARY KEY' 
    AND ct.table_name = c.table_name
    AND cu.column_name = c.column_name 
    ),0) AS IsPrimaryKey
FROM information_schema.columns c
INNER JOIN information_schema.tables t
ON c.table_name = t.table_name
WHERE @table = t.table_name and 
      (t.table_type = 'BASE TABLE' and not 
      (t.table_name = 'dtproperties') and not 
      (t.table_name = 'sysdiagrams'))
ORDER BY c.table_name, c.ordinal_position

I then added a new "LINQ to SQL classes" item to my project and dragged Customer onto the design surface (for this example that's what we'll be editing) from the Northwind database I had connected to in my Server Explorer. I then expanded the "Stored Procedures" and dragged the above procedure onto the methods pane. Next I manually created an object on the design surface called TableSchema that contained the same properties as the fields I'm returning from the stored proc. Once I have that all set up I can now map the result type of the stored proc to the TableSchema class:

Okay now that we have that set up we can get back to the fun stuff. I created a simple lookup textbox and "Find" and "Save" buttons at a fixed area at the top of my WPF window. Under that I dragged a ContentControl onto the form and named it DynamicContent. We're going to generate the content here from the Customer schema and bind to the customer object that is returned from our LINQ query when we click find.

<Window x:Class="Window1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    Title="Window1"  Name="Window1" SizeToContent="WidthAndHeight" >
    <Grid Name="MainGrid"  >
        <Grid.RowDefinitions>
            <RowDefinition Height="10*" />
            <RowDefinition Height="60*" />
        </Grid.RowDefinitions>
        <StackPanel Name="StackPanel1" Orientation="Horizontal" Margin="3" VerticalAlignment="Top">
            <Label Height="28" Name="Label1" Width="84" HorizontalContentAlignment="Right" FontWeight="Bold">ID</Label>
            <TextBox Height="25" Name="txtSearch" Width="120">ALFKI</TextBox>
            <Button Height="25" Name="btnFind" Width="75">Find</Button>
            <Button Height="25" Name="btnSave" Width="75">Save</Button>
        </StackPanel>
        <ContentControl Grid.Row="1" Name="DynamicContent" Margin="3" />
    </Grid>
</Window>

First let's generate the UI in the Load event of our form. The first thing is to add the appropriate Imports at the top of the file and then we can generate our UI. (I also have handlers here for the actual loading and saving of the customer.)

Imports <xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation">
Imports <xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">
Imports System.Windows.Markup

Class Window1

    Dim db As New NorthwindDataContext
    Dim CustomerData As Customer

    Private Sub Window1_Loaded() Handles MyBase.Loaded

        Dim customerSchema = db.GetTableSchema("Customers").ToList()

        Dim UI = <Grid xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
                     xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation">
                     <Grid.ColumnDefinitions>
                         <ColumnDefinition Width="100*"/>
                         <ColumnDefinition Width="200*"/>
                     </Grid.ColumnDefinitions>
                     <StackPanel Name="StackLabels" Margin="3">
                         <%= From column In customerSchema _
                             Where column.IsPrimaryKey = 0 _
                             Select <Label
                                        Height="28"
                                        Name=<%= column.ColumnName & "Label" %>
                                        HorizontalContentAlignment="Right">
                                        <%= column.ColumnName %>:</Label> %>
                     </StackPanel>
                     <StackPanel Grid.Column="1" Name="StackFields" Margin="3">
                         <%= From column In customerSchema _
                             Where column.IsPrimaryKey = 0 _
                             Select GetUIElement(column) %>
                     </StackPanel>
                 </Grid>

        Me.DynamicContent.Content = XamlReader.Load(UI.CreateReader)

    End Sub

    Private Function GetUIElement(ByVal column As TableSchema) As XElement
        Select Case column.DataType
            Case "datetime", "int"
                Return <TextBox
                           Height="28"
                           Name=<%= "txt" & column.ColumnName %>
                           Text=<%= "{Binding Path=" & column.ColumnName & ", ValidatesOnDataErrors=True}" %>/>
            Case "bit"
                Return <CheckBox
                           HorizontalContentAlignment="Left"
                           Name=<%= "chk" & column.ColumnName %>
                           IsChecked=<%= "{Binding Path=" & column.ColumnName & ", ValidatesOnDataErrors=True}" %>>
                           <%= column.ColumnName %>
                       </CheckBox>
            Case Else
                Return <TextBox
                           Height="28"
                           Name=<%= "txt" & column.ColumnName %>
                           MaxLength=<%= column.MaxLength %>
                           Text=<%= "{Binding Path=" & column.ColumnName & ", ValidatesOnDataErrors=True}" %>/>
        End Select
    End Function

    Private Sub btnFind_Click() Handles btnFind.Click
        If Me.txtSearch.Text <> "" Then
            Me.CustomerData = (From cust In db.Customers _
                              Where cust.CustomerID = Me.txtSearch.Text).FirstOrDefault()

            Me.DataContext = Me.CustomerData
        Else
            Me.DataContext = Nothing
        End If
    End Sub

    Private Sub btnSave_Click() Handles btnSave.Click
        If Me.DataContext IsNot Nothing Then
            Try
                db.SubmitChanges()
                MsgBox("Saved")

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub
End Class

The stored proc will work for any table contained in the database, so we could even abstract this further, and I obviously didn't get very fancy with the UI -- but I think you get the idea.

UPDATE: Read the latest post on dynamic data entry and download the code samples.

Enjoy!