How to: Bind a PivotGrid to an MS Access Database Programmatically

• Apr 15, 2018
• 4 minutes to read

The following example demonstrates how to programmatically bind the PivotGridControl to a “SalesPerson” view in the nwind.mdb database, which is shipped with the installation. The control will be used to analyse sales per country, customers, product categories and years.

First, data is obtained from an MDB database via the OleDbConnection, OleDbDataAdapter and DataSet components. Secondly, the PivotGridControl is bound to a table in the dataset via the PivotGridControl.DataSource property. Lastly, pivot grid fields are created that will represent datasource fields. They are positioned within appropriate areas to analyze the data in the way you want.

Note that if you want to see an example of how to add pivot grid fields in XAML, please refer to the How to: Bind a PivotGrid to an MS Access Database tutorial.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-bind-a-pivotgrid-to-an-ms-access-database-programmatically-e2118.

MainWindow.xaml

<Window x:Class="HowToBindToMDB.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid"
        Title="MainWindow" Height="350" Width="525" Loaded="Window_Loaded">
    <Grid>
        <dxpg:PivotGridControl Name="pivotGridControl1" 
                               HorizontalAlignment="Left" VerticalAlignment="Top" />
    </Grid>
</Window>

MainWindow.xaml.vb

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

Namespace HowToBindToMDB

    Partial Public 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 _
                New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND.MDB")
            ' Create a data adapter.
            Dim adapter As _
                New OleDbDataAdapter("SELECT * FROM SalesPerson", connection)

            ' Create and fill a dataset.
            Dim sourceDataSet As New DataSet()
            adapter.Fill(sourceDataSet, "SalesPerson")

            ' Assign the data source to the PivotGrid control.
            pivotGridControl1.DataSource = sourceDataSet.Tables("SalesPerson")

            pivotGridControl1.BeginUpdate()

            ' Create a row pivot grid field bound to the Country datasource field.
            Dim fieldCountry As New PivotGridField("Country", FieldArea.RowArea)

            ' Create a row pivot grid field bound to the Sales Person datasource field.
            Dim fieldCustomer As New PivotGridField("Sales Person", FieldArea.RowArea)
            fieldCustomer.Caption = "Customer"

            ' Create a column pivot grid field bound to the OrderDate datasource field.
            Dim fieldYear As New PivotGridField("OrderDate", FieldArea.ColumnArea)
            fieldYear.Caption = "Year"
            ' Group field values by years.
            fieldYear.GroupInterval = FieldGroupInterval.DateYear

            ' Create a column pivot grid field bound to the CategoryName datasource field.
            Dim fieldCategoryName As New PivotGridField("CategoryName", FieldArea.ColumnArea)
            fieldCategoryName.Caption = "Product Category"

            ' Create a filter pivot grid field bound to the ProductName datasource field.
            Dim fieldProductName As New PivotGridField("ProductName", FieldArea.FilterArea)
            fieldProductName.Caption = "Product Name"

            ' Create a data pivot grid field bound to the 'Extended Price' datasource field.
            Dim fieldExtendedPrice As New PivotGridField("Extended Price", FieldArea.DataArea)
            ' Specify the formatting setting to format summary values as integer currency amount.
            fieldExtendedPrice.CellFormat = "c0"

            ' Add the fields to the control's field collection.         
            pivotGridControl1.Fields.AddRange(fieldCountry, fieldCustomer, fieldCategoryName, _
                                              fieldProductName, fieldYear, fieldExtendedPrice)

            ' Arrange the row fields within the Row Header Area.
            fieldCountry.AreaIndex = 0
            fieldCustomer.AreaIndex = 1

            ' Arrange the column fields within the Column Header Area.
            fieldCategoryName.AreaIndex = 0
            fieldYear.AreaIndex = 1

            pivotGridControl1.EndUpdate()
        End Sub
    End Class
End Namespace

MainWindow.xaml.cs

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

namespace HowToBindToMDB {

    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.Jet.OLEDB.4.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();

            // Create a row pivot grid field bound to the Country datasource field.
            PivotGridField fieldCountry = new PivotGridField("Country", FieldArea.RowArea);

            // Create a row pivot grid field bound to the Sales Person datasource field.
            PivotGridField fieldCustomer = new PivotGridField("Sales Person", FieldArea.RowArea);
            fieldCustomer.Caption = "Customer";

            // Create a column pivot grid field bound to the OrderDate datasource field.
            PivotGridField fieldYear = new PivotGridField("OrderDate", FieldArea.ColumnArea);
            fieldYear.Caption = "Year";
            // Group field values by years.
            fieldYear.GroupInterval = FieldGroupInterval.DateYear;

            // Create a column pivot grid field bound to the CategoryName datasource field.
            PivotGridField fieldCategoryName = new PivotGridField("CategoryName", FieldArea.ColumnArea);
            fieldCategoryName.Caption = "Product Category";

            // Create a filter pivot grid field bound to the ProductName datasource field.
            PivotGridField fieldProductName = new PivotGridField("ProductName", FieldArea.FilterArea);
            fieldProductName.Caption = "Product Name";

            // Create a data pivot grid field bound to the 'Extended Price' datasource field.
            PivotGridField fieldExtendedPrice = new PivotGridField("Extended Price", FieldArea.DataArea);

            // Specify the formatting setting to format summary values as integer currency amount.
            fieldExtendedPrice.CellFormat = "c0";

            // Add the fields to the control's field collection.         
            pivotGridControl1.Fields.AddRange(fieldCountry, fieldCustomer,
                fieldCategoryName, fieldProductName, fieldYear, fieldExtendedPrice);

            // Arrange the row fields within the Row Header Area.
            fieldCountry.AreaIndex = 0;
            fieldCustomer.AreaIndex = 1;

            // Arrange the column fields within the Column Header Area.
            fieldCategoryName.AreaIndex = 0;
            fieldYear.AreaIndex = 1;

            pivotGridControl1.EndUpdate();
        }
    }
}