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

foreach (var c in foundContacts) {

Console.WriteLine("{0} - {1} {2}", c.id, c.FirstName, c.LastName);

}

The output looks like this:

1 - John Chen

2 - Maryann Chen

However, you can modify your query such that the result can be shaped into a custom class instead of type Contact. To do so, modify the query as the following highlighted code shows:

var foundContacts =

from c in Contacts

where c.LastName == "Chen"

select new {

id = c.id,

Name = c.FirstName + " " + c.LastName

};

Here, you reshape the result using the anonymous type feature new in C# 3.0. Notice that you now have to use the varkeyword to let the compiler automatically infer the type of foundContacts. Because the result is an anonymous type that you are defining, the following generates an error:

IEnumerable foundContacts =

from c in Contacts

where c.LastName == "Chen"

select new {

id = c.id,

Name = c.FirstName + " " + c.LastName

};

To print the results, use the foreachloop as usual:

foreach (var c in foundContacts) {

Console.WriteLine("{0} - {1}", c.id, c.Name);

}

Figure 14-4 shows that IntelliSense automatically knows that the result is an anonymous type with two fields — idand Name.

Figure 14-4

Besides manipulating data in memory, LINQ can also be used to query data stored in structures like DataSets and DataTables.

ADO.NET is the data access technology in .NET that allows you to manipulate data sources such as databases. If you are familiar with ADO.NET, you are familiar with the DataSet object, which represents an in-memory cache of data. Using LINQ to DataSet, you can use LINQ queries to access data stored in a DataSet object. Figure 14-5 shows the relationships between LINQ to DataSet and ADO.NET 2.0.

Figure 14-5

Notice that LINQ to DataSet is built on top of ADO.NET 2.0. You can continue using your ADO.NET code to access data stored in a DataSet, but using LINQ to DataSet will greatly simplify your tasks.

The best way to understand LINQ to DataSet is to look at an example and see how it can simplify your coding. The following code shows how, using ADO.NET, you can connect to the pubssample database, retrieve all the authors from the Authorstable, and then print their IDs and names to the output window:

Because SQL Server 2005 Express does not come with any sample databases, you need to install the pubsdatabase used in this section yourself.

You can install the pubs and Northwind databases by downloading the installation scripts at http://microsoft.com/downloads. Search for: "Northwind and pubs Sample Databases for SQL Server 2000."

Once the scripts are installed on your system, go to the Visual Studio 2008 Command Prompt (Start→Programs→Microsoft Visual Studio 2008→Visual Studio Tools→Visual Studio 2008 Command Prompt) and change to the directory containing your installation scripts. Type in the following to install the two databases:

C:\SQL Server 2000 Sample Databases>sqlcmd -S .\SQLEXPRESS -i instpubs.sql

C:\SQL Server 2000 Sample Databases>sqlcmd -S .\SQLEXPRESS -i instnwnd.sql

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace LINQtoDataset {

public partial class Form1 : Form {

public Form1() {

InitializeComponent();

}

private void Form1_Load(object sender, EventArgs e) {

SqlConnection conn;

SqlCommand comm;

SqlDataAdapter adapter;

DataSet ds = new DataSet();

//---loads the Authors table into the dataset---

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

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

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

adapter = new SqlDataAdapter(comm);

adapter.Fill(ds);

foreach (DataRow row in ds.Tables[0].Rows) {

Console.WriteLine("{0} - {1} {2}",

row["au_id"], row["au_fname"], row["au_lname"]);

}

}

}

}

Observe that all the rows in the Authorstable are now stored in the dsDataSet object (in ds.Tables[0]). To print only those authors living in CA, you would need to write the code to do the filtering:

foreach (DataRow row in ds.Tables[0].Rows) {

if (row["state"].ToString() == "CA") {

Console.WriteLine("{0} - {1} {2}",

row["au_id"], row["au_fname"], row["au_lname"]);

}

}

Using LINQ to DataSet, you can write a query that only retrieves authors living in CA:

//---query for authors living in CA---

EnumerableRowCollection authors =

from author in ds.Tables[0].AsEnumerable()

where author.Field("State") == "CA"

select author;

The result of the query is of type EnumerableRowCollection. Alternatively, you can also use the varkeyword to let the compiler determine the correct data type:

var authors =

from author in ds.Tables[0].AsEnumerable()

where author.Field("State") == "CA"

select author;