Lesson 1 - Bind MVCxPivotGrid to Microsoft SQL Server Database File

• Oct 29, 2020
• 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

1. Run Visual Studio.

2. Select File | New | Project… to create a new project. Open the Web section in the New Project dialog and choose the DevExpress v20.1 Template Gallery.

3. Navigate to the ASP.NET category and select Empty Web Application.

4. Navigate to the Choose Layout page in the DevExpress ASP.NET MVC Project Wizard and select Standard.

5. Select the Metropolis theme.

6. Click Create Project to create an MVC application.

Steps 7-8. Create a Microsoft SQL Server Database File

1. 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.

2. 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

1. 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.

2. Select the model created in the EF Designer based on an existing database.

3. The wizard prompts for a data connection. Click New Connection and select the Microsoft SQL Server Database File data source.

4. Select the file Nwind_SalesPerson.mdf created in Step 7. Leave the Windows Authentication box checked.

5. Select the data table to include in the model - the SalesPerson table.

6. A Microsoft securty warning popup about the T4 Text Template is displayed. Click OK.

7. The image below shows the created nwindDataContext data model used in this tutorial.

Steps 16-18. Add the PivotGrid Extension to the MVC Application

1. 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.

2. The Insert DevExpress Extension wizard appears. Switch to the Data tab , select PivotGrid and specify the following settings:

3. Modify the Views | Shared | _Layout.cshtml file. Attach stylesheets and scripts as shown in the code snippet below.

You now have a functional MVC PivotGrid bound to a data source.

Step 19. Configure Pivot Grid Fields

1. 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.

Run the project to see the result.