C# 2008 Programmer's Reference

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 Authors table are now stored in the ds DataSet 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<DataRow> authors =
 from author in ds.Tables[0].AsEnumerable()
 where author.Field<string>('State') == 'CA'
 select author;

The result of the query is of type EnumerableRowCollection<DataRow>. Alternatively, you can also use the var keyword to let the compiler determine the correct data type:

var authors =
 from author in ds.Tables[0].AsEnumerable()
 where author.Field<string>('State') == 'CA'
 select author;

To make use of LINQ to DataSet, ensure that you have a reference to System.Data.DataSetExtensions.dll in your project.

To display the result, you can either bind the result to a DataGridView control using the AsDataView() method:

//---bind to a datagridview control---
dataGridView1.DataSource = authors.AsDataView();

Or, iteratively loop through the result using a foreach loop:

foreach (DataRow row in authors) {
 Console.WriteLine('{0} - {1}, {2}',
  row['au_id'], row['au_fname'], row['au_lname']);
}

To query the authors based on their contract status, use the following query:

EnumerableRowCollection<DataRow> authors =
 from author in ds.Tables[0].AsEnumerable()
 where author.Field<Boolean>('Contract') == true
 select author;

Reshaping Data

Using the new anonymous types feature in C# 3.0, you can define a new type without needing to define a new class. Consider the following statement:

//---query for authors living in CA---
var authors =
 from author in ds.Tables[0].AsEnumerable()
 where author.Field<string>('State') == 'CA'
 select new {
  ID = author.Field<string>('au_id'),
  FirstName = author.Field<string>('au_fname'),
  LastName = author.Field<string>('au_lname')
 };

Here, you select all the authors living in the CA state and at the same time create a new type consisting of three properties: ID, FirstName, and LastName. If you now type the word authors, IntelliSense will show you that authors is of type EnumerableRowCollection<'a> authors, and 'a is an anonymous type containing the three fields (see Figure 14-6).

Figure 14-6

You can now print out the result using a foreach loop:

foreach (var row in authors) {
 Console.WriteLine('{0} - {1}, {2}',
  row.ID, row.FirstName, row.LastName);
}

To databind to a DataGridView control, you first must convert the result of the query to a List object:

//---query for authors living in CA---
var authors =
 (from author in ds.Tables[0].AsEnumerable()
 where author.Field<string>('State') == 'CA'
 select new {
  ID = author.Field<string>('au_id'),
  FirstName = author.Field<string>('au_fname'),
  LastName = author.Field<string>('au_lname')