Paul Kimmel's Blog

This blog post is in response to a customer query on our community support site at http://community.devexpress.com/forums/p/92876/319857.aspx. The customer wrote about an all too-often scenario. The customer had data from a second table containing data that was not correlated by keys; that is, there exists no natural relationship where a join could be established and a view created. (A preferable solution in a perfect world would be to have correlated data and use views, but I have run into this scenario hundreds of times.) 

The customer said: “I would like to append additional info at the end of each row”. The customer was kind enough to post a graphic of what he was trying to do and after a brief exchange the conversation migrated to code. As it is often easier to communicate with code then to stick to words the solution in this blog is my first attempt at getting him where he wants to be.

A core feature of the XtraPivotGrid is that you can add all kinds of aggregate data fields at the end (or beginning) of each data section. The XtraPivotGrid supports calculating things like the Sum, Min, and Max values. These aggregate behaviors will be automatically calculated. If you want to perform custom operations then you can select the SummaryType Custom and roll your own solution. This includes adding fields that contain unrelated or only quasi-related data. This is a slightly one-off solution—displaying uncorrelated data—which is why we need a little bit of code to get the job done. Figure 1 shows the end result. Then, we’ll walk through the steps to prepare the solution, followed by the code.

Figure 1: Note that the end of the group of data the summary data is followed by the extra, unrelated data.

Using data that is technically unrelated may not make technical sense but that doesn’t mean it won’t make sense to users or that customers won’t want such a solution.

In Figure 1 the Products table data is displayed with the Category ID and Product Name in the row area and the Unit Price in the data area. The total Unit Price is calculated by category and product. (Probably not a practical application—sum of unit prices—but it shows how summary information is automatically available in the XtraPivotGrid.) The three additional summary values could automatically be things like averages, minimum or maximum unit prices, or perhaps the number of items per category. For our scenario however we are adding some arbitrary data representing data that is not technically related but makes sense to a user. Here are the steps for adding additional custom totals followed by one solution for putting data in them.

Adding Custom Totals to a Pivot Grid

The following steps demonstrate how to add custom totals to an XtraPivotGrid and write code that puts programmer-defined values in the custom totals fields.

1. Create a WinForms project
2. Add an PivotGridControl and dock it to the Form
3. Use the PivotGridControl’s smart tags menu to configure the Northwind Products table to the pivot grid; this will add a northwindDataSet, productsBindingSource, and productsTableAdapter to the components tray in Visual Studio
4. Right-click on the pivot grid and select Run Designer
5. Close the designer
6. Click on the Properties Window and select the fieldCategory1 control
7. Click the Custom Totals property to open the designer-see Figure 2
8. In the PivotGridCustomTotal Collection Editor click Add three times to add three custom totals
9. For each of the three custom totals change the Tag properties to 1, 2, 3, respectively and the SummaryType to Custom
10. Click OK to close the Collection Editor
11. Back in the Properties window for the fieldCategoryID1 control—a PivotGridField object—change the TotalsVisibility to CustomTotals
12. Click on the PivotGridControl
13. Switch the Properties window view to the Events tab
14. Double click to implement a CustomCellValue event and a CustomDrawFieldValue event methods
15. Use CustomDrawFieldValue to change the custom summary captions, and use CustomCellValue to add the custom cell values for each custom summary field

Figure 2: Add three custom totals for the CategoryID field to the PivotGridCustomTotal Collection Editor .

The CustomCellValue event is used to do whatever it takes to correlate data where a natural correlation does not exist. In this example I contrived some arbitrary data and used the CategoryID’s to index the list of other data. Listing 1 contains the complete solution.

Listing 1: The custom events modify the custom total fields to contain data that is not ‘exactly’ aggregate values, but logically related to the records in some way—determined by a user scenario.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraPivotGrid;
using DevExpress.Data.PivotGrid;
using System.Reflection;

namespace S319857
{
  public partial class Form1 : Form
  {
    private List<Data> datum;
    public Form1()
    {
      InitializeComponent();

      datum = new List<Data>{
        new Data{ID=1, Cash="100", EftPos="Dummy", HealthFund="H1"},
        new Data{ID=2, Cash="200", EftPos="Dummy", HealthFund="H2"},
        new Data{ID=3, Cash="300", EftPos="Dummy", HealthFund="H3"},
        new Data{ID=4, Cash="400", EftPos="Dummy", HealthFund="H4"},
        new Data{ID=5, Cash="500", EftPos="Dummy", HealthFund="H5"},
        new Data{ID=6, Cash="600", EftPos="Dummy", HealthFund="H6"},
        new Data{ID=7, Cash="700", EftPos="Dummy", HealthFund="H7"},
        new Data{ID=8, Cash="800", EftPos="Dummy", HealthFund="H8"}};
    }

    private void Form1_Load(object sender, EventArgs e)
    {
      // TODO: This line of code loads data into the 
      // 'northwindDataSet.Products' table. You can move, or remove it, as needed.
      this.productsTableAdapter.Fill(this.northwindDataSet.Products);

    }

    private void pivotGridControl1_CustomCellValue(object sender, 
      PivotCellValueEventArgs e)
    {
      int ID = Convert.ToInt32(e.GetFieldValue(fieldCategoryID1));

      Data obj = datum.Find(d => d.ID == ID);

      if ((e.SummaryType == PivotSummaryType.Custom) 
        && (e.DataField == fieldUnitPrice1))
      {
        if (Convert.ToInt32(e.RowCustomTotal.Tag) == 1)
          e.Value = obj.Cash;
        else if (Convert.ToInt32(e.RowCustomTotal.Tag) == 2)
          e.Value = obj.EftPos;
        else if (Convert.ToInt32(e.RowCustomTotal.Tag) == 3)
          e.Value = obj.HealthFund;
      }
    }

    private void pivotGridControl1_CustomDrawFieldValue(object sender, 
      PivotCustomDrawFieldValueEventArgs e)
    {
      if (e.Info.Caption.Contains("Custom"))
      {
        if (Convert.ToInt32(e.CustomTotal.Tag) == 1)
          e.Info.Caption = "CASH";
        else if (Convert.ToInt32(e.CustomTotal.Tag) == 2)
          e.Info.Caption = "EFTPOS";
        else if (Convert.ToInt32(e.CustomTotal.Tag) == 3)
          e.Info.Caption = "Health Fund";      
      }
    }
  }


  public class Data
  {
    private int iD;
    public int ID
    {
      get { return iD; }
      set { iD = value; }
    }

    private string cash;
    public string Cash
    {
      get { return cash; }
      set { cash = value; }
    }

    private string eftPost;
    public string EftPos
    {
      get { return eftPost; }
      set { eftPost = value; }
    }

    private string healthFund;
    public string HealthFund
    {
      get { return healthFund; }
      set { healthFund = value; }
    }
  }

}

The CustomCellValue event uses the event argument’s GetFieldValue and the control who’s value you want to get as a parameter to get the current field value which is the CatorgoryID in this instance. datum.Find uses a Lambda expression to find the right Data object—representing arbitrary data. Finally, the if..conditional uses the predetermined Tag values to figure out which total’s fields get which data. CustomDrawField uses the Tags again to modify the custom total’s fields captions.

If you have naturally correlated data—disparate tables with primary and foreign keys—then you can use views and joins to populate the PivotGridControl. If you have a customers that have legacy data or just want to show something else then you can exploit the custom totals feature and events to get that data in the PivotGridControl.

Thanks for choosing Developer Express.

As a technophile who also happens to write I know how hard it can be when the documentation for a new technology is squiffy. Squiffy being you can’t figure it out from existing documentation and blogs because too many details are left out. This what happened when I started looking at EF4 and Model Defined Functions. A model “declared” function is one that you import from your storage provider, like a SQL Server stored procedure. A model defined function is one that is completely defined in your model. EF4 supports model defined functions but the documentation leaves out too many details that explain exactly how you get this to work. In this blog I will walk you through creating a model defined function in EF4 and then binding the results to our XtraPivotGrid.

An overview of the process requires some data you want to show in a Pivot Grid, an idea for tweaking the result set, and Entity Framework 4 model, and a custom query defined in the model only. The two basic steps are to add an XtraPivotGrid control to a WinForms project and add an ADO.NET Entity Data Model. (This example is based on Visual Studio 2010 and DevExpress controls—I used the a beta version of DevExpress controls for 10.2.) There are a lot of blogs and articles on EF Models but here is the nuts and bolts. (If you know how to do this step skip the next section.)

Adding an Entity Framework Model to Your WinForms Application

An Entity Framework Data Model is created from an applet in the Add New Items dialog. In its simplest form an EF model is simply some code generated classes that create entities based on some storage item like a database table. The basic idea is that once you have the data model interaction with the data store is easier. (We all refer to this as a data access layer in a general sense.)

To create the entity data model Follow these steps to create:

1. Create a WinForms project. (This will work in other kinds of projects too.)
2. Click Project|Add New Item
3. Select the ADO.NET Entity Data Model from the Add New Item dialog and name it NorthwindModel
4. Click Add
5. In the Entity Data Model Wizard (that starts up) select Generate from database and click Next
6. Choose an existing Northwind connection or click New Connection to add a connection
7. Use the defaults for everything else in this step and click Next
8. In the Choose Your Data Objects step expand Tables and select the Products table
9. Click Finish

That’s all there is to it. After you click Finish the .edmx file and designer will be open. An entity model is the important element you need.

Defining a Model Defined Function

A model defined function is one that exists only in your model. A model declared function is one you import from your server. We will be creating a model defined function. For our scenario we will add a model defined function that filters on ReorderLevel. It is worth stating that you could add such a trivial filter right in the LINQ query, but we will create the model defined function and use such a simple example to focus on the mechanics of model functions. As a general rule I would only add a model defined function if it gave my solution some extra leverage that might be hard for my developers to do themselves with LINQ like joins.

The key to adding a model defined function is to modify the model XML, but a lot of the blogs don’t tell you which XML to modify and leave out a few steps. You should be able to reproduce this process because I am not going to leave any steps out. To create the model defined function you need to modify the SSDL in the EDMX file and then import the function and call it with your LINQ query, binding the results to the PivotGrid. Here they are:

1. Right click on the .EDMX file in the solution explorer and select Open With
2. Choose the XML (Text) Editor—yep, we gotta modify the EDMX file directly
3. The EDMX file will look like Listing 1. Find the SSDL section
4. At the end of the SSDL section just before the closing </Schema> tag add a <Function> tag
5. Add a Name attribute Name=”GetProductsByReorderLevel”
6. Add an IsComposable=”false” attribute
7. In the <Function> tag add a <CommandText> tag and write your vanilla SQL inside of the <CommandText></CommandText> tag; I used select * from Products where ReorderLevel > @p1
8. Make sure you use an @ in the query itself or the framework will think the right hand side operand is a column
9. After the <CommandText>—parameters are expected to follow—add a <Parameter> tag with a Name=”p1”, Mode=”In”, and Type=”int” attributes. (Make sure you don’t use @ in the parameter Name attribute; doing so will kill the code generation.)

Here is the added XML block and Listing 1 shows the complete EDMX listing.

<Function Name="GetProductsByReorderLevel" IsComposable="false">
  <CommandText>
   select * FROM Products
   where ReorderLevel > @p1;
  </CommandText>
  <Parameter Name="p1" Mode="In" Type="int" MaxLength="75" />
</Function>

Listing 1: The complete .EDMX listing with the SSDL model defined function.

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <Schema Namespace="NorthwindModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
        <EntityContainer Name="NorthwindModelStoreContainer">
          <EntitySet Name="Products" EntityType="NorthwindModel.Store.Products" store:Type="Tables" Schema="dbo" />
        </EntityContainer>
        <EntityType Name="Products">
          <Key>
            <PropertyRef Name="ProductID" />
          </Key>
          <Property Name="ProductID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
          <Property Name="ProductName" Type="nvarchar" Nullable="false" MaxLength="40" />
          <Property Name="SupplierID" Type="int" />
          <Property Name="CategoryID" Type="int" />
          <Property Name="QuantityPerUnit" Type="nvarchar" MaxLength="20" />
          <Property Name="UnitPrice" Type="money" />
          <Property Name="UnitsInStock" Type="smallint" />
          <Property Name="UnitsOnOrder" Type="smallint" />
          <Property Name="ReorderLevel" Type="smallint" />
          <Property Name="Discontinued" Type="bit" Nullable="false" />
        </EntityType>
        <Function Name="GetProductsByReorderLevel" IsComposable="false">
          <CommandText>
            select * FROM Products
            where ReorderLevel > @p1;
          </CommandText>
          <Parameter Name="p1" Mode="In" Type="int" MaxLength="75" />
        </Function>
      </Schema>
    </edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema Namespace="NorthwindModel" Alias="Self"
      xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation"
      xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
        <EntityContainer Name="NorthwindEntities" annotation:LazyLoadingEnabled="true">
          <EntitySet Name="Products" EntityType="NorthwindModel.Product" />
        </EntityContainer>
        <EntityType Name="Product">
          <Key>
            <PropertyRef Name="ProductID" />
          </Key>
          <Property Name="ProductID" Type="Int32" Nullable="false"
          annotation:StoreGeneratedPattern="Identity" />
          <Property Name="ProductName" Type="String" Nullable="false"
          MaxLength="40" Unicode="true" FixedLength="false" />
          <Property Name="SupplierID" Type="Int32" />
          <Property Name="CategoryID" Type="Int32" />
          <Property Name="QuantityPerUnit" Type="String" MaxLength="20"
          Unicode="true" FixedLength="false" />
          <Property Name="UnitPrice" Type="Decimal" Precision="19" Scale="4" />
          <Property Name="UnitsInStock" Type="Int16" />
          <Property Name="UnitsOnOrder" Type="Int16" />
          <Property Name="ReorderLevel" Type="Int16" />
          <Property Name="Discontinued" Type="Boolean" Nullable="false" />
        </EntityType>
      </Schema>
    </edmx:ConceptualModels>
    <!-- C-S mapping content -->
    <edmx:Mappings>
      <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs">
        <EntityContainerMapping StorageEntityContainer="NorthwindModelStoreContainer"
        CdmEntityContainer="NorthwindEntities">
          <EntitySetMapping Name="Products"><EntityTypeMapping
          TypeName="NorthwindModel.Product"><MappingFragment StoreEntitySet="Products">
            <ScalarProperty Name="ProductID" ColumnName="ProductID" />
            <ScalarProperty Name="ProductName" ColumnName="ProductName" />
            <ScalarProperty Name="SupplierID" ColumnName="SupplierID" />
            <ScalarProperty Name="CategoryID" ColumnName="CategoryID" />
            <ScalarProperty Name="QuantityPerUnit" ColumnName="QuantityPerUnit" />
            <ScalarProperty Name="UnitPrice" ColumnName="UnitPrice" />
            <ScalarProperty Name="UnitsInStock" ColumnName="UnitsInStock" />
            <ScalarProperty Name="UnitsOnOrder" ColumnName="UnitsOnOrder" />
            <ScalarProperty Name="ReorderLevel" ColumnName="ReorderLevel" />
            <ScalarProperty Name="Discontinued" ColumnName="Discontinued" />
          </MappingFragment></EntityTypeMapping></EntitySetMapping>
        </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>
  </edmx:Runtime>
  <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
  <Designer xmlns="http://schemas.microsoft.com/ado/2008/10/edmx">
    <Connection>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
      </DesignerInfoPropertySet>
    </Connection>
    <Options>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="ValidateOnBuild" Value="true" />
        <DesignerProperty Name="EnablePluralization" Value="True" />
        <DesignerProperty Name="IncludeForeignKeysInModel" Value="True" />
      </DesignerInfoPropertySet>
    </Options>
    <!-- Diagram content (shape and connector positions) -->
    <Diagrams>
      <Diagram Name="NorthwindModel">
        <EntityTypeShape EntityType="NorthwindModel.Product" Width="1.5" PointX="0.75"
        PointY="0.75" Height="2.9417936197916665" IsExpanded="true" />
      </Diagram>
    </Diagrams>
  </Designer>
</edmx:Edmx>

I intentionally did not use code formatting and re-paginated the XML to fit the page, but don’t do that. Just add the <Function> block in bold in Listing 1.

One might think that is all there is too it, but the Entity Framework maps at the the conceptual storage level, CSDL, and we added the model function at the storage level. You need to map the function at the conceptual level next.

Before you continue close the .edmx file. Make sure you save it. Then, double-click the .edmx file to open the designer, right click, and click Validate to make sure your XML is good.

Mapping the Model Defined Function at the Conceptual Level

Your model-defined function at the SSDL should now be available. To view the model select View|Other Windows|Entity Data Model Browser. If everything is going well so far your function should be available in the Model Browser under Northwind.Store|Stored Procedures—see Figure 1.

Figure 1: The model-defined function at the storage level.

The next step is to import the function, which in essence adds an entry to the CSDL level and let’s you call it. To add the function at the conceptual level right click on the NorthwindModel.edmx file and follow these steps:

1. Click Add|Function Imports
2. Using Figure 2 as a visual guide, enter a function name. I used GetProductsByReorderLevel, but if you have funky Sproc names then you can make them more code friendly here.
3. Select the Stored Procedure Name
4. Change the Returns a Collection of to Entities and select the Product entity type
5. Click OK

Now if you check the Model Browser you will see that the Function Imports section contains a usable CSDL function—see Figure 3.

Figure 2: Add Function Import to add the function to the CSDL-conceptual level—so you can call it with LINQ to Entities.

Figure 3: The function GetProductsByReorderLevel can now be called from LINQ to Entities.

Binding the PivotGrid to the Model Defined Function

Unfortunately the data source wizard in Visual Studio 2010 doesn’t know about model defined functions so we can’t use the wizard to get the data from the model defined function. However, since the model-defined function is mapped to the Product entity you can use the Data Source Configuration Wizard to bind to an Object. Follow these steps to configure the PivotGrid with Product entity data.

1. Click the PivotGridControl’s smart tags menu, click Choose Data Source|Add Project Data Source
2. In the Data Source Configuration Wizard choose Object
3. Click Next
4. Expand the project’s node
5. Expand the first child node and check Product
6. Click Finish

Figure 4: Let the Data Source Configuration Wizard configure the XtraPivotGrid’s fields for you.

In the code behind you need just a couple of lines in the code behind and you are off to the races.

Listing 2 initializes the NorthwindEntities object, calls the model-defined function, and updates the productBindingSource.DataSource to point to the new data. It takes some work to get here but most of the effort was completed by wizards with just a tiny bit of XML editing and a very little bit of code. Since code is generally the most time consuming thing and the most expensive this is actually a beautiful thing.

Listing 2: Once you get your entities right it takes just a little bit of code to use them.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace ModelDefinedFunctionAndPivotGrid
{
  public partial class Form1 : Form
  {
    NorthwindEntities entities = new NorthwindEntities();
    public Form1()
    {
      InitializeComponent();

      var results = from product in entities.GetProductsByReorderLevel(10)
                    select product;
      productBindingSource.DataSource  = results;
    }
  }
}

I’d love to see an SSDL designer for model defined functions. I’d also like to eliminate the leap from SSDL to CSDL. It doesn’t make a lot of sense that one would add a function they didn’t want to use from LINQ, but EF4 is a big improvement over earlier versions. Figure 5 shows the running application after I have moved some fields around at runtime.

Figure 4: The XtraPivotGrid with data from a model-defined function and a single LINQ query.

I manage a TODO list with Microsoft Outlook. If it weren’t for Outlook and Calendar items synched on my iPhone I would forgot a lot of detailed tasks. Right now there are so many new features for 10.2 that I want to cover that my task list has gotten pretty long and is growing and projecting out for several weeks to come. It is long enough right now that I wonder if I am going to get some sleep this weekend or get to that 30 foot trunk of a tree that the wind just knocked over into my yard. The last week or so sleep has been at the bottom of the TODO list—although I did wheeze through 30 minutes of basketball at the gym last night.

The feature on the menu today is a single feature, the CustomFilterPopupItems event for the XtraPivotGrid. The XtraPivotGrid is a composable, searchable, sortable, filterable grid that supports data mining type tasks for your end users. The CustomFilterPopupItems event is new in 10.2 and is fired when the user displays the filter list for a field. Figure 1 shows the demo application with the filter list visible. A user brings up the filter list by clicking on the little filter button associated with a given field.

Figure 1: Pivot Grid Control with the filter items visible for the Category Name field. 

This demo handles the CustomFilterPopupItems event for the XtraPivotGrid control. Normally when you display the filter and deselect items those elements are removed from the result set—the data area in the grid and the row area. For example, if you expand the Category Name filter and uncheck Show All and check Beverages then the default behavior is that the products that are beverages are the only products shown .

By default the non-visible data items are available in the filter still. This sample demonstrates how to hide the checked filter items that aren’t visible in the grid. For example, in our scenario by checking the Beverages Category only beverages are visible in the grid, and with the sample code for CustomFilterPopupItems in Listing 1 the invisible items are also removed from the filter list. For instance, if you drop the Category Name filter in our scenario then the event code removes items that for all intents and purposes won’t be displayed anyway.

Figure 2 shows the Beverages scenario without the event code, and Figure 3 shows the scenario with the event code.

  
Figure 2: Notice that even though Beverages is the only Category checked all products are visible in the Product Name filter.

Figure 3: With the CustomFilterPopupItems event (as written in Listing 1) invisible items are removed from the popup filter list.

Listing 1: Using the CustomFilterPopupItems event to hide filter items that aren’t being displayed in the data grid based on other filter selections.

using System;

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraPivotGrid.Data;

namespace EmptyWinApp {
    public partial class Form1 : Form {
        public Form1() {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e) {
            // TODO: This line of code loads data into the 'productReports._ProductReports' 
            // table. You can move, or remove it, as needed.
            this.productReportsTableAdapter.Fill(this.productReports._ProductReports);
        }

        private void pivotGridControl1_CustomFilterPopupItems(object sender, 
        DevExpress.XtraPivotGrid.PivotCustomFilterPopupItemsEventArgs e) 
        {
            List<object> values = e.Field.GetVisibleValues();
            values.Sort();
            for(int i = e.Items.Count - 1; i >= 0; i--) {
                if(e.Items[i].IsChecked == true && values.BinarySearch(e.Items[i].Value) < 0)
                    e.Items.RemoveAt(i);
            }
        }
    }
}

We love customers at DevExpress, so when one of you guys writes me from the trenches I drop what I am doing and try to get a response out as soon as possible.

Today, while working on a blog post for the ASP.NET Pivot Grid Control’s (XtraPivotGrid) CustomFilterPopupItems event for 10.2 a customer sent me a message through the newsgroups. He wanted to know if it was possible to use LINQ not as a datasource for an XtraGrid but to manipulate data in an XtraGrid. As is customary I said if you send me a trimmed down demo then I will tweak it for you. This approach is mostly to ensure that I understand what the customer is trying to do. In this case I had a pretty good idea of what he was trying to accomplish so I whipped up a solution and some explanatory text that gives one an idea of how to approach a solution using LINQ.

LINQ uses deferred execution. This means LINQ won’t actually touch the data in the query until you start accessing it. A LINQ query itself runs when the data is accessed not at the time (or line) where the query exists unless you touch it in the query, like calling an extension method like First, or Take on the tail end of the query. It is also worth noting that LINQ is for querying not updating data necessarily, so updating something else in a LINQ query requires using side effects.

For the solution I demonstrated the side effects approach in a compiler directive #if and a preferable, more reader friendly solution in the #else condition. Both approaches are explained along with some sidebar discussions about the code in general—see Listing 1.

Listing 1: Touching data in an XtraGrid with a LINQ query.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace QueryXtraGridWithLINQ
{
  public partial class Form1 : Form
  {
    public Form1()
    {
      InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
      // TODO: This line of code loads data into the 'northwindDataSet.Orders' table. 
      // You can move, or remove it, as needed.
      this.ordersTableAdapter.Fill(this.northwindDataSet.Orders);

    }

    private void gridControl1_Click(object sender, EventArgs e)
    {
#if   TOGGLE
      // do something with LINQ on side effects but not a good idea
      Func<DataRow, DateTime> SideEffects = row => 
      {
        DateTime newDateTime = Convert.ToDateTime(row["OrderDate"]).AddYears(1);
        object[] data = row.ItemArray;
        data[3] = newDateTime;
        northwindDataSet.Orders.LoadDataRow(data, false);
        return newDateTime;
      };
      
      // you could try this but because of deferred execution side effects 
      // won't actually happen until you touch the data
      var orderDates = from DataRow row in northwindDataSet.Orders.Rows
                       select new {OrderID=row["OrderID"], OrderDate=SideEffects(row)};

      // this is mumbo jumbo - esoterrorism
      orderDates.ToArray();
      int rowsUpdated = ordersTableAdapter.Update(northwindDataSet.Orders);
      gridControl1.Refresh();
      MessageBox.Show(string.Format("rows {0} updated", rowsUpdated));
#else
      
      // a better solution
      var orderDates = from DataRow row in northwindDataSet.Orders.Rows
                       select new {OrderID=row["OrderID"], 
                       OrderDate=Convert.ToDateTime(row["OrderDate"]).AddYears(1), 
                       RowData=row};

      foreach(var r in orderDates)
      {
        object[] data = r.RowData.ItemArray;
        // get desired data element to change - this is the least reliable way
        data[3] = r.OrderDate;
        northwindDataSet.Orders.LoadDataRow(data, false);
      }

      Validate();
      ordersBindingSource.EndEdit();
      int rowsUpdated = ordersTableAdapter.Update(northwindDataSet.Orders);
      gridControl1.Refresh();
      MessageBox.Show(string.Format("rows {0} updated", rowsUpdated));
#endif
    }
  }
}

In the #if TOGGLE part of the code a generic delegate (Func) and a Lambda Expressions are used to update an arbitrary column’s value, in this instance OrderDate. The DataRow comes in, the new data is set, and data is updated with LoadDataRow. The Lambda Expression returns the new data and time value, so it can be assigned to the anonymous type’s projection value OrderDate in the select clause of the LINQ query. Using data[3] is risky so in general a better approach to identifying the correct column and table should be used. But this approach has bigger problems.

Func SideEffects has side effects, that is, it changes data. Doing unexpected things is generally considered a no no. Worse still the LINQ query (because LINQ uses deferred execution) won’t even run unless we touch it. In this case touching it is accomplished by calling orderDates.ToArray. ToArray actually causes the query to execute. This approach while functional is esoteric to say the least. So, it is possible to use LINQ directly to change data, but I wouldn’t use this approach.

The #else block contains a better approach.

In the #else block LINQ is used to get the data to be changed, but a for each is still used to actually effect the change. This is a better solution because we need to touch the data to execute the query anyway, and in the LINQ query itself updates were not intended. of course, as is shown in the projection—select new—you can update the results in the new, projected data.

Next, the foreach statement iterates over all of the data and updates the OrderDate value. Storing the DataRow in the projection and using LoadDataRow is a nice, convenient way to update the desired rows. If you wanted to update just some rows then use a where clause in the LINQ query to just get the data you wanted. TableAdapter.Update and Refresh will ensure that the database and the GridControl has the new values.

Using the smart tags menu for the ordersBindingSource an Update method was added to the XSD for the solution. Refer to the code attachment for the complete solution.

Believe it or not customers do write DevExpress and evangelists directly and we love to help them. Its fun to play in the puzzle palace—someone needs help, maybe it is tricky for that person, maybe it is tricky for me too and I get to help them figure out a solution. Recently someone from the ‘hood (East Lansing) asked me if it was possible to dynamically change the data source of an ASPxGridView on an AJAX call, a callback. The challenge is that the ASPxGridView is going to have one set of columns on a page load for the current datasource and then the callback will try to bind an alternate dataset. You could just use a postback. You could use multiple grids and multiple datasources and play control hide and seek, but this customer stated that he had ten possible datasets and didn’t want to manage ten pairs of datasources and ASPxGridViews. (We want what we want and we need what we need.)

By default the ASPxGridView’s AutoGenerateColumns is set to true. My solution reads the schema of the new datasource, dynamically adds the columns to the ASPxGridView, indicates which field is the primary key, and then binds the datasource. So, if you want an arbitrary number of datasources displayed on your web pages then you can try this solution. Session is used to manage the current datasource, and the Page_Load relads the current datasource, so updates should work too.

The solution—the code behind is shown in Listing 1—uses a ScriptManager, two buttons, a single ASPxGridView, and two AccessDataSources. Click one or the other of the buttons and the datasource is changed.

Listing 1: The code-behind for the solution.

Imports System.Data.OleDb
Imports System.Data
Imports DevExpress.Web.ASPxGridView


Partial Class Default2
    Inherits System.Web.UI.Page

  Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
    DataSource = AccessDataSource1
    BuildGridView()

  End Sub

  Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
    DataSource = AccessDataSource2
    BuildGridView()
  End Sub


  Private Sub BuildGridView()
    ASPxGridView1.Columns.Clear()
    ASPxGridView1.AutoGenerateColumns = False

    Dim sql As String = DataSource.SelectCommand
    Dim table As DataTable = New DataTable()

    Using connection As OleDbConnection = New OleDbConnection(DataSource.ConnectionString)
      connection.Open()
      Dim command As OleDbCommand = New OleDbCommand(DataSource.SelectCommand, connection)
      Dim Adapter As OleDbDataAdapter = New OleDbDataAdapter(command)
      Adapter.FillSchema(table, SchemaType.Source)
    End Using

    For Each column As DataColumn In table.Columns
      Dim co As GridViewDataColumn = New GridViewDataColumn()
      co.FieldName = column.ColumnName
      ASPxGridView1.Columns.Add(co)

    Next

    If (table.PrimaryKey.Count > 0) Then
      ASPxGridView1.KeyFieldName = table.PrimaryKey(0).ColumnName
    End If

    ASPxGridView1.DataSource = DataSource
    ASPxGridView1.DataBind()
  End Sub

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If (DataSource Is Nothing) Then Return
    BuildGridView()
  End Sub

  Private _dataSource As AccessDataSource
  Private KEY As String = "DATASOURCE"
  Public Property DataSource As AccessDataSource
    Get
      If (Session(KEY) Is Nothing) Then Return Nothing
      Return Session(KEY)
    End Get
    Set(ByVal Value As AccessDataSource)
      Session(KEY) = Value
    End Set
  End Property
End Class

The solution I uploaded for the demo uses the beta version 10.2. You can switch all references to the newer version with find and replace, but it is more fun to download a trial version of DXperience. The two buttons are used for the AJAX triggers.

I love new software and new features. It is like the best birthday present. Many of you have used our ASP.NET Pivot Grid-Table know that the ASPxPivotGrid control is highly composable at design time and runtime. You as well as your end users can move field data around and and change the way the data appears; rows can be move to columns, columns to rows, fields to filter and data areas, and by doing so the ASPxPivotGrid will display your data in ways that is meaningful to your end users.

Now the DevExpress ASP.NET Pivot Grid—in 10.2—can be paired with the ASPxPivotCustomizationControl—see Figure 1—and the user can manage column, data, row, filter, and hidden fields from a central control.  Check Defer Layout Update and the user can make several changes and apply the updates when all desired changes have been made.

To use use the ASPxPivotCustomizationControl place it and an ASPxPivotGrid control on a web form. Set the ASPxPivotCustomizationControl’s MasterControlID to point at the ASPxPivotGrid and you are off to the races.

In the next few days I will post some sample applications that demonstrate how to use the ASPxPivotCustomizationControl and some of the other new features of 10.2.

Figure 1: The ASPxPivotCustomizationControl let’s you manage the ASPxPivotGrid from a central location, sort of like a control panel for the ASPxPivotGrid.

I was working on a project for Lucent Technologies in 1998. I was reading Jim McCarthy’s “Dynamics of Software Development” at the time came across the phrase “Don’t Flip the Bozo Bit”, which basically means don’t write off people quickly, everyone may have something to offer. The phrase resonated at the time because we had a guy on the team dubbed Whizzy Whizardo; everything was a UML/design problem for this guy except he didn’t really know UML. (I am not sure anyone knew the UML that well in 1998.) Whizzy Whizardo got his bozo bit flipped big time and our variation on the phrase was “Don’t Bite the Flip Bozo”. Whizzy got sacked—I had nothing to do with it, I liked the guy—the project was a huge success and ten years later the clown-fear phenomena seems to be growing. There is a lot of clown fear in the world.

I work out of Michigan but am in LA this week. When in LA I pester everyone at the home office about what is knew, and what do they think is good blog fodder. it is a shortcut in a sense, but the point is getting information out to you faster.

The first thing is that we have a new dot-release in beta, 10.2. I am working with Ivan—a developer to plumb some of the new features for 10.2—and today I came across the regular expression feature in the RichEditControl. My first thought was that is pretty cool. My next thought was how many end users are going to know how to write a regular expression. Not many. (I wonder how many developers can write regular expressions well.)

Let’s talk about first fruits, first. The regular expression feature is tucked out of the way in the Find and Replace dialog, effectively making it an innocuous feature—one that can be ignored by most users. And, based on my experience regular expressions are great if you have complicated editing tasks and big documents, but they aren’t needed that often. However, if you are writing tools for power users then they will appreciate the Regex capabilities of the XtraRichEdit control and will take then time to use them. Users somewhere in between may need some coaching mechanisms, sample expressions, or help documentation for your product built on our product, so knowing your audience will help you decide if Regex for rich text editing is an issue or a non-issue.

To that end let’s break it down into two groups: what to do with the Regex feature of the XtraRichEdit control for end users who are not power users and everybody else—inspired mid-level users and power users. (Remember developers are by implication power users and they will take the initiative to figure our Regexes if they need them and use regular expressions when they want to.)

Regular Expressions in the XtraRichEdit Control for End Users

As the XtraRichEdit control adds new features like HTML table support and support for regular expressions it becomes clearer that this control pushing the boundaries of capabilities. By adding these features you will be able to build more powerful applications that use rich text editing. So, the objective is remove barriers by adding capabilities without raising the bar to entry.

The regular expression language has a very terse syntax but is a powerful shorthand notation for performing very complicated tasks like pattern matching. The challenge for users and developers alike is the terseness. I suspect for this reason a regular expression capability might be overlooked by a casual end user and its placement—see Figure 1—in the Find And Replace dialog is unobtrusive.

Figure 1: Regular expressions are supported in the XtraRichEdit control in 10.2.

The casual observer may not even notice the Regular expression checkbox. That’s fine because the casual observer may have a hard time figuring out how to write a regular expression. It is good that regular expression support (which can be managed programmatically too) is tucked away, but there is a lot of power in that one little checkbox, assuming you need a lot of power.

I’d like to see an option in the properties window to disable this feature but have it on by default. Since 10.2 is in beta we’ll see how that shakes out.

Regular Expressions for Power Users and Developers

Writing controls for consumption by programmers is sort of a balancing act. The trick is to create something that can be consumed and tweaked by all without having too many limiting factors. Many programmers wanted HTML table support and it ended up in the product. Programmers building power tools or advanced editors may want or really need regular expression support. Visual Studio’s find and replace feature supports regular expressions, but MS-Word’s find and replace does not.

If you are writing applications with RTF support for developers then they will likely pick up on the regex feature in the XtraRichEdit control and have an idea how to use it. These users at a minimum will know how to Google for it and know what to look for. If you are writing applications with RTF support for normal human beings but have some power users and want to promote the regular expression features—as a selling point—then you may need to give them an idea how to use this feature. Also, if you want to use regular expression support behind the scenes then you can. Let’s split the next two sub-sections into RTF regex support for developers and RTF regex support for power users.

Using Regular Expressions with the XtraRichEdit Control in the Code Behind

Calls to regular expressions can be called programmatically through the Document API, a sub-property/feature of the RichEditControl. The example in Listing 1 loads a text document (with no special formatting), permits you to enter some text or a regular expression and then highlights all matches. The sample uses RichEditControl.Document.FindAll which accepts a regular expression, ChangeFontBackColorCommand to change the background color of the matching items, and StartOfDocumentCommand to return the cursor to the start of the document.

Listing 1: Using DevExpress.XtraRichEdit.Commands and Regular Expressions to implement a Highlight All Search Feature for the XtraRichEdit control.

Imports System.Runtime.InteropServices
Imports System.Reflection
Imports System.IO
Imports System.Text.RegularExpressions
Imports DevExpress.XtraRichEdit.API.Native
Imports DevExpress.XtraRichEdit.Commands
Imports DevExpress.Utils.Commands


Public Class Form1

  Private Sub Form1_Load(ByVal sender As System.Object,
  ByVal e As System.EventArgs) Handles MyBase.Load

    RichEditControl1.Text = File.ReadAllText("..\..\RandomText.txt")

  End Sub

  Private Sub SimpleButton1_Click(ByVal sender As System.Object,
  ByVal e As System.EventArgs) Handles SimpleButton1.Click

    Dim regex As Regex = New Regex(MruEdit1.Text)
    Dim range() As DocumentRange = RichEditControl1.Document.FindAll(regex)

    For Each item In range

          RichEditControl1.Document.Selection = item

          Dim command As ChangeFontBackColorCommand = New ChangeFontBackColorCommand(RichEditControl1)
          Dim state As ICommandUIState = command.CreateDefaultCommandUIState
          state.EditValue = Color.Yellow
          command.ForceExecute(state)

    Next

    Dim startOfDocCommand As StartOfDocumentCommand = New StartOfDocumentCommand(RichEditControl1)
    startOfDocCommand.Execute()

    'DocumentRange[] FindAll(Regex regex);
    'DocumentRange[] FindAll(Regex regex, DocumentRange range);
    'int ReplaceAll(Regex regex, string replaceWith);
    'int ReplaceAll(Regex regex, string replaceWith, DocumentRange range);
  End Sub
End Class

The Form_Load event loads a random text document. This could just as easily be an RTF or HTML document. The SimpleButton click event uses the MruEdit control’s text property as a regular expression. Remember a regular expression can be plain text too. Then, RichEditControl1.Document.FindAll returns an array of DocumentRange objects reflecting the found matches. The for each loop updates the current selection with each DocumentRange and uses ChangeFontBackColorCommand to highlight that section of the document. After all selections are made the caret is moved to the start of the document. Refer to Figure 2 for a visualization of the running application.

 
Figure 2: In the demo the regular expression \w+at looks for strings of letters preceding the letters ‘a’ and ‘t’.

The commented text at the end of the function show some other Document API methods that you can use with regular expressions.

Defining a Regular Expression in the Find and Replace Dialog

The biggest part about regular expressions in the XtraRichEdit control is that you may have to support the occasional power user of your product. If you are using DevExpress tools to build developer tools then your users will probably know how to use regular expressions or at least Google for help. An office or desktop power user may think it is cool to have regular expressions but not know how to get started. For those users you may want to add something to your product’s documentation, give the power user a copy of Dan Appleman’s  Regular Expressions with .NET, pre-define some common regular expressions or sit with the power users and give them a little training. Which way you decide to go depends on how you write the customer agreement.

The picklist for regular expressions in the RichEditControl’s Find and Replace dialog has a few regex commands, but there are an infinite combination of regular expressions so such a list is not going to get a regex neophyte very far. (Of course, 10.2 is still in beta so we’ll see what the final form of this feature is.) Ultimately possible refinements to the beta implementation may be to permit turning regexes off and pre-canning regular expressions in the MruEdit control for the find dialog. (My next blog gives you a hack for stuffing the Find and Replace MruEdit control and hiding the regex checkbox.)

 
Figure 3: The picklist for regular expressions in the RichEditControl’s Find and Replace dialog.

I will be the first to admit that the DevExpress controls have a lot of properties, a lot of designers, and there is a lot to learn. To say that the DevExpress controls are feature rich in short is am understatement. I know this because I work every day trying to learn them all. I work on learning them, acquiring knowledge of them as extemporaneous knowledge so that when I run into customers at conferences I can offer assistance in a timely manner. This also explains why more frequently I see want ads that ask for Developer Express experience specifically. (For example, I just got an email from Domino in Ann Arbor; Dominos is look for just such a candidate. Contact Todd Bohlen at todd.bohlen@dominos.com if you are interested.)

I don’t mean to say that using our controls is hard. for the most part the default settings will get you up and running without a lot of tweaking, and the LookAndFeel feature will help you get a professional looking skin with one property setting. However, if you want to be an expert then you have some work ahead of you. To help DevExpress is cranking out new features all the time and to keep our customers going we are cranking out videos, blogs, tweets, and attending conferences all the time too. The way this works is the product group evangelists imagineer something or we get a custom query and write a blog in conjunction with the support answer to make sure the information gets as broad an audience as possible. Of course, we encourage our customers to post queries to support to help us make the products better, but I also tell people at conferences if you need help feel free to contact me directly. In response to a customer query one of our developers suggest I explore some of the features of exporting data to Excel from an XtraPivotGrid, and they had an example already to go.

MS-Excel is widely used by office workers and developers at the behest of customers. I know this because I wrote a revised edition of Excel 2003 VBA Programmer’s Reference in 2003 and it is still selling several hundred copies every quarter.

The example forwarded to me demonstrates hwo to manually create some data, but any data will do. The example also shows how to programmatically set a bunch of properties to tweak the WYSIWYG view of the data in Excel. For blogs screen shots are good and code works well because you can read the code, copy it, or download it. (For videos, I generally prefer to use designers because they play better in a video format.)

The customer query in question asked about getting rid of some of the header data for the Excel export, so that is included in the code sample. The data is just some make believe data, but collectively the code gives you an idea of just a few of the property-features that are available. When you see a property chain like pivotGridControl1.OptionsPrint.PrintFilterHeaders what you are seeing are nested properties. In this instance it means that you should look in the PivotGridControl property list for OptionsPrint, expand that and find PrintFilterHeaders. In feature rich controls like ours there are going to a lot of nested properties; this is done to help organize by property groups.

Listing 1 contains the code sample that produces the basic WinFoms GUI shown in Figure 1, and when the data is exported the results will look like Figure 2. An overview of the code is provided after Listing 1.

Figure 1: The GUI with the data added by the PopulateTable method.

Figure 2: The data output to MS Excel; notice that the selected cell A1 (or that general area) doesn’t include the data fields represented by the ellipses in Figure 1.

The design time view has an XtraPivotGrid, a button, and a DataSet on it. Follow these steps to create the DataSet’s DataTable structure.

1. Select the DataSet and display the Properties window
2. Open the DataSet’s Tables Collection Editor—see Figure 3
3. Click Add to add a DataTable
4. Change the TableName property to Data
5. Click on the Table’s Column property to open the Columns Collection Editor—see Figure 4
6. Click the Add button to Add three members, naming them Name, Date, Value, respectively
7. Change Name’s DataType to System.String
8. Change Date’s DataType to System.DateTime
9. Change Value’s DataType to System.Decimal
10. Close the Column’s Collection Editor
11. Close the Tables Collection Editor
12. Assign DataSet1 to the PivotGridControl’s DataSource property
13. Assign the Data table to the PivotGridControl’s DataMember property

Figure 3: Use the Tables Collection Editor to manually add tables to a DataSet.

Figure 4: Use the Columns Collection Editor to manually add columns to a DataTable.

After completing steps 1 through 13 above right-click on the PivotGridControl and select Run designer. Using Figure 5 as a visual guide drag and drop the Dame field once, the Date field twice, and the Value field three times to the PivotGrid Fields list. The field duplicates will be used for things like Sum, Count, and Average.

  
Figure 5: Add three more PivotGrid fields using the Add button.

Follow these steps to configure each of the fields:

1. Set fieldName Area property to RowArea
2. Set the fieldDate Area to ColumnArea and change the Caption to Year and the GroupInterval property to DateYear
3. Set the fieldValue Area to DataArea, Caption to Sum
4. Set fieldValue1 Area to DataArea, Caption to Count, and SummaryType to Count
5. Set fieldValue2 Area to DataArea, Caption to Average, and SummaryType to Average
6. Set fieldDate1 Area to ColumnArea, Caption to Date
7. Close the designer

The design time view should look like Figure 6. The code is provided in Listing 1. (It is worth noting that if you use an actual datasource, say from a database, a lot of these steps are performed for you. It is also worth nothing that you can drag and drop fields at design time and runtime rather than changing the Area property of each field.)

Figure 6: The design time view after adding additional fields to the PivotGridControl.

Listing 1: The code-behind that adds data and performs the export to Excel operation.

using System;
usingSystem.Collections.Generic;
usingSystem.ComponentModel;
usingSystem.Data;
usingSystem.Drawing;
usingSystem.Text;
usingSystem.Windows.Forms;
usingDevExpress.XtraPrinting;
usingDevExpress.XtraPivotGrid;

namespaceWindowsApplication53
{
    public partial classForm1: Form
   {
        publicForm1()
        {
            InitializeComponent();
        }
        private voidForm1_Load(objectsender, EventArgs e)
        {
            PopulateTable();
            pivotGridControl1.RefreshData();
            pivotGridControl1.BestFit();
        }
        private voidPopulateTable()
        {
            DataTable myTable = dataSet1.Tables["Data"];
            myTable.Rows.Add(new object[] {"Aaa", DateTime.Today, 7});
            myTable.Rows.Add(new object[] { "Aaa", DateTime.Today.AddDays(1), 4 });
            myTable.Rows.Add(new object[] { "Bbb", DateTime.Today, 12 });
            myTable.Rows.Add(new object[] { "Bbb", DateTime.Today.AddDays(1), 14 });
            myTable.Rows.Add(new object[] { "Ccc", DateTime.Today, 11 });
            myTable.Rows.Add(new object[] { "Ccc", DateTime.Today.AddDays(1), 10 });

            myTable.Rows.Add(new object[] { "Aaa", DateTime.Today.AddYears(1), 4 });
            myTable.Rows.Add(new object[] { "Aaa", DateTime.Today.AddYears(1).AddDays(1), 2 });
            myTable.Rows.Add(new object[] { "Bbb", DateTime.Today.AddYears(1), 3 });
            myTable.Rows.Add(new object[] { "Bbb", DateTime.Today.AddDays(1).AddYears(1), 1 });
            myTable.Rows.Add(new object[] { "Ccc", DateTime.Today.AddYears(1), 8 });
            myTable.Rows.Add(new object[] { "Ccc", DateTime.Today.AddDays(1).AddYears(1), 22 });
        }

        stringfileName = "pivot.xls";
        private voidbutton1_Click(objectsender, EventArgs e)
        {
            pivotGridControl1.BeginUpdate();
            pivotGridControl1.OptionsPrint.PrintColumnHeaders =
              DevExpress.Utils.DefaultBoolean.False;
            pivotGridControl1.OptionsPrint.PrintDataHeaders =
              DevExpress.Utils.DefaultBoolean.False;
            pivotGridControl1.OptionsPrint.PrintFilterHeaders =
            DevExpress.Utils.DefaultBoolean.False;

            PivotGridField fieldExportHeader = pivotGridControl1.Fields.Add();
            fieldExportHeader.Caption = "Export";
            fieldExportHeader.Name = "fieldExportHeader";
            fieldExportHeader.Area = DevExpress.XtraPivotGrid.PivotArea.ColumnArea;
            fieldExportHeader.Visible = true;
            fieldExportHeader.AreaIndex = 0;
            fieldExportHeader.TotalsVisibility = PivotTotalsVisibility.None;
            pivotGridControl1.OptionsView.ShowGrandTotalsForSingleValues = true;
            pivotGridControl1.EndUpdate();

            try
           {
                pivotGridControl1.ExportToXls(fileName);
                System.Diagnostics.Process.Start(fileName);
            }
            catch(System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
           {
                pivotGridControl1.Fields.Remove(fieldExportHeader);
            }
        }

        private voidpivotGridControl1_CustomExportFieldValue(objectsender,
        DevExpress.XtraPivotGrid.CustomExportFieldValueEventArgs e)
        {
            TextBrick brick = e.Brick as TextBrick;
            if(e.Brick == null) return;

            if(e.Field != null&& e.Field.Name == "fieldExportHeader"
           && e.ValueType == PivotGridValueType.Value )
            {
                brick.Text = GetLayoutDiscription(sender as PivotGridControl, e.Field );
            }
        }

        private voidpivotGridControl1_FieldValueDisplayText(objectsender,
        PivotFieldDisplayTextEventArgs e)
        {
            if(e.Field != null&& e.Field.Name == "fieldExportHeader"
           && e.ValueType == PivotGridValueType.Value)
                e.DisplayText = "Export in progress, please wait...";
        }


        private stringGetLayoutDiscription(PivotGridControl pivot,
        PivotGridField unnecessaryField)
        {
            string text = "ColumnFields: ";
            List<PivotGridField> fields = pivot.GetFieldsByArea(PivotArea.ColumnArea);
            for(int i = 0; i < fields.Count ; i++)
            {
                if(object.ReferenceEquals(fields[i], unnecessaryField)) continue;
                text = text + fields[i].ToString();
                if(i < fields.Count - 1)
                    text = text + " -> ";
            }
            return text;
        }
    }
}

Tip: In Visual Studio select the PivotGridControl and use the Events view to add a CustomExportFieldValue and FieldValueDisplayText events for the PivotGridControl.

Form_Load calls PopulateTable that gives you some data to work with. If you use a database table then you will likely skip this step. pivotGridControl1.RefreshData does just what it says—you added data now we need to refresh the grid. The BestFit method automatically adjusts field columns to the minimum width needed to display the contents of each field.

PopulateTable simply adds some rows of data to the DataTable defined in the designer.

button1_Click contains the export code. BeginUpdate prevents the grid from updating while a bunch of changes are made. Call EndUpdate when all tweaks are made. The OptionsPrint features are set to hide the PrintColumnHeaders, PrintDataHeaders, and PrintFilterHeaders. A new PivotGridField is added to the ColumnArea with the caption “Export”, and the OptionsView.ShowGrandTotals property—to show the grand totals for the data fields—is set to true. ExportToXls actually exports the data to an MS-Excel file, and System.Diagnostics.Process.Start with a filename uses the operating system’s knowledge of file types to figure out which process to start, in this case MS-Excel due to the .xls extension. (You can use Automation to control Excel directly, but this approach isn’t necessary.) Finally, after the export the new PivotGridField is removed.

When the ExportToXls method is called CustomExportFieldValue is called for the PivotGridField added by the code. This event handler calls GetLayoutDescription which performs a tweak on the added field’s display text. FieldValueDisplayText displays an “exporting” message on the grid during the export.

There is a lot of tweaks going on with this code. Extra PivotGridFields are added and removed. Redundant field headers are hidden. (These are the dragable buttons the user has access to on the forms—to move fields between data, row, and column areas—that aren’t really needed in an Excel worksheet.) And, some tweaking of the text output is done.

The .NET Framework has literally thousands (maybe tens of thousands) of hidden gems. In the dark ages programmers learned a language’s grammar and a few dozen keywords and used—don’t say it—books to learn about a language’s function library. Literally, you could pick up a new language relatively quickly—except for C++—but it was still hard to use because one had to read analog books; Kindle didn’t even exist. Now if you want to learn a new language or framework you can still pick up the keywords and grammar pretty quickly, but the frameworks are massive. If one had to search a book to figure out how to use the .NET Framework it would be an impossible task. Thankfully, with Google, Wikipedia, blogs, and searchable articles if a developer can state a problem in plain English then there is probably some sample code that demonstrates a solution. I would venture to say that with content being added daily a developer can find a solution to almost any programming problem. This blog is my homage to the Web eventually covering everything.

.NET 4.0 added the BigInteger. BigInteger theoretically has no upper or lower limit, which is why it has no MinValue or MaxValue properties like other numeric types. You can assign a BigInteger a number as big as your computer’s memory will permit. For example, I computed Fibonacci numbers—by adding the previous two numbers 1,1,2,3,5,8,13,21,34,etc—and assigned them to BigInteger; the numbers were very huge very quickly with no end in sight.

You can use BigInteger much as you would an other integer type in .NET, but you can’t use the Math class with BigInteger. The Math class so far hasn’t been retrofitted for BigInteger. However, BigInteger has its own methods and overloaded operators that provide many of the same capabilities as other integer types have, except square root, which we will come back to. BigIntegers can be initialized with string, byte arrays, and smaller integer types. If you want to initialize a BigInteger with a byte array then it is worth noting that BigInteger stores data in little-endian order—lower order bytes precede higher-order bytes in the array. Positive values are interpreted by the most significant bit of the last byte; that is, the last bit of the last byte in the array is the sign bit. If you want to create a positive BigInteger with a byte array then place a 0 as the last element of the byte array. For example,

new BigInteger(new byte[]{255});

creates a BigInteger with the value –1 and

new BigInteger(new byte[]{255, 0});

creates a BigInteger with the value of 255.

The other conspicuously missing method for BigInteger is square root. I am not sure why it is missing, but calculating the square root of a number is useful in a large number of mathematics equations. For instance, the quadratic formula is determined using a square root, and the Sieve of Eratosthenes is used to determine prime numbers which are used in public key encryption. I have grabbed and tweaked two methods for calculating prime numbers for BigIntegers. The first is a simple method based on doubles and has an upper limit of Double.MaxValue, and the second is taken from Michael Wein’s post based on the (Isaac) Newton-Raiphson method—see Listing 1 and 2.

Listing 1: Calculating square roots up to Double.MaxValue based on

  or

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Numerics;
using System.Timers;

namespace BigIntegerDemo
{
  class Program
  {
    static void Main(string[] args)
    {
      while (true)
      {
        Console.Write("Enter a number: ");
        string input = Console.ReadLine();
        if (input == "q") return;
        BigInteger number = new BigInteger(Convert.ToDecimal(input));
        double result = Math.Exp(BigInteger.Log(number) / 2);
        if (result != Double.PositiveInfinity)
          Console.WriteLine("Square root of {0} is {1}", number, result);
        else
          Console.WriteLine("something went wrong");
      }
    }
  }
}

double result = Math.Exp(BigInteger.Log(number) / 2) takes the Log of the BigInteger value, it is divided by 2 and then uses the result as the exponent of the value e, Euler’s number. The drawback with this approach is that the result is a double and if it exceeds Double.MaxValue then Math.Exp will return Double.PositiveInfinity, requiring another solution for very large BigIntegers.

Listing 2 uses an implementation of the Newton-Raiphson method for calculating the square root. Since this algorithm uses basic mathematical capabilities the entire algorithm works with BigIntegers (too) and is capable of returning the square root of a BigInteger, a very big integer.

Listing 2: Calculate the square root of a BigInteger using an implementation of the Newton-Raiphson method posted by Michael Wein.

  static  BigInteger Sqrt(BigInteger number)
  {
    // problem with lower numbers to to right bit shift
    int bitLength = number.ToByteArray().Length * 8 + 1;
    BigInteger G = number >> bitLength / 2;
    BigInteger LastG = BigInteger.Zero;
    BigInteger One = new BigInteger(1);
    while (true)
    {
      LastG = G;
      G = (number / G + G) >> 1;
      int i = G.CompareTo(LastG);
      if (i == 0) return G;
      if (i < 0)
      {
        if ((LastG - G).CompareTo(One) == 0)
          if ( (G * G).CompareTo(number) < 0 && 
            (LastG * LastG).CompareTo(number) > 0) return G;
      }
      else
      {
        if ((G-LastG).CompareTo(One) == 0)
          if((LastG * LastG).CompareTo(number) < 0 && 
            ((G*G).CompareTo(number) > 0)) return LastG;
      }
    }
  }

The original post was listed as a response at http://social.msdn.microsoft.com/Forums/en-US/csharplanguage/thread/c13d3fec-21d9-4f74-92de-a6132d5e9915/. In that post a revision was added—if (i==0) return G;—that returns G when right-shifting a number yields 0. This statement avoids a division by zero error. Also, I searched and there is no bitLength method for BigInteger, so I used ToByteArray().Length * 8 to calculate the bit length. Ananda Vardhana posted a while loop calculation that determines the bit length too. My solution is faster and a little simpler.

Conferences like DevLink are about learning and community. DevLink 2010 was at Lipscomb University in Nashville. On Friday, August 6th I met retired Chief Justice Judge Alston, his wife Kiyoko, and Judge Alston’s dog Pecos. Judge Alston was the only blind Judge in Michigan when he retired. Judge Alston told me that he wanted to build software as a service to make it easier for Michiganders to work with the Michigan courts. Judge Alston has one additional challenge, albeit I suspect a minor one, he is blind. Pecos is his seeing eye dog.

Judge Alston told me that a big challenge for him was designing GUIs. We talked about how great controls and components can help any developer create professional, compelling GUIs, which is of course part of my job as a Technical Evangelist, and of course it is true. I also told Judge Alston about GiveCamps. A GiveCamp is where developers get together for a weekend, volunteering their time, jump starting development for non-profit organizations. Judge Alston may not exactly represent a non-profit entity, but I suggested perhaps he could sign up to participate in a GiveCamp and get some help with developing the GUIs for his software. GiveCamps don’t have anything directly to do with DevExpress although DevExpress routinely sponsors them, and telling people about GiveCamps is not necessarily part of my job. However, I think of the opportunity to participate in community events as an opportunity to learn, to help build community, and to help create awareness about DevExpress’ products. This is the reason I present at these events—although seldom on DevExpress products directly---and this is why I like manning the DevExpress booth.

Other Party Software

Another attendee walked up on Friday and said: “I stop by your booth every year and ask you tell me why I need your products”. (I love cocky developers.) My first unspoken thought was well you have gotten by this long without our tools, clearly you think you don’t need tools. Of course, that response wouldn’t help anyone. Instead I gave my response based on my beliefs—although I doubt I convinced this particular individual.

My belief about tools are based on a quote attributed to the geometer Archimedes and a second lesson from my father. Archimedes is attributed with “give me a lever and a place to stand and I shall move the earth”, and my father said “a craftsman is known by his tools”. Collectively my belief is tools can move mountains and with quality tools you can move them in style and adroitly. From over 20 years experience I know that there is no way any developer with no tools can compete with a developer with a set of good tools. Even a less experienced developer is likely to outpace a great developer if the former has good tools. Sure, a really smart guy can build anything, eventually, but building everything from scratch is impossible. For example, if you are using the .NET framework you are already using about 2 million lines of pre-existing code.

Finally, there is no such thing as third party code. There is code each of us writes and code written by others, other party code. Most of the code you and I use is other party code already. There is a single canonical rule expressed in two ways that supports the idea of using other party code. One version of the canon is using existing code is always cheaper than writing code from scratch, and the other version is that intellectual property means more than just writing code. When one writes his or her own code intellectual property has been created and must be owned, and ownership exceeds the cost of initial creation. The code has to be designed, coded, debugged, and updated over time. So, intellectual property is like getting married and having babies, the cost lasts beyond conception; the cost spans a lifetime.

So, when someone asks me why do I need your (DevExpress’) tools, I say you don’t need our tools, but you do need tools. Ours just happen to be the best available. The reason DevExpress’ tools are the best available choice for developers is that the tools are great, the results speak for themselves, and DevExpress provides excellent help, online videos and blogs, and we send evangelists all over the world talking to developers who help us make them better, all the time. With Developer Express you get scores of professional controls and tools for Windows, the Web, Silverlight, WPF, Delphi, and CodeRush and Refactor Pro! The cost to an individual developer is a few hundred bucks. No royalties, no complicated distribution fees, no hidden costs, and no marriage to home grown intellectual property.

Comparing LINQ to XML to DynamicObects for XML in VB10

My session at DevLink was Saturday at 10:30 am. The session was Comparing LINQ to XML to Dynamic Objects for XML in VB2010. This is a little like comparing apples to oranges because LINQ to XML is for XML and Dynamic Objects for XML is just one scenario for Dynamic Objects; Dynamic Objects are not just for XML. The session went pretty well, even thought my session had a modest turn out. There were 150 sessions to choose from, it was Saturday—the end of the conference—and these things happen.

We had a drawing during my session and one lucky person—Kim Styles—is the winner of DXperience Enterprise and Visual Studio 2010 Ultimate with MSDN.

Thanks to John Kellar and company for coordinating a great event, and thanks to all of the attendees that stopped by the DX booth. A special shout out to Kevin Pilch-Bisson the Dev Lead for C# for the unsolicited comments about DevExpress’ CodeRush.