Lesson 1 - Bind a Pivot Grid to an MDB Database

  • 4 minutes to read

This tutorial will help you learn how to add the Pivot Grid Control to your WinForms application, bind it to a data source and map its fields to the database fields. In this example, we use a Microsoft Access database (.mdb), although the pivot grid can be bound to a variety of other data providers.

To create a simple WinForms application with a pivot grid, do the following.

Steps 1-4. Create a New Project and Add PivotGridControl

  1. Run Microsoft Visual Studio.
  2. Create a new Windows Forms Application project.
  3. Drag the PivotGridControl item from the DX.20.1: Data & Analytics toolbox tab onto the form.

    GettingStarted_L1_Toolbox

  4. Click the smart tag icon (SmartTag) displayed at the top-right of the pivot grid to invoke the PivotGridControl Tasks popup window.

    GettingStarted_L1_SmartTag

    In this window, click Dock in Parent Container to let the pivot grid fill the entire window's client area.

    GettingStarted_L1_Dock

Steps 5-16. Prepare a Data Source

Now you need to add a data source to your WinForms Application. To do this, follow the steps below.

  1. Click the Data Source Wizard panel:

    or in the PivotGridControl Tasks popup window, select the Data Source Wizard item.

    GettingStarted_L1_InvokeDataSourceWizard

  2. In the invoked wizard, select ADO.NET Typed DataSet technology and click the New Data Source... button.

    GettingStarted_L1_AddDataSource

    Click OK to create a new data source.

    GettigStarted_L1_MessageBox

  3. On the first page of the wizard, choose Database as the type of a data source and click Next.

    DXGrid_GettingStarted1_03

  4. Choose Dataset as a Database Model and proceed.

    DXGrid_GettingStarted1_04

  5. On the Choose Your Data Connection page, click New Connection... to create a connection to a database.

    DXPivotGridGettingStartedL1_ChooseYourDataConnection

    The Add Connection dialog will be invoked.

  6. In this dialog, do the following.

    • In the Data source field, specify Microsoft Access Database File (OLE DB) as the database type. If necessary, use the Change... button to select between different types.
    • Specify the path to the Northwind database file in the Database file name field. By default, this database is stored in the following path:

      C:\Users\Public\Documents\DevExpress Demos 20.1\Components\Data\nwind.mdb

      You can use the Browse... button to locate this file.

    DXPivotGridGettingStartedL1_AddConnectionDialog

    Click OK to close the dialog and then click Next in the wizard.

  7. A message box that asks whether to copy the database file to the project appears. Click Yes.

    DXPivotGridGettingStartedL1_MessageBox

  8. On the next step, you can choose whether to save the connection settings to a connection string or not.

    DXPivotGridGettingStartedL1_ConnectionString

    Make sure that the Yes, save the connection as check box is checked and click Next.

  9. On the Choose Your Database Objects page, choose the database columns that you need to include in the data source.

    To do this, expand the Views and SalesPerson nodes and select the following columns in the SalesPerson view.

    • Country
    • ProductName
    • CategoryName
    • Extended Price
    • Sales Person

    GettingStarted_L1_ChooseDatabaseObjects

    After this, click Finish to close the wizard.

  10. Invoke the Data Source Configuration Wizard again, select the ADO.NET Typed DataSet technology and choose the previously created data source. Click Next.

    GettingStarted_L1_BindingToData

  11. In the next window, select Direct Binding to Data Source and click Next.

    GettingStarted_L1_BindingToData2

  12. Select a table and click Finish to close the Data Source Configuration Wizard.

    GettingStarted_L1_SelectTable

Steps 17-18. Create and Arrange Pivot Grid Fields

  1. Click the pivot grid's smart tag icon and then click Retrieve Fields in the popup window.

    GettingStarted_L1_RetrieveFields

    The Pivot Grid Control will create fields for all columns in the data source and locate the created fields in the Filter Header Area.

    GettingStarted_L1_PivotGridControl

  2. Put the Country and Sales Person fields into the Column Header Area, the Category Name and Product Name fields into the Row Header Area, and the Extended Price field into the Data Header Area.

    You can drag fields between areas and change their order within areas with drag-and-drop.

    GettingStarted_L1_ReorderFields

    After you have finished, the pivot grid fields should be arranged as illustrated below.

    GettingStarted_L1_OrderedFields

NOTE

Alternatively, you can create fields manually using the Fields Page in the PivotGrid Designer. This page allows you to add new fields and customize their properties. To learn which properties you will need to set in order to map a field to a data source column and locate it in the appropriate area, see Pivot Grid Fields.

Result

Run the project to see the result.

GettingStarted_L1_Result

TIP

You can convert standard Windows form to XtraForm for style consistency. In Visual Studio Desgner, click the smart tag ans select the Convert to Skinable Form option.

See Also