June 16, 2011

Query DataTable using Linq

In this example, I will show how to use Linq to query DataTable. A DataTable is a table of data stored in memory. Since Linq can query data of IEnumerable type, the data rows are declared as as IEnumerable so that Linq can query the data.

I have already setup the web application and have added a page. I will now define the schema of the DataTable and then create it. The code for creating the DataTable is below.

Create DataTable
  1. private DataTable GetDataTable()
  2. {
  3.     DataTable dt = new DataTable();
  5.     //add columns
  6.     dt.Columns.Add("ID", typeof (Int32));
  7.     dt.Columns.Add("Name", typeof(string));
  8.     dt.Columns.Add("DateOfBirth", typeof(DateTime));
  10.     //add rows of data
  11.     dt.Rows.Add(new object[] { 1, "Bugs Bunny", DateTime.Now});
  12.     dt.Rows.Add(new object[] { 2, "Daffy Duck", DateTime.Now });
  13.     dt.Rows.Add(new object[] { 3, "Scooby Doo", DateTime.Now });
  14.     return dt;        
  15. }

As can be seen, columns of type int, string and DateTime are added to the DataTable. Then, rows of data are added. This DataTable is later used for querying using Linq.

As mentioned earlier, to query DataTable using Linq, it has to be of type System.Collections.Generic.IEnumerable . To do this, DataTable’s AsEnumerable method can be used. This method converts the standard DataTable to a DataTable of IEnumerable type. The method for converting and querying the DataTable is below.

Query DataTable
  1. private void QueryDataTable(DataTable dt)
  2. {
  3.     var q = from DataRow row in dt.AsEnumerable()
  4.             select row;
  6.     int col = dt.Columns.Count;//no of columns
  8.     foreach (DataRow row in q)
  9.     {
  10.         for (int i = 0; i < col; i++)
  11.         {
  12.             L1.Text += row[i].ToString() + "&nbsp;";
  13.         }
  14.         L1.Text += "<br/>";
  15.     }
  16. }

At first, I have converted the DataTable to of type IEnumerable and have selected all the rows. Then, I have queried each of the DataRow and retrieved the values for each of the columns. L1 is a Literal control that has been declared on the aspx page.

Please note that, I have simply added the values returned in string variables, ideally, a System.Text.StringBuilder object should be used to achieve this.

The above methods can then be called from Page_Load or other methods as shown below.

Page_Load event
  1. protected void Page_Load(object sender, EventArgs e)
  2. {
  3.     QueryDataTable(GetDataTable());
  4. }


Reference: Shahed Kazi at AspNetify.com