Lesson 1 - Bind MVCxPivotGrid to Microsoft SQL Server Database File
- 4 minutes to read
This topic describes how to add the ASP.NET MVC PivotGrid extension to a Web application, bind the extension to a data source and map pivot grid fields to data fields. This example gets its data from a Microsoft SQL Server Database File created in Visual Studio Designer using the query included in the project files.
Note
The complete sample project is available in the DevExpress Examples repository: Getting Started - How to bind ASP.NET MVC PivotGrid to Microsoft SQL Server Database File
Follow the steps below to create an ASP.NET MVC application with the PivotGrid extension.
Steps 1-6. Create an ASP.NET MVC Application
- Run Visual Studio.
Select File | New | Project… to create a new project. Open the Web section in the New Project dialog and choose the DevExpress v23.2 Template Gallery.
Navigate to the ASP.NET category and select Empty Web Application.
Navigate to the Choose Layout page in the DevExpress ASP.NET MVC Project Wizard and select Standard.
Select the Metropolis theme.
- Click Create Project to create an MVC application.
Steps 7-8. Create a Microsoft SQL Server Database File
Select the App_Data folder and press Ctrl-Shift-A to create an empty database file and add a new data item. Select SQL Server Database and name it Nwind_SalesPerson.mdf.
Create a table and populate it with data: double-click the newly created database file in Solution Explorer to open the Server Explorer window. Right-click the Nwind_SalesPerson.mdf database and select New Query to invoke the SQLQuery1.sql window. Open the App_Data/NWind_SalesPerson.sql file, copy its content to the query window. Execute the query (select Execute in the context menu). After successful execution, close the SQLQuery1.sql query window without saving the changes.
Steps 9-15. Create a Data Model
Right-click the Models folder and select Add → New Item…. In the item list, select a new ADO.NET Entity Data Model and name it nwindDataContext.
Click Add to invoke the Entity Data Model Wizard.
Select the model created in the EF Designer based on an existing database.
The wizard prompts for a data connection. Click New Connection and select the Microsoft SQL Server Database File data source.
Select the file Nwind_SalesPerson.mdf created in Step 7. Leave the Windows Authentication box checked.
The resulting data connection string is shown in the picture below.
Select the data table to include in the model - the SalesPerson table.
A Microsoft securty warning popup about the T4 Text Template is displayed. Click OK.
The image below shows the created nwindDataContext data model used in this tutorial.
Rebuild the solution before proceeding to the next step.
Steps 16-18. Add the PivotGrid Extension to the MVC Application
Switch to Views | Home | Index.cshtml, set the cursor to the new line below the ViewBag.Title assignment, right-click and select Insert DevExpress MVC Extension… in the context menu.
The Insert DevExpress Extension wizard appears. Switch to the Data tab , select PivotGrid and specify the following settings:
- In the Data context class combo box, select the DXWebApplication1.Models.Nwind_SalesDataEntities class.
- In the Model class combo box, select the DXWebApplication1.Models.SalesPerson data model.
- In the Columns combo box, select the Country, ProductName, CategoryName, ExtendedPrice, and SalesPerson1 fields to add them to the PivotGrid.
Click Insert. The Insert Extension Wizard generates a partial view with PivotGrid settings and inserts the code into the controller class.
Modify the Views | Shared | _Layout.cshtml file. Attach stylesheets and scripts as shown in the code snippet below.
@Html.DevExpress().GetStyleSheets( new StyleSheet { ExtensionSuite = ExtensionSuite.NavigationAndLayout }, new StyleSheet { ExtensionSuite = ExtensionSuite.PivotGrid }, new StyleSheet { ExtensionSuite = ExtensionSuite.Editors }, new StyleSheet { ExtensionSuite = ExtensionSuite.GridView } ) @Html.DevExpress().GetScripts( new Script { ExtensionSuite = ExtensionSuite.NavigationAndLayout }, new Script { ExtensionSuite = ExtensionSuite.PivotGrid }, new Script { ExtensionSuite = ExtensionSuite.Editors }, new Script { ExtensionSuite = ExtensionSuite.GridView } )
You now have a functional MVC PivotGrid bound to a data source.
Step 19. Configure Pivot Grid Fields
To configure a PivotGrid layout, open the automatically generated Views | Home | _PivotGridPartial.cshtml file containing PivotGrid settings. Use the field’s PivotGridFieldBase.Area and PivotGridFieldBase.AreaIndex properties to specify a field’s location and order, and the PivotGridFieldBase.Caption property to change its caption.
The code snippet below shows the _PivotGridPartial.cshtml file containing recommended settings.
@Html.DevExpress().PivotGrid(settings => { settings.Name = "PivotGrid"; settings.CallbackRouteValues = new { Controller = "Home", Action = "PivotGridPartial" }; settings.Fields.Add(field => { field.Area = PivotArea.RowArea; field.FieldName = "CategoryName"; field.Caption = "Category"; field.AreaIndex = 0; }); settings.Fields.Add(field => { field.Area = PivotArea.ColumnArea; field.FieldName = "Country"; field.Caption = "Country"; field.AreaIndex = 0; }); settings.Fields.Add(field => { field.Area = PivotArea.DataArea; field.FieldName = "ExtendedPrice"; field.Caption = "Extended_Price"; field.AreaIndex = 0; }); settings.Fields.Add(field => { field.Area = PivotArea.RowArea; field.FieldName = "ProductName"; field.Caption = "Product Name"; field.AreaIndex = 1; }); settings.Fields.Add(field => { field.Area = PivotArea.ColumnArea; field.FieldName = "SalesPerson1"; field.Caption = "Sales Person"; field.AreaIndex = 1; }); }).Bind(Model).GetHtml()
Run the project to see the result.
