Wei-Meng Lee - C# 2008 Programmer's Reference

With the typed DataSet created, rewrite the query as follows:

SqlConnection conn;

SqlCommand comm;

SqlDataAdapter adapter;

TypedCustomersDataSet ds = new TypedCustomersDataSet();

conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;" +

"Initial Catalog=Northwind;Integrated Security=True");

comm = new SqlCommand("SELECT * FROM Customers", conn);

adapter = new SqlDataAdapter(comm);

adapter.Fill(ds, "Customers");

var query1 =

(from customer in ds.Customers

where customer.Country == "USA"

select new {

customer.CustomerID,

customer.CompanyName,

customer.ContactName,

customer.ContactTitle

}).ToList();

dataGridView1.DataSource = query1;

Notice that the query is now much clearer because there is no need to use the Field()extension method. Figure 14-12 shows the output.

Figure 14-12

Using the same query used in the previous section, let's modify it so that you can retrieve all customers living in the WA region:

var query1 =

(from customer in ds.Customers

where customer.Region=="WA"

select new {

customer.CustomerID,

customer.CompanyName,

customer.ContactName,

customer.ContactTitle

}).ToList();

When you execute the query, the program raises an exception. That's because some of the rows in the Customerstable have nullvalues for the Regionfield. To prevent this from happening, you need to use the IsNull()method to check for null values, like this:

var query1 =

(from customer in ds.Customers

where !customer.IsNull("Region") && customer.Region == "WA"

select new {

customer.CustomerID,

customer.CompanyName,

customer.ContactName,

customer.ContactTitle

}).ToList();

Notice that LINQ uses short-circuiting when evaluating the conditions in the wherestatement, so the IsNull()method must be placed before other conditions.

Interestingly, the Field()extension method handles nullable types, so you do not have to explicitly check for null values if you are not using typed DataSets.

The result of a LINQ query can be saved into a DataTableobject by using the CopyToDataTable()method. The CopyToDataTable()method takes the result of a query and copies the data into a DataTable, which can then be used for data binding.

The following example shows a LINQ query using typed DataSet with the result copied to a DataTableobject and then bound to a DataGridView control:

var query1 =

from customer in ds.Customers

where customer.Country == "USA"

select customer;

DataTable USACustomers = query1.CopyToDataTable();

dataGridView1.DataSource = USACustomers;

Note that the CopyToDataTable()method only operates on an IEnumerablesource where the generic parameter Tis of type DataRow. Hence, it does not work for queries that project anonymous types or queries that perform table joins.

Also very cool is LINQ's capability to manipulate XML documents. In the past, you had to use XPath or XQuery whenever you need to manipulate XML documents. Using LINQ to XML, you can now query XML trees and documents using the familiar LINQ syntax.

To use the LINQ to XML, you must add a reference to the System.Xml.Linq.dllin your project and also import the System.Xml.Linqnamespace.

To create an XML document tree in memory, use the XDocumentobject, which represents an XML document. To create an XML element, use the XElementclass; for attributes, use the XAttributeclass. The following code shows how to build an XML document using these objects:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Xml.Linq;

namespace LINQtoXML {

class Program {

static void Main(string[] args) {

XDocument library = new XDocument(

new XElement("Library",

new XElement("Book",

new XAttribute("published", "NYP"),

new XElement("Title", "C# 2008 Programmers' Reference"),

new XElement("Publisher", "Wrox")

),

new XElement("Book",

new XAttribute("published", "Published"),

new XElement("Title", "Professional Windows Vista " +

"Gadgets Programming"),

new XElement("Publisher", "Wrox")

),

new XElement("Book",

new XAttribute("published", "Published"),

new XElement("Title", "ASP.NET 2.0 - A Developer's " +

"Notebook"),

new XElement("Publisher", "O'Reilly")

),

new XElement("Book",

new XAttribute("published", "Published"),

new XElement("Title", ".NET 2.0 Networking Projects"),

new XElement("Publisher", "Apress")

),

new XElement("Book",

new XAttribute("published", "Published"),

new XElement("Title", "Windows XP Unwired"),

new XElement("Publisher", "O'Reilly")