如何在具有多个排序条件的数据集VB.NET中对coulumn进行排序 [英] How to sort a coulumn in dataset VB.NET having multiple sort conditions
问题描述
我有一个数据集，其中有一个列包含各种字符串类型值，如下所示：
飞机
犯罪
套餐总额
公寓
DIC - 人员
现在要求是申请后如果该列中包含Package Total值，则对该列进行排序，然后它必须位于数据集的顶部位置，之后所有其他值应按字母顺序排序，如下所示：
套餐总额
飞机
公寓
犯罪
DIC - 人员
我们在数据库中使用了以下逻辑工作正常但无法弄清楚如何在Fronend方面在Dataset VB.net上做到这一点：
当UseCarrierAllocation = 0时按案例订购当案例时InvoiceItemLevel LIKE'套餐总计％'那么0结束1结束结束，InvoiceItemLevel ASC
任何回复/想法将很有帮助！
I have a dataset in which there is a column contains various string type values like below:
Aircraft
Crime
Package Total
Apartments
DIC - Personnel
Now the requirement is that after applying sorting logic on this colum if there is a "Package Total" value in it then it must come at the top position on the Dataset and after that all other values should be in alphabatically sorted order like below:
Package Total
Aircraft
Apartments
Crime
DIC - Personnel
We have used in Database below logic which is working fine but can't figure it out how to do it on Dataset VB.net from Fronend side:
ORDER BY CASE WHEN UseCarrierAllocation = 0 THEN CASE WHEN InvoiceItemLevel LIKE 'Package Total%' THEN 0 ELSE 1 END END, InvoiceItemLevel ASC
Any reply/idea will be helpful!
以下是实现此目的的两个选项。第一个是纯粹的DataTable操作，第二个使用LINQ to DataSet [ ^ ]创建一个DataView。
编辑：修改前面的示例以包含使用缓存。
Here are two options to achieve this. The first is purely DataTable manipulations and the second uses "LINQ to DataSet"[^] to create a DataView.
Modified previous example to include using Cache.
Partial Public Class _Default
Inherits System.Web.UI.Page
Private ds As New DataSet("dsFred")
Private Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load
GetDataSet()
' Generate the Columns
AddColumnsToGridView(GridView1, ds.Tables("Fred"))
AddColumnsToGridView(GridView2, ds.Tables("Fred"))
GridView1.DataSource = Option1(ds.Tables("Fred"))
GridView1.DataBind()
GridView2.DataSource = Option2(ds.Tables("Fred"))
GridView2.DataBind()
End Sub
Private Sub form1_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Unload
If Cache("dsFred") Is Nothing Then Cache("dsFred") = ds
End Sub
Private Function Option1(ByVal dt As DataTable) As DataView
If dt.Columns("PrimarySort") Is Nothing Then
' add a computed column to the DataTable
' it will set the column value to 0(zero) if the pattern matches, else 1
dt.Columns.Add("PrimarySort", GetType(Byte), "iif([sourcecolumnname] Like 'Package Total%', 0, 1)")
End If
Dim dv As New DataView(dt)
' set the sort to sort first on PrimarySort then original source column
dv.Sort = "[PrimarySort] Asc, [sourcecolumnname] Asc"
Return dv
End Function
Private Function Option2(ByVal dt As DataTable) As DataView
' This use Linq To DataSet
' Make sure that you have a project reference for: System.Data.DataSetExtensions
' 2 ways to define the query
Dim sort As System.Data.EnumerableRowCollection(Of DataRow) = _
dt.AsEnumerable(). _
OrderBy(Function(r As DataRow) IIf(r.Field(Of String)("sourcecolumnname") Like "Package Total*", 0, 1)). _
ThenBy(Function(r As DataRow) r.Field(Of String)("sourcecolumnname"))
' this query may be a little bit easier to read
'Dim sort As System.Data.EnumerableRowCollection(Of DataRow) = _
' From r In dt.AsEnumerable _
' Order By IIf(r.Field(Of String)("sourcecolumnname") Like "Package Total*", 0, 1), r.Field(Of String)("sourcecolumnname") _
' Select r
Return sort.AsDataView()
End Function
Private Sub GetDataSet()
If Cache("dsFred") Is Nothing Then
Fill(ds)
Else
ds = CType(Cache("dsFred"), DataSet)
End If
End Sub
Private Sub Fill(ByRef ds As DataSet)
' used to simulate filling the table from a query
Dim dt As New DataTable
With dt
dt.TableName = "Fred"
.Columns.Add("sourcecolumnname", GetType(String))
.Rows.Add(New Object() {"Package Total 2"})
.Rows.Add(New Object() {"Crime"})
.Rows.Add(New Object() {"Apartments"})
.Rows.Add(New Object() {"DIC - Personnel"})
.Rows.Add(New Object() {"Package Total"})
End With
ds.Tables.Add(dt)
End Sub
Private Sub AddColumnsToGridView(ByVal gv As GridView, ByVal dt As DataTable)
' Ref - Answer from Steve Hibbert: http://stackoverflow.com/questions/2091457/how-to-hide-columns-in-an-asp-net-gridview-with-auto-generated-columns
gv.Columns.Clear()
For Each col As DataColumn In dt.Columns
Dim field As New BoundField
field.DataField = col.ColumnName
field.HeaderText = col.ColumnName
gv.Columns.Add(field)
Next
End Sub
End Class
使用主要和次要排序字段。主要由包控制，次要由其他值的字母顺序控制。
Use a primary and secondary sort field. Primary controlled by "Package" and the secondary by the alphabetic order of the other values.
insert into sortTest (name) values('Package')
insert into sortTest (name) values('Crime')
insert into sortTest (name) values('DIC')
insert into sortTest (name) values('Aircraft')
insert into sortTest (name) values('Apartments')
select
name,
case when name like 'Package%' then 0 else 1 end as PrimarySort,
case when name like 'Package%' then null else name end as SecondarySort
from sortTest
order by PrimarySort Asc, SecondarySort Asc
name PrimarySort SecondarySort
---------- ----------- -------------
Package 0 NULL
Aircraft 1 Aircraft
Apartments 1 Apartments
Crime 1 Crime
DIC 1 DIC
如果有多个值以Pac开头kage并且你想在其中订购，然后二级排序选择变为：
If there are multiple values starting with "Package" and you want ordering within them then the secondary sort selection becomes:
select
name,
case when name like 'Package%' then 0 else 1 end as PrimarySort,
name as SecondarySort
from sortTest
order by PrimarySort Asc, SecondarySort Asc
name PrimarySort SecondarySort
---------- ----------- -------------
Package 0 Package
Package A 0 Package A
Package B 0 Package B
Aircraft 1 Aircraft
Apartments 1 Apartments
Crime 1 Crime
DIC 1 DIC
当然，您现在不需要单独的SecondarySort列，因为它只是名称，但使用它会使您的意图清晰，这意味着如果您的排序规则发生变化，您可以更改服务器端代码，最小化（可能没有）客户端代码中断。
丢弃包价值并重新运行选择。
Of course you don't need a separate SecondarySort column now as it's just name, but using it makes your intentions clear and it means if your sort rules change you can change server side code with minimal (possibly no) disruption to client code.
Throw away the package value(s) and re-run the select.
delete from sortTest where name like 'Package%'
select
name,
case when name like 'Package%' then 0 else 1 end as PrimarySort,
name as SecondarySort
from sortTest
order by PrimarySort Asc, SecondarySort Asc
name PrimarySort SecondarySort
---------- ----------- -------------
Aircraft 1 Aircraft
Apartments 1 Apartments
Crime 1 Crime
DIC 1 DIC
如果您使用数据视图来输出输出而不是数据集，那么在您的VB中想要确保这个订单得到保留..
And in your VB if you use a dataview to feed the output rather than the dataset and you want to make doubly sure that this order is preserved..
Dim dv as DataView = New DataView(myDataSet.Tables("myTable"))
dv.Sort = "PrimarySort Asc, SecondarySort Asc"
...但除非您允许用户从数据表客户端删除行，否则我认为没有必要。
...but I don't think that'll be necessary unless you are allowing users to remove rows from the datatable client-side.
如果我理解你，你在结果集中有2列，例如：
If i understand you well, you have got 2 columns in a result set, for example:
NameOfSomething Total
Package Total 500
Aircraft 300
Apartments 250
Crime 200
DIC - Personnel 150
如果是真的，你应该对第二列的数据进行排序。
If it is a true, you should sort data on the second column.
SELECT NameOfSomething, Total
FROM (
SELECT NameOfSomething, SUM(CountOfName) AS Total
FROM TableName
GROUP BY NameOfSomething
) AS T
ORDER BY Total DESC, NameOfSomething ASC
详情请见：ORDER BY子句（T-SQL） [ ^ ]
当然，你仍然可以使用 CASE 带有 ORDER 子句的stetement。
For further information, please see: ORDER BY clause (T-SQL)[^]
Of course, you can still use CASE stetement with ORDER clause.
SELECT ...
FROM ...
ORDER BY CASE WHEN NameOfSomething Like '%Total' THEN 0 ELSE 1 END ASC, OtherField DESC
更多关于：
CASE（T-SQL）语句 [ ^ ]
SQL Server - ORDER BY子句中的自定义排序 [ ^ ]
这篇关于如何在具有多个排序条件的数据集VB.NET中对coulumn进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
