DataTable

21 Dec 20229 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.

<ejs-grid id="Grid" dataSource=((System.Data.DataTable)ViewBag.DataSource) allowPaging="true">
            
    <e-grid-columns>
        <e-grid-column field="OrderID" headerText="Order ID" width="120"></e-grid-column>
        <e-grid-column field="CustomerID" headerText="Customer Name" width="150"></e-grid-column>
        <e-grid-column field="EmployeeID" headerText="Employee ID" width="150"></e-grid-column>
        <e-grid-column field="OrderDate" headerText=" Order Date" format="yMd" width="130"></e-grid-column>
   </e-grid-columns>

 </ejs-grid>
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();
}

NOTE

DataTable will be supported from .Net Core 2.0.

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.

<ejs-grid id="Grid" allowPaging="true" allowSorting="true" allowFiltering="true">
    <e-data-manager url="@Url.Action("UrlDatasource", "Home")" adaptor="UrlAdaptor"></e-data-manager>
    <e-grid-aggregates>
        <e-grid-aggregate>
            <e-aggregate-columns>
                <e-aggregate-column field="EmployeeID" type="Sum"></e-aggregate-column>
            </e-aggregate-columns>
        </e-grid-aggregate>
    </e-grid-aggregates>
    <e-grid-filterSettings type="Excel"></e-grid-filterSettings>
    <e-grid-columns>
        <e-grid-column field="OrderID" headerText="Order ID" textAlign="Right" width="120"></e-grid-column>
        <e-grid-column field="CustomerID" headerText="Customer ID" width="150"></e-grid-column>
        <e-grid-column field="EmployeeID" headerText="Employee ID" textAlign="Right" width="130"></e-grid-column>
        <e-grid-column field="OrderDate" headerText="Order Date" format="yMd" textAlign="Right" width="130"></e-grid-column>
        <e-grid-column field="ShipCountry" headerText="Ship Country" width="130"></e-grid-column>
    </e-grid-columns>
</ejs-grid>
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.

<ejs-grid id="Grid" toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Update", "Cancel" })" allowPaging="true">
    <e-data-manager url="/Home/UrlDataSource" adaptor="UrlAdaptor"></e-data-manager>
    <e-grid-editSettings allowAdding="true" allowDeleting="true" allowEditing="true"></e-grid-editSettings>
    <e-grid-columns>
        <e-grid-column field="OrderID" headerText="Order ID" isPrimaryKey="true" textAlign="Right" width="100"></e-grid-column>
        <e-grid-column field="CustomerID" headerText="Customer ID" type="string" width="120"></e-grid-column>
        <e-grid-column field="EmployeeID" headerText="EmployeeID" textAlign="Right" width="120"></e-grid-column>
        <e-grid-column field="ShipCountry" headerText="Ship Country" width="150"></e-grid-column>
    </e-grid-columns>
</ejs-grid>
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);
}