The Ignite UI for MVC Grid supports binding to DataTable
objects. This topic introduces the feature and demonstrates how to configure and use a DataTable
with the Grid in Ignite UI for MVC. In addition, you will see how you can use the DataTable
in conjunction with the grid’s editing functionality.
The following topics are prerequisites to understanding this topic:
igGrid Overview: This topic explains how to add the igGrid
to a web page.
igGrid/igDataSource Architecture Overview: This topic explains the inner workings of igGrid
and how it collaborates with igDataSource
to enable binding to different data sources.
igGrid Updating: This topic explains how to use the Updating feature of the igGrid
control.
Developing ASP.NET MVC Applications with igGrid: This topic explains how to use igGrid
in ASP.NET MVC Applications
This topic contains the following sections:
Starting with version 12.2, the igGrid
supports binding to an ADO.NET DataTable
.
Binding to a DataTable
can be done by passing the DataTable
through the GridDataSourceAction
and defining the grid in the view.
In the scenario below, the model is a DataTable
(you may set the type to be a .NET dynamic as well). In this case, the DataTable
is returned from a GridDataSourceAction
ActionMethod
which serializes the model (DataTable
) into JSON and returns it from the server to be consumed on the client.
In Razor:
@model System.Data.DataTable
@(Html.Infragistics().Grid<System.Data.DataTable>().ID("grid1").Height("500px")
…
.DataSource(Model)
.DataSourceUrl(Url.Action("UpdateDataTableGrid"))
.DataBind()
.Render()
)
In C#:
[GridDataSourceAction]
[ActionName("UpdateDataTableGrid")]
public ActionResult UpdateDataTableGrid()
{
DataTable dt = this.MyEmployees;
return View("UpdateDataTableGrid", dt);
}
When the AutoGenerateColumns
property is set to true, the grid columns' definitions are extracted entirely from the DataTable
definition.
Note: Having one or more defined columns alongside
AutoGenerateColumns
set to true is not a valid scenario - you need to either have all columns defined or auto-generated.
There is a new property introduced in the igGrid
: DataMember
.
It contains the name of the DataTable
from the DataSet
bound to the current igGrid
.
When set, the grid looks for the name of the DataTable
matching the DataMember
value from the DataSet
to which the grid is bound. The property is useful when AutoGenerateLayouts
is false and the layouts are defined manually.
Note: Another change made in v12.2 is that
AutogenerateLayouts
is false by default.
When the igGrid
performs remote filtering, sorting, and grouping, it uses LINQ expressions internally to filter and sort the data. When using a DataTable
or DataSet
as the data source, LINQ expressions won’t work. As a result, filtering, sorting, and grouping features can only be configured for local data when binding to a DataTable
or DataSet
.
Similarly, the summaries feature is unsupported when using a DataTable
/DataSet
as a data source.
In Razor:
@(Html.Infragistics().Grid<dynamic>().ID("grid1").Height("500px")
.AutoGenerateColumns(true)
.RenderCheckboxes(true)
.AutoCommit(false)
.Features(features => {
features.Sorting().Type(OpType.Local).Mode(SortingMode.Single);
features.Filtering().Type(OpType.Local);
features.GroupBy().Type(OpType.Local);
features.Paging().PageSize(5).Type(OpType.Remote);
features.Updating();
features.Hiding();
features.Selection().Mode(SelectionMode.Row).MultipleSelection(true);
features.RowSelectors();
features.Resizing();
features.Tooltips().Visibility(TooltipsVisibility.Always);
})
.GenerateCompactJSONResponse(false)
.DataSource(Model)
.DataSourceUrl(Url.Action("datatable-interactions"))
.UpdateUrl(Url.Action("EditingSaveChanges"))
.DataBind()
.Width("100%")
.Height("100%")
.Render()
)
Remote sorting and filtering can be implemented by processing the request and filtering and/or sorting the data on the DataSet
/DataTable
level before returning the data.
The below example demonstrates how to implement remote sorting by processing the request and sorting the data on the DataTable
level before returning it.
In C#:
[GridDataSourceAction]
[ActionName("UpdateDataTableGrid")]
public ActionResult UpdateDataTableGrid()
{
DataTable dt = this.MyEmployees;
NameValueCollection queryString = HttpUtility.ParseQueryString(Request.QueryString.ToString());
// check the query string for sorting expressions
List<SortExpression> sortExpressions = BuildSortExpressions(queryString, "sort", true);
DataView dv = customers.DefaultView;
if (sortExpressions.Count > 0)
{
String sortExpression = "";
foreach (SortExpression expr in sortExpressions)
{
sortExpression += expr.Key + " " + (expr.Mode == SortMode.Ascending ? "asc" : "desc") + ",";
}
dv.Sort = sortExpression.Substring(0, sortExpression.Length - 1);
}
return View("UpdateDataTableGrid", dv.ToTable());
}
public List<SortExpression> BuildSortExpressions(NameValueCollection queryString, string sortKey, bool isTable)
{
List<SortExpression> expressions = new List<SortExpression>();
List<string> sortKeys = new List<string>();
foreach (string key in queryString.Keys)
{
if (!string.IsNullOrEmpty(key) && key.StartsWith(sortKey))
{
SortExpression e = new SortExpression();
e.Key = key.Substring(key.IndexOf("(")).Replace("(", "").Replace(")", "");
e.Logic = "AND";
e.Mode = queryString[key].ToLower().StartsWith("asc") ? SortMode.Ascending : SortMode.Descending;
expressions.Add(e);
sortKeys.Add(key);
}
}
if (sortKeys.Count > 0 && isTable)
{
foreach (string sortedKey in sortKeys)
{
queryString.Remove(sortedKey);
}
string url = Request.Url.AbsolutePath;
string updatedQueryString = "?" + queryString.ToString();
Response.Redirect(url + updatedQueryString);
}
return expressions;
}
The following features work remotely when binding to a DataTable
or DataSet
:
LoadTransactions
If a DataTable
or a DataSet
has a PrimaryKey
defined then the igGrid
automatically uses it, otherwise set this manually. This is needed when updating feature is enabled.
In Razor:
// The Model in the code snippet below is a DataTable
@(Html.Infragistics().Grid<DataTable>(Model).ID("grid1").Height("500px")
.PrimaryKey("ProductID")
.AutoGenerateLayouts(true)
.AutoGenerateColumns(true)
.RenderCheckboxes(true)
.AutoCommit(false)
.GenerateCompactJSONResponse(false)
.DataSource(Model)
.DataSourceUrl(Url.Action("datatable-interactions"))
.UpdateUrl(Url.Action("EditingSaveChanges"))
.DataBind()
.Width("100%")
.Height("100%")
.Render()
)
When updating, a serializer which supports Dictionary<string, string>
should be used It is a known issue that the Microsoft JSON serializer doesn’t support deserializing a Dictionary to JSON. If you want to use the updating feature, the GridModel
must override the LoadTransactions
method. You can use the Json.NET serializer for this purpose. You can download it from the page below and it is also included in ASP.NET MVC 4 as the default serializer.
http://www.newtonsoft.com/json
In C#:
public List<Transaction<T>> LoadTransactionsDictionary<T>(string postdata) where T : class
{
List<Transaction<T>> transactions = JsonConvert.DeserializeObject<List<Transaction<T>>>(postdata);
return transactions;
}
Another approach is to create a model based on the table’s layout and use it in the LoadTransactions
method. The sample code below demonstrates how to use the updating feature when binding to DataTable
and passing a model based on the table’s layout in the LoadTransactions
method.
In C#:
public class Employee
{
public int EmployeeID { get; set; }
public int DepartmentID { get; set; }
public string Name { get; set; }
public string Site { get; set; }
public DateTime DateOfHire { get; set; }
public bool OnSite { get; set; }
}
...
public ActionResult EditingSaveChanges()
{
ViewData["GenerateCompactJSONResponse"] = false;
GridModel m = new GridModel();
List<Transaction<Employee>> transactions = m.LoadTransactions<Employee>(HttpContext.Request.Form["ig_transactions"]);
foreach (Transaction<Employee> t in transactions)
{
if (t.type == "row")
{
DataRow dr = this.MyEmployees.Rows.Find(Int32.Parse(t.rowId));
if (t.row.Name != null)
{
dr["Name"] = t.row.Name;
}
if (t.row.DateOfHire != null)
{
dr["DateOfHire"] = t.row.DateOfHire;
}
if (t.row.Site != null)
{
dr["Site"] = t.row.Site;
}
if (t.row.OnSite != null)
{
dr["OnSite"] = t.row.OnSite;
}
if (t.row.DepartmentID != null)
{
dr["DepartmentID"] = t.row.DepartmentID;
}
this.MyEmployees.AcceptChanges();
}
}
JsonResult result = new JsonResult();
Dictionary<string, bool> response = new Dictionary<string, bool>();
response.Add("Success", true);
result.Data = response;
return result;
}
If the igGrid
is defined in the view and a DataTable
is used as the grid’s Model then the columns can only be auto-generated. If you want to define the columns manually, define a model which corresponds to the DataTable
structure and set it as the grid’s type.
Note: The fields of the custom type Employee must match the type and key of the
DataColumns
of theDataTable
that they represent.
In C#:
DataTable dtData = new DataTable("Employees");
dtData.Columns.Add("EmployeeID", typeof(int));
dtData.Columns.Add("Name", typeof(string));
dtData.Columns.Add("OnSite", typeof(bool));
dtData.Columns.Add("DateOfHire", typeof(DateTime));
dtData.Columns.Add("DepartmentID", typeof(int));
dtData.Columns.Add("Site", typeof(string));
In C#:
public class Employee
{
public int EmployeeID { get; set; }
public int DepartmentID { get; set; }
public string Name { get; set; }
public string Site { get; set; }
public DateTime DateOfHire { get; set; }
public bool OnSite { get; set; }
}
In Razor:
@( Html.Infragistics().Grid<Employee>().ID("grid1").Height("500px").Width("1000px")
.Columns(column =>
{
column.For(x => x.EmployeeID).DataType("number").HeaderText("Employee ID");
column.For(x => x.Name).DataType("string").HeaderText("Name");
column.For(x => x.Site).DataType("string").HeaderText("Site");
column.For(x => x.DepartmentID).DataType("number").HeaderText("Department");
column.For(x => x.DateOfHire).DataType("date").HeaderText("Date Of Hire");
column.For(x => x.OnSite).DataType("bool").HeaderText("On site");
})
.DataBind()
.Render()
)
This procedure explains how to implement the LoadTransaction
method when using the Grid’s updating feature while bound to a DataTable
To complete the procedure, you need the following:
This can be downloaded from http://www.newtonsoft.com/json
Following is a conceptual overview of the process:
Following are the general conceptual steps for enabling the updating feature when binding the igGrid
to a DataTable
.
Pass the DataTable in the GridDataSourceAction
DataTable
in the DataSourceUrl
action and set the DataTable
as the grid’s model.Also set the UpdateUrl
to point to the EditingSaveChanges
controller action. It handles persisting changes in the data base through LINQ
In Razor:
@(Html.Infragistics().Grid<DataTable>(Model).ID("grid1").Height("500px")
.AutoGenerateColumns(true)
.RenderCheckboxes(true)
.AutoCommit(false)
.Features(features => {
features.Sorting().Type(OpType.Local).Mode(SortingMode.Single);
features.Filtering().Type(OpType.Local);
features.GroupBy().Type(OpType.Local);
features.Paging().PageSize(5).Type(OpType.Remote);
features.Updating();
features.Hiding();
features.Selection().Mode(SelectionMode.Row).MultipleSelection(true);
features.RowSelectors();
features.Resizing();
features.Tooltips().Visibility(TooltipsVisibility.Always);
})
.GenerateCompactJSONResponse(false)
.DataSource(Model)
.DataSourceUrl(Url.Action("DataTableInteractions"))
.UpdateUrl(Url.Action("EditingSaveChanges"))
.DataBind()
.Width("100%")
.Height("100%")
.Render()
)
In C#:
public ActionResult DataTableInteractions()
{
DataTable dt = this.MyEmployees;
return View("DataTableInteractions",dt);
}
Add the Json.NET library to the project and reference it
Download the Newtonsoft.Json.dll
http://www.newtonsoft.com/json
This is required for ASP.NET MVC 3 and below but is included in the default ASP.NET MVC 4 templates. This JSON library can also be obtained through NuGet.
Implement the LoadTransactionsDictionary method in order to use the new serializer
LoadTransactionsDictionary
method in order to use the new serializerGridModel
Implement LoadTransactionsDictionary
method
In C#:
using Newtonsoft.Json;
public class NewtonSoftGridModel : GridModel
{
/// <summary>
/// loads a list of transactions from the POST data, in case saveChanges()
/// is invoked in the client side and UpdateUrl is set
/// to a valid controller action (GridUpdating)
/// The method is using the Newtonsoft serializer, which gives the ability
/// to deserialize objects in to Dictionary<string, string>
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="postdata"></param>
/// <returns></returns>
public List<Transaction<T>> LoadTransactionsDictionary<T>(string postdata) where T : class
{
List<Transaction<T>> transactions = JsonConvert.DeserializeObject<List<Transaction<T>>>(postdata);
return transactions;
}
}
Parse the dates from the JSON format
DateTime
object.ActionMethod
accepts an Ajax post to persist data changes from the grid.ig_transactions
for processing against the data layer.Dates are received in JSON format and need to be converted to DateTime
In C#:
[ActionName("EditingSaveChanges")]
public ActionResult EditingSaveChanges()
{
DataTable dt = this.MyEmployees as DataTable;
NewtonSoftGridModel m = new NewtonSoftGridModel();
List<Transaction<Dictionary<string, string>>> transactionsDict =
m.LoadTransactionsDictionary<Dictionary<string, string>>(HttpContext.Request.Form["ig_transactions"]);
foreach (Transaction<Dictionary<string, string>> t in transactionsDict)
{
...
if (t.type == "row")
{
DataRow dr = dt.Rows.Find(Int32.Parse(t.rowId));
if (t.row["Name"] != null)
{
dr["Name"] = t.row["Name"];
}
if (t.row["DateOfHire"] != null)
{
String dateString = t.row["DateOfHire"];
dateString = dateString.Replace("/Date(", "");
dateString = dateString.Replace(")/", "");
long lngString = Int64.Parse(dateString);
DateTime origin = new DateTime(1970, 1, 1, 0, 0, 0, 0, DateTimeKind.Utc);
DateTime dateValue = origin.AddSeconds(lngString / 1000);
dr["DateOfHire"] = dateValue;
}
if (t.row["Site"] != null)
{
dr["Site"] = t.row["Site"];
}
if (t.row["OnSite"] != null)
{
dr["OnSite"] = Boolean.Parse(t.row["OnSite"]);
}
if (t.row["DepartmentID"] != null)
{
dr["DepartmentID"] = Int32.Parse(t.row["DepartmentID"]);
}
dt.AcceptChanges();
}
}
JsonResult result = new JsonResult();
Dictionary<string, bool> response = new Dictionary<string, bool>();
response.Add("Success", true);
result.Data = response;
return result;
}
The following topics provide additional information related to this topic.
igHierarchicalGrid
™ control in order to bind it to DataSet
The following material (available outside the Infragistics family of content) provides additional information related to this topic.
View on GitHub