Question

如何绑定DevExpress PivotGridControl到数据库

Answer 1

事例数据库如下： 代码如下： 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"