Beth's Chinese blog
Scott posted a podcast with with Paul Vick, Principal VB Architect, and Paul Yuknewicz, Senior Program Manager on the VB Team about the past, present and future of Visual Basic. Check it out!
Enjoy!
Rod Paddock's latest Community Article is up on the Visual Basic Developer Center on Creating Visual Studio Add-Ins which first appeared in CoDe Magazine. Rod goes a little bit deeper than I did when building an add-in and shows how to work with the project system and automatically open files and insert code. Thanks for submitting, Rod!
One of the Dev's, Matt Manela, on my VS community team has started playing with Visual Basic. I always enjoy reading the adventures a developer has learning a new language. You can read the excitement someone has as they learn. I think I'm the same way. :-) Read about some of the Lambda Expression fun Matt's having.
One thing I really love about LINQ in VB is that I don't have to do too much work of creating my own lambdas and calling extension methods explicitly for queries most of the time because VB has an expanded query syntax to do most of the common things especially with aggregates. Only in complex or dynamic queries have I had to create my own lambdas to pass to extension methods. They are a very powerful feature, indeed, but it's nice to have the simple ones taken care of for me like Sum, Min, Max, Avg, also Take [While] and Skip [While].
Read more about Lambda Expressions in Visual Basic in this MSDN Article from Tim Ng from the Visual Basic Team.
I've mentioned before that you can use XML literals in VB 9 to do text merging so I figured it should be pretty easy to do some mail merging in Word the same way. Word 2007 (and Excel, etc) now uses a standard XML format called Office Open XML to describe documents so I thought this would be the perfect opportunity to try out how to do this with LINQ and XML literals in Visual Basic.
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. Of course, I had to create some new orders first since every order in Northwind was placed before the turn of the century! Anyway, 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:
Then I just saved the Doc1.docx file to my project folder. If we rename this file with a .zip extension we can see that it's just a zip file with a bunch of XML documents inside it. This is how the Office Open XML works. Basically it's a zip package with a bunch of related XML files inside. You can read more about Office Open XML in Microsoft Office here. 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.
If I open this document.xml file in a text editor like notepad, copy all the text into the clipboard, and then paste it into a Visual Basic program it will be inferred as an XDocument object and we can see our text and placeholders in the XML (I collapsed a couple sections at the top and bottom for readability).
So what we want to do in our mail merge program is write a query that creates this XDocument for all our customers that have shipping orders and then pops it back into a zip file that Word 2007 can open. There's a namespace called System.IO.Packaging that will help us extract and replace the document.xml with the XDocument we create. But first, let's create the query by connecting our Server Explorer to our Northwind database and then adding a new item to our project and selecting the "LINQ to SQL Classes" template. Then just drag-drop the Customers and Orders tables onto the designer. (I show how to do this in this video).
What we want to end up with is a collection of XDocuments above, with our data merged into it. Instead of just creating a collection of XDocuments I also want to capture the CustomerID so that we can use it to create unique file names for our Word documents. So let's create a simple class called Letter that has two properties, CustomerID As String and Document As XDocument.
Public Class Letter Private m_doc As XDocument Public Property Document() As XDocument Get Return m_doc End Get Set(ByVal value As XDocument) m_doc = value End Set End Property Private m_ID As String Public Property CustomerID() As String Get Return m_ID End Get Set(ByVal value As String) m_ID = value End Set End Property
End Class
Now we can write our query to create a collection of these classes.
Dim db As New NorthwindDataContext
Dim letters = _ From Order In db.Orders _ Where Order.OrderDate IsNot Nothing AndAlso _ Order.ShippedDate IsNot Nothing AndAlso _ Order.ShippedDate.Value.Date = Date.Today _ Let OrdDate = Order.OrderDate.Value.ToShortDateString _ Let ShipDate = Order.ShippedDate.Value.ToShortDateString _ Select New Letter With { _ .CustomerID = Order.Customer.CustomerID, _ .Document = <?xml version="1.0" encoding="UTF-8" standalone="yes"?> rest of doc omitted for clarity ...}
We're selecting the CustomerID and the XML literal with our embedded expressions in place of the placeholder fields I created in the document. I omitted the rest of the XML literal in the query above for clarity. All we need to do is run through the literal and replace all the placeholder fields with embedded expressions from our query. For instance, here's a snapshot of the body of our letter :
...<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="00AA2EC6" 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><%= OrdDate %></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><%= ShipDate %></w:t> </w:r>...
Now that we have a collection of our Letter objects, we need to create the Word document by making a copy of our original document (which remember is just a .zip file) and replacing the \word\document.xml file in that package. You can also create packages from scratch but then you have to create a relationship file that describes the relation of all the files in the package and it can get tricky. It's easier to just make a copy of the original Word docx file in our case and just replace the part we modified. The key to writing the document back properly is getting the content type right when we call CreatePart on the Package object. The Package object lives in the System.IO.Packaging namespace.
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) For Each letter In letters Dim customerFile = letterDir & letter.CustomerID & ".docx" Console.WriteLine(String.Format("Creating letter {0}", letter.CustomerID)) My.Computer.FileSystem.CopyFile(sourceFile, customerFile, True) Using p As Package = Package.Open(customerFile) '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()) letter.Document.Save(sw) sw.Close() End Using p.Close() End Using Next
This creates a directory of our letters all nicely mail merged with the customer and order data that met our criteria in the Where clause of the letters query.
If we open one of the documents we'll see the data properly merged into the letter.
I've attached the complete example to this post. Have fun exploring the Office Open XML format because there's just so many things you can do with it especially combined with LINQ, XML literals and Visual Basic!
With Visual Basic 9 and LINQ you can easily create XML from multiple data sources including relational data, other XML sources or any other queryable object. Since most modern systems interact with each other in some form of XML the possibilities are endless. SOAP, XAML, HTML, RSS can all be created easily with LINQ to XML in Visual Basic 9. For instance, what if we wanted to display all our customers in the Northwind database on a map generated by Microsoft Virtual Earth?
Virtual Earth allows you to pass it an RSS document of items specifying their latitude and longitude to easily map out multiple locations in the world. There are a couple different formats you can pass it and one is the GeoRSS standard. All we have to do is create this XML by obtaining the latitude and longitude from the addresses we have in our customers table and then pass this GeoRSS to Virtual Earth. We can grab the latitude and longitude of our customers in the United States using the service at http://geocoder.us. This service can return a set of coordinates from any US address in a variety of formats including REST-ful RDF. We can use this service in our LINQ query in order to create the GeoRSS from our customers table in the Northwind database.
Assuming you already have a connection in Server Explorer to Northwind (or another database with addresses will do), first add a new "LINQ to SQL classes" item to your project, name it Northwind.dbml and then drag the Customers table onto the designer from the Server Explorer. The next thing to do is to import the geo namespace at the top of our code file because we’ll be using it to return the location information in the geo namespace from the XML that is returned from the geocoder.us service.
Imports <xmlns:geo="http://www.w3.org/2003/01/geo/wgs84_pos#">
Now we can write a query to create the GeoRSS for our customers. Since the Northwind database contains mostly fictitious addresses you can change the addresses to real locations or we can select just the customers living in Oregon (OR) since there are a couple valid addresses there.
Dim geoRSS = _ <rss xmlns:geo="http://www.w3.org/2003/01/geo/wgs84_pos#"> <channel> <title>Northwind Customer Locations</title> <link></link> <%= From Customer In db.Customers _ Let Desc = Customer.Address & ", " & Customer.City _ Let Address = Customer.Address & "," & Customer.PostalCode _ Where Customer.Country = "USA" AndAlso Customer.Region = "OR" _ Select <item> <title><%= Customer.ContactName %></title> <description><%= Desc %></description> <%= GetGeoCode(Address).Descendants %> </item> %> </channel> </rss>
In this query we’re building up the GeoRSS and calling a user defined function called GetGeoCode that accepts the address of the customer and returns the latitude and longitude. Also notice that we’re using the Let keyword in the query in order to create query variables for description and address which are being used as we build the <item> elements. The GetGeoCode function will return an XElement of the location if one was found. The Descendants method on the XElement is then called back up in the query in order to place just the <geo:lat> and <geo:long> nodes into the GeoRSS.
Function GetGeoCode(ByVal address As String) As XElement Dim url = "http://geocoder.us/service/rest/?address=" & Server.UrlEncode(address) Try Dim geo = XElement.Load(url) Return <location> <%= geo.<geo:Point>.<geo:long> %> <%= geo.<geo:Point>.<geo:lat> %> </location> Catch ex As Exception Return <location></location> End Try End Function
Now that we have the GeoRSS we can pass this to Virtual Earth to create our map. For example, we can just create a simple ASP.NET application and save the GeoRSS above to a session variable. The default page contains the JavaScript code we’re going to need to send the GeoRSS to Virtual Earth and a <div> section with the id=”myMap” that identifies the area to place the map on the page. Take a look at the Virtual Earth documentation for more information on the API.
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="NorthwindVirtualEarth._Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Northwind Customers on Virtual Earth</title> <link href="style.css" rel="stylesheet" type="text/css" /> <script type="text/javascript" src="http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=5"> </script> <script type="text/javascript"> var map = null; var layerid=1; function GetMap() { map = new VEMap('myMap'); map.LoadMap(); var l = new VEShapeLayer(); var veLayerSpec = new VEShapeSourceSpecification(VEDataType.GeoRSS, "georss.aspx", l); map.ImportShapeLayerData(veLayerSpec, null); } </script> </head> <body id="body" runat="server" > <form id="form1" runat="server"> <h1>Northwind Customers on Virtual Earth</h1> <div id='myMap' style="position: relative; width: 800px; height: 400px;"> <asp:Label ID="lblStatus" runat="server" Text="No items found" Visible="False"></asp:Label> </div> </form> </body> </html>
The VB code-behind for the Default.aspx page simply checks to see if there were any <item> elements returned from our geoRSS query above and if so, dynamically adds the code to call the GetMap Javascript function in the onload event of the body.
If geoRSS...<item>.Count > 0 Then Session("georss") = geoRSS Me.body.Attributes.Add("onload", String.Format("GetMap()")) Else Me.lblStatus.Visible = True Session("georss") = <rss></rss> End If
Another page called GeoRss.aspx is just a blank page that simply returns the GeoRSS stored in the session variable that the JavaScript calls to get the content.
Public Partial Class GeoRSS Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim georss As XElement = CType(Session("georss"), XElement) Response.ContentType = "text/xml" Response.Write(georss.ToString()) End Sub End Class
The key takeaway here is that in one LINQ statement we queried over multiple data sources, the Northwind Database and the geocoder.us service, to create a single XML document that conformed to the GeoRSS standard and passed that to the Virtual Earth service to generate our map. As you can see, it's pretty easy to create XML, in this case RSS, from multiple sources with LINQ and Visual Basic 9. The code is attached.
And if you're interested in creating dynamic maps from your data using WPF, don't forget to check out Scott Wisniewski's DevCenter featured article Create Dynamic Maps with Visual Basic 9.0 and WPF.