LINQ to DataSets

There are tons of LINQ 2 SQL and LINQ to object examples out there but not many LINQ 2 DataSet examples. Here is a quick sample that creates a table, adds three rows and queries the rows.

The key parts of the query are casting the table as enumerable using the AsEnumerable() extension and the generic Field<T>() method.

 

   1: static void Main(string[] args)

   2:       {

   3:           // create a table with three columns

   4:           DataTable table = new DataTable();

   5:           table.Columns.Add(new DataColumn("ID", typeof(System.Int32)));

   6:           table.Columns.Add(new DataColumn("ContactName", typeof(System.String)));

   7:           table.Columns.Add(new DataColumn("CompanyName", typeof(System.String)));

   8:  

   9:           // add three rows

  10:           DataRow row = table.NewRow();

  11:           row["ID"] = 1;

  12:           row["ContactName"] = "James Smith";

  13:           row["CompanyName"] = "Petes Hardware";

  14:           table.Rows.Add(row);

  15:  

  16:           DataRow row2 = table.NewRow();

  17:           row2["ID"] = 2;

  18:           row2["ContactName"] = "Jon Jones";

  19:           row2["CompanyName"] = "Rodgers Travel";

  20:           table.Rows.Add(row2);

  21:  

  22:           DataRow row3 = table.NewRow();

  23:           row3["ID"] = 3;

  24:           row3["ContactName"] = "Jeff Jones";

  25:           row3["CompanyName"] = "Rodgers Travel";

  26:           table.Rows.Add(row3);

  27:  

  28:           // create a data set, not needed but it's more common

  29:           DataSet dataSet = new DataSet();

  30:           // add the table

  31:           dataSet.Tables.Add(table);

  32:  

  33:           // get the jones brothers

  34:           var jones = (from r in dataSet.Tables[0].AsEnumerable()

  35:                        where r.Field<string>("ContactName").Contains("Jones")

  36:                        select r).ToList();

  37:  

  38:           // list the results

  39:           foreach (var item in jones)

  40:           {

  41:               Console.WriteLine(String.Format("Found {0} working for {1}",

  42:                   item.Field<string>("ContactName").ToString(),

  43:                   item.Field<string>("CompanyName").ToString()));

  44:           }

  45:  

  46:           Console.Read();

  47:       }