Using LINQ Queries to Add Data in FlexPivot

FlexPivot can use various collections such as LINQ queries to add data. LINQ provides a flexible and efficient data querying model to create new queries within the client application without modifying the database. As a result, FlexPivot control can use LINQ queries as a data source so that end users can create their own views for data analysis.

Complete the following steps to create a LINQ query and use it as a data source for FlexPivot.

Create a new Windows Forms Application project in Visual Studio.
Drag-and-drop FlexPivotPage control (see C1FlexPivotpage icon in the Toolbox) onto the form.

Switch to the code view and add the following namespaces.

Visual Basic
C#

Visual Basic

Imports System.Data.OleDb
Imports System.Linq

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

Add the following code in the Forms1.cs constructor to load the data using LINQ query.

Visual Basic
C#

Visual Basic

Dim ds = New DataSet()
For Each table As String In "Products,Categories,Employees,Customers,Orders,Order Details".Split(","c)
    Dim sql As String = String.Format("select * from [{0}]", table)
    Dim da = New OleDbDataAdapter(sql, GetConnectionString())
    da.Fill(ds, table)
Next table

var ds = new DataSet();
foreach (string table in "Products,Categories,Employees,Customers,Orders,Order Details".Split(','))
{
    string sql = string.Format("select * from [{0}]", table);
    var da = new OleDbDataAdapter(sql, GetConnectionString());
    da.Fill(ds, table);
}

Initialize a connection string to connect with a database installed on your system. This example uses C1NWind.mdb file as the database. You can find this file at Documents\ComponentOne Samples\Common location on your system. In case your database file is kept at a different location then make changes in the path defined in the GetConnectionString method.

Visual Basic
C#

Visual Basic

Private Shared Function GetConnectionString() As String
    Dim path As String = Environment.GetFolderPath(Environment.SpecialFolder.Personal) & "\ComponentOne Samples\Common"
    Dim conn As String = "provider=microsoft.jet.oledb.4.0;data source={0}\c1nwind.mdb;"
    Return String.Format(conn, path)
End Function

static string GetConnectionString()
{
    string path = Environment.GetFolderPath(Environment.SpecialFolder.Personal) + @"\ComponentOne Samples\Common";
    string conn = @"provider=microsoft.jet.oledb.4.0;data source={0}\c1nwind.mdb;";
    return string.Format(conn, path);
}

Add the following LINQ query to connect the FlexPivotPage control with the tables loaded from the database.

Visual Basic
C#

Visual Basic

Dim q = From detail In ds.Tables("Order Details").AsEnumerable() _
        Join product In ds.Tables("Products").AsEnumerable() On detail.Field(Of Integer)("ProductID") Equals product.Field(Of Integer)("ProductID") _
        Join category In ds.Tables("Categories").AsEnumerable() On product.Field(Of Integer)("CategoryID") Equals category.Field(Of Integer)("CategoryID") _
        Join order In ds.Tables("Orders").AsEnumerable() On detail.Field(Of Integer)("OrderID") Equals order.Field(Of Integer)("OrderID") _
        Join customer In ds.Tables("Customers").AsEnumerable() On order.Field(Of String)("CustomerID") Equals customer.Field(Of String)("CustomerID") _

var q =
   from detail in ds.Tables["Order Details"].AsEnumerable()
   join product in ds.Tables["Products"].AsEnumerable()
       on detail.Field<int>("ProductID") equals product.Field<int>("ProductID")
   join category in ds.Tables["Categories"].AsEnumerable()
       on product.Field<int>("CategoryID") equals category.Field<int>("CategoryID")
   join order in ds.Tables["Orders"].AsEnumerable()
       on detail.Field<int>("OrderID") equals order.Field<int>("OrderID")
   join customer in ds.Tables["Customers"].AsEnumerable()
       on order.Field<string>("CustomerID") equals customer.Field<string>("CustomerID")
   join employee in ds.Tables["Employees"].AsEnumerable()
       on order.Field<int>("EmployeeID") equals employee.Field<int>("EmployeeID")

Each table connects to the query by joining its primary key to a given field. For instance, Products table is joined using ProductID, Categories is joined using CategoryID, and so on.

Add the following Select statement to build a new anonymous class containing fields specified in query above. Note that the fields may map directly to the table fields or may be calculated.

Visual Basic
C#

Visual Basic

Select New With
  {
    Key .Sales = (detail.Field(Of Short)("Quantity") * CDbl(detail.Field(Of Decimal)("UnitPrice"))) * (1 - CDbl(detail.Field(Of Single)("Discount"))),
    Key .OrderDate = order.Field(Of Date)("OrderDate"),
    Key .Product = product.Field(Of String)("ProductName"),
    Key .Customer = customer.Field(Of String)("CompanyName"),
    Key .Country = customer.Field(Of String)("Country"),
    Key .Employee = employee.Field(Of String)("FirstName") & " " & employee.Field(Of String)("LastName"),
    Key .Category = category.Field(Of String)("CategoryName")}

select new
{
    Sales = (detail.Field<short>("Quantity") * (double)detail.Field<decimal>("UnitPrice")) * (1 - (double)detail.Field<float>("Discount")),
    OrderDate = order.Field<DateTime>("OrderDate"),
    Product = product.Field<string>("ProductName"),
    Customer = customer.Field<string>("CompanyName"),
    Country = customer.Field<string>("Country"),
    Employee = employee.Field<string>("FirstName") + " " + employee.Field<string>("LastName"),
    Category = category.Field<string>("CategoryName")
};

Convert the LINQ query to a list using the ToList() method and assign the resultant to the DataSource property of C1FlexPivotPage class.
- Visual Basic
- C#
- Visual Basic
c1FlexPivotPage1.DataSource = q.ToList()
- C#
c1FlexPivotPage1.DataSource = q.ToList();

Create a default view that gets loaded as the initial view when the application runs.

Visual Basic
C#

Visual Basic

Dim fp = c1FlexPivotPage1.FlexPivotPanel.FlexPivotEngine
fp.BeginUpdate()
fp.RowFields.Add("Country")
fp.ColumnFields.Add("Category")
fp.ValueFields.Add("Sales")
fp.EndUpdate()

var fp = c1FlexPivotPage1.FlexPivotPanel.FlexPivotEngine;
fp.BeginUpdate();
fp.RowFields.Add("Country");
fp.ColumnFields.Add("Category");
fp.ValueFields.Add("Sales");
fp.EndUpdate();

Run the project to observe that the FlexPivotPage control bound to data from tables defined in the C1NWind.mdb database file appears with an initial view.