none
Custom User-Defined Aggregate Functions? (I want to aggregate into a comma-delimited list) RRS feed

  • Question

  • Our UI is data-bound to our DataSets, so there is huge advantage to using computed expression columns in our tables... they instantly update as edits are made... consistent with everything else in our UI.

    I have one column that is aggregating over a child table using the aggregate function COUNT... Count(Child.Option)... so, it'll say "6" or "3", etc.  But what I want it to do is to do a String.Join(",", Child.Option), where I have merged C# and Expression code invalidly, such that I get "Red, Blue, Green, White, Black, Purple" or "Jump, Duck, Run" instead of "6" or "3".

    SQL Server lets you do custom user-defined aggregate functions, coded in CLR.  For ADO.Net DataSets, it seems that should be way easier... but I am not seeing it anywhere.

    So, is there a way to create custom user-defined aggregate functions for DataSets?

    If not, is there a way to implement what I am wanting?

    Sunday, August 19, 2012 5:49 PM

Answers

  • Hi,

    i've cooked something up that seems to work:

    Partial Class SomeDataSet
    
        Partial Class SomeDataTableRow
    
            Friend Overridable Sub OnChildChanged()
                Me.ExpressionCol = Form1.Stragg(Me, "FK_SomeDataTable_SomechildDataTable", "ChildString")
            End Sub
    
        End Class
    
        Partial Class SomechildDataTableDataTable
            Private Sub SomechildDataTableDataTable_SomechildDataTableRowChanged(ByVal sender As Object, ByVal e As SomechildDataTableRowChangeEvent) Handles Me.SomechildDataTableRowChanged
                e.Row.SomeDataTableRow.OnChildChanged()
            End Sub
    
            Private Sub SomechildDataTableDataTable_SomechildDataTableRowDeleted(ByVal sender As Object, ByVal e As SomechildDataTableRowChangeEvent) Handles Me.SomechildDataTableRowDeleted
                e.Row.SomeDataTableRow.OnChildChanged()
            End Sub
        End Class
    
    End Class
    
    Public Class Form1
    
        Private ds As New SomeDataSet
    
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            ds.SomeDataTable.AddSomeDataTableRow("testrow1", "")
            ds.SomeDataTable.AddSomeDataTableRow("testrow2", "")
            ds.SomeDataTable.AddSomeDataTableRow("testrow3", "")
    
            Dim newchild As SomeDataSet.SomechildDataTableRow
            newchild = ds.SomechildDataTable.NewSomechildDataTableRow()
            newchild.ChildFK = 0
            newchild.ChildString = "A"
            ds.SomechildDataTable.AddSomechildDataTableRow(newchild)
    
            newchild = ds.SomechildDataTable.NewSomechildDataTableRow()
            newchild.ChildFK = 0
            newchild.ChildString = "B"
            ds.SomechildDataTable.AddSomechildDataTableRow(newchild)
    
            newchild = ds.SomechildDataTable.NewSomechildDataTableRow()
            newchild.ChildFK = 1
            newchild.ChildString = "C"
            ds.SomechildDataTable.AddSomechildDataTableRow(newchild)
    
            newchild = ds.SomechildDataTable.NewSomechildDataTableRow()
            newchild.ChildFK = 1
            newchild.ChildString = "D"
            ds.SomechildDataTable.AddSomechildDataTableRow(newchild)
    
            newchild = ds.SomechildDataTable.NewSomechildDataTableRow()
            newchild.ChildFK = 2
            newchild.ChildString = "E"
            ds.SomechildDataTable.AddSomechildDataTableRow(newchild)
    
            newchild = ds.SomechildDataTable.NewSomechildDataTableRow()
            newchild.ChildFK = 2
            newchild.ChildString = "F"
            ds.SomechildDataTable.AddSomechildDataTableRow(newchild)
    
            'ds.SomeDataTable.ExpressionColColumn.Expression = "Stragg"
    
            DataGridView1.DataSource = ds
            DataGridView2.DataSource = ds
    
    
        End Sub
    
        Public Function Stragg(ByVal parentrow As DataRow, ByVal relationName As String, ByVal childFieldName As String) As String
            Dim res As String = ""
            For Each dr As DataRow In parentrow.GetChildRows(relationName)
                res &= dr(childFieldName).ToString & ", "
            Next
            If res.Length > 2 Then res = res.Substring(0, res.Length - 2)
            Return res
        End Function
    End Class

    with a dataset that looks like this:

    btw, i made this into a sample: http://code.msdn.microsoft.com/Create-a-custom-aggregate-ec423cc8#content


    Regards, Nico


    • Edited by Nico Boey Tuesday, August 21, 2012 3:15 PM
    • Marked as answer by TCC Developer Tuesday, August 21, 2012 9:13 PM
    Tuesday, August 21, 2012 9:48 AM

All replies

  • Hi,

    If you have strongly typed data set, you can extend your class and place your custom function with partial class. In that function you can do what ever as per your need in application.


    Thanks and Regards, Shailesh B. Davara

    Monday, August 20, 2012 6:13 AM
  • If you have strongly typed data set, you can extend your class and place your custom function with partial class. In that function you can do what ever as per your need in application.

    Hmmm.  I do have strongly typed data sets.  I do define custom functions in the partial classes.

    However, I don't see how I can use one of those as an Aggregate Function in an Expression field of a computed column.  If that is possible, great... but I can't find any clue as to how.

    I can certainly add a Property that computes what I want using C#... but that won't get me the automatic computation of that and automatic events that I need to drive the databinding like a computed Column will.  Is there a reasonable way to wire that up?

    Monday, August 20, 2012 10:24 PM
  • Custom User-defined aggregate functions is not support in dataset

    http://social.msdn.microsoft.com/Forums/no/adodotnetdataproviders/thread/43e5424b-ef89-4386-83ec-a10c5947e226

    Tuesday, August 21, 2012 6:15 AM
  • Hi,

    i've cooked something up that seems to work:

    Partial Class SomeDataSet
    
        Partial Class SomeDataTableRow
    
            Friend Overridable Sub OnChildChanged()
                Me.ExpressionCol = Form1.Stragg(Me, "FK_SomeDataTable_SomechildDataTable", "ChildString")
            End Sub
    
        End Class
    
        Partial Class SomechildDataTableDataTable
            Private Sub SomechildDataTableDataTable_SomechildDataTableRowChanged(ByVal sender As Object, ByVal e As SomechildDataTableRowChangeEvent) Handles Me.SomechildDataTableRowChanged
                e.Row.SomeDataTableRow.OnChildChanged()
            End Sub
    
            Private Sub SomechildDataTableDataTable_SomechildDataTableRowDeleted(ByVal sender As Object, ByVal e As SomechildDataTableRowChangeEvent) Handles Me.SomechildDataTableRowDeleted
                e.Row.SomeDataTableRow.OnChildChanged()
            End Sub
        End Class
    
    End Class
    
    Public Class Form1
    
        Private ds As New SomeDataSet
    
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            ds.SomeDataTable.AddSomeDataTableRow("testrow1", "")
            ds.SomeDataTable.AddSomeDataTableRow("testrow2", "")
            ds.SomeDataTable.AddSomeDataTableRow("testrow3", "")
    
            Dim newchild As SomeDataSet.SomechildDataTableRow
            newchild = ds.SomechildDataTable.NewSomechildDataTableRow()
            newchild.ChildFK = 0
            newchild.ChildString = "A"
            ds.SomechildDataTable.AddSomechildDataTableRow(newchild)
    
            newchild = ds.SomechildDataTable.NewSomechildDataTableRow()
            newchild.ChildFK = 0
            newchild.ChildString = "B"
            ds.SomechildDataTable.AddSomechildDataTableRow(newchild)
    
            newchild = ds.SomechildDataTable.NewSomechildDataTableRow()
            newchild.ChildFK = 1
            newchild.ChildString = "C"
            ds.SomechildDataTable.AddSomechildDataTableRow(newchild)
    
            newchild = ds.SomechildDataTable.NewSomechildDataTableRow()
            newchild.ChildFK = 1
            newchild.ChildString = "D"
            ds.SomechildDataTable.AddSomechildDataTableRow(newchild)
    
            newchild = ds.SomechildDataTable.NewSomechildDataTableRow()
            newchild.ChildFK = 2
            newchild.ChildString = "E"
            ds.SomechildDataTable.AddSomechildDataTableRow(newchild)
    
            newchild = ds.SomechildDataTable.NewSomechildDataTableRow()
            newchild.ChildFK = 2
            newchild.ChildString = "F"
            ds.SomechildDataTable.AddSomechildDataTableRow(newchild)
    
            'ds.SomeDataTable.ExpressionColColumn.Expression = "Stragg"
    
            DataGridView1.DataSource = ds
            DataGridView2.DataSource = ds
    
    
        End Sub
    
        Public Function Stragg(ByVal parentrow As DataRow, ByVal relationName As String, ByVal childFieldName As String) As String
            Dim res As String = ""
            For Each dr As DataRow In parentrow.GetChildRows(relationName)
                res &= dr(childFieldName).ToString & ", "
            Next
            If res.Length > 2 Then res = res.Substring(0, res.Length - 2)
            Return res
        End Function
    End Class

    with a dataset that looks like this:

    btw, i made this into a sample: http://code.msdn.microsoft.com/Create-a-custom-aggregate-ec423cc8#content


    Regards, Nico


    • Edited by Nico Boey Tuesday, August 21, 2012 3:15 PM
    • Marked as answer by TCC Developer Tuesday, August 21, 2012 9:13 PM
    Tuesday, August 21, 2012 9:48 AM
  • Thanks, Nico.  Based on initial experimentation, that's not enough events... sometimes it fires, sometimes it doesn't... getting the events right is my big concern.  But it does look like it should be solvable that way.  Thanks.
    Tuesday, August 21, 2012 9:16 PM