Browse DevX
Sign up for e-mail newsletters from DevX


Sharpening Your Axis with Visual Basic 9 : Page 4

Visual Basic 9 completely eliminates the barrier between the code you write and the XML you're trying to express. Creating, querying, and transforming XML is much more intuitive and productive than ever before.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Working with Relational Data and XML using LINQ
Now that you've seen a foundation for working with XML in Visual Basic, here are some practical examples that show how to use the new features. In typical data-oriented applications developers work with XML as well as with relational data stored in a database. This walkthrough shows some examples of creating and consuming XML in VB9 when working with relational data.

Creating XML from Relational Data
One common requirement in modern applications is to create XML from data stored in a database. Visual Studio 2008 offers many ways to access database data, including the new O/R designer and LINQ to SQL, so you can easily connect and query your databases directly and then use the query results to create XML. To work with LINQ to SQL in Visual Studio 2008, right-click on your project, add a new item, and select the "LINQ to SQL Classes" template. The example in this section uses the Northwind database, so name the model Northwind.dbml.

Figure 10. Object/Relational Designer: The figure shows the O/R Designer in Visual Studio 2008 displaying the Customer class from the Northwind database.
Visual Studio creates a new model and opens the Object Relational Designer (O/R designer), which allows you to drag-and-drop tables from connected databases in the Server Explorer onto the design surface. After you have a connection to the Northwind database in Server Explorer, drag the Customers table onto the design surface. The O/R designer will automatically create a Customer class, and infer its properties from the table's fields. It also sets up a DataContext, called "NorthwindDataContext" in this case, that you use to access the Northwind database (Figure 10). With those in place, you can write a LINQ query that selects all the customers located in the United States and their addresses from the Customers table, and (at the same time) format the results into an XML document containing the information.

Dim db As New NorthwindDataContext Dim customerUSA = _ <customers> <%= From Customer In db.Customers _ Where Customer.Country = "USA" _ Select <customer id= <%= Customer.CustomerID %>> <name> <%= Customer.ContactName %> </name> <address> <%= Customer.Address %> </address> <city> <%= Customer.City %> </city> <zip> <%= Customer.PostalCode %> </zip> </customer> %> </customers> customerUSA.Save("Customers.xml")

Notice that this sample uses one LINQ query to create an XML document. Although this example uses LINQ to SQL to handle selecting the data from the database, you aren't limited to getting the data in that manner. For example, if you already have a typed DataSet containing the customers, then can write the query using LINQ to DataSets instead, which performs a query against the in-memory, client-side DataSet.

Dim NorthwindDataSet As New NorthwindDataSet Dim ta As New _ NorthwindDataSetTableAdapters.CustomerTableAdapter ta.Fill(NorthwindDataSet.Customers) Dim customerUSA = _ <customers> <%= From Customer In NorthwindDataSet.Customers _ Where Customer.Country = "USA" _ Select <customer id= <%= Customer.CustomerID %>> <name> <%= Customer.ContactName %> </name> <address> <%= Customer.Address %> </address> <city> <%= Customer.City %> </city> <zip> <%= Customer.PostalCode %> </zip> </customer> %> </customers> customerUSA.Save("Customers.xml")

Using either query, the resulting XML document will look something like this (only the first three customers are shown here):

<?xml version="1.0" encoding="utf-8"?> <customers> <customer id="GREAL"> <name>Howard Snyder</name> <address>2732 Baker Blvd.</address> <city>Eugene</city> <zip>97403</zip> </customer> <customer id="HUNGC"> <name>Yoshi Latimer</name> <address>City Center Plaza 516 Main St.</address> <city>Elgin</city> <zip>97827</zip> </customer> <customer id="LAZYK"> <name>John Steel</name> <address>12 Orchestra Terrace</address> <city>Walla Walla</city> <zip>99362</zip> </customer>

Figure 11. More Northwind Tables: The O/R Designer infers object associations from the database relationships.
. . . </customers>
As you can see it's extremely easy to create XML from your relational data using XML literals and embedded expressions. And because you can easily nest embedded expressions, you can create much more complex documents in a single query. For example, what if you also want to include orders and order details for these customers? No problem. You can add Orders, Order Details and Products tables to the Northwind data model created by the O/R designer and then easily include those items in your query (see Figure 11).

When you add those new tables to the model, the O/R designer automatically sets up associations between the classes it creates by reading the database relations. Then you can write a query with nested embedded expressions to create more complex XML shown in Listing 1.

The code walks down the hierarchy of Customers, Orders, and then Order Details, creating XElements as it goes. Notice that, even though this XML document is
Figure 12. Complex Query: Here's the output of Listing 1, shown in Internet Explorer.
complex, LINQ to XML still lets you take a natural top-down approach to writing the document-generation code. Also note that the aggregate query that calculates the order totals, which completely avoids any For loops. The query in Listing 1 outputs the document shown in Figure 12 (shortened for clarity and displayed in Internet Explorer).

You can create XML from multiple data sources as well, not just a single data source such as the database used in the examples so far. Because most modern systems interact with each other in some form of XML, the possibilities are endless. You can use LINQ to XML to easily create SOAP, XAML, HTML, and RSS.

For example, suppose you wanted to display all the customers on a map generated by Microsoft Virtual Earth? Virtual Earth allows you to pass it an RSS document containing items that specify their latitude and longitude, which it uses to create a map showing multiple locations. You can pass the data in a couple of different formats; one is the GeoRSS standard. All you would need to do is create the XML input file by obtaining the latitude and longitude from the addresses you have in your customers table and then pass this GeoRSS to Virtual Earth.

For this example you can grab the latitude and longitude of the customers in the United States using the service at http://geocoder.us. This service can return a set of coordinates from any U.S. address in a variety of formats, including REST-ful RDF. You can use this service in your LINQ query to create the GeoRSS from the Customers table. The first thing to do is to import the geo namespace at the top of your code file, because you'll be using it to return the location information in the geo namespace from the XML returned from the geocoder.us service:

Imports <xmlns:geo= "http://www.w3.org/2003/01/geo/wgs84_pos#">

Now you can write a query to create the GeoRSS containing the customer data. Because the Northwind database contains mostly fictitious addresses you can change the addresses to real locations—or you can select just the customers living in Oregon (OR) because some of those entries have valid addresses.

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 = region _ 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 an address, and returns the latitude and longitude corresponding to that address. Also notice that the query uses the Let keyword to create query variables for description and address, which are later used to build the <item> elements. The GetGeoCode function returns an XElement containing the location if one was found. The XElement's Descendants method is then called to place just the <geo:lat> and <geo:long> nodes into the GeoRSS. Here's the GetGeoCode method implementation:

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 you can pass the GeoRSS to Virtual Earth to create a map. For this example you can just create a simple ASP.NET application and save the GeoRSS above to a session variable. The default.aspx page (shown in Listing 2) contains the JavaScript code you need to send the GeoRSS to Virtual Earth, and a <div id="myMap"> section that identifies the area to display the map on the page. Take a look at the Virtual Earth documentation for more information on the API.

The code behind for the default.aspx page simply checks whether the geoRSS query described above returned any <item> elements. If so, it dynamically adds the code to call the GetMap JavaScript function in the body onload event.

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

The project contains another page called GeoRss.aspx, which simply returns the GeoRSS stored in the session variable that the JavaScript calls to get the content. It's important that you set the ContentType property on the Response object to text/xml.

Figure 13. Sample Map Showing Customer Locations: By formatting the results of the LINQ to XML query as GeoRSS, and passing that to Virtual Earth, you can display the locations of customers on a map.

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

You can see the results in Figure 13.

The key takeaway here is that in one LINQ statement, you queried 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 the map.

Thanks for your registration, follow us on our social networks to keep up-to-date