In the previous part of the article, we looked at the new syntax provided by LINQ through exploring some simple scenarios involving an array of Person objects. We extracted certain elements, ordered them and grouped them according to the specific scenario. LINQ, however, is not limited to simple arrays. While arrays are useful for illustrating the basic principles behind LINQ, LINQ is much more powerful and can work with other sources of data, such as XML files or relational databases. In this article, we'll explore, in brief, the usage of LINQ with XML and with SQL databases.
To access a particular type of datasource, something called a provider is used. Providers basically provide a link between LINQ and the appropriate datasource. In the last article, we used LINQ to query an array. The LINQ to Objects provider was involved in this, though knowledge of the provider was, of course, unnecessary to the task. .NET also comes with other providers, such as LINQ to XML and LINQ to SQL, which we'll be using in this article, and it's possible to create providers; a number of third party providers are available.
The Adventure Works Cycles Sample Database
We'll start by examining LINQ to SQL. To do this, we'll use the Adventure Works Cycles sample database. It's available for download at CodePlex:
We'll use the lighter version of the database since the full database is very complex, and we're not ready to add more complexity at this stage. The lighter version is called AdventureWorksLT. Download the appropriate installer and run it. The installer creates the database at X:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksLT_Data.mdf.
Create a new project in Visual Studio using the Console Application template. Then, add the Adventure Works database as a datasource. We're now ready to begin using LINQ to SQL.
Adding LINQ to SQL Classes
Now, we need a way to map the information contained in the relational database to classes that we can use. It's possible to do this manually, but this is unnecessary since Visual Studio will do it for us with just a few clicks. Add a new item to the project. Notice how there is a LINQ to SQL Classes item. This is what we need. Name the item AdventureWorks and then add it.
We're now able to visually create the classes that we need. Upon creating the new item, you should see the Object Relational Designer. Open the Database Explorer and view the list of tables included in the Adventure Works database. We're going to be working with customers. Drag the Customer table over to the main pane of the Object Relational Designer. Visual Studio will automatically create the proper class. You're able to view the source inside of the AdventureWorks.designer.cs file, but we're only interested in the visual representation right now. In the Designer, you should see a box representing the Customer table containing a list of properties that correspond to database fields.
We're almost ready to begin querying the database. The final preparation step is to create an instance of the data context, which will allow us to use the class we just created, along with any other classes we may choose to create later:
AdventureWorksDataContext db = newAdventureWorksDataContext();
Now that we have everything ready, we can begin to use LINQ to query the Adventure Works database. Let's begin with an easy task: querying the database to get every customer:
var customers = from c in db.Customers
select c;
This is made remarkably simple. It's just like querying the array in the last article, a similarity which is, of course, intended.
We now have access to each customer's information. We can access each of the properties defined in the Customer class we created earlier, which correspond to the database table's fields. For example, let's display the first and last names of each customer:
Again, there are no surprises. LINQ behaves the same here with an SQL database as it did in the last article with an array of Person objects.
Since, above, we only use the first and last name of the customer, we could have narrowed down the properties we obtained from the database by using an anonymous type containing only the properties for the first and last names:
var customers = from c in db.Customers
selectnew { c.FirstName, c.LastName };
Of course, if we run the loop again, the exact same results are displayed.
This article is not intended to be a comprehensive, in-depth guide to LINQ to SQL, but we'll look at one final thing before moving on to LINQ to XML: associations. The Adventure Works database contains a table called SalesOrderHeader. This table contains general details about sales orders. (Details about the contents of orders are stored in the SalesOrderDetails table, with one entry per type of product ordered). Notice how the table contains a CustomerID field, which, of course, corresponds to a particular entry in the Customer table. So, each entry in the SalesOrderHeader corresponds to exactly one entry in the Customer table. Given an entry in the SalesOrderHeader table, we should be able to, for example, print the name of the customer involved.
Visual Studio makes this easy. Go to the LINQ to SQL Classes item we created earlier and drag the SalesOrderHeader onto the Object Relational Designer. A graphical representation of the table will appear, and so should an arrow between the Customer table and that SalesOrderHeader table. This arrow represents an association between the two tables. In this case, the association is built around the CustomerID field, which appears in both tables. If you double click the arrow, the Association Editor dialog will appear, showing the properties of the association. Customer is listed as the parent class, since the CustomerID is the primary key of this class, and SalesOrderHeader is listed as the child class. Visual Basic was able to automatically determine this relationship (though it is possible to manually add associations through the Designer's context menu).
Now, let's display the order number of each order, along with the customer's name. This is actually very easy:
As you can see, there is nothing special about the query itself. We simply retrieve every element in the SalesOrderHeader table. There is also nothing complex in the loop where we print out the information. The corresponding entry in the Customer table is added as a property to the SalesOrderHeader object we pulled from the database, and we access the FirstName and LastName properties of the Customer object just as we did earlier. This is a very nice capability, especially considering that all we did was drag and drop a table.
Now let's take a look at LINQ to XML. LINQ to XML gives us a new way to represent and work with XML. Let's examine this through example. Suppose we want to represent a group of people. For each person, we want to represent a name and an age. So, an XML representation of all of this data might look like this:
In order to demonstrate the new API provided by LINQ to XML, let's actually build the above XML document first. You'll need to import the System.Xml.Linq namespace to get started. This namespace provides a variety of classes, of which we'll look at a few. The first is XDocument, which represents an XML document. Our entire document above can be contained within an XDocument class. The second is XDeclaration, which can contain the first line of our document – the XML declaration. The third is XElement, which represents an element. The body of our document can be represented by nesting XElement objects.
When creating, for example, an XElement object, we're able to pass in other child elements as objects. For example, here, we create a single person:
XElement bob = newXElement("person",
newXElement("name", "Bob"),
newXElement("age", "35")
);
Dim bob AsNew XElement("person", _
New XElement("name", "Bob"), _
New XElement("age", "35") _
)
If we print out the object, the proper XML is outputted:
Console.WriteLine(bob);
<person>
<name>Bob</name>
<age>35</age>
</person>
As you can see, building XML using the API is pretty straightforward. Let's now build the entire XML document:
XDocument document = newXDocument(
newXDeclaration("1.0", "utf-8", "yes"),
newXElement("people",
newXElement("person",
newXElement("name", "Bob"),
newXElement("age", "35")),
newXElement("person",
newXElement("name", "Henry"),
newXElement("age", "43")),
newXElement("person",
newXElement("name", "Joe"),
newXElement("age", "22")),
newXElement("person",
newXElement("name", "Chuck"),
newXElement("age", "29"))
)
);
Dim document AsNew XDocument( _
New XDeclaration("1.0", "utf-8", "yes"), _
New XElement("people", _
New XElement("person", _
New XElement("name", "Bob"), _
New XElement("age", "35")), _
New XElement("person", _
New XElement("name", "Henry"), _
New XElement("age", "43")), _
New XElement("person", _
New XElement("name", "Joe"), _
New XElement("age", "22")), _
New XElement("person", _
New XElement("name", "Chuck"), _
New XElement("age", "29")) _
) _
)
The result is actually quite readable and manageable, which is, of course, a very good thing. If we want to, we can save our document for future use:
document.Save("people.xml");
We can now query our document using a combination of the query syntax and the classes that we just went over. Let's retrieve everyone over the age of thirty:
var overThirty = from p in document.Descendants("person")
where (int)p.Element("age") > 30
selectnew
{
Name = (string)p.Element("name"),
Age = (int)p.Element("age")
};
Dim overThirty = From p In document.Descendants("person") _
WhereCInt(p.Element("age")) > 30 _
SelectNewWith { _
.Name = CStr(p.Element("name")), _
.Age = CInt(p.Element("age")) _
}
Unfortunately, we do have to cast each element to the proper type.
We're also able to go further and create a new XML document by querying an old one. For example, suppose wanted to create an entirely new XML document based on the people over the age of thirty. To do this, we just have to drop a query where we want the elements to go:
LINQ is a powerful new tool in the .NET developer's toolbox. It integrates data access directly into .NET languages, providing a very readable query language that can be used on a variety of data sources. In these two articles, we covered objects, XML files and databases, but LINQ supports other data sources, too, that you may want to look into.
/ 4 
ASP.NET Export to Excel and Word using VB.NET and C#
