Maintain sorting through out Paging in ASP.Net GridView using C# and VB.Net

Hi georgeacuster,

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

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<asp:GridView ID="gvCustomers" runat="server" CssClass="table table-bordered table-striped"
    AutoGenerateColumns="false" AllowPaging="true" AllowSorting="true" OnSorting="gvCustomers_Sorting"
    PageSize="10" DataKeyNames="CustomerID" OnPageIndexChanging="gvCustomers_PageIndexChanging"
    PagerStyle-HorizontalAlign="Center" PagerSettings-Mode="NumericFirstLast"
    PagerSettings-Position="TopAndBottom" EmptyDataText="No Data Found" ShowHeaderWhenEmpty="true">
    <Columns>
        <asp:TemplateField HeaderText="#" ItemStyle-HorizontalAlign="Center">
            <ItemTemplate>
                <asp:Label ID="lblNumber" runat="server" Text='<%#Container.DataItemIndex+1 %>'></asp:Label>
            </ItemTemplate>
            <HeaderStyle HorizontalAlign="Left" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="CustomerId" SortExpression="CustomerId">
            <ItemTemplate>
                <asp:Label ID="lblCustomerId" runat="server" Text='<%# Eval("CustomerId") %>'></asp:Label>
            </ItemTemplate>
            <HeaderStyle HorizontalAlign="Left" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ContactName" SortExpression="ContactName">
            <ItemTemplate>
                <asp:Label ID="lblContactName" runat="server" Text='<%# Eval("ContactName") %>'></asp:Label>
            </ItemTemplate>
            <HeaderStyle HorizontalAlign="Left" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="City" SortExpression="City">
            <ItemTemplate>
                <asp:Label ID="lblCity" runat="server" Text='<%# Eval("City") %>'></asp:Label>
            </ItemTemplate>
            <HeaderStyle HorizontalAlign="Left" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Country" SortExpression="Country">
            <ItemTemplate>
                <asp:Label ID="lblCountry" runat="server" Text='<%# Eval("Country") %>'></asp:Label>
            </ItemTemplate>
            <HeaderStyle HorizontalAlign="Left" />
        </asp:TemplateField>
    </Columns>
    <PagerSettings Mode="NumericFirstLast" />
    <PagerStyle BorderStyle="None" CssClass="pagination-ys" />
</asp:GridView>

Namespaces

C#

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

VB.Net

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

Code

C#

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

public class Customer
{
    public string CustomerId { get; set; }
    public string ContactName { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
}


List<Customer> listCustomer = new List<Customer>();

public List<Customer> GetAllCustomers(string StoreId)
{
    DataTable dt = new DataTable();
    string constring = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT CustomerID, ContactName, City, Country from Customers", con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                sda.Fill(dt);
            }
        }
    }

    List<Customer> customers = new List<Customer>();
    if (!string.IsNullOrEmpty(StoreId))
    {
        customers = dt.AsEnumerable()
            .Where(x => x["CustomerId"].ToString() == StoreId)
            .Select(x => new Customer
            {
                CustomerId = x["CustomerID"].ToString(),
                ContactName = x["ContactName"].ToString(),
                City = x["City"].ToString(),
                Country = x["Country"].ToString()
            }).ToList();
    }
    else
    {
        customers = dt.AsEnumerable()
            .Select(x => new Customer
            {
                CustomerId = x["CustomerID"].ToString(),
                ContactName = x["ContactName"].ToString(),
                City = x["City"].ToString(),
                Country = x["Country"].ToString()
            }).ToList();
    }

    return customers;
}


private void GetAllCustomers()
{
    listCustomer = GetAllCustomers(Convert.ToString(Session["StoreId"]));
    BindDataToGrid();
}

public void BindDataToGrid()
{
    if (listCustomer.Count != 0)
    {
        gvCustomers.DataSource = listCustomer;
        gvCustomers.DataBind();
    }
    else
    {
        DataTable dt = new DataTable();
        gvCustomers.DataSource = dt;
        gvCustomers.DataBind();
    }
}

protected void gvCustomers_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    gvCustomers.PageIndex = e.NewPageIndex;
    string sortDirection = ViewState["SortDirection"] != null ? ViewState["SortDirection"].ToString() : "ASC";
    string sortExpression = ViewState["SortExpression"] != null ? ViewState["SortExpression"].ToString() : "CustomerId";

    var list = GetAllCustomers(Convert.ToString(Session["StoreId"]));
    if (sortDirection == "ASC")
    {
        listCustomer = Sort<Customer>(list, sortExpression, SortDirection.Ascending);
    }
    else
    {
        listCustomer = Sort<Customer>(list, sortExpression, SortDirection.Descending);
    }
    BindDataToGrid();
}

protected void gvCustomers_Sorting(object sender, GridViewSortEventArgs e)
{
    string Sortdir = GetSortDirection(e.SortExpression);
    string SortExp = e.SortExpression;

    var list = GetAllCustomers(Convert.ToString(Session["StoreId"]));
    if (Sortdir == "ASC")
    {
        listCustomer = Sort<Customer>(list, SortExp, SortDirection.Ascending);
    }
    else
    {
        listCustomer = Sort<Customer>(list, SortExp, SortDirection.Descending);
    }
    BindDataToGrid();
}

public List<Customer> Sort<TKey>(List<Customer> list, string sortBy, SortDirection direction)
{
    PropertyInfo property = list.GetType().GetGenericArguments()[0].GetProperty(sortBy);
    if (direction == SortDirection.Ascending)
    {
        return list.OrderBy(e => property.GetValue(e, null)).ToList<Customer>();
    }
    else
    {
        return list.OrderByDescending(e => property.GetValue(e, null)).ToList<Customer>();
    }
}

private string GetSortDirection(string column)
{
    string sortDirection = "ASC";
    string sortExpression = ViewState["SortExpression"] as string;
    if (sortExpression != null)
    {
        if (sortExpression == column)
        {
            string lastDirection = ViewState["SortDirection"] as string;
            if ((lastDirection != null) && (lastDirection == "ASC"))
            {
                sortDirection = "DESC";
            }
            else
            {
                sortDirection = "ASC";
            }
        }
    }
    ViewState["SortDirection"] = sortDirection;
    ViewState["SortExpression"] = column;
    return sortDirection;
}

VB.Net

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

Public Class Customer
    Public Property CustomerId As String
    Public Property ContactName As String
    Public Property City As String
    Public Property Country As String
End Class

Private listCustomer As List(Of Customer) = New List(Of Customer)()

Public Function GetAllCustomers(ByVal StoreId As String) As List(Of Customer)
    Dim dt As DataTable = New DataTable()
    Dim constring As String = ConfigurationManager.ConnectionStrings("constring").ConnectionString

    Using con As SqlConnection = New SqlConnection(constring)

        Using cmd As SqlCommand = New SqlCommand("SELECT CustomerID, ContactName, City, Country from Customers", con)

            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                sda.Fill(dt)
            End Using
        End Using
    End Using

    Dim customers As List(Of Customer) = New List(Of Customer)()

    If Not String.IsNullOrEmpty(StoreId) Then
        customers = dt.AsEnumerable().Where(Function(x) x("CustomerId").ToString() = StoreId).[Select](Function(x) New Customer With {
    .CustomerId = x("CustomerID").ToString(),
    .ContactName = x("ContactName").ToString(),
    .City = x("City").ToString(),
    .Country = x("Country").ToString()
}).ToList()
    Else
        customers = dt.AsEnumerable().[Select](Function(x) New Customer With {
    .CustomerId = x("CustomerID").ToString(),
    .ContactName = x("ContactName").ToString(),
    .City = x("City").ToString(),
    .Country = x("Country").ToString()
}).ToList()
    End If

    Return customers
End Function

Private Sub GetAllCustomers()
    listCustomer = GetAllCustomers(Convert.ToString(Session("StoreId")))
    BindDataToGrid()
End Sub

Public Sub BindDataToGrid()
    If listCustomer.Count <> 0 Then
        gvCustomers.DataSource = listCustomer
        gvCustomers.DataBind()
    Else
        Dim dt As DataTable = New DataTable()
        gvCustomers.DataSource = dt
        gvCustomers.DataBind()
    End If
End Sub

Protected Sub gvCustomers_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
    gvCustomers.PageIndex = e.NewPageIndex
    Dim _sortDirection As String = If(ViewState("SortDirection") IsNot Nothing, ViewState("SortDirection").ToString(), "ASC")
    Dim sortExpression As String = If(ViewState("SortExpression") IsNot Nothing, ViewState("SortExpression").ToString(), "CustomerId")
    Dim list = GetAllCustomers(Convert.ToString(Session("StoreId")))
    If _sortDirection = "ASC" Then
        listCustomer = Sort(Of Customer)(list, sortExpression, SortDirection.Ascending)
    Else
        listCustomer = Sort(Of Customer)(list, sortExpression, SortDirection.Descending)
    End If
    BindDataToGrid()
End Sub

Protected Sub gvCustomers_Sorting(ByVal sender As Object, ByVal e As GridViewSortEventArgs)
    Dim Sortdir As String = GetSortDirection(e.SortExpression)
    Dim SortExp As String = e.SortExpression
    Dim list = GetAllCustomers(Convert.ToString(Session("StoreId")))
    If Sortdir = "ASC" Then
        listCustomer = Sort(Of Customer)(list, SortExp, SortDirection.Ascending)
    Else
        listCustomer = Sort(Of Customer)(list, SortExp, SortDirection.Descending)
    End If
    BindDataToGrid()
End Sub

Public Function Sort(Of TKey)(ByVal list As List(Of Customer), ByVal sortBy As String, ByVal direction As SortDirection) As List(Of Customer)
    Dim [property] As PropertyInfo = list.GetType().GetGenericArguments()(0).GetProperty(sortBy)
    If direction = SortDirection.Ascending Then
        Return list.OrderBy(Function(e) [property].GetValue(e, Nothing)).ToList()
    Else
        Return list.OrderByDescending(Function(e) [property].GetValue(e, Nothing)).ToList()
    End If
End Function

Private Function GetSortDirection(ByVal column As String) As String
    Dim sortDirection As String = "ASC"
    Dim sortExpression As String = TryCast(ViewState("SortExpression"), String)
    If sortExpression IsNot Nothing Then
        If sortExpression = column Then
            Dim lastDirection As String = TryCast(ViewState("SortDirection"), String)
            If (lastDirection IsNot Nothing) AndAlso (lastDirection = "ASC") Then
                sortDirection = "DESC"
            Else
                sortDirection = "ASC"
            End If
        End If
    End If
    ViewState("SortDirection") = sortDirection
    ViewState("SortExpression") = column
    Return sortDirection
End Function

Screenshot