Introduction
Mostly we deal with the Database to fetch data and store in some variable for further use in application. We can use DataTable class which is going to store data. We will discuss some additional features of DataTable and below is the agenda of the article.
  1. Methods, Properties in DataTable
  2. Filtering in DataTable
  3. JOIN in DataTable
  4. Sorting in DataTable
  5. Copying specific column from a Datatable
  6. Edit, Delete DataTable rows
  7. Difference between Add() and ImportRow()
  8. Difference between clone() and copy()
  9. Miscellaneous(group by, serialization etc..)
What is DataTable?

This class represents in-memory data to store in rows and columns. It is present in System.Data namespace. You can add rows, columns, edit, and filter programmatically.

Methods, Properties in DataTable
DataTable contains a couple of properties and functions which we can use to achieve more. Here we will discuss some important properties and functions.

Properties
  1. DataTable dtObj = new DataTable("tableName1"); // Set table name
  2. Console.WriteLine(dtObj.TableName); //tableName1
  4. foreach(DataRow row in dtObj.Rows)
  5. {
  6. foreach(DataColumn column in dtObj.Columns)
  7. {
  8. Console.WriteLine(row[column]);
  9. }
  10. }
  12. var dv = dtObj.DefaultView;
  13. dv.Sort = "StartDate";
  15. dtObj = dv.ToTable();
Functions
  1. DataTable dtObj1 = new DataTable("tableName1"); // Set table name
  2. DataTable dtObj2 = new DataTable("tableName2"); // Set table name

  4. //merging first data table into second data table
  5. dtObj2.Merge(dtObj1);
  6. dtObj2.AcceptChanges();
  8. string filePath = "D:\\SelfPractice\\\\Information.xml";
  9. DataSet ds = new DataSet();
  10. ds.ReadXml(filePath); // Read xml value as DataSet object
  11. ds.ReadXml(filePath); // Write data into XML file
More on properties and function, please visit here.
Filter in DataTable

Sometimes we need to filter data from DataTable. For instance, we have a table called Players, requirement is to get all employees of the country India. Below code is to create custom DataTable with demo data.
  1. DataTable playerTable = new DataTable("Players");
  2. playerTable.Columns.Add(new DataColumn("ID", typeof(int)));
  3. playerTable.Columns.Add(new DataColumn("Name", typeof(string)));
  4. playerTable.Columns.Add(new DataColumn("DOJ", typeof(DateTime)));
  5. playerTable.Columns.Add(new DataColumn("Country", typeof(string)));
  6. playerTable.Columns.Add(new DataColumn("IsActive", typeof(bool)));
  8. playerTable.Rows.Add(1, "Sourav Singh", DateTime.Now, "India", true);
  9. playerTable.Rows.Add(2, "Simon David", DateTime.Now, "England", true);
  10. playerTable.Rows.Add(3, "Chris Brown", DateTime.Now, "WestIndies", true);
  11. playerTable.Rows.Add(4, "Manas Nayak", DateTime.Now, "India", true);
  12. playerTable.Rows.Add(5, "John Lee", DateTime.Now, "England", true);
  13. playerTable.Rows.Add(6, "Salim Desmukh", DateTime.Now, "India", false);
  14. playerTable.Rows.Add(7, "N Patil", DateTime.Now, "India", true);
  15. playerTable.Rows.Add(8, "Ricky Samson", DateTime.Now, "England", false);
We can accomplish filter in the following ways.

Option 1 Using Normal way
  1. foreach (DataRow row in playerTable.Rows)
  2. {
  3. if (row["Country"].ToString() == "India")
  4. {
  5. Console.WriteLine(row["Name"]);
  6. }
  7. }
Option 2 Using Linq with Rows properties
  1. var tempResults = (from DataRow dr in playerTable.Rows
  2. where (string)dr["Country"] == "India"
  3. select dr);

  5. foreach (DataRow row in tempResults)
  6. {
  7. Console.WriteLine("{0}, {1}", row[0], row[1]);
  8. }
Option 3 Using Select() method
  1. DataRow[] result = playerTable.Select("ID >= 2 AND IsActive = true");
  2. foreach (DataRow row in result)
  3. {
  4. Console.WriteLine("{0}, {1}", row[0], row[1]);
  5. }
Option 4 Using Linq

To implement LINQ in DataTable, you need add a reference to the System.Data.DataSetExtensions for AsEnumerable() method.
  1. var results2 = from myRow in playerTable.AsEnumerable()
  2. where myRow.Field<string>("Country") == "India"
  3. select myRow;
  5. foreach (DataRow dr in results2)
  6. {
  7. Console.WriteLine(dr["Name"]);
  8. }
Join in DataTable

To implement, we create two DataTables. One is "Emp" which contains two columns called EmpId and EmpName and second table is "EmpGrade" which contains two columns EmpId and Grade. And column EmpId of two tables are referring to each other. Here is the code to create DataTables.
  1. DataTable dt = new DataTable();
  2. DataRow dr = null;
  3. dt.TableName = "Emp";
  5. dt.Columns.Add("EmpId", typeof(int));
  6. dt.Columns.Add("EmpName", typeof(string));
  8. dr = dt.NewRow();
  9. dr["EmpId"] = 1;
  10. dr["EmpName"] = "Manas";
  11. dt.Rows.Add(dr);
  13. DataRow dr1 = null;
  14. dr1 = dt.NewRow();
  15. dr1["EmpId"] = 2;
  16. dr1["EmpName"] = "Prakas";
  17. dt.Rows.Add(dr1);
  19. DataRow dr2 = null;
  20. dr2 = dt.NewRow();
  21. dr2["EmpId"] = 3;
  22. dr2["EmpName"] = "Akas";
  23. dt.Rows.Add(dr2);
  25. DataTable dt2 = new DataTable();
  27. dt2.TableName = "EmpGrade";
  28. dt2.Columns.Add("EmpId", typeof(int));
  29. dt2.Columns.Add("Grade", typeof(int));
  31. DataRow drgrade = null;
  32. drgrade = dt2.NewRow();
  33. drgrade["EmpId"] = 1;
  34. drgrade["Grade"] = 3;
  35. dt2.Rows.Add(drgrade);
  37. DataRow drgrade2 = null;
  38. drgrade2 = dt2.NewRow();
  39. drgrade2["EmpId"] = 3;
  40. drgrade2["Grade"] = 2;
  41. dt2.Rows.Add(drgrade2);
Following code implements Inner Join.
  1. var JoinResult = (from p in dt.AsEnumerable()
  2. join t in dt2.AsEnumerable()
  3. on p.Field<string>("EmpId") equals t.Field<string>("EmpId")
  4. select new
  5. {
  6. EmpId = p.Field<int>("EmpId"),
  7. EmpName = p.Field<sting>("EmpName"),
  8. Grade = t.Field<int>("Grade")
  9. }).ToList();
  11. // Result:
  12. // -----------------------
  13. // EmpId EmpName Grade
  14. // 1 Manas 3
  15. // 3 Akas 2
Copying specific column data from a DataTable

Sometimes we get a requirement to copy specific column data of a DataTable to another DataTable. For instance, in Players table we have two columns ID, Name and we need another DataTable with only Name column data.
  1. DataTable playerTables = new DataTable("Players");
  2. playerTables.Columns.Add(new DataColumn("ID", typeof(int)));
  3. playerTables.Columns.Add(new DataColumn("Name", typeof(string)));
  5. playerTables.Rows.Add(1, "Kalia");
  6. playerTables.Rows.Add(3, "Chris");
  7. playerTables.Rows.Add(2, "Rima");
  9. DataTable playerTables3 = playerTables.Copy();
  10. playerTables3.Columns.Remove("ID");
  12. DataView view = new DataView(playerTables);
  13. DataTable playerTables4 = view.ToTable(false, "Name");
Sorting in DataTable
Sometimes we need records in an ordered way. For instance, we need records based on player name. Here is the implementation:
  1. DataTable playerTables = new DataTable("Players");
  2. playerTables.Columns.Add(new DataColumn("ID", typeof(int)));
  3. playerTables.Columns.Add(new DataColumn("Name", typeof(string)));
  5. playerTables.Rows.Add(1, "Kalia");
  6. playerTables.Rows.Add(3, "Chris");
  7. playerTables.Rows.Add(2, "Rima");
  9. // Option1
  10. DataView dtView = playerTables.DefaultView;
  11. dtView.Sort = "Name desc";
  12. playerTables = dtView.ToTable();
  14. //Option2
  15. DataRow[] foundRows = playerTables.Select().OrderBy(u => u["Name"]).ToArray();
  16. DataTable dtTemp = foundRows.CopyToDataTable();
Reverse
It means records will be reversed in DataTable object.
  1. var query = (from rec in playerTables.AsEnumerable()
  2. orderby rec.Field("Name")
  3. select rec).Reverse();
  5. // If you are dealing with DataSet table then
  6. var reversedTables = _ds.Tables.Cast<DataTable>().Reverse();
  7. foreach(DataTable table in reversedTables)
  8. {
  9. // ...
  10. }
Edit, Delete DataTable rows
Sometimes we need to update DataTable records like to update the name whose ID is 2.
Edit
  1. DataRow[] customerRow = dtObj.Select("ID = 2");
  2. customerRow[0]["Name"] = "Manas1";
Delete
  1. // When you are using DataSet
  2. dataSet.Tables["Players"].Rows[0].Delete();

  4. // Delete the record whose ID is 1
  5. playerTables.Select("ID == 1").Delete();
  7. playerTables.Rows.Cast<DataRow>().Where(r => r.ItemArray[0] == "filterValue").ToList().ForEach(r => r.Delete());
  9. // Using DataView object to elete the records.
  10. DataView view = new DataView(ds.Tables["MyTable"]);
  11. view.RowFilter = "ID = 1";
  12. // Delete these rows.
  13. foreach (DataRowView row in view)
  14. {
  15. row.Delete();
  16. }

  18. // Delete records in normal way..
  19. for(int i = playerTables.Rows.Count-1; i >= 0; i--)
  20. {
  21. DataRow dr = playerTables.Rows[i];
  22. if (dr["ID"] == "1")
  23. dr.Delete();
  24. }
Difference between Add() and ImportRow()

The Add() creates a new row with specified values and adds it to DataTableCollection. The ImportRow() method of DataTable copies a row into a DataTable with all of the property settings and data of the row. It actually calls NewRow method on destination DataTable with current table schema and sets DataRowState to Added. If you want to make a new row in table you can use Row.Add() but if you want to import row from another table you can use ImportRow(). DataTable. ImportRow method is good when we use it for huge amounts of data.
  1. DataTable dt1 = new DataTable();
  2. DataRow dr1 = dt1.NewRow();
  3. DataTable dt2 = new DataTable();
  4. dt2.Rows.Add(dr1); // will give error
  5. dt2.ImportRow(dr1); // it works perfectly
Difference between clone() and copy()

There are two functions available to copy data from one table to another table, these are Clone() and Copy().

DataTable.Copy() returns a DataTable with the structure and data of the DataTable.
  1. //Creating another DataTable to copy
  2. DataTable dtCopy = new DataTable();
  3. dt.TableName = "CopyTable";
  4. dtCopy = dt.Copy();
DataTable.Clone() only returns the structure of the DataTable, not the rows or data of the DataTable.
  1. //Creating another DataTable to clone
  2. DataTable dtClone = new DataTable();
  3. dt.TableName = "CloneTable";
  4. dtClone = dt.Clone();
Miscellaneous
Below code defines how to implement GroupBy in DataTable.
  1. DataTable dtEmp = new DataTable();
  2. dtEmp.Columns.Add("EmpID", typeof(int));
  3. dtEmp.Columns.Add("EmpName", typeof(string));
  4. dtEmp.Columns.Add("Sal", typeof(decimal));
  5. dtEmp.Columns.Add("DeptNo", typeof(int));
  7. dtEmp.Rows.Add(1, "Manas", 10000, 1);
  8. dtEmp.Rows.Add(2, "Himesh", 20000, 2);
  9. dtEmp.Rows.Add(3, "Debu", 30000, 2);
  10. dtEmp.Rows.Add(4, "Amulya", 5000, 3);
  12. var GroupBy = dtEmp.AsEnumerable().GroupBy(e=>e.Field<int>("DeptNo")).Select(d => new{ d.Key, Count = d.Count() });
  14. var result = dtEmp.AsEnumerable().Where(e => e.Field<decimal>("Sal") == dtEmp.AsEnumerable().Max(emp => emp.Field<decimal>("Sal")));
Serialization

We can store state object either in XML format or json or binary format. Here we will discuss for json and xml format, for xml we can use ReadXML/WriteXML() to convert DataTable to xml format(discussed above). For json serialization we need to use third party dll JSON.NET, follow below code.
  1. string json = JsonConvert.SerializeObject(dataSet, Formatting.Indented);
  3. Console.WriteLine(json);
  1. {
  2. "Table1": [{
  3. "EmpID": 0,
  4. "EmpName": "item 0",
  5. "Sal": 10000,
  6. "DeptNo": "1",
  7. }, {
  8. "EmpID": 2,
  9. "EmpName": "Himesh",
  10. "Sal": 20000,
  11. "DeptNo": 2,
  12. }]
  13. }
Conclusion
We discussed about DataTable in C# and its advanced features like filtering, sorting, copying, deleting DataTable records. So as per your requirement you can check the code implementation.

Hope this helps.