Bind Grid View to Large Data (Database Server Mode)
- 5 minutes to read
Database Server Mode Overview
The MVC GridView extension supports a specific binding mode designed to work with large datasets. Within this binding mode, data-aware operations (sorting, grouping, etc.) are performed on the database server side, which is why this mode is called database server mode.
In database server mode, the MVC GridView is bound to a queryable source via the GridViewExtension.BindToLINQ method, which allows the GridView to load data from the queryable source on demand.
When an end-user performs data operations (sorting, grouping, etc.), the GridView generates smart queries to receive only those records that must be displayed on screen. These requests are passed to the associated queryable source. The queryable source translates these requests into the required queries and executes them. This ensures a quick response and improved performance for large data sources.
For end-users, the MVC GridView functions identically in regular and server modes. In database server mode, end-users can use an automatic filtering feature to access a particular data range, sort, group and filter data, calculate summaries, etc.
Binding to Data
Perform the following steps to bind the GridView to a data source in database server mode.
Add the GridView to your project
Add the GridView to your project, and bind it to a data source in regular mode using the Code First development approach or Database First development approach.
Change the View code
In the grid’s View code, use the GridViewExtension.BindToLINQ method to bind the GridView to a data source. This enables binding to data in database server mode.
@{ var grid = Html.DevExpress().GridView(settings =>{ settings.Name = "GridView"; settings.CallbackRouteValues = new { Controller = "Home", Action = "GridViewPartial" }; settings.SettingsEditing.AddNewRowRouteValues = new { Controller = "Home", Action = "GridViewPartialAddNew" }; settings.SettingsEditing.UpdateRowRouteValues = new { Controller = "Home", Action = "GridViewPartialUpdate" }; settings.SettingsEditing.DeleteRowRouteValues = new { Controller = "Home", Action = "GridViewPartialDelete" }; //... settings.KeyFieldName = "ID"; settings.EnableRowsCache = true; }); if (ViewData["EditError"] != null) { grid.SetEditErrorText((string)ViewData["EditError"]); } } @grid.BindToLINQ(typeof(MyProject.Models.LargeDatabaseEntities), "Emails").GetHtml()Note
Enable the GridViewSettings.EnableRowsCache option to reduce the number of calls to a bound data source when the GridView functions in Database Server Mode. When the GridView extension functions in regular data binding mode, the GridViewSettings.EnableRowsCache should be disabled.
Change the Controller code
The code sample below demonstrates the Controller code with actions that handle callbacks related to creating, reading, updating, and deleting records within the GridView using the Entity Framework ORM.
using System; using System.Linq; using System.Web.Mvc; namespace MyProject.Controllers { public class HomeController : Controller { public ActionResult Index() { return View(); } MyProject.Models.LargeDatabaseEntities db = new MyProject.Models.LargeDatabaseEntities(); [ValidateInput(false)] public ActionResult GridViewPartial() { return PartialView("_GridViewPartial"); } //Action that handles callbacks related to adding a new row [HttpPost, ValidateInput(false)] public ActionResult GridViewPartialAddNew(MyProject.Models.Email item) { var model = db.Emails; if (ModelState.IsValid) { try { model.Add(item); db.SaveChanges(); } catch (Exception e) { ViewData["EditError"] = e.Message; } } else ViewData["EditError"] = "Please, correct all errors."; return PartialView("_GridViewPartial"); } //Action that handles callbacks related to row updates [HttpPost, ValidateInput(false)] public ActionResult GridViewPartialUpdate(MyProject.Models.Email item) { var model = db.Emails; if (ModelState.IsValid) { try { var modelItem = model.FirstOrDefault(it => it.ID == item.ID); if (modelItem != null) { this.UpdateModel(modelItem); db.SaveChanges(); } } catch (Exception e) { ViewData["EditError"] = e.Message; } } else ViewData["EditError"] = "Please, correct all errors."; return PartialView("_GridViewPartial"); } //Action that handles callbacks related to row deleting [HttpPost, ValidateInput(false)] public ActionResult GridViewPartialDelete(System.Int32 ID) { var model = db.Emails; if (ID != null) { try { var item = model.FirstOrDefault(it => it.ID == ID); if (item != null) model.Remove(item); db.SaveChanges(); } catch (Exception e) { ViewData["EditError"] = e.Message; } } return PartialView("_GridViewPartial"); } } }
Server mode limitations
In server mode, the MVC GridView does not have simultaneous access to bound data in its entirety. This imposes some limitations on the grid’s features that are still available in regular binding mode. see the table below for information on features that have limitations in server mode:
- Custom sorting (via GridViewSettings.CustomColumnSort) and sorting by displayed values are not supported.
Custom grouping (via GridViewSettings.CustomColumnGroup) is not supported.
Rows are always grouped by the values of grouping columns. Thus, group modes specified by the GridViewDataColumnSettings.GroupInterval property are not in affect.
- Filtering by displayed values is not supported. Only filtering by edit values is allowed.
- Case-sensitive filtering. The grid converts a search string to lower-case before filtering.
- Custom summary calculation (via GridViewSettings.CustomSummaryCalculate) is not supported.
- You can only enable sorting, grouping, filtering and summary calculation for unbound columns that are populated using expressions (see GridViewDataColumn.UnboundExpression).
- The grid does not support selection of all grid rows with the “SelectAll” check box (when the GridViewCommandColumn.SelectAllCheckboxMode property is set to GridViewSelectAllCheckBoxMode.AllPages). In this scenario, you can set the SelectionStoringMode property to “PerfomanceOptimized” to make the grid add only unselected row keys when a user selects all rows.