Results 1 to 7 of 7

Thread: Sorting a Dataview based on the row filter

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Sorting a Dataview based on the row filter

    I have a Data view that filters data from a datatable and i want to sort the results
    so that the records return in the same order as their primary key occurs in the row filter.

    This may better be illustrated by example:

    Code:
      Dim DV As New DataView(mytable)
            DV.RowFilter = "primarykey IN ('8','1','7','3')"
    without any sort being applied the records would obviously be returned as follows:

    1
    3
    7
    8

    As their existence within the datatable would be numeric

    My question is how do i set the DV.Sort property to return the records as follows:

    8
    7
    7
    3

    i.e. the order of priority as they exist in the filter

    Is this possible or is there a recommended work around?

    Thanks in advance.

    Ken

  2. #2

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Re: Sorting a Dataview based on the row filter

    Apologies the return order should be:

    8
    1
    7
    3

  3. #3
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Sorting a Dataview based on the row filter

    Firstly, I was not aware the dataview supported the IN clause.
    Second, its just a simple matter of sorting what ever column you want... maybe I am not understanding you..

    Code:
    dt.DefaultView.Sort = "MyColumn Asc"
    'where dt=the datatable
    And I am sure that your DataView variable is inferring the DefaultView from that table, but you should really explicitly assign it
    Code:
    Dim DV As New DataView=mytable.DefaultView
    Which really you may as well just work with the defaultview instead of creating the variable in the first place...

  4. #4
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,181

    Re: Sorting a Dataview based on the row filter

    kpmc,
    It appears the OP is asking for the sort order to be the same as the order of the numbers in the IN clause "IN ('8','1','7','3')" see post #2
    It's not an Asc or Desc sort order. I'm curious if it can be done with out manually creating a table sorted in that order.

  5. #5
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Sorting a Dataview based on the row filter

    Quote Originally Posted by wes4dbt View Post
    kpmc,
    It appears the OP is asking for the sort order to be the same as the order of the numbers in the IN clause "IN ('8','1','7','3')" see post #2
    It's not an Asc or Desc sort order. I'm curious if it can be done with out manually creating a table sorted in that order.
    Oh, I see.

    I really thought for sure the RowFilter did not support IN clause, I swore I've tried it and had to take the long way around in a previous solution.

    Anyway, as for the issue. The only way I can think to do this is by cloning the datatable and iterating an array by the order in which you want to 'sort'.

    This maybe a better case to use some kind of linq vodo, which my skills aren't that great....yet

    Code:
        Private Sub ButtonDTFilter_Click(sender As Object, e As EventArgs) Handles ButtonDTFilter.Click
            'array to hold the IN values
            Dim INClause() As Integer = {6, 1, 2, 12}
            'sample DataTable
            Dim dt As New DataTable
            With dt
                'add unoffical PK
                Dim PKCol As New DataColumn With {.ColumnName = "PK",
                    .DataType = GetType(Int32), .AutoIncrement = True}
                .Columns.Add(PKCol)
                'add a bunch of test rows
                For i As Integer = 0 To 100
                    .Rows.Add(dt.NewRow)
                Next
            End With
    
            'filter the values by joining the IN array, note you used strings, youll need to append single quotes
            dt.DefaultView.RowFilter = "PK IN (" & String.Join(",", INClause) & ")"
    
            'clone the sample table(only brings in schema, no rows)
            Dim dt2 As DataTable = dt.Clone
    
            'iterate the IN array
            For Each INInt As Integer In INClause
                'iterate the sample data
                For Each Drow As DataRow In dt.Rows
                    'if PK value = the values IN array
                    If Convert.ToInt32(Drow("PK")) = INInt Then
                        'Import the row
                        dt2.ImportRow(Drow)
                    End If
                Next
            Next
    
            DataGridView1.DataSource = dt2
        End Sub
    Last edited by kpmc; Oct 11th, 2018 at 07:03 PM.

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Sorting a Dataview based on the row filter

    Quote Originally Posted by kpmc View Post
    Firstly, I was not aware the dataview supported the IN clause.
    The documentation for the DataView.RowFilter property directs the reader to the documentation for the DataColumn.Expression property for syntax information. That second topic describes everything that is supported, including the IN operator.

    As for the question, I think that the best option would be to add another column specifically for sorting, populate it with values based on the filter and sort by that column, e.g.
    vb.net Code:
    1. Private Sub SetRowFilterAndSort(view As DataView,
    2.                                 keyColumnName As String,
    3.                                 sortColumnName As String,
    4.                                 ParamArray keys As String())
    5.     view.RowFilter = String.Format("{0} IN ('{1}')",
    6.                                    keyColumnName,
    7.                                    String.Join("', '", keys))
    8.  
    9.     For Each rowView As DataRowView In view
    10.         rowView(sortColumnName) = Array.IndexOf(keys, CStr(rowView(keyColumnName)))
    11.     Next
    12. End Sub
    You would have already assigned the name of the sort column to the Sort property of the DataView.

    EDIT: Here's a more complete example:
    vb.net Code:
    1. Private table As DataTable
    2.  
    3. Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    4.     Using adapter As New SqlDataAdapter("SELECT * FROM MyTable", "connection string here")
    5.         table = New DataTable
    6.         adapter.Fill(table)
    7.         table.Columns.Add("Sort", GetType(Integer))
    8.         table.DefaultView.RowFilter = "Sort"
    9.     End Using
    10. End Sub
    11.  
    12. Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    13.     Dim ids(ListBox1.SelectedItems.Count - 1) As String
    14.  
    15.     ListBox1.SelectedItems.CopyTo(ids, 0)
    16.  
    17.     SetRowFilterAndSort(table.DefaultView, "Id", "Sort", ids)
    18. End Sub
    19.  
    20. Private Sub SetRowFilterAndSort(view As DataView,
    21.                             keyColumnName As String,
    22.                             sortColumnName As String,
    23.                             ParamArray keys As String())
    24.     view.RowFilter = String.Format("{0} IN ('{1}')",
    25.                                    keyColumnName,
    26.                                    String.Join("', '", keys))
    27.  
    28.     For Each rowView As DataRowView In view
    29.         rowView(sortColumnName) = Array.IndexOf(keys, CStr(rowView(keyColumnName)))
    30.     Next
    31. End Sub

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Re: Sorting a Dataview based on the row filter

    Thanks Very much guys

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width