Click here to Skip to main content
15,881,089 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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!
Posted
Comments
woopsydoozy 27-Nov-13 12:20pm    
what mechanism are you using to apply the sort on the front end? The Sort property of a DataView, I assume? Probably need to add a data column that will do the sort for you. Like add a case column to your query, something like: CASE WHEN InvoiceItemLevel LIKE 'Package Total%' THEN 'AAAAA' ELSE InvoiceItemLevel END AS SORTVAL

Here are two options to achieve this. The first is purely DataTable manipulations and the second uses "LINQ to DataSet"[^] to create a DataView.

Edit: 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
 
Share this answer
 
v3
Comments
Goel Himanshu 29-Nov-13 5:59am    
Hi it helps me a lot but there is a another issue after using your first option which is that i get this dataset from a cache then after applying sorting on defaultview it makes it sort for default view only. I want that it should be applied directly on the dataset so that after this step i can just update this dataset in cache so that further on i just retrive that dataset from cache where ever i want which gives me data in the tables in sorted order.
TnTinMn 29-Nov-13 10:55am    
I have revised the code to include using the Cache. Be advised that this is my first attempt at a WebForm any more complex than "Hello World", so I may not be handling this correctly. :)

I see no need to store a sorted version of the dataset unless it is a performance issue with a WebForm. If that is the case, you could replace the DataTable with the sorted version by creating a table from the DataView (DataView.ToTable()), delete the existing table from the DataSet, and finally adding the new Table to the DataSet.

Use a primary and secondary sort field. Primary controlled by "Package" and the secondary by the alphabetic order of the other values.


SQL
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


If there are multiple values starting with "Package" and you want ordering within them then the secondary sort selection becomes:

SQL
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

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.

SQL
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


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..

VB
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.
 
Share this answer
 
v3
Comments
Goel Himanshu 28-Nov-13 6:14am    
Thanks for your reply... the solution which you told me will not work as i have apply both condition only on one column. I have this dataset table with various columns and one of the column is "InvoiceItemLevel" which i have mentioned with various values. So i need to sort that dataset table using multiple condition like i have said i need package line first then all in alphabetical order.
I have used below logic but it is for single alphabetical sorting condition but i also had to add a condition for "Pacakage total" value and if this value is exist in dataset table this row must be moved to first position of the dataset.
DataSet.Tables[0].DefaultView.Sort = "InvoiceItemLevel asc";
cigwork 28-Nov-13 14:27pm    
If you can't you use two (or more if necessary) sort columns can you concatenate values?
Something like ...

select
name,
case when name like 'PackageTotal' then '00' else '01' + name end as CombinedSort
from sortTest
order by CombinedSort

myDataView.Sort = "CombinedSort ASC"

If you can't add columns to the result set then perhaps return a single InvoiceItemLevel with a prefix as shown for the CombinedSort above and then (oh this is horrible) use a template column rather than a "plain text" column in your datagrid to display the invoiceitemlevel without the sort prefix
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.
SQL
SELECT NameOfSomething, Total
FROM ( 
    SELECT NameOfSomething, SUM(CountOfName) AS Total
    FROM TableName
    GROUP BY NameOfSomething
     ) AS T
ORDER BY Total DESC, NameOfSomething ASC


For further information, please see: ORDER BY clause (T-SQL)[^]

Of course, you can still use CASE stetement with ORDER clause.
SQL
SELECT ...
FROM ...
ORDER BY CASE WHEN NameOfSomething Like '%Total' THEN 0 ELSE 1 END ASC, OtherField DESC


More about:
CASE (T-SQL) statement[^]
SQL Server – Custom sorting in ORDER BY clause[^]
 
Share this answer
 
Comments
Goel Himanshu 28-Nov-13 6:12am    
Thanks for your reply... the solution which you told me is from Database side which i have already implemented and written in my question. The problem is that i have to do it from Frontend side means using vb.net. I have this dataset table with various columns and one of the column is "InvoiceItemLevel" which i have mentioned with various values. So i need to sort that dataset table using multiple condition like i have said i need package line first then all in alphabetical order.
I have used below logic but it is for single alphabetical sorting condition but i also had to add a condition for "Pacakage total" value and if this value is exist in dataset table this row must be moved to first position of the dataset.
DataSet.Tables[0].DefaultView.Sort = "InvoiceItemLevel asc";

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900