C#

More On C# DataTable

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
  • Columns Gets the collection of columns that belong to this table
  • DefaultView Gets a customized view of the table that may include a filtered view, or a cursor position
  • Constraints Gets the collection of constraints maintained by this table
  • Rows Gets the collection of rows that belong to this table
  • TableName Gets or sets the name of the DataTable.
  1. DataTable dtObj = new DataTable("tableName1"); // Set table name  
  2. Console.WriteLine(dtObj.TableName); //tableName1  
  3.   
  4. foreach(DataRow row in dtObj.Rows)  
  5. {  
  6.     foreach(DataColumn column in dtObj.Columns)  
  7.     {  
  8.         Console.WriteLine(row[column]);  
  9.     }  
  10. }  
  11.   
  12. var dv = dtObj.DefaultView;  
  13. dv.Sort = "StartDate";  
  14.   
  15. dtObj = dv.ToTable(); 
Functions
  • AcceptChanges() Commits all the changes made to this table since the last time AcceptChanges was called.
  • Reset() Resets the DataTable to its original state. Reset removes all data, indexes, relations, and columns of the table. If a DataSet includes a DataTable, the table will still be part of the DataSet after the table is reset.
  • Merge() Merge the specified DataTable with the current DataTable.
  • ReadXml() Reads XML schema and data into the DataTable from the specified file.
  • WriteXml() Writes the current contents of the DataTable as XML using the specified Stream.
  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();  
  7.   
  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)));  
  7.   
  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;  
  4.   
  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";      
  4.        
  5. dt.Columns.Add("EmpId", typeof(int));      
  6. dt.Columns.Add("EmpName", typeof(string));    
  7.        
  8. dr = dt.NewRow();      
  9. dr["EmpId"] = 1;      
  10. dr["EmpName"] = "Manas";     
  11. dt.Rows.Add(dr);      
  12.        
  13. DataRow dr1 = null;      
  14. dr1 = dt.NewRow();      
  15. dr1["EmpId"] = 2;      
  16. dr1["EmpName"] = "Prakas";     
  17. dt.Rows.Add(dr1);      
  18.        
  19. DataRow dr2 = null;      
  20. dr2 = dt.NewRow();      
  21. dr2["EmpId"] = 3;      
  22. dr2["EmpName"] = "Akas";      
  23. dt.Rows.Add(dr2);      
  24.        
  25. DataTable dt2 = new DataTable();      
  26.     
  27. dt2.TableName = "EmpGrade";      
  28. dt2.Columns.Add("EmpId", typeof(int));      
  29. dt2.Columns.Add("Grade", typeof(int));      
  30.        
  31. DataRow drgrade = null;      
  32. drgrade = dt2.NewRow();      
  33. drgrade["EmpId"] = 1;      
  34. drgrade["Grade"] = 3;      
  35. dt2.Rows.Add(drgrade);        
  36.        
  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();  
  10.   
  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)));  
  4.   
  5. playerTables.Rows.Add(1, "Kalia");             
  6. playerTables.Rows.Add(3, "Chris");  
  7. playerTables.Rows.Add(2, "Rima");  
  8.   
  9. DataTable playerTables3 = playerTables.Copy();  
  10. playerTables3.Columns.Remove("ID");  
  11.   
  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)));  
  4.   
  5. playerTables.Rows.Add(1, "Kalia");             
  6. playerTables.Rows.Add(3, "Chris");  
  7. playerTables.Rows.Add(2, "Rima");  
  8.   
  9. // Option1  
  10. DataView dtView = playerTables.DefaultView;  
  11. dtView.Sort = "Name desc";  
  12. playerTables = dtView.ToTable();  
  13.   
  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();  
  4.   
  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();  
  6.   
  7. playerTables.Rows.Cast<DataRow>().Where(r => r.ItemArray[0] == "filterValue").ToList().ForEach(r => r.Delete());  
  8.       
  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));  
  6.   
  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);  
  11.   
  12. var GroupBy = dtEmp.AsEnumerable().GroupBy(e=>e.Field<int>("DeptNo")).Select(d => new{ d.Key, Count = d.Count() });  
  13.   
  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);  
  2.   
  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.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs