linq to xml query .... sortability of datagridview

Question

Hey,

feeling less like a newbie lately then I come across this challenge....

I'm querying an XML database and sending to a DGV with a LINQ query.  Thats all going fine.  Now I'd like the user to be able to use the inherent sorting function normally associated with the column header to sort the data.... the more I read it looks like I need to bind the source to the DGV to sort it in this way...  does this mean I need to send it to a dataset or a datatable?   Need help with the concept more than anything.... code follows:

Imports System.Xml
Imports System.IO


Public Class Form1
    Public piepath As String = "C:\Users\pete\Desktop\object programming\xml data reading\sorting xml data and sending to datagrid\XMLtest\pies.xml"
    Public doc As XDocument = XDocument.Load(piepath)
    
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim query =
                  From el In doc.Descendants("RECIPE")
                  Where el.Element("NAME").Value = "MAMAS PIE"
                  From apple In el.Descendants("APPLE")
                  Order By apple.Element("NAME").Value
                  Select New With {
                      .NAME = apple.Element("NAME").Value,
                      .SWEETNESS = apple.Element("SWEETNESS").Value,
                      .COLOR = apple.Element("COLOR").Value
          }
        DataGridView1.DataSource = query.ToArray
    End Sub
End Class

and the XML

<?xml version="1.0" encoding="utf-8"?>
<RECIPES>
  <RECIPE>
    <NAME>MAMAS PIE</NAME>
    <STYLE>OLD FASHIONED</STYLE>
    <CHEF>MAMA</CHEF>
    <DATE>10</DATE>
    <INGREDIENTS>
     <APPLES>
      <APPLE>
        <NAME>Gala</NAME>
        <SWEETNESS>4</SWEETNESS>
        <COLOR>pink</COLOR>
      </APPLE>
      <APPLE>
        <NAME>MACINTOSH</NAME>
        <COLOR>RED</COLOR>
        <SWEETNESS>1</SWEETNESS>
      </APPLE>
      <APPLE>
        <NAME>GOLDEN DELICIOUS</NAME>
        <COLOR>GOLD</COLOR>
        <SWEETNESS>2</SWEETNESS>
      </APPLE>
    </APPLES>
    <SUGARS>
      <SUGAR>
        <NAME>TRUCKLE</NAME>
        <COLOR>BROWN</COLOR>
        <PRICE>2.25</PRICE>
      </SUGAR>
    </SUGARS>
   </INGREDIENTS>
  </RECIPE>
  <RECIPE>
    <NAME>DADS PIE</NAME>
    <STYLE>NEW AGE</STYLE>
    <CHEF>DAD</CHEF>
    <DATE>15</DATE>
    <INGREDIENTS>
     <APPLES>
      <APPLE>
        <NAME>GALA</NAME>
        <COLOR>PINK</COLOR>
        <SWEETNESS>3</SWEETNESS>
      </APPLE>
      <APPLE>
        <NAME>GRANNY SMITH</NAME>
        <COLOR>GOLD</COLOR>
        <SWEETNESS>4</SWEETNESS>
      </APPLE>
      <APPLE>
        <NAME>Golden Delicious</NAME>
        <SWEETNESS>4</SWEETNESS>
        <COLOR>gold</COLOR>
      </APPLE>
    </APPLES>
    <SUGARS>
      <SUGAR>
        <NAME>CANE</NAME>
        <COLOR>WHITE</COLOR>
        <PRICE>1.5</PRICE>
      </SUGAR>
     </SUGARS>
   </INGREDIENTS>
  </RECIPE>
  <RECIPE>
    <NAME>PETES PIE</NAME>
    <STYLE>OLD FASHIONED</STYLE>
    <CHEF>MAMA</CHEF>
    <DATE>12</DATE>
    <INGREDIENTS>
     <APPLES>
      <APPLE>
        <NAME>MACINTOSH</NAME>
        <COLOR>RED</COLOR>
        <SWEETNESS>1</SWEETNESS>
      </APPLE>
      <APPLE>
        <NAME>GOLDEN DELICIOUS</NAME>
        <COLOR>GOLD</COLOR>
        <SWEETNESS>2</SWEETNESS>
      </APPLE>
    </APPLES>
    <SUGARS>
      <SUGAR>
        <NAME>TRUCKLE</NAME>
        <COLOR>BROWN</COLOR>
        <PRICE>2.25</PRICE>
      </SUGAR>
     </SUGARS>
   </INGREDIENTS>
  </RECIPE>
</RECIPES>

Thanks yet again in advance

Pete

Answer 1

See http://msdn.microsoft.com/en-us/library/ms993236.aspx for details on how to set up a data source for which DataGridView allows sorting.

---

MVP Data Platform Development My blog

Answer 2

Thanks... started into it.... looks like this is C+..... any idea if there's a VB version

Answer 3

 I am sorry the sample is in C#, I tried to convert the code presented in http://www.codeproject.com/Articles/31418/Implementing-a-Sortable-BindingList-Very-Very-Quic with the help of http://converter.telerik.com/ and with some editing I came up with

Imports System.ComponentModel
Imports System.Linq.Expressions

Public Class MySortableBindingList(Of T)
    Inherits BindingList(Of T)

    ' reference to the list provided at the time of instantiation
    Private originalList As List(Of T)
    Private sortDirection As ListSortDirection
    Private sortProperty As PropertyDescriptor

    ' function that refereshes the contents
    ' of the base classes collection of elements
    Private populateBaseList As Action(Of MySortableBindingList(Of T), List(Of T)) = Sub(a, b) a.ResetItems(b)

    ' a cache of functions that perform the sorting
    ' for a given type, property, and sort direction
    Shared cachedOrderByExpressions As New Dictionary(Of String, Func(Of List(Of T), IEnumerable(Of T)))()

    Public Sub New()
        originalList = New List(Of T)()
    End Sub

    Public Sub New(enumerable As IEnumerable(Of T))
        originalList = enumerable.ToList()
        populateBaseList(Me, originalList)
    End Sub

    Public Sub New(list As List(Of T))
        originalList = list
        populateBaseList(Me, originalList)
    End Sub

    Protected Overrides Sub ApplySortCore(prop As PropertyDescriptor, direction As ListSortDirection)
        '
        '         Look for an appropriate sort method in the cache if not found .
        '         Call CreateOrderByMethod to create one. 
        '         Apply it to the original list.
        '         Notify any bound controls that the sort has been applied.
        '         


        sortProperty = prop

        Dim orderByMethodName = If(sortDirection = ListSortDirection.Ascending, "OrderBy", "OrderByDescending")
        Dim cacheKey = GetType(T).GUID.ToString() + prop.Name + orderByMethodName

        If Not cachedOrderByExpressions.ContainsKey(cacheKey) Then
            CreateOrderByMethod(prop, orderByMethodName, cacheKey)
        End If

        ResetItems(cachedOrderByExpressions(cacheKey)(originalList).ToList())
        ResetBindings()
        sortDirection = If(sortDirection = ListSortDirection.Ascending, ListSortDirection.Descending, ListSortDirection.Ascending)
    End Sub


    Private Sub CreateOrderByMethod(prop As PropertyDescriptor, orderByMethodName As String, cacheKey As String)

        '
        '         Create a generic method implementation for IEnumerable<T>.
        '         Cache it.
        '        


        Dim sourceParameter = Expression.Parameter(GetType(List(Of T)), "source")
        Dim lambdaParameter = Expression.Parameter(GetType(T), "lambdaParameter")
        Dim accesedMember = GetType(T).GetProperty(prop.Name)
        Dim propertySelectorLambda = Expression.Lambda(Expression.MakeMemberAccess(lambdaParameter, accesedMember), lambdaParameter)
        Dim orderByMethod = GetType(Enumerable).GetMethods().Where(Function(a) a.Name = orderByMethodName AndAlso a.GetParameters().Length = 2).[Single]().MakeGenericMethod(GetType(T), prop.PropertyType)

        Dim orderByExpression = Expression.Lambda(Of Func(Of List(Of T), IEnumerable(Of T)))(Expression.[Call](orderByMethod, New Expression() {sourceParameter, propertySelectorLambda}), sourceParameter)

        cachedOrderByExpressions.Add(cacheKey, orderByExpression.Compile())
    End Sub

    Protected Overrides Sub RemoveSortCore()
        ResetItems(originalList)
    End Sub

    Private Sub ResetItems(items As List(Of T))

        MyBase.ClearItems()

        For i As Integer = 0 To items.Count - 1
            MyBase.InsertItem(i, items(i))
        Next
    End Sub

    Protected Overrides ReadOnly Property SupportsSortingCore() As Boolean
        Get
            ' indeed we do
            Return True
        End Get
    End Property

    Protected Overrides ReadOnly Property SortDirectionCore() As ListSortDirection
        Get
            Return sortDirection
        End Get
    End Property

    Protected Overrides ReadOnly Property SortPropertyCore() As PropertyDescriptor
        Get
            Return sortProperty
        End Get
    End Property

    Protected Overrides Sub OnListChanged(e As ListChangedEventArgs)
        originalList = MyBase.Items.ToList()
    End Sub
End Class

Then I wrote

Public Class Apple
    Public Property Name As String
    Public Property Color As String
    Public Property Sweetness As Integer
End Class

and finally the Windows Forms stuff is

Public Class Form1
    Public doc As XDocument = XDocument.Load("XMLFile1.xml")


    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim query As IEnumerable(Of Apple) =
                   From el In doc.Descendants("RECIPE")
                   Where el.Element("NAME").Value = "MAMAS PIE"
                   From apple In el.Descendants("APPLE")
                   Order By apple.Element("NAME").Value
                   Select New Apple With {
                       .Name = apple.Element("NAME").Value,
                       .Sweetness = CType(apple.Element("SWEETNESS"), Integer),
                       .Color = apple.Element("COLOR").Value
           }
        DataGridView1.DataSource = New MySortableBindingList(Of Apple)(query)
    End Sub
End Class

That way the columns are clickable and sorting works, although it appears on the first click in any of the columns no change happens, only on the second click. IIf you need more help on using data binding with a Windows Forms control then I think you are better off asking in http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/threads, here we can help you with LINQ to XML to query an XML document.

---

MVP Data Platform Development My blog