C# 2008 Programmer's Reference

In the Server Explorer window, open a connection to the database you want to use (in this case it is the Northwind database) and drag and drop the Customers table onto the design surface of TypedCustomersDataSet.xsd (see Figure 14-11). Save the TypedCustomersDataSet.xsd file.

Figure 14-11

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

Detecting Null Fields

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 Customers table have null values for the Region field. 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 where statement, 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.

Saving the Result of a Query to a DataTable

The result of a LINQ query can be saved into a DataTable object 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 DataTable object 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 IEnumerable<T> source where the generic parameter T is of type DataRow. Hence, it does not work for queries that project anonymous types or queries that perform table joins.

LINQ to XML

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.dll in your project and also import the System.Xml.Linq namespace.