ComponentOne FlexPivot for WinForms
Task-Based Help / Using LINQ Queries to Add Data in FlexPivot
In This Topic
    Using LINQ Queries to Add Data in FlexPivot
    In This Topic

    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.

    1. Create a new Windows Forms Application project in Visual Studio.
    2. Drag-and-drop FlexPivotPage control (see FlexPivotpage icon in the Toolbox) onto the form.
    3. Switch to the code view and add the following namespaces. 
      Imports System.Data.OleDb
Imports System.Linq

      using System.Data.OleDb;
using System.Linq;
    4. Add the following code in the Forms1.cs constructor to load the data using LINQ query. 
      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);
}
    5. 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. 
      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

Private Sub FlexPivotPage1_Load(sender As Object, e As EventArgs)
End Sub

      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);
}
    6. Add the following LINQ query to connect the FlexPivotPage control with the tables loaded from the database. 
      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.

    7. 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. 
      Select CaseNew 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")
};
    8. Convert the LINQ query to a list using the ToList() method and assign the resultant to the DataSource property of FlexPivotPage class. 
      FlexPivotPage1.DataSource = q.ToList()

      flexPivotPage1.DataSource = q.ToList();
    9. Create a default view that gets loaded as the initial view when the application runs. 
          Dim fp = FlexPivotPage1.FlexPivotPanel.FlexPivotEngine
    fp.BeginUpdate()
fp.RowFields.Add("Country")
fp.ColumnFields.Add("Category")
fp.ValueFields.Add("Sales")
fp.EndUpdate()

      var fp = flexPivotPage1.FlexPivotPanel.FlexPivotEngine;
fp.BeginUpdate();
fp.RowFields.Add("Country");
fp.ColumnFields.Add("Category");
fp.ValueFields.Add("Sales");
fp.EndUpdate();
    10. 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.