Sorting and paging with a custom DataGrid class

• Download source files - 5.04 Kb
• Download demo project - 24.7 Kb

Introduction

ASP.NET DataGrid is a powerful component and utilizing the sorting and the paging features of it is quite simple which requires adding only a few lines of code. However as a class library builder, I want to have a DataGrid class that has all the built-in features of sorting and paging so that the programmers don't have to even write those few lines of code. The updated version of the article provides the sorting feature for HyperLink Column and Template Column besides the Bound Column from the original version.

Using the code

The DLL contains the following classes: CmdButtonForm, SPDataGrid, DataGridNavigationBar and the OridinalProvider. After adding the reference to the DLL and adding the DLL to the ToolBox, you can drag and drop the latter two controls to your Web page in Design mode. The HTML code should contain a reference to the DLL as given in the code below:

<%@ Register TagPrefix="ntm" Namespace="NTM.Controls" Assembly="SPDataGrid" %>

Your web page should be inherited from the CmdButtonForm class like this:

public class WebForm1 : NTM.Controls.CmdButtonForm

You can utilize either both the sorting and the paging features of the SPDataGrid class or just one of them. Firstly, the ConnectionStr property (which of course specifies the connection string) should be set correctly. To fill up the DataGrid, call the Populate public method. It has only one parameter that is the SQLSelectStr which contains the SELECT statement. Properties used with the sorting feature are:

• ListOfSortableColumns: A comma separated string containing zero-based indexes of the columns you want to make as sortable (e.g. "0,1,3,4"). If you want to make all the columns in the DataGrid sortable, set ListOfSortableColumns = "all".
• InitialSortingColumnIndex: The zero-based index of the column you initially want to sort by. Default value of -1 means the DataGrid content will not be sorted at the first time while calling the Populate method. Note that the initial sorting column may not be in the list of sortable columns.
• InitialSortingDirection: DESC (ending) or any other value means ascending.
• myOrdinalProvider: The cooperating ordinal provider object. If you don't specify this, the default OrdinalProvider will step into action. Check the details under the Sorting section.

You should set AllowPaging = "true" if you want to use the paging feature. Properties used with the paging feature are:

• NavigationBarControl: The cooperating navigation bar. For those who are using the original version, please note that the NavigationBarControlID was used and now replaced by this property.
• InitialPageSize: The initial size of each page. Default value is 20.

You can add as many SPDataGrid to your page as you want. You can also check the demo project to see how to use the components. It works with the NorthWind database of MS SQL Server.

Points of interest

What is a CmdButtonForm class?

You may wonder what the CmdButtonForm class is doing here when all you want is to work with DataGrids. Actually, that is the trick I usually use while working with pages having multiple submit buttons at the class library level. The idea is that the page will add some hidden fields and each Submit button will modify their values before submitting, so by checking their values, we will know which Submit button was pressed. In this article I have used two hidden fields to store the command and the command parameter of Submit. The CmdButtonForm class will also add the client-side code necessary to this submitting mechanism. Let us take a closer look at it. The generated JavaScript code will look like this:

function __SetCmd(cmd, param)
{
    var CmdTag = document.getElementById('__Cmd');
    var ParamTag = document.getElementById('__CmdParam');
    if (CmdTag != null)
        CmdTag.value=cmd;
    if (ParamTag != null)
        ParamTag.value=param;
}

The JavaScript OnClick event handler of each Submit button on the page will call the __SetCmd function above. For instance, the first button in the navigation bar attached to the SPDataGrid2 DataGrid will have the following JavaScript event handler:

OnClick="__SetCmd('DG_NAV_F','SPDataGrid2');"

When the page is submitted, on the server side, the DataGrid will check the values of the hidden fields and "execute" the command if necessary, as shown in the following code:

protected override void OnLoad(EventArgs e)
{    
    ...
    string Command = GetInputHiddenValue("__Cmd"); 
    string CommandParam = GetInputHiddenValue("__CmdParam"); 
    if (CommandParam.Length>0) 
    {
        string ControlID = "";
        int nOff = CommandParam.IndexOf("$"); 
        if (nOff>0)
        { 
            ControlID = CommandParam.Substring(0,nOff); 
            CommandParam = CommandParam.Substring(nOff+1);
        } 
        else 
        {
            ControlID = CommandParam; 
            CommandParam = "";
        }
        if (ControlID.Equals(this.ID)) 
        {
            ExecuteCmd(Command, CommandParam);
        }            
    }
    ...
}

As you can see, the DataGrid will check the Command parameter against its ID to see if the command was intended to be executed by it. If the command has more than one parameter, then the parameters will be concatenated into a string with a pre-defined separator (in our case it is the dollar sign ($)). Talking about real object-oriented encapsulation, it could be better if the DataGrid class can add those hidden fields by itself instead of the container page class. However, .NET will throw an exception saying: "A control cannot modify its parents' control collections" if you try to do so.

The SPDataGrid class

The SPDataGrid class is using the ViewState to store some data which should be persisted over the requests. One of these persistent data is the SQL SELECT statement. It is set in the Populate method and will last until the next Populate is called.

Sorting

Unlike the other .NET sorting samples, I have used the sorting feature of the database engine instead of utilizing the sorting feature of the .NET platform provided by the DataView.Sort method. I believe that this sorting method can provide much better performance while working with large datasets, especially if you create proper indexes for the DB tables. However, I didn't have time to test and measure it and I will be very glad if you share your experience related to this matter with me. The SPDataGrid class needs the information about the ordinal of each (sortable) column in the SQL SELECT statement in order to sort its content and the OrdinalProvider class (newly introduced in the updated 1.1 version) is providing that info. The SPDataGrid class "stores" this information in the JavaScript OnClick event handler of each Sort image (button) in the column's header, so when the image button is clicked, the class will know of which column the user wants to get the content sorted by. Now let's see how the OrdinalProvider class can provide this info by default. In the case of BoundColumn, it takes the DataField property of each column and checks it against the SQL SELECT statement to get the ordinal of the column in the SELECT statement. With HyperLinkColumn the DataTextField property will do the same:

    ...
    if (dg.Columns[ColumnIndex] is BoundColumn)
        return myReader.GetOrdinal((
          (BoundColumn)dg.Columns[ColumnIndex]).DataField);
    else if (dg.Columns[ColumnIndex] is HyperLinkColumn)
        return myReader.GetOrdinal((
          (HyperLinkColumn)dg.Columns[ColumnIndex]).DataTextField);       
    ...

However, if you are using Template columns or you are not happy with this "default service" you can have your own Ordinal provider. In the example web page, I use a Template column with a checkbox in the Customers DataGrid (SPDataGrid1), which shows that if the customer is an international (non-USA) customer. Here is the entire SELECT statement:

select CustomerID, CompanyName, ContactName, ContactTitle, Address, 
  City, Country,(case country when 'USA' then 0 else 1 end) as Int from customers

The Template column looks like this:

<asp:templatecolumn HeaderText="Int">
 <ItemTemplate>
   <asp:checkbox id=CheckBox1 Enabled="False" 
      Checked='<%#Convert.ToBoolean(DataBinder.Eval(Container.DataItem, 
                     "Int"))%>' Text="" runat="server">
   </asp:CheckBox>
 </ItemTemplate>
</asp:TemplateColumn>

To make this Template column sortable, I override the default OrdinalProvider class and provide the ordinal of this column in the SQL SELECT statement:

public class SPDataGrid1_OrdinalProvider : OrdinalProvider
{
    public override int GetColumnOrdinal(SPDataGrid dg, 
                    SqlDataReader myReader, int ColumnIndex)
    {
        //The Int Template column

        if (ColumnIndex == 7)
            return 7;
        else
            return base.GetColumnOrdinal(dg, myReader, ColumnIndex);
    }
}

The only one thing more to do is to tell the DataGrid to use this OrdinalProvider instead of the default one:

private void Page_Load(object sender, System.EventArgs e)
{
    ...
    SPDataGrid1.myOrdinalProvider = new SPDataGrid1_OrdinalProvider();
    ...
}

Paging

The DataGridNavigationBar is a composite control inherited from the Panel class. It contains several labels, textboxes and buttons. You can customize it to your taste by using adjust/add/remove elements. Just remember to add the proper OnClick event handler to the buttons as I mentioned. The class doesn't have any public methods but there are two internal methods that are called from the cooperating DataGrid:

internal void Initialize(string AttachedTo)
internal void AdjustNavigationBar(int NumberOfRows, int CurrentPageIndex, 
                                          int PageCount, int PageSize)

The first method "attaches" the navigation bar to the DataGrid and the second method is called to enable/disable the navigation buttons depending on the CurrentPageIndex property of the DataGrid.

Conclusion

The DataGrid class presented here (together with the "escorting" classes) provide a reusable mechanism of adding sorting and paging features to ASP.NET DataGrids. The class works only with the DataGrids displaying contents populated from a relational database engine (RDBMS). However, with some modifications, you can build your own DataGrid class that works with any other data source. To give you something to start with, think of using the DataView.Sort method instead of building the SQL SELECT statement with the ORDER BY part as I did in this article...

Happy programming!

History

• April 26^th, 2005 - Initial version.
• July 14th, 2005 - Updated version 1.1:
  • NavigationBarControlID was replaced by NavigationBarControl for better performance and clearer code.
  • Introducing the SortingMgr class to provide sorting feature for HyperLink and Template columns.
  • ResetInputHiddenValue method was created and called inside the SPDataGrid's OnLoad event to clear __Cmd and __CmdParam (bug fix).