Database Server Mode

• Oct 29, 2020
• 3 minutes to read

Database Server Mode Overview

The MVC PivotGrid extension supports a specific binding mode designed to work with large datasets. Within this binding mode, data-aware operations (for instance, filtering, grouping and aggregation) are performed on the database server side, hence, this mode is called database server mode.

In database server mode, the MVC PivotGrid is bound to a queryable source using the PivotGridExtension.BindToLINQ method, which allows the PivotGrid to load data from the queryable source on demand.

When an end-user performs data operations (filtering, grouping, etc.), the PivotGrid 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 quick response and improved performance for large data sources.

For end-users, the MVC PivotGrid functions identically in regular and server modes.

Binding to Data

Perform the following steps to bind the PivotGrid to a data source in database server mode.

1. Add the PivotGrid to your project

   Add the PivotGrid to your project, and bind it to a data source in regular mode using the Code First development approach or Database First development approach.

2. Change the View code

   In the pivot grid’s View code, use the PivotGridExtension.BindToLINQ method to bind the PivotGrid to a data source. This enables binding to data in database server mode.

   Partial View code (“_PivotGridPartial.cshtml”):

   C#

   @Html.DevExpress().PivotGrid(settings => {
       settings.Name = "PivotGrid";
       settings.CallbackRouteValues = new { Controller = "Home", Action = "PivotGridPartial" };
       settings.EnableRowsCache = true;
   
           settings.Fields.Add(field => {
           field.Area = PivotArea.RowArea;
           field.FieldName = "Name";
       });
           settings.Fields.Add(field => {
           field.Area = PivotArea.ColumnArea;
           field.FieldName = "Date";
       });
           settings.Fields.Add(field => {
           field.Area = PivotArea.DataArea;
           field.FieldName = "Price";
       });
   }).BindToLINQ(typeof(DXWebApplication1.Models.Entities), "Products").GetHtml()
   

   If the database table that is used to generate a model does not have a primary key, pass the selectingMethod delegate method as the third PivotGridExtension.BindToLINQ method parameter and specify the LinqServerModeDataSourceSelectEventArgs.KeyExpression and LinqServerModeDataSourceSelectEventArgs.QueryableSource properties.

   C#

   @Html.DevExpress().PivotGrid(settings => {
   // ...
   }).BindToLINQ(string.Empty, string.Empty, (s, e) =>
   {
       e.KeyExpression = "ProductID"; e.QueryableSource = new DXWebApplication1.Models.AdventureWorksEntities().Products;
   }).GetHtml()
   

   Note

   Enable the PivotGridSettings.EnableRowsCache option to reduce the number of calls to a bound data source when the PivotGrid functions in Database Server Mode. When the PivotGrid extension functions in regular data binding mode, the PivotGridSettings.EnableRowsCache should be disabled.

Server Mode Limitations

In server mode, the pivot grid does not have simultaneous access to bound data in its entirety, and this imposes some limitations on the pivot grid’s features. See the list below for information on features that are not supported in server mode.

• Custom summaries calculated using the PivotGridSettings.CustomSummary property.
• Custom grouping that uses the PivotGridSettings.CustomGroupInterval property.
• Providing data for unbound fields using PivotGridSettings.CustomUnboundFieldData property.

Besides the limitations above, the pivot grid extension has an export limitation. The PivotGridExtension class does not provide special methods for exporting if the Server Mode data source is used. The following example demonstrates how to use the default ExportTo… methods to export data by creating a ServerModeDataSource object in code.

C#

return PivotGridExtension.ExportToXls(GetPivotSettings(),
    new DevExpress.PivotGrid.ServerMode.ServerModeDataSource(
        new DevExpress.PivotGrid.ServerMode.Queryable.QueryableQueryExecutor(
            new MPG_ServerMode.Models.ContosoRetailDWEntities().V_CustomerOrders)));

Note

Note that in server mode, the pivot grid is bound to data in read-only mode.