Ordering numbers according to length and display in ASP.Net GridView

Hi Mehram,

Please refer below sample.

SQL

CREATE PROCEDURE GetAccountTree
AS
BEGIN
	CREATE TABLE #abc
	(
		AccountID VARCHAR(100)
	)

	INSERT INTO #abc VALUES (1)
	INSERT INTO #abc VALUES (1101)
	INSERT INTO #abc VALUES (111)
	INSERT INTO #abc VALUES (1101001)
	INSERT INTO #abc VALUES (1101001001002)
	INSERT INTO #abc VALUES (1101001001001)
	INSERT INTO #abc VALUES (1101001001)

	SELECT AccountIDTree = CASE 
		WHEN LEN(AccountID) = 2 THEN CHAR(32) + AccountID 
		WHEN LEN(AccountID) = 4 THEN CHAR(32) + CHAR(32) + AccountID 
		WHEN LEN(AccountID) = 7 THEN CHAR(32) + CHAR(32) + CHAR(32) + AccountID 
		WHEN LEN(AccountID) = 10 THEN CHAR(32) + CHAR(32) + CHAR(32) + CHAR(32) + AccountID 
		WHEN LEN(AccountID) = 13 THEN CHAR(32) + CHAR(32) + CHAR(32) + CHAR(32) + CHAR(32) + AccountID 
		ELSE accountid end 
	FROM #abc
	ORDER BY CAST(AccountID AS BIGINT)

	DROP TABLE #abc
END

HTML

<asp:GridView ID="gvData" runat="server" AutoGenerateColumns="false" OnRowDataBound="OnRowDataBound">
    <Columns>
        <asp:BoundField DataField="AccountIDTree" HeaderText="AccountID" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net

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

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindGrid();
    }
}

private void BindGrid()
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand("GetAccountTree", con))
        {
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            gvData.DataSource = cmd.ExecuteReader();
            gvData.DataBind();
            con.Close();
        }
    }
}

protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        string accountId = e.Row.Cells[0].Text;
        e.Row.Cells[0].Text = e.Row.Cells[0].Text.Replace(" "," ") ;
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindGrid()
    End If
End Sub

Private Sub BindGrid()
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(conString)
        Using cmd As SqlCommand = New SqlCommand("GetAccountTree", con)
            con.Open()
            cmd.CommandType = CommandType.StoredProcedure
            gvData.DataSource = cmd.ExecuteReader()
            gvData.DataBind()
            con.Close()
        End Using
    End Using
End Sub

Protected Sub OnRowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
    If e.Row.RowType = DataControlRowType.DataRow Then
        Dim accountId As String = e.Row.Cells(0).Text
        e.Row.Cells(0).Text = e.Row.Cells(0).Text.Replace(" ", " ")
    End If
End Sub

Output