So I would have thought this problem would have already been solved by the Internets at large. As it turns out, I couldn’t find a very simple method to solve this relatively simple task. So here’s my attempt

Here’s a dead simple pivot table.

Let’s say I have a table that looks like this:

And I want to pivot it to to look like this:

Here’s How

Private Function PivotTable(oldTable As DataTable, 
                            Optional pivotColumnOrdinal As Integer = 0
                           ) As DataTable
    Dim newTable As New DataTable
    Dim dr As DataRow

    ' add pivot column name
    newTable.Columns.Add(oldTable.Columns(pivotColumnOrdinal).ColumnName)

    ' add pivot column values in each row as column headers to new Table
    For Each row In oldTable.Rows
        newTable.Columns.Add(row(pivotColumnOrdinal))
    Next

    ' loop through columns
    For col = 0 To oldTable.Columns.Count - 1
        'pivot column doen't get it's own row (it is already a header)
        If col = pivotColumnOrdinal Then Continue For

        ' each column becomes a new row
        dr = newTable.NewRow()

        ' add the Column Name in the first Column
        dr(0) = oldTable.Columns(col).ColumnName

        ' add data from every row to the pivoted row
        For row = 0 To oldTable.Rows.Count - 1
            dr(row + 1) = oldTable.Rows(row)(col)
        Next

        'add the DataRow to the new table
        newTable.Rows.Add(dr)
    Next

    Return newTable
End Function

Then just call like this:

Dim newTable = PivotTable(oldTable, 0)

And that’s that.

As a general rule, you should not modify a collection that your are looping over, only the items inside of that collection. The problem with removing items inside of a for loop is that it changes the collection that is being looped which will interfere with the list count in an indexed for loop and the iterator location in a for each loop.

Two common solutions are to:

1. Create a new collection so you can modify one collection and loop over another.
2. Loop backwards through the collection so changes to the iterator won’t impact the execution.

In this article, we’ll create two extension methods that utilize each of these solutions.

Create New Collection:

By calling ToList on the original collection, you create a brand new collection. Then, you can loop over the new list to find items that need to be removed from the original. Whenever you find an object that matches your removal criteria, you can safely remove it from the original collection because it is not currently being enumerated over.

I think it looks pretty spiffy too:

<Extension()>
Public Sub RemoveEachObject(Of T)(ByVal col As Collection(Of T), 
                                  ByVal match As Func(Of T, Boolean))
    For Each o As T In col.ToList()
        If match(o) Then col.Remove(o)
    Next
End Sub

Loop Backwards:

The previous solution works well, but a more efficient solution would be to loop backwards. For starters, the previous answer will have to create a copy of the entire collection. More importantly, when removing items, the Remove() method will have to loop through entire collection and check each item for reference equality with the passed in value. This can be quite expensive. It would be much easier to keep track of the current index in the collection and remove whatever item happened to be occupying it.

To do this, we’ll loop backwards and check the validity of each item in the collection based on the passed in lambda function. If it matches, then we’ll remove the current index.

<Extension()>
Public Sub RemoveEach(Of T)(ByVal col As Collection(Of T),
                            ByVal match As Func(Of T, Boolean))
    For i = col.Count - 1 To 0 Step -1
        If match(col(i)) Then col.RemoveAt(i)
    Next
End Sub

Usage

Then we can use either method like this:

Dim col As New Collection(Of Integer) From {1, 2, 3, 4}
col.RemoveEach(Function(i) (i Mod 2) = 0)
Console.WriteLine(String.Join(",", col))
'Produces: 1,3

This code has been written to extend Collection(Of T). You could just as easily extend List(Of T) as well, but the list class already exposes the method RemoveAll which already does this same thing.

For more info, check out this great answer to Remove from a List within a ‘foreach’ loop.

SQL has a wonderful terse and natural syntax for checking if an item is inside of a collection by using the IN keyword. Which can be expressed like this:

expression IN (value_1, value_2, .... value_n)

In .NET, we can preform the same operation with .Contains() method on any enumerable collection which:

Determines whether a sequence contains a specified element

Beginning with VB 2010, we can even do this on the fly by using the Collection Initializers.

For example, to check whether a variable called personName was either "Sally" or "Jenny", we could use the following expression:

{"Sally","Jenny"}.Contains(personName)

However, I think this syntax leaves something to be desired. The verbiage is all wrong. I don’t really care if a collection contains some item. I care if an item is in a collection. Of course, logically, this is performing the same operation, but I want the personName variable to be in the drivers seat. I want personName to be the subject that is verbing against the other items.

For a bit of syntactic sugar, we can add a generic extension method to take in an ParamArray and check if the extended element falls inside that array.

Here’s the In method:

Note: In needs to be inside of square brackets because it is a Protected Keyword.

Visual Basic

''' <summary>
''' Determines if the Item is contained within the listed array
''' </summary>
''' <typeparam name="T">The type of object</typeparam>
''' <param name="item">The calling item</param>
''' <param name="range">An array or comma separated list of the items to check against the calling</param>
''' <returns>True if item is found in list</returns>
''' <remarks>Provides syntatic sugar by reordering the subject of the IEnumerable.Contains method</remarks>
<Extension()>
Public Function [In](Of T)(ByVal item As T, ByVal ParamArray range() As T) As Boolean
    Return range.Cast(Of T).Contains(item)
End Function

C Sharp

public static class Extensions
{
    /// <summary>
    /// Determines if the Item is contained within the listed array
    /// </summary>
    /// <typeparam name="T">The type of object</typeparam>
    /// <param name="item">The calling item</param>
    /// <param name="range">An array or comma separated list of the items to check against the calling</param>
    /// <returns>True if item is found in list</returns>
    /// <remarks>Provides syntatic sugar by reordering the subject of the IEnumerable.Contains method</remarks>
    public static bool In<T>(this T item, params T[] range)
    {
        return range.Contains(item);
    }
}

Throw this inside any module in your assembly, preferably one named something like Utilities or ExtensionMethods. Now we can call like this:

personName.In("Sally","Jenny")

If you’re checking against a predefined list you can pass that in as a parameter and cast back into an array.

Personally, I take this utility method with me wherever I go. I find it incredibly helpful for checking if an item exists within a defined range. Once you start using it, you won’t stop, and I think that’s a good thing! For my money, it substantially improves readability, especially if you find yourself working on older code bases without collection initializers.

I couldn’t believe there was no native way in .NET to do something alarmingly simple like calculate someone’s age from their date of birth. Of all the amazing functions and properties on DateTime objects, this seems surprisingly non-existent.

For such an easy task, it’s also surprisingly complex; so much so that Jeff Atwood, creator of StackOverflow, even asked that question himself here.

For all my (and the internet’s) failed attempts, read on. If you’re just looking for something to copy and paste in that will work, grab the following code

Public Function GetCurrentAge(ByVal dob As Date) As Integer
    Dim age As Integer
    age = Today.Year - dob.Year
    If (dob > Today.AddYears(-age)) Then age -= 1
    Return age
End Function

All of these methods, except the bottom one fail for various reasons:

Dim dob As Date = #5/14/1994#
Dim today As Date = #5/13/2013#
Dim age As Integer

age = DateDiff(DateInterval.Year, dob, today)
Console.WriteLine("DateDiff Year Age: {0}", age)
'19

age = today.Subtract(dob).TotalDays / 365.25
Console.WriteLine("Subtraction Age: {0}", age)
'19

age = Math.Floor(DateDiff(DateInterval.Month, dob, today) / 12)
Console.WriteLine("DateDiff Month Age: {0}", age)
'19

age = today.Year - dob.Year
If (dob > today.AddYears(-age)) Then age -= 1
Console.WriteLine("Year Part and Compare Age: {0}", age)
'18

Console.ReadKey()

Hope that helps!

Introduction

You may find, as I did, the lack of symmetry in the XmlReader Class quite odd. If we look at the NodeType property, which exposes the XmlNodeType enumeration, we find an EndElement, but no StartElement (The Element member actually only identifies opening xml tag elements, but it’s not this shortage on which I’d like to elaborate). Only when we look at the methods available to the class do we see an IsStartElement() method for evaluation, but without a corollary method named something like: IsEndElement().

There are dozens of ways of getting around this deficiency, but I’m all for visually appealing code that leaves other coders with a easy and quick understanding of what you are attempting to do. Checking if the reader is on an opening tag with IsStartElement and then finding closing tags by evaluation the reader’s NodeType property might work correctly, but just looks wrong to me.

Extension Methods

What I’d like to do is create a method that looks and feels like the IsStartElement() function, but instead evaluates if the reader is currently on an EndElement node type. This is where extension methods come into play. Extension methods allow you to extend the functionality of a built in class with custom methods that act as if they were native to the class. I think they are best suited for when you are of the sincere belief that method deficit is a slight oversight of the framework designers, and if given the opportunity, they would happily accept your additional coding to improve the underlying code. Since all instances of this type or any derived type will have automatic access to any extension methods written on top of a particular class, you want to be careful with their implementation .

The first step is to open your project, right click on it, click add, then select module:

Then give your module a name. I like to create a single Module for all extensions with a name like ExtensionMethods.

IsEndElement Code

First, you’ll need an imports(vb) or using(c#) statement with the extensions Namespace:

Imports System.Runtime.CompilerServices

Then, add the following code to your module:

<Extension()>  
Public Function IsEndElement(ByVal xmlReader As XmlReader, 
                             Optional ByVal name As String = "") As Boolean  
    If xmlReader.NodeType = XmlNodeType.EndElement Then  
        If name = "" Then  
            'node is end element, name value not set  
            Return True  
        Else  
            If xmlReader.Name = name Then  
                'node is end element, AND named the same as parameter  
                Return True  
            Else  
                'node is end element, but name is diff than parameter  
                Return False  
            End If  
        End If  
    Else  
        'node is not an end element  
        Return False  
    End If  
End Function  

And voilà! Now you can call your code on native objects with full IntelliSense:

The Problem

You may have noticed that .NET won’t let you change the Text value of a ComboBox during the SelectedIndexChanged event. This can be quite frustrating and difficult to work-around if you have want to update the ComboBox text during a when the user makes a selection. For example, you might want to back out of a bad user selection or bind each selection item with different formatting when selected. Let’s clear up a couple things that will help us understand why .NET prevents us from doing what seems like a reasonable default functionality, and then we’ll show one way to workaround this limitation.

Understanding the System.Windows.Forms.ComboBox

When you select an item from a combo box, the SelectedIndex property of the ComboBox changes to the index location of the item you selected. The ComboBox Text property is automatically updated to the display value of the item at the specified index. This is the only update to the ComboBox Text that is allowed by default. To understand this, let’s look at another out-of-the-box ComboBox behavior.

If you have a ComboBox populated with the following items:

1. Red
2. Blue
3. Green

Let’s say the current SelectedIndex value is -1 and the current Text value is Select a color...

If in our code we change the value of the Text to blue:

ComboBox1.Text = "Blue"

Then .NET will automatically recognize that this matches an item in our collection and change the SelectedIndex value to 2, thereby also firing the SelectedIndexChanged event. The reason .NET won’t let you change the Text property during a SelectedIndexChanged event is because they are worried about creating an endless loop by firing another SlectedIndexChanged event.

The Solution

We can resolve this relatively easily by invoking a delegate during the selection changed event that will eventually change the text property. We’ll load a generic WinForms window by adding two items to a ComboBox, one of which we want to reset the form when selected. This item represents a selection that may or may not be invalid, but is expensive to figure out, so we don’t want to necessarily evaluate it early and remove it from the list before the user has a chance to select it.

We’ll start by declaring a Delegate Sub within our class. The only important thing about a Delegate is the method signature that you are passing in. Since we want to call the ResetComboBox method which contains on parameters, our Delegate will not contain any arguments as well.

On the SelectedIndexChanged event, we’ll call BeginInvoke and specify that when it invokes, it should look to a method at the AddressOf ResetComoboBox.

'Declares a delegate sub that takes no parameters  
Delegate Sub ComboDelegate()  

'Loads form and controls  
Private Sub LoadForm(sender As System.Object, e As EventArgs) _  
 Handles MyBase.Load  
 ComboBox1.Items.Add("This is okay")  
 ComboBox1.Items.Add("This is NOT okay")  
 ResetComboBox()  
End Sub  

'Handles Selected Index Changed Event for combo Box  
Private Sub ComboBoxSelectionChanged(sender As System.Object, e As EventArgs) _  
 Handles ComboBox1.SelectedIndexChanged  
 'if option 2 selected, reset control back to original  
 If ComboBox1.SelectedIndex = 1 Then  
  BeginInvoke(New ComboDelegate(AddressOf ResetComboBox))  
 End If  

End Sub  

'Exits out of ComboBox selection and displays prompt text   
Private Sub ResetComboBox()  
 With ComboBox1  
  .SelectedIndex = -1  
  .Text = "Select an option"  
  .Focus()  
 End With  
End Sub  

You’ll notice that when ResetComboBox is eventually called by the delegate, it will also fire the SelectionChanged event when we change the SlectedIndex to -1. If there’s a chance that you’re handling anything in the change event that could cause a repetitive loop, you can include a private cancelAction boolean property in your class, defaulted to False. Then when you start the ResetComboBox method, set cancelAction to True and reset it to False at the end of method. In the selection changed event, exit the sub if cancel action will set and you will never accidentally execute code when you’re resetting controls

If cancelAction Then Exit Sub

Source Code

You can find the source code for this application from SkyDrive. Please comment with any suggestions or questions

Intro

Let’s say you have a list of things displayed in a DataGridView and you want a user to be able to select among them on a DataGridViewCheckBoxColumn. Further, let’s say that you’d like to know as soon as the user has made a change to their selection. You might want to handle this for a number of reasons: to enable a save button, to change the appearance of selected items, to display a pop-up window, or to check for consistency against other choices. It turns out that this is trickier than it might seem and doesn’t work great out of the box from .NET, but, not to fear, there are several easy work-arounds that will get the trick done.

The Problem

'This won't fire until the cell has lost focus
Private Sub DataGridCellValueChanged(sender As DataGridView, 
                                     e As DataGridViewCellEventArgs) _ 
        Handles DataGridView1.CellValueChanged
 IsDirty = True
End Sub

When you click a CheckBox in a DataGridViewCheckBoxColumn, the check marker will update immediately, but CellValueChanged event on the DataGridView will not fire until the user happens to click elsewhere and the cell has lost focus. Why? Well, the DataGridView thinks it’s a little preemptive to go declaring that the cell value has changed while you are still selected on it. This makes more sense when we think about a TextBox column. We would not want the CellValueChanged event firing every single time a letter was added to a person’s last name. Still, it doesn’t make much sense when we think about the way CheckBox’s work, in that, they can only ever be On or Off, and that once you have made your selection, you’re probably pretty confident that you want to change the value. Any solution is going to involve using an event that definitely will fire to stop the DataGridView from thinking it’s in edit mode.

The Solution

This problem was also raised in a StackOverflow Question which seemed to advocate for handling the MouseUp event on the DataGridView and then call the EndEdit method, thereby ensuring that the grid would evaluate whether or not the Cell’s value had, indeed, changed and fire the corresponding event appropriately I must admit, this works, but feels like more of a work around than a solution. What if someone is able to make a selection without a click event? There seems to be a non-zero percent chance that this might fire incorrectly in some unforeseen situation. I’d rather code to do exactly what it says it’s doing.

We could also handle the DataGrid’s CellContentClick event. This has the added bonus of not firing when you have clicked outside of the checkbox area, but still falls perhaps under the same category of not being entirely clear to someone unfamiliar with this issue why this particular event should force the grid to exit edit mode.

After looking into the problem at some length, MSDN actually seems to offer the best solution right on their CellContentClick event page. Here’s a cleaned up version:

'Ends Edit Mode So CellValueChanged Event Can Fire
Private Sub EndEditMode(sender As System.Object, 
                        e As EventArgs) _
            Handles DataGridView1.CurrentCellDirtyStateChanged
    'if current cell of grid is dirty, commits edit
    If DataGridView1.IsCurrentCellDirty Then
        DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit)
    End If
End Sub

'Executes when Cell Value on a DataGridView changes
Private Sub DataGridCellValueChanged(sender As DataGridView, 
                                     e As DataGridViewCellEventArgs) _
            Handles DataGridView1.CellValueChanged
    'check that row isn't -1, i.e. creating datagrid header
    If e.RowIndex = -1 Then Exit Sub

    'mark as dirty
    IsDirty = True
End Sub

In the code sample above, we monitor the CurrentCellDirtyStateChanged event. I like that it tells us very specifically what has happened to the grid at the point in time when the event is raised and handled. It’s important to note that this event will get called twice, once on changing the state of the cell to dirty (before committing changes) and once when changing the cell state back to ‘clean’ (after the changes have been committed). For this reason, before preforming any action, it checks to see when the current cell is dirty or not. If it is, it’ll call the CommittEdit method and pass in the DataGridViewDataErrorContexts enumerator type of Commit

Sample / Source Code

I worked up what I hope to be an interesting sample of the different methods by which we can get the CellValueChanged event to eventually fire.

The form loads by default to only handle the CellValueChanged event itself, which means we will immediately notice the problem at hand. Changing the active status for a given person will not fire the event until some other object on the form is clicked and receives focus. Anytime a grid event is handled, a notification will pop-up and fade out. This helps identify when, and in what order, events are being handled without explicitly having to set breakpoints and wait for the code to catch each event.

In the options group box, you can choose to include event handlers notifications or not. Also, the drop down list will add / remove handlers so you can easily test out which handlers do what without having to specifically comment out lines of code.

You can download the mini application and the source code for this demo on SkyDrive or by clicking the icons below: