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
Re: Sorting a Dataview based on the row filter
Apologies the return order should be:
8
1
7
3
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...
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.
Re: Sorting a Dataview based on the row filter
Quote:
Originally Posted by
wes4dbt
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
Re: Sorting a Dataview based on the row filter
Quote:
Originally Posted by
kpmc
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:
Private Sub SetRowFilterAndSort(view As DataView,
keyColumnName As String,
sortColumnName As String,
ParamArray keys As String())
view.RowFilter = String.Format("{0} IN ('{1}')",
keyColumnName,
String.Join("', '", keys))
For Each rowView As DataRowView In view
rowView(sortColumnName) = Array.IndexOf(keys, CStr(rowView(keyColumnName)))
Next
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:
Private table As DataTable
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Using adapter As New SqlDataAdapter("SELECT * FROM MyTable", "connection string here")
table = New DataTable
adapter.Fill(table)
table.Columns.Add("Sort", GetType(Integer))
table.DefaultView.RowFilter = "Sort"
End Using
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim ids(ListBox1.SelectedItems.Count - 1) As String
ListBox1.SelectedItems.CopyTo(ids, 0)
SetRowFilterAndSort(table.DefaultView, "Id", "Sort", ids)
End Sub
Private Sub SetRowFilterAndSort(view As DataView,
keyColumnName As String,
sortColumnName As String,
ParamArray keys As String())
view.RowFilter = String.Format("{0} IN ('{1}')",
keyColumnName,
String.Join("', '", keys))
For Each rowView As DataRowView In view
rowView(sortColumnName) = Array.IndexOf(keys, CStr(rowView(keyColumnName)))
Next
End Sub
Re: Sorting a Dataview based on the row filter