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

13
votes

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!

3
c#vb.netsortingdatatablegridview-sorting

3 Answers

9
votes

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"
8
votes

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();
0
votes

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?