Sunday, February 8, 2009

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:       }
 

No comments: