-
Oct 11th, 2018, 09:40 AM
#1
Thread Starter
Lively Member
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
-
Oct 11th, 2018, 09:42 AM
#2
Thread Starter
Lively Member
Re: Sorting a Dataview based on the row filter
Apologies the return order should be:
8
1
7
3
-
Oct 11th, 2018, 01:29 PM
#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...
-
Oct 11th, 2018, 07:14 PM
#4
Re: Sorting a Dataview based on the row filter
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
Last edited by jmcilhinney; Oct 11th, 2018 at 07:22 PM.
-
Oct 11th, 2018, 05:12 PM
#5
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.
-
Oct 11th, 2018, 06:59 PM
#6
Re: Sorting a Dataview based on the row filter
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
Last edited by kpmc; Oct 11th, 2018 at 07:03 PM.
-
Oct 12th, 2018, 12:05 AM
#7
Thread Starter
Lively Member
Re: Sorting a Dataview based on the row filter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|