Lesson 1 - Bind a Pivot Grid to an MDB Database (.NET)
- 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.
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.23.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:
<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>
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
OleDbDataAdapterinstance to select records from the data source. - Creates a new
DataSetobject and populates it with data. - Uses the PivotGridControl.DataSource property to assign the resulting data source to the Pivot Grid.
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"];
// ...
}
// ...
}
}
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.
AreaIndexcan 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
DataSourceColumnBindingobject to the field’s PivotGridField.DataBinding property.
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;
}
}
}
Result
The resulting code appears as follows:
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;
}
}
}
<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:
