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.

DataTable dtObj = new DataTable("tableName1"); // Set table name
Console.WriteLine(dtObj.TableName); //tableName1

foreach(DataRow row in dtObj.Rows)
{
    foreach(DataColumn column in dtObj.Columns)
    {
        Console.WriteLine(row[column]);
    }
}

var dv = dtObj.DefaultView;
dv.Sort = "StartDate";

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.
  

DataTable dtObj1 = new DataTable("tableName1"); // Set table name
DataTable dtObj2 = new DataTable("tableName2"); // Set table name

//merging first data table into second data table
dtObj2.Merge(dtObj1);
dtObj2.AcceptChanges();

string filePath = "D:\\SelfPractice\\\\Information.xml";
DataSet ds = new DataSet();
ds.ReadXml(filePath); // Read xml value as DataSet object
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.

DataTable playerTable = new DataTable("Players");
playerTable.Columns.Add(new DataColumn("ID", typeof(int)));
playerTable.Columns.Add(new DataColumn("Name", typeof(string)));
playerTable.Columns.Add(new DataColumn("DOJ", typeof(DateTime)));
playerTable.Columns.Add(new DataColumn("Country", typeof(string)));
playerTable.Columns.Add(new DataColumn("IsActive", typeof(bool)));

playerTable.Rows.Add(1, "Sourav Singh", DateTime.Now, "India", true);
playerTable.Rows.Add(2, "Simon David", DateTime.Now, "England", true);
playerTable.Rows.Add(3, "Chris Brown", DateTime.Now, "WestIndies", true);
playerTable.Rows.Add(4, "Manas Nayak", DateTime.Now, "India", true);
playerTable.Rows.Add(5, "John Lee", DateTime.Now, "England", true);
playerTable.Rows.Add(6, "Salim Desmukh", DateTime.Now, "India", false);
playerTable.Rows.Add(7, "N Patil", DateTime.Now, "India", true);
playerTable.Rows.Add(8, "Ricky Samson", DateTime.Now, "England", false);

We can accomplish filter in the following ways.

Option 1 Using Normal way

foreach (DataRow row in playerTable.Rows)
{
    if (row["Country"].ToString() == "India")
    {
        Console.WriteLine(row["Name"]);
    }
}

Option 2 Using Linq with Rows properties

var tempResults = (from DataRow dr in playerTable.Rows
                   where (string)dr["Country"] == "India"
                   select dr);

foreach (DataRow row in tempResults)
{
    Console.WriteLine("{0}, {1}", row[0], row[1]);
}

Option 3 Using Select() method

DataRow[] result = playerTable.Select("ID >= 2 AND IsActive = true");
foreach (DataRow row in result)
{
    Console.WriteLine("{0}, {1}", row[0], row[1]);
}

Option 4 Using Linq

To implement LINQ in DataTable, you need add a reference to the System.Data.DataSetExtensions for AsEnumerable() method.

var results2 = from myRow in playerTable.AsEnumerable()
               where myRow.Field<string>("Country") == "India"
               select myRow;

foreach (DataRow dr in results2)
{
    Console.WriteLine(dr["Name"]);
}

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.

DataTable dt = new DataTable();
DataRow dr = null;
dt.TableName = "Emp";

dt.Columns.Add("EmpId", typeof(int));
dt.Columns.Add("EmpName", typeof(string));

dr = dt.NewRow();
dr["EmpId"] = 1;
dr["EmpName"] = "Manas";
dt.Rows.Add(dr);

DataRow dr1 = null;
dr1 = dt.NewRow();
dr1["EmpId"] = 2;
dr1["EmpName"] = "Prakas";
dt.Rows.Add(dr1);

DataRow dr2 = null;
dr2 = dt.NewRow();
dr2["EmpId"] = 3;
dr2["EmpName"] = "Akas";
dt.Rows.Add(dr2);

DataTable dt2 = new DataTable();

dt2.TableName = "EmpGrade";
dt2.Columns.Add("EmpId", typeof(int));
dt2.Columns.Add("Grade", typeof(int));

DataRow drgrade = null;
drgrade = dt2.NewRow();
drgrade["EmpId"] = 1;
drgrade["Grade"] = 3;
dt2.Rows.Add(drgrade);

DataRow drgrade2 = null;
drgrade2 = dt2.NewRow();
drgrade2["EmpId"] = 3;
drgrade2["Grade"] = 2;
dt2.Rows.Add(drgrade2);

Following code implements Inner Join.

var JoinResult = (from p in dt.AsEnumerable()
                  join t in dt2.AsEnumerable()
                  on p.Field<string>("EmpId") equals t.Field<string>("EmpId")
                  select new
                  {
                      EmpId = p.Field<int>("EmpId"),
                      EmpName = p.Field<sting>("EmpName"),
                      Grade = t.Field<int>("Grade")
                  }).ToList();

//  Result:
//  -----------------------
//  EmpId         EmpName        Grade
//    1            Manas           3
//    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.

DataTable playerTables = new DataTable("Players");
playerTables.Columns.Add(new DataColumn("ID", typeof(int)));
playerTables.Columns.Add(new DataColumn("Name", typeof(string)));

playerTables.Rows.Add(1, "Kalia");
playerTables.Rows.Add(3, "Chris");
playerTables.Rows.Add(2, "Rima");

DataTable playerTables3 = playerTables.Copy();
playerTables3.Columns.Remove("ID");

DataView view = new DataView(playerTables);
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:

DataTable playerTables = new DataTable("Players");
playerTables.Columns.Add(new DataColumn("ID", typeof(int)));
playerTables.Columns.Add(new DataColumn("Name", typeof(string)));

playerTables.Rows.Add(1, "Kalia");
playerTables.Rows.Add(3, "Chris");
playerTables.Rows.Add(2, "Rima");

// Option1
DataView dtView = playerTables.DefaultView;
dtView.Sort = "Name desc";
playerTables = dtView.ToTable();

//Option2
DataRow[] foundRows = playerTables.Select().OrderBy(u => u["Name"]).ToArray();
DataTable dtTemp = foundRows.CopyToDataTable();

Reverse 

 

It means records will be reversed in DataTable object. 

var query  = (from rec in playerTables.AsEnumerable()
              orderby rec.Field("Name")
              select rec).Reverse();

// If you are dealing with DataSet table then
var reversedTables = _ds.Tables.Cast<DataTable>().Reverse();
foreach(DataTable table in reversedTables)
{
    // ...
}

Edit, Delete DataTable rows

 

Sometimes we need to update DataTable records like to update the name whose ID is 2. 

 

Edit

DataRow[] customerRow = dtObj.Select("ID = 2");
customerRow[0]["Name"] = "Manas1";

Delete

// When you are using DataSet
dataSet.Tables["Players"].Rows[0].Delete();

// Delete the record whose ID is 1
playerTables.Select("ID == 1").Delete();

playerTables.Rows.Cast<DataRow>().Where(r => r.ItemArray[0] == "filterValue").ToList().ForEach(r => r.Delete());

// Using DataView object to elete the records.
DataView view = new DataView(ds.Tables["MyTable"]);
view.RowFilter = "ID = 1";
// Delete these rows.
foreach (DataRowView row in view)
{
  row.Delete();
}

// Delete records in normal way..
for(int i = playerTables.Rows.Count-1; i >= 0; i--)
{
    DataRow dr = playerTables.Rows[i];
    if (dr["ID"] == "1")
        dr.Delete();
}

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.

DataTable dt1 = new DataTable();
DataRow dr1 = dt1.NewRow();
DataTable dt2 = new DataTable();
dt2.Rows.Add(dr1); // will give error
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.

//Creating another DataTable to copy
DataTable dtCopy = new DataTable();
dt.TableName = "CopyTable";
dtCopy = dt.Copy();

DataTable.Clone() only returns the structure of the DataTable, not the rows or data of the DataTable.

//Creating another DataTable to clone
DataTable dtClone = new DataTable();
dt.TableName = "CloneTable";
dtClone = dt.Clone();

Miscellaneous

 

Below code defines how to implement GroupBy in DataTable. 

DataTable dtEmp = new DataTable();
dtEmp.Columns.Add("EmpID", typeof(int));
dtEmp.Columns.Add("EmpName", typeof(string));
dtEmp.Columns.Add("Sal", typeof(decimal));
dtEmp.Columns.Add("DeptNo", typeof(int));

dtEmp.Rows.Add(1, "Manas", 10000, 1);
dtEmp.Rows.Add(2, "Himesh", 20000, 2);
dtEmp.Rows.Add(3, "Debu", 30000, 2);
dtEmp.Rows.Add(4, "Amulya", 5000, 3);

var GroupBy = dtEmp.AsEnumerable().GroupBy(e=>e.Field<int>("DeptNo")).Select(d => new{ d.Key, Count = d.Count() });

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.

string json = JsonConvert.SerializeObject(dataSet, Formatting.Indented);

Console.WriteLine(json);

{
    "Table1": [{
        "EmpID": 0,
        "EmpName": "item 0",
        "Sal": 10000,
        "DeptNo": "1",
    }, {
        "EmpID": 2,
        "EmpName": "Himesh",
        "Sal": 20000,
        "DeptNo": 2,
    }]
}

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.