How to create a Pivot Grid with SQL DataSource in WPF
We don’t have a PivotGrid control under Web Forms. We have the PivotGrid control under WPF and Silverlight Platforms. We can bind the SQL database file data as the DataTable to the PivotGrid
Please find the steps to have our PivotGrid bind with the data from the SQL Database file.
1) - Create the sdf file with the datas stored in it (in the provided sample DataBase1.sdf) and add it to the WPF application.
2) - Drag and drop our PivotGrid control from the toolbox to the MainPage.XAML.
3) - Then, write a method and use the SqlCeConnection to make the connection with the database, SqlCeCommand to get the columns from the sql Table and SqlCeDataAdapter which is used to execute the command and returns the datas from the sdf file as the DataTable as shown in the below code (we have this under OrderDetails.cs file)
C#
public static DataSet GetOrderDetials() { try { DataSet ds = new DataSet(); using (SqlCeConnection con = new SqlCeConnection(@"DataSource=Data\Database1.sdf")) { con.Open(); SqlCeCommand cmd = new SqlCeCommand(@"SELECT Top(100) [Product], [Country], [State],[Date], [Quantity],[Amount] FROM [Products] ", con); SqlCeDataAdapter da = new SqlCeDataAdapter(cmd); da.Fill(ds); return ds; } } catch(Exception e) { MessageBox.Show("Exception in connection: \t"+e.Message); return null; } }
Now, bind this DataTable to the PivotGrid using ItemSource property in PivotGrid with the below code.
C#
this.pivotGrid1.ItemSource = OrderDetails.GetOrderDetials().Tables[0]; 5) // Atlast, have the datas added to the PivotGrid Rows, Columns and Calculation fields as in the following code snippet: //To add the PivotRows this.pivotGrid1.PivotRows.Add(new PivotItem { FieldHeader = "Product", FieldMappingName = "Product", TotalHeader = "Total" }); this.pivotGrid1.PivotRows.Add(new PivotItem { FieldHeader = "Date", FieldMappingName = "Date", TotalHeader = "Total" }); //To add the PivotColumns this.pivotGrid1.PivotColumns.Add(new PivotItem { FieldHeader = "Country", FieldMappingName = "Country", TotalHeader = "Total" }); this.pivotGrid1.PivotColumns.Add(new PivotItem { FieldHeader = "State", FieldMappingName = "State", TotalHeader = "Total" }); //To add the PivotCalculations this.pivotGrid1.PivotCalculations.Add(new PivotComputationInfo { CalculationName = "Amount", FieldName = "Amount", Format = "C", SummaryType = SummaryType.DecimalTotalSum }); this.pivotGrid1.PivotCalculations.Add(new PivotComputationInfo { CalculationName = "Quantity", FieldName = "Quantity", SummaryType = SummaryType.Count });