Published:
26 Dec 2007
|
Abstract
In this article, Richard demonstrates how to create an XML file from a SQL Server 2005 database using LINQ. He provides a detailed explanation of the relevant steps with the help of source code and screenshots captured from Visual Basic 2008 Express Edition. At the end of the article, he also gives a few references where you can learn more regarding the techniques involved with LINQ. |
|
by Richard Dudley
Feedback
|
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days):
39511/
62
|
|
|
Introduction |
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.
|
Setting up the Environment |
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.
|
Getting Started |
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
|
Retrieving Data |
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.
|
Checking our progress |
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.
|
Generating XML |
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.
|
References |
|
Summary |
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.
|
|
|
User Comments
Title:
SQL or XML
Name:
Sunil
Date:
2012-05-21 2:05:53 AM
Comment:
Dear sir, i want to create and access the Database using VB 2008. which one i have to use SQL or XML. Thank u.
|
Title:
hi
Name:
titi
Date:
2008-08-21 8:48:59 AM
Comment:
very nice article
|
|
Product Spotlight
|
|