Articles in this section
Category / Section

How to create a Pivot Grid with SQL DataSource in WPF

1 min read

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 }); 
 

 

D:\documentation\WPF Sample\PivotGrid.PNG

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments
Please sign in to leave a comment
Access denied
Access denied