[asp.net] sorting and paging with gridview asp.net

I'm trying to get a gridview to sort and page manually with no success.

The problem is that when a user clicks the column they want to sort, it sorts that page, but doesn't sort the datasource (dataview) behind the gridview. So when they progress to a different page, their sort is lost. Pretty much I'm looking for a sort that will actually sort the datasource behind the gridview. Here is what I have so far:

protected void GridView_OnSort(object sender, GridViewSortEventArgs e)
    {
        String sortExpression = e.SortExpression;

        if (GridViewSortDirection == SortDirection.Ascending)
        {
            DataView myDataView = new DataView(mybll.GetItemsOrdered());
            myDataView.Sort = sortExpression + " DESC";
            GridView.DataSource = myDataView;
            GridView.DataBind();
        }
        else
        {
            DataView myDataView = new DataView(mybll.GetItemsOrdered());
            myDataView.Sort = sortExpression + " ASC";
            GridView.DataSource = myDataView;
            GridView.DataBind();
        }
    }

Any help would be appreciated. Thanks.

I found a much easier way, which allows you to still use the built in sorting/paging of the standard gridview...

create 2 labels. set them to be visible = false. I called mine lblSort1 and lblSortDirection1

then code 2 simple events... the page sorting, which writes to the text of the invisible labels, and the page index changing, which uses them...

Private Sub gridview_Sorting(sender As Object, e As GridViewSortEventArgs) Handles gridview.Sorting
lblSort1.Text = e.SortExpression
lblSortDirection1.Text = e.SortDirection
End Sub

Private Sub gridview_PageIndexChanging(sender As Object, e As GridViewPageEventArgs) Handles gridview.PageIndexChanging
    gridview.Sort(lblSort1.Text, CInt(lblSortDirection1.Text))
End Sub

this is a little sloppier than using global variables, but I've found with asp especially that global vars are, well, unreliable...

<asp:GridView 
    ID="GridView1" runat="server" AutoGenerateColumns="false" AllowSorting="True" onsorting="GridView1_Sorting" EnableViewState="true"> 
    <Columns>
        <asp:BoundField DataField="bookid" HeaderText="BOOK ID"SortExpression="bookid"  />
        <asp:BoundField DataField="bookname" HeaderText="BOOK NAME" />
        <asp:BoundField DataField="writer" HeaderText="WRITER" />
        <asp:BoundField DataField="totalbook" HeaderText="TOTALBOOK" SortExpression="totalbook"  />
        <asp:BoundField DataField="availablebook" HeaderText="AVAILABLE BOOK" />
    </Columns>
</asp:GridView>

Code behind:

protected void Page_Load(object sender, EventArgs e) {
        if (!IsPostBack) {
            string query = "SELECT * FROM book";
            DataTable DT = new DataTable();
            SqlDataAdapter DA = new SqlDataAdapter(query, sqlCon);
            DA.Fill(DT);

            GridView1.DataSource = DT;
            GridView1.DataBind();
        }
    }

    protected void GridView1_Sorting(object sender, GridViewSortEventArgs e) {

        string query = "SELECT * FROM book";
        DataTable DT = new DataTable();
        SqlDataAdapter DA = new SqlDataAdapter(query, sqlCon);
        DA.Fill(DT);

        GridView1.DataSource = DT;
        GridView1.DataBind();

        if (DT != null) {
            DataView dataView = new DataView(DT);
            dataView.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(e.SortDirection);

            GridView1.DataSource = dataView;
            GridView1.DataBind();
        }
    }

    private string GridViewSortDirection {
        get { return ViewState["SortDirection"] as string ?? "DESC"; }
        set { ViewState["SortDirection"] = value; }
    }

    private string ConvertSortDirectionToSql(SortDirection sortDirection) {
        switch (GridViewSortDirection) {
            case "ASC":
                GridViewSortDirection = "DESC";
                break;

            case "DESC":
                GridViewSortDirection = "ASC";
                break;
        }

        return GridViewSortDirection;
    }
}

More simple way...:

    Dim dt As DataTable = DirectCast(GridView1.DataSource, DataTable)
    Dim dv As New DataView(dt)

    If GridView1.Attributes("dir") = SortDirection.Ascending Then
        dv.Sort = e.SortExpression & " DESC" 
        GridView1.Attributes("dir") = SortDirection.Descending

    Else
        GridView1.Attributes("dir") = SortDirection.Ascending
        dv.Sort = e.SortExpression & " ASC"

    End If

    GridView1.DataSource = dv
    GridView1.DataBind()

Tarkus's answer works well. However, I would suggest replacing VIEWSTATE with SESSION.

The current page's VIEWSTATE only works while the current page posts back to itself and is gone once the user is redirected away to another page. SESSION persists the sort order on more than just the current page's post-back. It persists it across the entire duration of the session. This means that the user can surf around to other pages, and when he comes back to the given page, the sort order he last used still remains. This is usually more convenient.

There are other methods, too, such as persisting user profiles.

I recommend this article for a very good explanation of ViewState and how it works with a web page's life cycle: https://msdn.microsoft.com/en-us/library/ms972976.aspx

To understand the difference between VIEWSTATE, SESSION and other ways of persisting variables, I recommend this article: https://msdn.microsoft.com/en-us/library/75x4ha6s.aspx