Sorting in gridview is not working

Question

Dear All, I am trying Sorting functionality in Grid view but its not working.Can some body help?

Code:

  private string ConvertSortDirectionToSql(SortDirection sortDirection)
{
    string newSortDirection = String.Empty;

    switch (sortDirection)
    {
        case SortDirection.Ascending:
            newSortDirection = "ASC";
            break;

        case SortDirection.Descending:
            newSortDirection = "DESC";
            break;
    }

    return newSortDirection;
}
protected DataSet FillDataSet()
{
    string source = "Database=GridTest;Server=Localhost;Trusted_Connection=yes";
    con = new SqlConnection(source);
    cmd = new SqlCommand("proc_mygrid", con);
    ds = new DataSet();
    da = new SqlDataAdapter(cmd);
    da.Fill(ds);
    GridView1.DataSource = ds;
    GridView1.DataBind();

    return ds;


}
 protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
    DataTable dt = GridView1.DataSource as DataTable;
    if (dt != null)
    {
        DataView dv = new DataView(dt);
        dv.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(e.SortDirection);
        GridView1.DataSource = dv;
        GridView1.DataBind();
   }

Here dt is coming null.why? pls help thanks.

EDIT:

enter code here  <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" 
    GridLines="None" AllowPaging="true" AllowSorting="true" PageSize="12" 
    onpageindexchanging="GridView1_PageIndexChanging" 
    onsorting="GridView1_Sorting">

EDIT(Total code)

public partial class _Default : System.Web.UI.Page 
{
    SqlConnection con;
    SqlCommand cmd;
    DataSet ds;
    SqlDataAdapter da;
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    private string ConvertSortDirectionToSql(SortDirection sortDirection)
    {
        string newSortDirection = String.Empty;

        switch (sortDirection)
        {
            case SortDirection.Ascending:
                newSortDirection = "ASC";
                break;

            case SortDirection.Descending:
                newSortDirection = "DESC";
                break;
        }

        return newSortDirection;
    }
    protected DataSet FillDataSet()
    {
        string source = "Database=GridTest;Server=Localhost;Trusted_Connection=yes";
        con = new SqlConnection(source);
        cmd = new SqlCommand("proc_mygrid", con);
        ds = new DataSet();
        da = new SqlDataAdapter(cmd);
        da.Fill(ds);
        GridView1.DataSource = ds;
        GridView1.DataBind();

        return ds;


    }
    protected void GetValues(object sender, EventArgs e)
    {
        FillDataSet();
    }

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
       int newPagenumber = e.NewPageIndex;
       GridView1.PageIndex = newPagenumber;
       GridView1.DataSource = FillDataSet();
       GridView1.DataBind();

    }


    protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
    {

        DataSet ds = FillDataSet();
        DataTable dt = ds.Tables[0];
        if (dt != null)
        {
            dt.DefaultView.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(e.SortDirection);
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }

Answer 1

Because you're setting a DataSet as DataSource and then casting it to DataTable with the operator as.

The 'as' operator in C# is a tentative cast - if it's impossible to cast (types are not compatible) instead of throwing an exception as the direct cast the 'as' operator sets the reference to null.

If you only have one datatable in your dataset then you can get the first element like this:

ds.Tables[0];

... or use the name of the table:

ds.Tables["myTable"];

in your case you can try...

DataTable dt = GridView1.DataSource.Tables[0] as DataTable;

Hope it helps!

EDIT:

with regards to your sorting problem (once you get the datatable):

if (dt != null) 
{ 
   dt.DefaultView.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(e.SortDirection); 
   GridView1.DataBind(); 
}

You can do this because dt is a reference to the exact same object that's already set as DataSource for your grid. It should do the trick - if not there's smt else I am missing (such as we're sorting the wrong table meaning you have more than one table in the DataSet).

EDIT:

had a look at your code. I don't know exactly when GetValues gets fired but I suspect it's causing your problem (I think it might be overriding your sorting or smt along those lines).

If you comment out FillDataSource from getValues and modify your PageLoad to do this:

  void Page_Load(Object sender, EventArgs e)
  { 

     // Load data only once, when the page is first loaded.
     if (!IsPostBack)
     { 
        Session["myDataSet"] = FillDataSet();
     }

  }

then in your sort method you retrieve the DataSource like this:

DataTable dt = ((DataSet)Session["myDataSet"]).Tables[0];

Also you can retrieve the DataSet from session in your pagination handler method.

You should also notice an improvement in performance since you're retrieving the stuff form the db just once.

Give it a shot!

Answer 2

CODE:

  private string GetSortDirection(string column)

{

string sortDirection = "DESC";


string sortExpression = ViewState["SortExpression"] as string;

if (sortExpression != null)
{

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


ViewState["SortDirection"] = sortDirection;
ViewState["SortExpression"] = column;

return sortDirection;

}

 protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)

{

DataTable dtbl = ((DataSet)Session["myDataSet"]).Tables[0];
dtbl.DefaultView.Sort = e.SortExpression + " " + GetSortDirection(e.SortExpression);
GridView1.DataSource = dtbl;
GridView1.DataBind();

}