Sort DataTable (DataSet) blank cells at bottom using C# and VB.Net in Windows Application

Hi ryutenkan,

Check this example. Now please take its refrence and correct your code.

Database

I have made use of the following table Customers with the schema as follows.

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.

Download SQL file

Namespace

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

VB.Net

Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data.Odbc

Code

C#

private void Button2_Click(System.Object sender, System.EventArgs e)
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString);
    using (SqlCommand cmd = new SqlCommand("SELECT Customerid,Name,Country FROM Customers", con))
    {
        cmd.CommandType = CommandType.Text;
        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
        {
            DataTable dt = new DataTable();
            DataTable newdt = new DataTable();
            sda.Fill(dt);
            DataTable dtNull = dt.Clone();
            DataTable dtFinal = dt.Clone();
            switch (TabControl1.SelectedTab.Name)
            {
                case "TabPage1":
                    dt.DefaultView.Sort = "Customerid ASC, Name DESC, Country DESC";
                    for (int i = 0; i <= dt.DefaultView.ToTable().Rows.Count - 1; i++)
                    {
                        if (dt.DefaultView.ToTable().Rows[i]["Customerid"] == DBNull.Value)
                        {
                            dtNull.ImportRow(dt.DefaultView.ToTable().Rows[i]);
                        }
                        else
                        {
                            dtFinal.ImportRow(dt.DefaultView.ToTable().Rows[i]);
                        }
                    }
                    dtFinal.Merge(dtNull);
                    this.DataGridView1.DataSource = dtFinal;
                    break;
                case "TabPage2":
                    dt.DefaultView.Sort = "Customerid DESC, Name DESC, Country DESC";
                    for (int i = 0; i <= dt.DefaultView.ToTable().Rows.Count - 1; i++)
                    {
                        if (dt.DefaultView.ToTable().Rows[i]["Customerid"] == DBNull.Value)
                        {
                            dtNull.ImportRow(dt.DefaultView.ToTable().Rows[i]);
                        }
                        else
                        {
                            dtFinal.ImportRow(dt.DefaultView.ToTable().Rows[i]);
                        }
                    }
                    dtFinal.Merge(dtNull);
                    this.DataGridView2.DataSource = dtFinal;
                    break;
            }
        }
    }
}

VB.Net

Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
    Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("conString").ConnectionString)
    Using cmd As SqlCommand = New SqlCommand("SELECT Customerid,Name,Country FROM Customers", con)
        cmd.CommandType = CommandType.Text
        Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
            Dim dt As DataTable = New DataTable
            sda.Fill(dt)
            Dim dtNull As DataTable = dt.Clone()
            Dim dtFinal As DataTable = dt.Clone()
            Select Case TabControl1.SelectedTab.Name
                Case "TabPage1"
                    dt.DefaultView.Sort = "Customerid ASC, Name DESC, Country DESC"
                    For i As Integer = 0 To dt.DefaultView.ToTable().Rows.Count - 1
                        If IsDBNull(dt.DefaultView.ToTable().Rows(i)("Customerid")) Then
                            dtNull.ImportRow(dt.DefaultView.ToTable().Rows(i))
                        Else
                            dtFinal.ImportRow(dt.DefaultView.ToTable().Rows(i))
                        End If
                    Next
                    dtFinal.Merge(dtNull)
                    Me.DataGridView1.DataSource = dtFinal
                Case "TabPage2"
                    dt.DefaultView.Sort = "Customerid DESC,Name DESC, Country DESC"
                    For i As Integer = 0 To dt.DefaultView.ToTable().Rows.Count - 1
                        If IsDBNull(dt.DefaultView.ToTable().Rows(i)("Customerid")) Then
                            dtNull.ImportRow(dt.DefaultView.ToTable().Rows(i))
                        Else
                            dtFinal.ImportRow(dt.DefaultView.ToTable().Rows(i))
                        End If
                    Next
                    dtFinal.Merge(dtNull)
                    Me.DataGridView2.DataSource = dtFinal
            End Select
        End Using
    End Using
End Sub