如何绑定DevExpress PivotGridControl到数据库

本文通过一个具体的事例，为大家阐述如何绑定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.0Data 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"

这里，我的解决办法是，建两个map，一个从index 到uid，一个相反。

然后，初始化时，以数据库里的信息来装载。

当用户需要改变时，在d出的对话框前，对数据做点手脚，这样看起来就一直对了。

代码

declare two map:

[csharp] view plain copy

int[] arLabelToWorkTypeId

Dictionary<int, int>mapWorktypeId2LabelIndex=null

load labes from database when from load:

[csharp] view plain copy

private void Your_Load(object sender, EventArgs e)

{

// TODO: This line of code loads data into the 'schedulerDBDataSet.Resources' table. You can move, or remove it, as needed.

this.resourcesTableAdapter.Fill(this.schedulerDBDataSet.Resources)

// TODO: This line of code loads data into the 'schedulerDBDataSet.Appointments' table. You can move, or remove it, as needed.

this.appointmentsTableAdapter.Fill(this.schedulerDBDataSet.Appointments)

InitializeLabels()

//////////////////////////////////////////////////////////////////////////

schedulerControl.ActiveViewType = DevExpress.XtraScheduler.SchedulerViewType.Timeline

schedulerControl.GroupType = SchedulerGroupType.Resource

AdjustResourceHeaders()

cbView.EditValue = schedulerControl.ActiveViewType

cbGrouping.EditValue = schedulerControl.GroupType

}

private void InitializeLabels()

{

this.workTypeTableAdapter.Fill(this.schedulerDBDataSet.WorkType)

DataTable labels = this.schedulerDBDataSet.WorkType

if (labels.Rows.Count == 0)

return

schedulerControl.Storage.Appointments.Labels.Clear()

schedulerControl.Storage.Appointments.Labels.BeginUpdate()

arLabelToWorkTypeId = new int[labels.Rows.Count]

mapWorktypeId2LabelIndex = new Dictionary<int, int>()

for (int i = 0i <labels.Rows.Counti++)

{

Color color = Color.FromArgb(Int32.Parse(labels.Rows[i]["Color"].ToString()))

string dislayName = labels.Rows[i]["name"].ToString()

string menuCaption = labels.Rows[i]["name"].ToString()

AppointmentLabel aptLabel = new AppointmentLabel(color, dislayName, menuCaption)

schedulerControl.Storage.Appointments.Labels.Add(aptLabel)

arLabelToWorkTypeId[i] = int.Parse(labels.Rows[i]["WorktypeID"].ToString())

mapWorktypeId2LabelIndex.Add(arLabelToWorkTypeId[i],i)

}

schedulerControl.Storage.Appointments.Labels.EndUpdate()

}

[csharp] view plain copy

private void schedulerControl_EditAppointmentFormShowing(object sender, AppointmentFormEventArgs e)

{

DevExpress.XtraScheduler.SchedulerControl scheduler = ((DevExpress.XtraScheduler.SchedulerControl)(sender))

//appoint make a copy

DevExpress.XtraScheduler.Appointment tmpAppointment = e.Appointment

//converty to worktype

int originalId = tmpAppointment.LabelId

int labelIdx=0

if (true == mapWorktypeId2LabelIndex.TryGetValue(tmpAppointment.LabelId,out labelIdx))

{

tmpAppointment.LabelId = labelIdx

}

DevExpress.XtraScheduler.Demos.Modules.CustomAppointmentForm form = new DevExpress.XtraScheduler.Demos.Modules.CustomAppointmentForm(scheduler, tmpAppointment, e.OpenRecurrenceForm)

try

{

e.DialogResult = form.ShowDialog()

if (DialogResult.OK == e.DialogResult)

{

e.Appointment.LabelId = arLabelToWorkTypeId[tmpAppointment.LabelId]

}

else

{

e.Appointment.LabelId = originalId

}

e.Handled = true

}

finally

{

form.Dispose()

}

}

//新生成一个

private void schedulerControl_InitNewAppointment(object sender, AppointmentEventArgs e)

{

//赋到第一个值

e.Appointment.LabelId = arLabelToWorkTypeId[0]

}

你新建一个SQL 数据库，比如我新建的是：DevExpress.mdf

双击上面的数据库，然后在数据连接中，打开属性。

拷贝 连接字符串

写code。。

            //下面这个就是来自上面的链接字符串。

            string strConn = @"Data Source=.\SQLEXPRESSAttachDbFilename=C:\Users\Administrator\Documents\Visual Studio 2010\Projects\WindowsFormsApplication1\DevExpress\NORTHWND.MDFIntegrated Security=TrueUser Instance=True"

            SqlConnection conn = new SqlConnection(strConn)

            conn.Open()

            using (SqlCommand cmd = conn.CreateCommand())

            {

                cmd.CommandText = "Select * from Orders"

                DataSet ds = new DataSet()

                SqlDataAdapter adapter = new SqlDataAdapter(cmd)

                adapter.Fill(ds)

                gridControl1.DataSource = ds.Tables[0]

                gridControl1.UseEmbeddedNavigator = true

            }

---