ADO.Net DataTables sort Null values last instead of first

Published


I have a DataTable of values that I sort using a DataView.  Its a typical string-based (alphabetical) sort, but my customers want to be able to highlight certain rows to always appear first.

I thought this would be simple, as SQL Server always puts NULL values last in results, and I assumed ADO.Net would do the same, but I was wrong.  Take this example where we want to force superheroes to appear first:

Dim dt As New DataTable
dt.Columns.Add(New DataColumn("MySort", GetType(System.Int32)))
dt.Columns.Add(New DataColumn("MyStr", GetType(System.String)))

Dim dr As DataRow = dt.NewRow()
dr.Item("MySort") = System.DBNull.Value
dr.Item("MyStr") = "Bob"
dt.Rows.Add(dr)

dr = dt.NewRow()
dr.Item("MySort") = System.DBNull.Value
dr.Item("MyStr") = "Jim"
dt.Rows.Add(dr)

dr = dt.NewRow()
dr.Item("MySort") = System.DBNull.Value
dr.Item("MyStr") = "Arnold"
dt.Rows.Add(dr)

dr = dt.NewRow()
dr.Item("MySort") = 1 ' Superheroes come first!'
dr.Item("MyStr") = "Superman"
dt.Rows.Add(dr)

dr = dt.NewRow()
dr.Item("MySort") = 1 ' Superheroes come first!'
dr.Item("MyStr") = "Spiderman"
dt.Rows.Add(dr)

Dim dv As New DataView(dt)
dv.Sort = "MySort, MyStr"
Dim dt2 As DataTable = dv.ToTable()

Dim gv As New GridView
Page.Form.Controls.Add(gv)
gv.DataSource = dt2
gv.DataBind()

We would expect our two superheroes to appear at the top (in alphabetical order) before the other three dudes, but we get the opposite:

MySortMyStr
  Arnold
  Bob
  Jim
1 Spiderman
1 Superman

Many people have experienced the same, and there are different ways to overcome this.  After trying a few, I have settled with introducing a new column which performs the initial sort:

Dim dt As New DataTable
dt.Columns.Add(New DataColumn("MySort", GetType(System.Int32)))
dt.Columns.Add(New DataColumn("MyStr", GetType(System.String)))

Dim dr As DataRow = dt.NewRow()
dr.Item("MySort") = System.DBNull.Value
dr.Item("MyStr") = "Bob"
dt.Rows.Add(dr)

dr = dt.NewRow()
dr.Item("MySort") = System.DBNull.Value
dr.Item("MyStr") = "Jim"
dt.Rows.Add(dr)

dr = dt.NewRow()
dr.Item("MySort") = System.DBNull.Value
dr.Item("MyStr") = "Arnold"
dt.Rows.Add(dr)

dr = dt.NewRow()
dr.Item("MySort") = 1 ' Superheroes come first!'
dr.Item("MyStr") = "Superman"
dt.Rows.Add(dr)

dr = dt.NewRow()
dr.Item("MySort") = 1 ' Superheroes come first!'
dr.Item("MyStr") = "Spiderman"
dt.Rows.Add(dr)

dt.Columns.Add(New DataColumn("SortOrderPresent", GetType(System.Boolean))) ' introduce a primary sort column '
For Each r As DataRow In dt.Rows()
    If Not IsDBNull(r.Item("MySort")) AndAlso Not String.IsNullOrEmpty(r.Item("MySort").ToString()) AndAlso IsNumeric(r.Item("MySort")) Then
        r.Item("SortOrderPresent") = True
    Else
        r.Item("SortOrderPresent") = False
    End If
Next

Dim dv As New DataView(dt)
dv.Sort = "SortOrderPresent DESC, MySort, MyStr" ' push Nulls into last place using DESC, then proceed with the usual sorting order '
Dim dt2 As DataTable = dv.ToTable()
dt2.Columns.Remove("SortOrderPresent")

Dim gv As New GridView
Page.Form.Controls.Add(gv)
gv.DataSource = dt2
gv.DataBind()

Notice that we first sort by our new column in DESCending order?  This forces Nulls to the bottom.  After this, the normal sorting is performed (e.g. to put the superheroes in order, then put the other three dudes in order):

MySortMyStr
1 Spiderman
1 Superman
  Arnold
  Bob
  Jim

 

Comments

No comments have been posted. Be the first to write a comment...

New Comment





This is to prove that you are not a spam robot posting links to dodgy sites!