ADO.NET Data

• Mar 12, 2021
• 3 minutes to read

The Data Source Configuration Wizard allows you to bind the PivotGridControl to an ADO.NET DataSet at design time. You can also bind the PivotGridControl to a DataSet in code. This topic shows how to do this.

• Binding to a DataSet at Design Time
• Binding to a DataSet in Code

Binding to a DataSet at Design Time

1. Click the Data Source Wizard button placed on the PivotGridControl or use a corresponding command in a control’s smart tag.

   

2. In the invoked Data Source Configuration Wizard, select ADO.NET Typed DataSet…

   

   … and click the New Data Source button placed within the Data Sources area.

   

3. The following message will appear.

   

   Click OK.

4. In the invoked Data Source Configuration Wizard, you can establish a connection to the required database.

   

   To learn how to use the Data Source Configuration Wizard, see the following MSDN topic: Data Source Configuration Wizard.

   Important

   After you created the data source, rebuild the solution.

5. After creating a data source, click the Data Source Wizard button again.

   

6. In the invoked Data Source Configuration Wizard, select the created DataSet.

   

   Click Next.

7. On the next page, select the required binding method.

   

   • Direct Binding to Data Source - allows you to bind the PivotGridControl to a DataSet directly.
   • Binding via the BindingSource Component - allows you to bind the PivotGridControl to a DataSet using the BindingSource component that simplifies design-time data binding. To learn more, see BindingSource Component Specifics.

   Select Direct Binding to Data Source and click Next.

8. On the final page, you can specify the table from the created DataSet.

   

   Select the required table and click Finish.

9. The PivotGridControl will be bound to the created DataSet.

   

   You can now use the Retrieve Fiels command to retrieve the available data source fields. As an alternative, you can add specific fields manually using the PivotGrid Designer. To do this, use the Run Designer… command.

Binding to a DataSet in Code

The following code illustrates how to bind the PivotGridControl to the SalesPerson view of the Northwind database at runtime.

C#

using System.Data;
using System.Data.OleDb;

            // ...
            // Create a connection object.
            OleDbConnection connection =
            new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\Data\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 PivotGridControl and create pivot grid fields for all data source fields.
            pivotGridControl1.DataSource = sourceDataSet.Tables["SalesPerson"];
            pivotGridControl1.RetrieveFields();

VB.NET

Imports System.Data
Imports System.Data.OleDb

            ' ...
            ' Create a connection object.
            Private connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\Data\nwind.mdb")
            ' Create a data adapter.
            Private adapter As New OleDbDataAdapter("SELECT * FROM SalesPerson", connection)

            ' Create and fill a dataset.
            Private sourceDataSet As New DataSet()
            adapter.Fill(sourceDataSet, "SalesPerson")

            ' Assign the data source to the PivotGridControl and create pivot grid fields for all data source fields.
            pivotGridControl1.DataSource = sourceDataSet.Tables("SalesPerson")
            pivotGridControl1.RetrieveFields()