Gridview Sorting, Updating and Editing

Question

Hello all - seasons greetings to you all. I really need some help as this is driving me mad.

The aim. I have a textbox which a client enters a number, they click on a radio button list to select a material and hit a button to populate a gridview.

This is the front end code:

<asp:TextBox ID="tbxHowMany" runat="server" 
        style="z-index: 1; left: 300px; top: 250px; position: absolute"></asp:TextBox>

    <asp:Button ID="btnDisplayTopReport" runat="server" 
        style="z-index: 1; left: 645px; top: 285px; position: absolute; height: 25px; width: 170px" 
        Text="Display TOP Report" onclick="btnDisplayTopReport_Click" />

    <asp:RadioButtonList ID="radTOP" runat="server" 
        style="z-index: 1; left: 575px; top: 180px; position: absolute; height: 177px; width: 86px">
        <asp:ListItem>Paper</asp:ListItem>
        <asp:ListItem>Glass</asp:ListItem>
        <asp:ListItem>Aluminium</asp:ListItem>
        <asp:ListItem>Steel</asp:ListItem>
        <asp:ListItem>Plastic</asp:ListItem>
        <asp:ListItem>Wood</asp:ListItem>
        <asp:ListItem>Other</asp:ListItem>
    </asp:RadioButtonList>

    <asp:LinqDataSource ID="LQTOPReportDS" runat="server" 
        ContextTypeName="CompleteWeightsDataContext" 
        EnableUpdate="True" TableName="tblOnlineReportingCOMPLETEWeights"
        Where="IDDesc == @IDDesc && UnitUserfield1 == @UnitUserfield1 && UnitUserfield2 == @UnitUserfield2 && MaterialLevel == @MaterialLevel"
        OrderBy="UnitId, MaterialLevel, MaterialText, UnitWeight"
        StoreOriginalValuesInViewState="True">
    </asp:LinqDataSource>

    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
        AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" 
        DataKeyNames="PriKey" DataSourceID="LQTOPReportDS" ForeColor="#333333" 
        GridLines="None"  Font-Size="X-Small" 
        style="z-index: 1; left: 5px; top: 375px; position: absolute; height: 133px; width: 187px" 
        onpageindexchanging="GridView1_PageIndexChanging" 
            onrowediting="GridView1_RowEditing" onrowupdating="GridView1_RowUpdating" 
            onsorting="GridView1_Sorting">
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
        <Columns>
            <asp:CommandField ShowEditButton="True" />
           <asp:BoundField DataField="UnitId" HeaderText="UnitId" 
                SortExpression="UnitId" />
            <asp:BoundField DataField="UnitDescription" HeaderText="UnitDescription" 
                SortExpression="UnitDescription" />
            <asp:BoundField DataField="PackagingGroupId" HeaderText="PackagingGroupId" 
                SortExpression="PackagingGroupId" />
            <asp:CheckBoxField DataField="IsPackagingGroup" HeaderText="IsPackagingGroup" 
                SortExpression="IsPackagingGroup" />
            <asp:BoundField DataField="PackagingTypeCode" HeaderText="PackagingTypeCode" 
                SortExpression="PackagingTypeCode" />
            <asp:BoundField DataField="UnitWeight" HeaderText="UnitWeight" 
                SortExpression="UnitWeight" />
            <asp:BoundField DataField="WeightUnitCode" HeaderText="WeightUnitCode" 
                SortExpression="WeightUnitCode" />
            <asp:BoundField DataField="MaterialLevel" HeaderText="MaterialLevel" 
                SortExpression="MaterialLevel" />
            <asp:BoundField DataField="MaterialText" HeaderText="MaterialText" 
                SortExpression="MaterialText" />
            <asp:BoundField DataField="ProductPercentage" HeaderText="ProductPercentage" 
                SortExpression="ProductPercentage" />
             <asp:BoundField DataField="UnitUserfield2" HeaderText="UnitUserfield2" 
                SortExpression="UnitUserfield2" />
            <asp:BoundField DataField="Comment" HeaderText="Comment" 
                SortExpression="Comment" />

        </Columns>
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#999999" />
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    </asp:GridView>

And this is the code behind:

public partial class TOP : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Session["MemberKey"] = "FM00012";

            GridView1.DataSourceID = null;
            GridView1.DataBind();
        }
    }

    private object GetMaterialData(string MemberKey, string MaterialType, string MaterialLevel, int Count)
    {
        CompleteWeightsDataContext db = new CompleteWeightsDataContext();
        var query = db.tblOnlineReportingCOMPLETEWeights
                    .Where(x => x.MemberId == MemberKey && x.MaterialText == MaterialType && x.MaterialLevel == MaterialLevel)
                    .OrderByDescending(x => x.ProductPercentage)
                    .Take(Count);
        return query;
      }

       protected void btnDisplayTopReport_Click(object sender, EventArgs e)
    {
        GridView1.DataSourceID = null;
        GridView1.DataBind();

        if (radTOP.SelectedValue == "" || tbxHowMany.Text == "")
        {
            MessageBox.Show("Please Ensure that BOTH 'The Number of Products' and Appropriate material Is selected Before You Attempt To Run a TOP X Report", "Top X Error!!!",
                 MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            GridView1.DataSourceID = null;
            GridView1.DataBind();
        }
       else
        {
            int max = 0;
            if (int.TryParse(tbxHowMany.Text, out max))
            {
                GridView1.DataSource = GetMaterialData(Session["MemberKey"].ToString(), radTOP.SelectedItem.Value, "Primary", max);
                GridView1.DataBind();
            }
        }
    }


    protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
    {

    }

}

Now I know I have to populate the sorting event but I have tried sooo many combinations, I have got myself in a right pickle (hence the null!).

I suspect I will have difficulty as the linq that it is using to populate the gridview is using take so the data might be lost when the sorting is run.

Can someone please point me in the right direction and how I can achieve this? Similarly, I know I will encounter similar problems with the rowupdating and rowediting so if I can have suggests for achieving the desired results, I would be most grateful...in fact, you are welcome to join me for a festive drink at my local!

Answer 1

I'm not totally sure what you are trying to do, but generally, a sort handler is used for sorting columns in a gridview. You can refer to this sample code below, in which I'm handling sorting by ascending or descending, and also caching so I don't have to hit the database again when they sort by a column.

But in general, you want to update the data source for the grid view and rebind it with the sorted result.

/// <summary>
/// <para>Event handler for sorting items in the grid. It allows the user to sort by any column in the grid.
/// The method makes use of caching to reduce access to the persistence layer.</para>
/// </summary>
/// <param name="sender">The sender which triggered the event.</param>
/// <param name="e">The event arguments.</param>
protected void gvFileList_Sorting(object sender, GridViewSortEventArgs e)
{
    string sortExpression;

    DataSet ds = (DataSet)HttpRuntime.Cache[Session.SessionID + "dsFileList"];
    if (ds == null)
    {
        LoadGridData();
        ds = (DataSet)HttpRuntime.Cache[Session.SessionID + "dsFileList"];
    }
    ds.Tables[0].DefaultView.Sort = e.SortExpression;

    // check the cache to see if user has sorted by this column (i.e. this SortExpression)
    // previously, and if they have, reverse the sort direction
    if (HttpRuntime.Cache[Session.SessionID + "dsFileList" + e.SortExpression] != null)
    {
        // get the previous sort direction from the cache
        string dir = (string)HttpRuntime.Cache[Session.SessionID + "dsFileList" + e.SortExpression];

        // reverse the sort direction and update the cache
        if (dir == "ASC")
        {
            Helper.AddToCache(Session.SessionID + "dsFileList" + e.SortExpression, "DESC", 120);
            ds.Tables[0].DefaultView.Sort += " DESC";
            sortExpression = e.SortExpression + " DESC";
        }
        else
        {
            Helper.AddToCache(Session.SessionID + "dsFileList" + e.SortExpression, "ASC", 120);
            ds.Tables[0].DefaultView.Sort += " ASC";
            sortExpression = e.SortExpression + " ASC";
        }
    }
    else
    {
        // this column has not been sorted on previously, so update the cache with
        // sort direction = ASC for this column since ascending is the initial
        // sort direction
        Helper.AddToCache(Session.SessionID + "dsFileList" + e.SortExpression, "ASC", 120);
        sortExpression = e.SortExpression + " ASC";
    }

    gvFileList.DataSource = ds.Tables[0].DefaultView;
    gvFileList.DataBind();

    // store the sort expression in cache so that when the grid is refreshed by the tmTFFileList_Tick
    // method, we can get the sort expression to use
    Helper.AddToCache(Session.SessionID + "dsFileList.SortExpression", sortExpression, 120);
}