半岛外围网上直营

本文通过一个具体的事例，为大家阐述如何绑定DevExpress的 PivotGridControl 到数据库。

事例数据库如下：

代码如下：

C#

using DevExpress.LookAndFeel;
using DevExpress.XtraPivotGrid;
using System.Data.OleDb;

// Create a connection object.
OleDbConnection connection = 
new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\DB\\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 XtraPivotGrid control.
pivotGridControl1.DataSource = sourceDataSet.Tables["SalesPerson"];

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

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

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

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

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

// Create a data PivotGridControl field bound to the 'Extended Price' datasource field.
PivotGridField fieldExtendedPrice = new PivotGridField("Extended Price", PivotArea.DataArea);
fieldExtendedPrice.CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
// Specify the formatting setting to format summary values as integer currency amount.
fieldExtendedPrice.CellFormat.FormatString = "c0";

// Add the fields to the control's field collection.         
pivotGridControl1.Fields.AddRange(new PivotGridField[] {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;         

// Customize the control's look-and-feel via the Default LookAndFeel object.
UserLookAndFeel.Default.UseWindowsXPTheme = false;
UserLookAndFeel.Default.Style = LookAndFeelStyle.Skin;
UserLookAndFeel.Default.SkinName = "Money Twins";

VB

Imports DevExpress.LookAndFeel
Imports DevExpress.XtraPivotGrid
Imports System.Data.OleDb

' Create a connection object.
Dim connection As OleDbConnection = 
New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\DB\\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 XtraPivotGrid control.
PivotGridControl1.DataSource = sourceDataSet.Tables("SalesPerson")

' Create a row PivotGridControl field bound to the Country datasource field.
Dim fieldCountry As PivotGridField = New PivotGridField("Country", PivotArea.RowArea)

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

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

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

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

' Create a data PivotGridControl field bound to the 'Extended Price' datasource field.
Dim fieldExtendedPrice As PivotGridField = New PivotGridField("Extended Price", PivotArea.DataArea)
fieldExtendedPrice.CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric
' Specify the formatting setting to format summary values as integer currency amount.
fieldExtendedPrice.CellFormat.FormatString = "c0"

' Add the fields to the control's field collection.         
PivotGridControl1.Fields.AddRange(New PivotGridField() {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

' Customize the control's look-and-feel via the Default LookAndFeel object.
UserLookAndFeel.Default.UseWindowsXPTheme = False
UserLookAndFeel.Default.Style = LookAndFeelStyle.Skin
UserLookAndFeel.Default.SkinName = "Money Twins"