In today's business, it's very common to exchange data with business partners.  Most of the information needing to be exchanged resides in a database of some sort.  Because of its flexibility and increasing ease of handling, XML has been utilized more and more as the method of information exchange.

In this article, we'll pretend we are AdventureWorks Cycles, a manufacturer and retailer of bicycles.  To assist the stores which carry our products, we provide them with an updated product catalog on a quarterly basis.  They import this catalog into their databases so their product information is up to date.  Our task in this article is to create an XML file from the AdventureWorks database so it can be securely transferred via FTP to our logistics provider.

We'll use Visual Basic 2008 Express Edition for this example, and we'll create a simple console application which can be run as a scheduled task. Remember this is just example coding, not production level coding, so be sure to implement best practices before implementing anything you see here.

Before we get started, you need to make sure you have Microsoft Visual Basic 2008 Express Edition and Microsoft SQL Server 2005 Express Edition installed.  You'll also need to download and install the AdventureWorks sample database, using the link in Reference 1 below.  I'm assuming you have a small amount of experience with the VBX 2008.

Open Visual Basic 2008 Express, and create a new project.  Choose a Console Application, and name it whatever you want—I've named mine ProductstoXml.

Figure 1

Our project will open with Module1.vb.  In order to retrieve data from our database, we need to add a LINQ to SQL class, which will provide us with the methods for easy data access.  Right-click on the project, choose Add >> New Item.  Select LINQ to SQL Classes, name it Products.dbml, and click OK.  We'll now have a split screen with the Object Relational Designer in the left pane and a method creator on the right.

Figure 2

Click the Database Explorer link in the Object Relational Designer.  The Database Explorer toolbar should open.  Add a Data Connection. Choose Microsoft SQL Server Database File and click OK.

Figure 3

On the next screen, we need to Browse to the database, which for SQL Server Express 2005 is installed by default at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.  Choose Adventureworks_Data.mdf, and click Open, then OK.

Figure 4

The connection to Adventureworks_Data.mdf will now be listed in the Database Explorer toolbar.  Expand the database, and then the Views.  To finish creating the class, locate the vProductModelCatalogDescription view and drag and drop it onto the Object Relational Designer, as shown. If you're prompted to copy the data file to your project, click No.  Save this file, and switch back to Module1.vb.

Figure 5

So far we've laid the foundation for our project, and now it's time to fetch some data.  Believe it or not, the hard part is over.

If we were using ADO.NET, we'd have to create a Database object, or a connection of some form.  In LINQ, we create a DataContext instead.  The properties for a DataContext are set from the database connection and the views we added above.  The data context is automatically named with the name of the DBML file (in my case, Products), with “Data Context” at the end.  In my case, this would give me a name of ProductsDataContext.

Working back in Module1.vb, we instantiate a data context as shown below.  As you type the following line of code, you'll see we have full Intellisense with our data context.

Listing 1

Sub Main()
  Dim _db As New ProductsDataContext
End Sub

Figure 6

So far, so good.  Now, let's get some data.  Again, as you type the new line of code, you'll see we have full Intellisense.

Listing 2

Sub Main()
  Dim _db As New ProductsDataContext
  Dim _products = From p In _db.vProductModelCatalogDescriptions Select p
End Sub

Figure 7

The first difference you see is that we don't declare a type for _products.  We declare _products as an “anonymous type”, which is a new addition to the Visual Basic language in .NET 3.5.  Anonymous types are not variants with no known type, but are instead objects whose types are inferred automatically by the compiler.  As we develop, the compiler is working in the background to update these objects, and make the methods and properties available to us via Intellisense.  If you really, really wanted to declare a type, you could use the following line of code instead:

Listing 3

Dim _products As IQueryable(Of vProductModelCatalogDescription) _
= From p In _db.vProductModelCatalogDescriptions Select p

_products is now an IQueryable object of type vProductModelCatalogDescription.  If you try this, you'll see that once again, we have full Intellisense.  LINQ objects are first class objects, whose exact type, methods and properties are determined automatically by the compiler.

The second difference you'll notice is how similar to SQL the LINQ syntax is.  This new syntax is very powerful, and yet very simple.

So far, we've modeled a data context from a view, and written two lines of code.  Can it really be this easy?  Let's add a couple additional lines of code to see.  We'll loop through our entire collection, and print the name of the product to the console window.  The ReadLine method will wait until we press a key to execute, so this will hold the window open long enough for us to see if anything printed out.

Listing 4

Sub Main()
Dim _db As New ProductsDataContext
Dim _products = From p In _db.vProductModelCatalogDescriptions Select p
For Each _p In _products
Console.WriteLine(_p.Name)
Next
Console.ReadLine()
End Sub

Press F5 to start the project, and a console window should open up with the product names printed out.  Press any key to close the window.  It really was that simple to query the data we need.

As easy as LINQ has made data access, it has made working with XML even easier.  With LINQ, you don't need to create complex classes in order to serialize them.  Instead, XML is a native type in Visual Basic, so XML elements can be stated explicitly, intermixed with output commands to insert the correct values.

To create our XML document, we'll still need to instantiate an XML document, then add our elements, starting with the outermost (root) element.  Change Sub Main() to read as below:

Listing 5

Sub Main()
Dim _db As New ProductsDataContext
Dim _products = From p In _db.vProductModelCatalogDescriptions Select p
Dim _xml As New Xml.Linq.XDocument
Dim _xp As New XElement("Products")
_xml.Add(_xp)
_xml.Save("ProductCatalog.xml")
End Sub

Press F5 to run this code - it should execute just fine.  You can see the sample output in your bin/Debug folder (if necessary, remember to click the Show All Files button in the Solution Explorer).  We should have an XML declaration, and an empty Products element.

To add the details for each product, we need to loop through our collection of products and insert the proper values into each element.  We'll use another of the new features in VB9, and explicitly state XML elements in our code.  Change Sub Main() to read as below.  As you type the XML elements, notice that Visual Studio adds the closing element automatically.  Feel free to add more elements if you want to.   You can refer to the DBML file we created above for a list of available fields.  If you've ever used inline ASP.NET code on a web form, the shorthand <%= %> notation will be familiar to you.  Essentially, this shorthand means to insert (or print) the value contained inside the brackets.

Listing 6

Sub Main()
Dim _db As New ProductsDataContext
Dim _products = From p In _db.vProductModelCatalogDescriptions Select p
Dim _xml As New Xml.Linq.XDocument
Dim _xp As New XElement("Products")
For Each _p In _products
_xp.Add(<Product>
<ProductId><%= _p.ProductModelID %></ProductId>
<Name><%= _p.Name %></Name>
<Wheel><%= _p.Wheel %></Wheel>
<BikeFrame><%= _p.BikeFrame %></BikeFrame>
</Product>)
Next
_xml.Add(_xp)
_xml.Save("ProductCatalog.xml")
End Sub

Once you've made these changes, press F5, and check the output file after execution completes.  You should see all the products listed, with all the elements you specified.  If your example is not working properly, double check your syntax and make sure you entered it as show above.  The syntax inside the for each loop will seem strange at first, but keep in mind that XML is a native type in VB 9, so XML elements can be directly specified in our code.

AdventureWorks for SQL Server 2005 SP2a

Visual Basic LINQ Hands On Labs for Visual Studio 2008

"How do I?" with LINQ Videos

In .NET 3.5 and Visual Basic 9, Microsoft added a number of features to make coding quicker and clearer.  In addition to LINQ, these features include anonymous types and the inclusion of XML as a native type.  With only 14 lines of code, we were able to extract data from a SQL Server database and convert it into an XML document.

There is so much more to LINQ than what we've covered here. This example should provide a good foundation for exploring LINQ even more.  There are a couple of references listed below with a wealth of information about LINQ.