Lesson 1 - Bind a Pivot Grid to an MDB Database (.NET)

Aug 09, 2025
7 minutes to read

This tutorial describes how to add the PivotGridControl to your WPF application, bind the Pivot Grid to a data source, and map the control’s fields to the database fields. In this example, the Pivot Grid is bound to a SalesPerson view in the nwind.mdb database, which ships with the installation.

View Example

Prerequisites

Create a New Project and Add the Pivot Grid Control

Run Microsoft Visual Studio.
Select WPF Application for .NET and click Next.
The next step of the New Project wizard allows you to configure project settings and set the project’s target .NET version. Select the target framework version — .NET 6.0.
Drag the PivotGridControl item from the DX.25.2: Data & Analytics toolbox tab to add the control to your project.
Right-click the Pivot Grid and choose the Layout → Reset All option in the context menu. This option stretches the control to fill the entire window.
After this, your XAML may look like the following:

Add Data Source to the Project

Right-click the project in the Solution Explorer and select Add | Existing Item… from the context menu.
Locate the nwind.mdb database file and click Add.

If you used the DevExpress Unified Installer and accepted the default installation path, you can find the MDB file in the following folder:

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

Create Data Objects and Connect the Pivot Grid to a Data Source

Important

You cannot bind the Pivot Grid to data at design time in .NET 5+ projects.

The code below shows how to bind the Pivot Grid to the nwind.mdb database. The sample executes the following actions:

Creates an OleDbConnection object and specifies the connection string in its constructor.
Creates an OleDbDataAdapter instance to select records from the data source.
Creates a new DataSet object and populates it with data.
Uses the PivotGridControl.DataSource property to assign the resulting data source to the Pivot Grid.

MainWindow.xaml.cs
MainWindow.xaml.vb

csharp

using DevExpress.Xpf.PivotGrid;
using System.Data;
using System.Data.OleDb;
using System.Windows;

namespace HowToBindToMDB {
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window {
        public MainWindow() {
            InitializeComponent();
        }
        private void Window_Loaded(object sender, RoutedEventArgs e) {
            // Create a connection object.
            OleDbConnection connection =
                new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=NWIND.MDB");
            // Create a data adapter.
            OleDbDataAdapter adapter =
                new OleDbDataAdapter("SELECT * FROM SalesPerson", connection);

            // Create and fill a dataset.
            DataSet sourceDataSet = new DataSet();
            adapter.Fill(sourceDataSet, "SalesPerson");

            // Assign the data source to the PivotGrid control.
            pivotGridControl1.DataSource = sourceDataSet.Tables["SalesPerson"];
            // ...
        }
        // ...
    }
}

Imports DevExpress.Xpf.PivotGrid
Imports System.Data
Imports System.Data.OleDb
Imports System.Windows

Namespace HowToBindToMDB
    ''' <summary>
    ''' Interaction logic for MainWindow.xaml
    ''' </summary>
    Public Partial Class MainWindow
        Inherits Window

        Public Sub New()
            InitializeComponent()
        End Sub

        Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
            ' Create a connection object.
            Dim connection As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=NWIND.MDB")
            ' Create a data adapter.
            Dim adapter As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM SalesPerson", connection)
            ' Create and fill a dataset.
            Dim sourceDataSet As DataSet = New DataSet()
            adapter.Fill(sourceDataSet, "SalesPerson")
            ' Assign the data source to the PivotGrid control.
            pivotGridControl1.DataSource = sourceDataSet.Tables("SalesPerson")
            ' ...
        End Sub
        ' ...
    End Class
End Namespace

Create Pivot Grid Fields and Bind them to Data

The following steps describe how to populate Pivot Grid fields with data in Optimized mode. The PivotGridControl.DataProcessingEngine property is set to Optimized once you add the Pivot Grid from the toolbox.

The code below creates several fields, binds them to the data source, and specifies their position within the control. Follow the steps below:

Create a PivotGridField object and add it to the PivotGridControl.Fields collection.
Specify the field’s area and position within this area. For this, use the Area and AreaIndex properties. AreaIndex can be set only after the field is added to the control’s field collection.
Create a DataSourceColumnBinding object for each field.
Set the DataSourceColumnBinding.ColumnName property to the name of the column in the data source. The Pivot Grid fields obtain their values from columns in the data source.
Assign the DataSourceColumnBinding object to the field’s PivotGridField.DataBinding property.

MainWindow.xaml.cs
MainWindow.xaml.vb

csharp

using DevExpress.Xpf.PivotGrid;
using System.Data;
using System.Data.OleDb;
using System.Windows;

namespace HowToBindToMDB {
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window {
        public MainWindow() {
            InitializeComponent();
        }
        private void Window_Loaded(object sender, RoutedEventArgs e) {
        // ...
            pivotGridControl1.BeginUpdate();

            AddField("Country", FieldArea.RowArea, "Country", 0);
            AddField("Person", FieldArea.RowArea, "Sales Person", 1);
            AddField("Category", FieldArea.ColumnArea, "CategoryName", 0);
            AddField("Year", FieldArea.ColumnArea, "OrderDate", 1);
            AddField("Price", FieldArea.DataArea, "Extended Price", 0);

            DataBinding orderDateBinding = pivotGridControl1.Fields["OrderDate"].DataBinding;
            (orderDateBinding as DataSourceColumnBinding).GroupInterval = FieldGroupInterval.DateYear;

            pivotGridControl1.EndUpdate();

        }
        private void AddField(string caption, FieldArea area, string columnName, int index) {
            PivotGridField field = pivotGridControl1.Fields.Add();
            field.Caption = caption;
            field.Area = area;
            field.DataBinding = new DataSourceColumnBinding(columnName);
            field.AreaIndex = index;
        }

    }
}

Imports DevExpress.Xpf.PivotGrid
Imports System.Data
Imports System.Data.OleDb
Imports System.Windows

Namespace HowToBindToMDB
    ''' <summary>
    ''' Interaction logic for MainWindow.xaml
    ''' </summary>
    Public Partial Class MainWindow
        Inherits Window

        Public Sub New()
            InitializeComponent()
        End Sub

        Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
        ' ...
            pivotGridControl1.BeginUpdate()
            AddField("Country", FieldArea.RowArea, "Country", 0)
            AddField("Person", FieldArea.RowArea, "Sales Person", 1)
            AddField("Category", FieldArea.ColumnArea, "CategoryName", 0)
            AddField("Year", FieldArea.ColumnArea, "OrderDate", 1)
            AddField("Price", FieldArea.DataArea, "Extended Price", 0)
            Dim orderDateBinding As DataBinding = pivotGridControl1.Fields("OrderDate").DataBinding
            TryCast(orderDateBinding, DataSourceColumnBinding).GroupInterval = FieldGroupInterval.DateYear
            pivotGridControl1.EndUpdate()
        End Sub

        Private Sub AddField(ByVal caption As String, ByVal area As FieldArea, ByVal columnName As String, ByVal index As Integer)
            Dim field As PivotGridField = pivotGridControl1.Fields.Add()
            field.Caption = caption
            field.Area = area
            field.DataBinding = New DataSourceColumnBinding(columnName)
            field.AreaIndex = index
        End Sub
    End Class
End Namespace

Result

The resulting code appears as follows:

MainWindow.xaml.cs
MainWindow.xaml.vb

using DevExpress.Xpf.PivotGrid;
using System.Data;
using System.Data.OleDb;
using System.Windows;

namespace HowToBindToMDB {
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window {
        public MainWindow() {
            InitializeComponent();
        }
        private void Window_Loaded(object sender, RoutedEventArgs e) {
            // Create a connection object.
            OleDbConnection connection =
                new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=NWIND.MDB");
            // Create a data adapter.
            OleDbDataAdapter adapter =
                new OleDbDataAdapter("SELECT * FROM SalesPerson", connection);

            // Create and fill a dataset.
            DataSet sourceDataSet = new DataSet();
            adapter.Fill(sourceDataSet, "SalesPerson");

            // Assign the data source to the PivotGrid control.
            pivotGridControl1.DataSource = sourceDataSet.Tables["SalesPerson"];

            pivotGridControl1.BeginUpdate();

            AddField("Country", FieldArea.RowArea, "Country", 0);
            AddField("Person", FieldArea.RowArea, "Sales Person", 1);
            AddField("Category", FieldArea.ColumnArea, "CategoryName", 0);
            AddField("Year", FieldArea.ColumnArea, "OrderDate", 1);
            AddField("Price", FieldArea.DataArea, "Extended Price", 0);

            DataBinding orderDateBinding = pivotGridControl1.Fields["OrderDate"].DataBinding;
            (orderDateBinding as DataSourceColumnBinding).GroupInterval = FieldGroupInterval.DateYear;

            pivotGridControl1.EndUpdate();

        }
        private void AddField(string caption, FieldArea area, string columnName, int index) {
            PivotGridField field = pivotGridControl1.Fields.Add();
            field.Caption = caption;
            field.Area = area;
            field.DataBinding = new DataSourceColumnBinding(columnName);
            field.AreaIndex = index;
        }

    }
}

Imports DevExpress.Xpf.PivotGrid
Imports System.Data
Imports System.Data.OleDb
Imports System.Windows

Namespace HowToBindToMDB
    ''' <summary>
    ''' Interaction logic for MainWindow.xaml
    ''' </summary>
    Public Partial Class MainWindow
        Inherits Window

        Public Sub New()
            InitializeComponent()
        End Sub

        Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
            ' Create a connection object.
            Dim connection As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=NWIND.MDB")
            ' Create a data adapter.
            Dim adapter As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM SalesPerson", connection)
            ' Create and fill a dataset.
            Dim sourceDataSet As DataSet = New DataSet()
            adapter.Fill(sourceDataSet, "SalesPerson")
            ' Assign the data source to the PivotGrid control.
            pivotGridControl1.DataSource = sourceDataSet.Tables("SalesPerson")

            pivotGridControl1.BeginUpdate()
            AddField("Country", FieldArea.RowArea, "Country", 0)
            AddField("Person", FieldArea.RowArea, "Sales Person", 1)
            AddField("Category", FieldArea.ColumnArea, "CategoryName", 0)
            AddField("Year", FieldArea.ColumnArea, "OrderDate", 1)
            AddField("Price", FieldArea.DataArea, "Extended Price", 0)
            Dim orderDateBinding As DataBinding = pivotGridControl1.Fields("OrderDate").DataBinding
            TryCast(orderDateBinding, DataSourceColumnBinding).GroupInterval = FieldGroupInterval.DateYear
            pivotGridControl1.EndUpdate()
        End Sub

        Private Sub AddField(ByVal caption As String, ByVal area As FieldArea, ByVal columnName As String, ByVal index As Integer)
            Dim field As PivotGridField = pivotGridControl1.Fields.Add()
            field.Caption = caption
            field.Area = area
            field.DataBinding = New DataSourceColumnBinding(columnName)
            field.AreaIndex = index
        End Sub
    End Class
End Namespace

MainWindow.xaml

xaml

<Window
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:HowToBindToMDB"
        xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid" x:Class="HowToBindToMDB.MainWindow"
        mc:Ignorable="d"
        Title="MainWindow" Height="450" Width="800" Loaded="Window_Loaded">
    <Grid>

        <dxpg:PivotGridControl Name="pivotGridControl1" DataProcessingEngine="Optimized"/>

    </Grid>
</Window>

Run the project and see the result: