DataTable

2 Mar 20227 minutes to read

DataTable represents a table with relational data which has in-built schema to work easily with data column and data row objects.

You can bind DataTable to the grid by using DataSource property. While binding DataTable like below, grid actions such as Sorting, Filtering, Grouping, Paging etc., will handle at client side.

@Html.EJS().Grid("DataTable").DataSource((System.Data.DataTable)ViewBag.dataSource).Columns(col =>
{
   col.Field("OrderID").HeaderText("Order ID").Width("120").Add();
   col.Field("CustomerID").HeaderText("Customer Name").Width("150").Add();
   col.Field("EmployeeID").HeaderText("Employee ID").Width("150").Add();
   col.Field("OrderDate").HeaderText("Order Date").Width("130").Format("yMd").Add();

}).AllowPaging().Render()
public IActionResult Index()
{
    DataTable dt = new DataTable("Table");
    dt.Columns.AddRange(new DataColumn[4] { new DataColumn("OrderID", typeof(long)),
        new DataColumn("CustomerID", typeof(string)),
        new DataColumn("EmployeeID",typeof(int)),
        new DataColumn("OrderDate",typeof(DateTime))
    });
    int code = 10000;
    for (int i = 1; i < 10; i++)
    {
        dt.Rows.Add(code + 1, "ALFKI", i + 0, new DateTime(1991, 05, 15));
        dt.Rows.Add(code + 2, "ANATR", i + 2, new DateTime(1990, 04, 04));
        dt.Rows.Add(code + 3, "ANTON", i + 1, new DateTime(1957, 11, 30));
        dt.Rows.Add(code + 4, "BLONP", i + 3, new DateTime(1930, 10, 22));
        dt.Rows.Add(code + 5, "BOLID", i + 4, new DateTime(1953, 02, 18));
        code += 5;
    }
    ViewBag.DataSource = dt;
    return View();
}

DataTable with on-demand Grid actions

On-Demand grid actions helps you to improve performance for large data application. To perform server side grid actions for DataTable, you need to convert DataTable to IEnumerable using Utils.DataTableToJson method. Thereby in controller action, you need to use DataOperations class for performing grid actions and return result as JSON with result and count pair.

@Html.EJS().Grid("RemoteData").DataSource(ds => ds.Url("/Home/UrlDatasource").Adaptor("UrlAdaptor")).Columns(col =>
  {
      col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Width("120").Add();
      col.Field("CustomerID").HeaderText("Customer Name").Width("150").Add();
      col.Field("EmployeeID").HeaderText("Employee ID").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Width("130").Add();
      col.Field("OrderDate").HeaderText("Order Date").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Format("yMd").Add();
      col.Field("ShipCountry").HeaderText("Ship Country").Width("130").Add();

  }).AllowPaging().AllowSorting().AllowFiltering().Aggregates(agg =>
  {
      agg.Columns(new List<Syncfusion.EJ2.Grids.GridAggregateColumn>() { new Syncfusion.EJ2.Grids.GridAggregateColumn() { Field = "EmployeeID", Type = "Sum"} }).Add();
  }).FilterSettings(filter => filter.Type(Syncfusion.EJ2.Grids.FilterType.Excel)).Render()
public IActionResult UrlDatasource([FromBody]DataManagerRequest dm)
{
    IEnumerable DataSource = Utils.DataTableToJson(dt); //Here dt is the dataTable
    DataOperations operation = new DataOperations();
    if (dm.Search != null && dm.Search.Count > 0)
    {
        DataSource = operation.PerformSearching(DataSource, dm.Search);  //Search
    }
    if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting
    {
        DataSource = operation.PerformSorting(DataSource, dm.Sorted);
    }
    if (dm.Where != null && dm.Where.Count > 0) //Filtering
    {
        DataSource = operation.PerformFiltering(DataSource, dm.Where, dm.Where[0].Operator);
    }
	List<string> str = new List<string>();
    if (dm.Aggregates != null)
    {           
        for (var i = 0; i < dm.Aggregates.Count; i++)
            str.Add(dm.Aggregates[i].Field);
    }
    IEnumerable aggregate = operation.PerformSelect(DataSource, str);
    int count = DataSource.Cast<object>().Count();
    if (dm.Skip != 0)
    {
        DataSource = operation.PerformSkip(DataSource, dm.Skip);   //Paging
    }
    if (dm.Take != 0)
    {
        DataSource = operation.PerformTake(DataSource, dm.Take);
    }
    return dm.RequiresCounts ? Json(new { result = DataSource, count = count, aggregate = aggregate }) : Json(DataSource);
}

DataTable with CRUD operations

To perform server side CRUD operations for DataTable, you need to use InsertUrl, UpdateUrl and RemoveUrl of DataManager for inserting, updating and deleting the records in the specified controller actions.

To pass the data from client side to server side when performing CRUD operations, you need to use ExpandoObject as a parameter for inserting and adding actions.

@Html.EJS().Grid("RemoteData").DataSource(ds => ds.Url("/Home/UrlDatasource").Adaptor("UrlAdaptor").InsertUrl("/Home/Insert").RemoveUrl("/Home/Delete").UpdateUrl("Home/Update")).Columns(col =>
{
  col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Width("120").Add();
  col.Field("CustomerID").HeaderText("Customer Name").Width("150").Add();
  col.Field("EmployeeID").HeaderText("Employee ID").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Width("130").Add();
  col.Field("OrderDate").HeaderText("Order Date").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Format("yMd").Add();
  col.Field("ShipCountry").HeaderText("Ship Country").Width("130").Add();

}).AllowPaging().EditSettings(edit => { edit.AllowAdding(true).AllowEditing(true).AllowDeleting(true).Mode(Syncfusion.EJ2.Grids.EditMode.Normal); }).Toolbar(new List<string>() { "Add", "Edit", "Delete", "Update", "Cancel" }).Render()
public ActionResult Update(ExpandoObject value)
{
    //Here you can Update a record based on your scenario
    return Json(value, JsonRequestBehavior.AllowGet);
}

       
public ActionResult Insert(ExpandoObject value)
{
    //Here you can Insert a record based on your scenario
	return Json(value, JsonRequestBehavior.AllowGet);
}

		
public ActionResult Delete(int key)
{
    //Here you can Delete a record based on your scenario
    return Json(key, JsonRequestBehavior.AllowGet);
}