Sorting DataTable string column, but with null/empty at the bottom

Question

I need to sort a DataTable or DataGridView by a column that is a string value, but with null/empty values at the BOTTOM when sorting ASCENDING.

The DataTable is NOT populated by a SQL statement, so no order by.

If I do

DataGridView1.Sort(New RowComparer(System.ComponentModel.ListSortDirection.Ascending))

then it throws an exception, saying that the DataGridView is DataBound, which is correct, but doesn't help me, and I want to keep it databound.

It's .NET 2.0, which means no LINQ available!

Answer 1

in some cases you could do this if you have another extra column in your table:

SELECT completed, completed IS NULL AS isnull
FROM TABLE
ORDER BY isnull DESC, completed DESC

Edit:
Like this in VB.NET

        For Each srSearchResult In srcSearchResultCollection

            Try
                dr = dt.NewRow()
                dr("cn") = srSearchResult.GetDirectoryEntry().Properties("cn").Value
                dr("Account") = srSearchResult.GetDirectoryEntry().Properties("sAMAccountName").Value
                dr("Nachname") = srSearchResult.GetDirectoryEntry().Properties("sn").Value
                dr("Vorname") = srSearchResult.GetDirectoryEntry().Properties("givenname").Value
                dr("Mail") = srSearchResult.GetDirectoryEntry().Properties("mail").Value
                dr("HomeDirectory") = srSearchResult.GetDirectoryEntry().Properties("homedirectory").Value
                dr("LogonScript") = srSearchResult.GetDirectoryEntry().Properties("scriptPath").Value

                dr("IsNull") = String.IsNullOrEmpty(dr("Nachname").ToString())

                dt.Rows.Add(dr)
            Catch ex As Exception

            End Try

        Next srSearchResult
dt.DefaultView.Sort = "IsNull ASC, Nachname ASC"

Answer 2

even if the DataTable you bind to the user interface control DataGridView is not populated via SQL, you can still sort it using a DataView, you can do something like this:

DataView myView = myDataTable.DefaultView;
myView,Sort = "yourColumnName ASC";

then you do your binding.

how does it work? Are the null values in the top or in the bottom?

Answer 3

Davide Piras has a good solution, however there is another simplest solution I have

Add a new column and do it in just one line

// just, add a new column
ActualDataTable.Columns.Add("NullEmptyCheck", typeof(int), "ColumnNameToSort is Null OR ColumnNameToSort = ''");

// apply sort expression
ActualDataTable.DefaultView.Sort = "NullEmptyCheck asc, ColumnNameToSort asc";
// pass datasource to grid
MyGridView.DataSource = ActualDataTable.DefaultView;
MyGridView.DataBind();

Answer 4

For those who are looking for a way to achieve this in .NET 4+, here is a example :

DataTable orderedDataTable = sourceDataTable.AsEnumerable()
                                            .OrderByDescending(row => !string.IsNullOrWhiteSpace(row.Field<string>("COLUMN_NAME")))
                                            .ThenBy(row => row.Field<string>("COLUMN_NAME"))
                                            .CopyToDataTable();

This will place null values at the end of the table (note that both empty and white spaces strings will have the same rank) and then order the requested column in ascending order.