To read the article online, visit http://www.4GuysFromRolla.com/articles/011205-1.aspx

A Code Template for a Pageable, Bi-Directional Sortable DataGrid

By Scott Mitchell


Introduction


Any ASP.NET developer that has had to display database data on an ASP.NET page is likely familiar with the DataGrid Web control. This handy control can be bound to data with just two lines of code and supports a number of useful features, such as paging, sorting, deleting, and editing of its data. (If you are unfamiliar with the DataGrid control, check out the An Extensive Examination of the DataGrid Web Control article series.) There have been articles here on 4Guys that have looked at how to page the records in a DataGrid - see Part 15 of the DataGrid article series - and how to sort records in a DataGrid - see Part 4 of the DataGrid article series - along with information on how to provide bi-directional sorting - see Part 12. There are articles on the Web that show how to build a DataGrid that is both pageable and sortable, such as my MSDN article Creating a Pageable, Sortable DataGrid.

As these myriad of articles show, adding paging and/or sorting support to a DataGrid is not a terribly difficult thing to do. It requires adding a few event handlers for the appropriate DataGrid events. The amount of code required in these event handlers is typically just a couple of lines of code, but can be longer when creating a pageable, bi-directional sortable DataGrid.

While the code for creating such DataGrids is not terribly difficult, it can quickly become tedious if you are building a Web site that contains numerous pages that have pageable, bi-directional sortable DataGrids. To overcome this tedium, I created a code template for building a pageable, bi-directional sortable DataGrid that I could use on multiple pages to quickly add one or more DataGrids to a page with minimal effort. This code template, specifically, is a class that extends the System.Web.UI.Page class, adding some additional methods and properties. Whenever I want to create a page that will require one or more pageable, bi-directional sortable DataGrids, I simply have that page's code-behind class derive from this custom class rather than from the System.Web.UI.Page class. Once this has been done, adding the required functionality to the DataGrid can be done with just a few lines of code.

In this article we'll examine this code template of mine, looking at the code itself as well as using it to quickly build an ASP.NET page with a pageable, bi-directional sortable DataGrid. This article does not delve into the specifics of creating a bi-directional sortable DataGrid or of creating a pageable, sortable DataGrid. If you are not familiar with these topics, I'd encourage you to first read An Extensive Examination of the DataGrid Web Control: Part 12 and Creating a Pageable, Sortable DataGrid.

The Essential Elements for a Pageable, Bi-Directional Sortable DataGrid


Before examining the code template, let's first talk about what pieces are required for creating a pageable, bi-directional sortable DataGrid. The DataGrid itself contains properties that record the DataGrid's current page, but does not contain any property recording its sort expression. When creating a DataGrid that is both pageable and sortable, we need to remember the sort expression across postbacks because if a user sorts the DataGrid and then steps to a different page, we want to make sure that we're showing them the specified page in the correct sort order. Therefore, we'll need a property that utilizes the page's view state to remember the sort expression. (See Creating a Pageable, Sortable DataGrid for a more thorough discussion on why remembering the sort expression across postbacks is vital.) When creating a bi-directional sortable DataGrid we also need to remember the direction with which the column was last sorted, and this information must survive postbacks.

So, at this point we've identified two properties we'll need in our code template: SortExpression, a string property that persists the sort expression across postbacks; and SortAscending, a Boolean property that indicates if the data is currently sorted in ascending or descending order.

In addition to these two properties, when creating a pageable, bi-directional sortable DataGrid we need to create event handlers for the DataGrid's PageIndexChanged and SortCommand events, which fire when the DataGrid is paged or sorted, respectively. The code template does not contain event handlers since the code template doesn't contain a DataGrid itself. Rather, the code template contains methods that the actual ASP.NET page with the DataGrid will call from the DataGrid's event handlers. (If this sounds a bit confusing, don't worry; once we look at the code template code and examine using the code template things should become clearer.)

Examining BidirectionalSortableDataGridPage


I created the pageable, bi-directional sortable DataGrid code template as a class called BidirectionalSortableDataGridPage that extends the System.Web.UI.Page class. The class's three germane parts are shown below, one at a time; the complete code can be downloaded at the end of this article.

Part 1: The Properties


public class BidirectionalSortableDataGridPage : System.Web.UI.Page
{
    protected string SortExpression
    {
        get
        {
            object o = ViewState["SortExpression"];
            if (o == null)
                return string.Empty;
            else
                return o.ToString();
        }
        set
        {
            ViewState["SortExpression"] = value;
        }
    }

    protected bool SortAscending
    {
        get
        {
            object o = ViewState["SortAscending"];
            if (o == null)
                return true;
            else
                return Convert.ToBoolean(o);
        }
        set
        {
            ViewState["SortAscending"] = value;
        }
    }

    ...
}

The first thing to notice here is that the BidirectionalSortableDataGridPage class extends the System.Web.UI.Page class. Next, take note of the two properties, SortExpression and SortAscending. Both use the Page class's ViewState StateBag to store their values. The page's view state is used so that these property values are persisted correctly across postbacks. If we had just used a private member variable to hold these properties' values the values would be lost on a non-sorting-related postback, such as when the user stepped to a different page of data.

Part 2: Code to Be Called by the Paging and Sorting Event Handlers


    ...

    protected void HandleSorting(DataGrid dg, string sortExpr)
    {
        dg.CurrentPageIndex = 0;

        // if clicking on same, sorted column then...
        if (SortExpression == sortExpr)
            SortAscending = !SortAscending;
        else
            SortAscending = true;  // reset ascending to true 
                                   // when sorting by a new column

        SortExpression = sortExpr;
    }

    protected void HandlePaging(DataGrid dg, int newPageIndex)
    {
        // disable editing
        dg.EditItemIndex = -1;

        dg.CurrentPageIndex = newPageIndex;
    }
    
    ...

The HandleSorting() and HandlePaging() methods will be called from the ASP.NET page that extends this code template class, specifically from the DataGrid's SortCommand and PageIndexChanged event handlers. Note that both methods accept a DataGrid as an input parameter along with either the sort expression the DataGrid is to be sorted by or the page index that the user wants to visit. The HandleSorting() method starts by resetting the DataGrid to the first page and then updates the SortExpression and SortAscending properties accordingly. The HandlePaging() method disables the DataGrid's editing capabilities (which is handy if the DataGrid you are working with is not only pageable and bi-directional sortable, but also editable), and then sets the DataGrid's CurrentPageIndex property to the new page index. (If the purpose of these two methods still seems a bit murky, wait until we see an example of using the code template in an ASP.NET page - things should become clearer then.)

Part 3: The BindData() Method
    ...

    protected void BindData(DataGrid dg, object dataSource)
    {
        // Update the columns in the DataGrid
        foreach(DataGridColumn c in dg.Columns)
        {
            // Clear any <img> tags that might be present
            c.HeaderText = Regex.Replace(c.HeaderText, 
                    "&nbsp;&lt;.*&gt;", String.Empty);

            if (SortExpression != string.Empty && 
                c.SortExpression == SortExpression)
            {
                if (SortAscending)
                    c.HeaderText += string.Concat(" <img src=\"", 
                          dg.ResolveUrl("~/images/up.gif"), 
                          "\" border=\"0\" width=\"11\" height=\"7\" />");
                else
                    c.HeaderText += string.Concat(" <img src=\"", 
                          dg.ResolveUrl("~/images/down.gif"), 
                          "\" border=\"0\" width=\"11\" height=\"7\" />");
            }
        }

        dg.DataSource = dataSource;
        dg.DataBind();
    }
}

The code template has a BindData() method because I wanted to add an image next to the column the data was currently sorted on: an up arrow if the data was sorted in ascending order, a down arrow if it was sorted in descending order. By creating this BindData() method I could have this image-setting code in one place, rather than having to replicate it in each page.

The image-setting code works in the following manner: it starts by looping through each column in the DataGrid removing any image tag from the column's HeaderText property. It then checks to see if the SortExpression property equals the current column's SortExpression property. If so, it places an up or down arrow, depending on whether or not the column is sorted in ascending or descending order. (The up and down GIF files are included in the download at the end of this article.)

Creating an ASP.NET Page Using the Code Template


Let's look at creating an ASP.NET page that utilizes the code template. To start, create a new ASP.NET page and add a DataGrid to the page, setting its ID to myDataGrid. Next, have the page's code-behind class derive from BidirectionalSortableDataGridPage. At this point your code-behind class should look like:

public class WebForm1 : BidirectionalSortableDataGridPage
{
    protected System.Web.UI.WebControls.DataGrid myDataGrid;
    
    private void Page_Load(object sender, System.EventArgs e)
    {
        // Put user code to initialize the page here
    }

    ...
}

Next, create a method in your code-behind class called GetDataAndBindToGrid() that accesses the database data, sorts it properly, and then calls the base class's BindData() method. GetDataAndBindToGrid() should be called from the Page_Load event handler on the first page visit (but not on subsequent postbacks).

private void GetDataAndBindToGrid()
{
    // get the data in the properly sorted order
    string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                    Server.MapPath("SampleDB.mdb");
    using (OleDbConnection myConnection = new OleDbConnection(connString))
    {
        string sql = "SELECT * FROM Employees ";

        // Specify that the data should be sorted, if needed...
        if (base.SortExpression != string.Empty)
            sql += "ORDER BY " + base.SortExpression;
        if (!base.SortAscending)
            sql += " DESC";

        OleDbCommand myCommand = new OleDbCommand(sql, myConnection);
        OleDbDataAdapter myAdapter = new OleDbDataAdapter(myCommand);

        DataTable dt = new DataTable();
                
        // Populate the DataTable with the results of the query
        myConnection.Open();
        myAdapter.Fill(dt);
        myConnection.Close();

        // now, bind the Data to the DataGrid
        base.BindData(myDataGrid, dt);
    }
}

For this demo I am grabbing some data from the Access database SampleDB.mdb. This database has an Employees table with fields EmployeeID, LastName, FirstName, and Salary. The data is retrieved sorted according to the base class's SortExpression and SortAscending property values and is populated in a DataTable. The base class's BindData method is then called, passing in the DataGrid and the DataTable.

All that remains is to create the event handlers for the DataGrid's PageIndexChanged and SortCommand event handlers. The code for these event handlers are incredibly simple since the more complicated logic is handed in the base class's HandleSorting() and HandlePaging() methods.

private void myDataGrid_SortCommand(object source, 
             System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
{
    base.HandleSorting(myDataGrid, e.SortExpression);
    GetDataAndBindToGrid();
}

private void myDataGrid_PageIndexChanged(object source, 
             System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
    base.HandlePaging(myDataGrid, e.NewPageIndex);
    GetDataAndBindToGrid();
}

And that's all there is to it! As you can see, in our ASP.NET page all we had to do was write a method to grab the data in the correctly sorted order and then add these two very short event handlers. With this minimal amount of code and effort we now have a pageable, bi-directional sortable DataGrid. The following screenshots show the DataGrid in action:

Page 1, Sorted by Last Name in Ascending Order


Page 1, Sorted by Salary in Descending Order


Page 3, Sorted by Salary in Descending Order


Conclusion


In this article we saw how to build a reusable code template to ease the tedium of creating pageable, bi-directional sortable DataGrids. This code template was implemented as a class that extended the System.Web.UI.Page class and could be inherited by a code-behind class for easily creating DataGrids. An ASP.NET page that extends this class only needs to access the specific data, sorting it accordingly, and create two simple event handlers. The rest of the logic is encapsulated within the base class.

Happy Programming!

  • By Scott Mitchell


    Attachments


  • Download the complete code and support files (in ZIP format)

  • Article Information
    Article Title: ASP.NET.A Code Template for a Pageable, Bi-Directional Sortable DataGrid
    Article Author: Scott Mitchell
    Published Date: January 12, 2005
    Article URL: http://www.4GuysFromRolla.com/articles/011205-1.aspx


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers