What's New in C# 3.0? Part 2 : Page 2

LINQ to Dataset
Besides manipulating data in memory, LINQ can also be used to query data stored in structures like datasets and datatables. The following example (in C#) shows how the Authors table within the pubs database is loaded onto a Dataset object and then queried using LINQ:

            SqlConnection conn;
            SqlCommand comm;
            SqlDataAdapter adapter;
            DataSet ds = new DataSet();

            //---load the Employees 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);
            
            //---query for authors living in CA---
            var authors = from author in ds.Tables[0].AsEnumerable()
                          where author.Field("State") == "CA"
                          select author;

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"]);
            }

If you want to query the authors based on their contract status, use the following query:

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

The earlier section mentioned the C# 3.0's new anonymous types feature. Using this feature, you can define a new type without needing to define a new class. Here's one good use of anonymous types. Consider the following statement:

Figure 1. An Anonymous Type: authors is an anonymous type with three fields.

        //---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 state of CA while simultaneously creating 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 1).

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

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

Previous Page

Next Page