Adding, filtering, sorting rows and deleting rows in DataTable ADO.Net

Introduction:

DataTable is a class.intially we declare datatable by creating object from the datatable class.

DataTable plays important role in ado.net.when retrieving data from the database, you cannot avoid of using datatable. Here I will explain how to handle the data and do different activity in datatable.

How to add columns and rows in DataTable

DataTable dt = new DataTable();             dt.Columns.Add("Sub1");             dt.Columns.Add("sub2");             dt.Columns.Add("sub3");             dt.Columns.Add("Total");             dt.Rows.Add ("66","77","99");             dt.Rows.Add("45", "97", "89");             dt.Rows.Add("56", "56", "25");             dt.Rows.Add("76", "57", "89");             dt.Rows.Add("66", "87", "79");             dt.Rows.Add("86", "67", "89");             dt.AcceptChanges();

How  to modify and delete the rows

dt.Rows[0]["sub1"] = "76";             dt.Rows[0]["sub2"] = "76";             dt.AcceptChanges();             dt.Rows[0].Delete();             dt.AcceptChanges();

Apply Filter in DataTable

Find the row should exact match value

//Find the row should exact match value             DataRow[] dr = dt.Select("sub1= '50'");             DataTable dt1 = new DataTable();             dt1 = dt.Clone();                         foreach (DataRow dr1 in dr)             {                dt1.ImportRow(dr1);              }

Find all rows the value should be greater than 50

//Find all rows the value should be greater than 50             DataRow[] dr = dt.Select("sub1 > '50' and sub2 > '50' and sub3 > '50'");             DataTable dt1 = new DataTable();             dt1 = dt.Clone();                           foreach (DataRow dr1 in dr)             {                 dt1.ImportRow(dr1);                             }

Sorting data in DataTable

Ascending and descending Rows

//Ascending and descending Rows             DataRow[] dr = dt.Select("","sub1 ASC");             //DataRow[] dr = dt.Select("", "sub2 DESC");             DataTable dt1 = new DataTable();             dt1 = dt.Clone();             foreach (DataRow dr1 in dr)             {                 dt1.ImportRow(dr1);             }

Finding MAX,MIN ,AVG and SUM values using aggregate function

object str = dt.Compute("MAX(sub1)","");             object str1 = dt.Compute("SUM(sub1)", ""

I hope this article will helpful. Please post your comments or suggestion about this article..Share the article with your friend using share button