Click here to Skip to main content
Click here to Skip to main content

ASP.NET Letter Paging Based on Data Content

, 5 Apr 2004
Rate this:
Please Sign up or sign in to vote.
Paged table data display in DataGrid by selecting letter from a letter list which is derived from existing data.

Sample Image - LetterPaging.gif

Introduction

To display the data records like names, grouping by letters based on the initials of the names is often a good design choice. I have been using letter paging for the "Users" module in my web portal which is a highly modified version of IBuySpy portal. A Code Project article demonstrated this approach, but the paging implementation in that article left some room for improvement. First, the letter list is better derived from the existing data records, rather than an A-to-Z, because there may be no records in the table at all for some less frequently used letters, and as an added bonus, this table data derived letter list can also include record count information to display as tooltips, before the data is pulled from the table. Second, to be more efficient, the data filtering should be done inside SQL Server, and pull out only those data rows to be displayed, this is especially important for large data tables at busy sites.

The paging implementation presented here is essentially the same as my portal's "Users" module, and like that, I also use an ASP.NET user control, and a 3-tier web model. To run it, you need to have Visual Studio .NET, SQL Server or MSDE. The data is from the Customers table of the Northwind database. This VS.NET solution is built with VS.NET 1.0, so it can work with 1.1 as well.

Using the code

The SQL Server connection information is set in the web.config file as:

"server=localhost;Trusted_Connection=true;database=Northwind"

Trusted_Connection requires the ASPNET account being given access to the Northwind database. To get a letter list from a data table, a little SQL code is required, here is the stored procedure to get the name initials from the Customers table:

CREATE PROCEDURE GetCustomerLNameInitials
AS

SELECT UPPER(SUBSTRING(CompanyName, 1, 1)) lni, COUNT(*) Num
FROM Customers 
GROUP BY UPPER(SUBSTRING(CompanyName, 1, 1))
ORDER BY lni

This stored procedure not only gets the list of initial letters, it also returns the record count for each letter to be displayed as tooltip on the letter selection list. This letter list is the data source for the Repeater control. The (All) button is just a separate LinkButton, not part of the Repeater control. When a letter is selected, the data rows with that letter as initial are pulled from the database with another stored procedure:

CREATE PROCEDURE GetCustomersByLNameInitial
(
   @Lni nchar(1)
)
AS

SELECT CompanyName, Address, City, Country
FROM Customers 
WHERE CompanyName LIKE @Lni + '%'
ORDER BY CompanyName

The proper design of data-driven applications should always try to minimize the impact on the database server, when there is need to get some data out, get just enough, no more, no less.

Another stored procedure is used to get all data rows out for the (All) button.

All the stored procedures are wrapped in a data access class, and all the data read methods return SqlDataReader, this is the most efficient way to get data out, and bind to web controls. Because web applications are stateless, there is no way to keep some data around beyond an user request without some serious side effects, there is no point to create DataTable or DataSet, only to discard right away when the page is rendered. DataTable and DataSet are often good choices for WinForm applications, or when the data needs to be manipulated/transported. The data access methods in the data access class looks like the following:

    public SqlDataReader GetCustomersByLNameInitial(string Lni) 
    {
        SqlDataReader dr = null;
        // Create Instance of Connection and Command Object
        SqlConnection myConnection = new 
          SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
        SqlCommand myCommand = new 
          SqlCommand("GetCustomersByLNameInitial", myConnection);
            
        try 
        {
            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;
               
            // Add Parameters to SPROC
            SqlParameter parameterLni = 
               new SqlParameter("@Lni", SqlDbType.NChar, 1);
            parameterLni.Value = Lni;
            myCommand.Parameters.Add(parameterLni);                

            // Open the database connection and execute the command
            myConnection.Open();
            dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

            // clear Paramters
            myCommand.Parameters.Clear();
        }
        catch 
        {
            myConnection.Close();
            //throw;
        }
        return dr;
    }

Nothing fancy! Now coming to the user control. In it, is the Repeater control:

<asp:Repeater id=LetterList Visible="true" runat="server">
  <ITEMTEMPLATE>
    <asp:linkbutton id=lniLetter runat="server" 
        ToolTip='<%# "Subtotal: " + DataBinder.Eval(Container.DataItem, "Num") %>' 
        commandargument='<%# DataBinder.Eval(Container.DataItem, "Lni")%>'>
      <%# DataBinder.Eval(Container.DataItem, "Lni")%>
    </asp:linkbutton>
  </ITEMTEMPLATE>
</asp:Repeater>

The others are trivial stuff in the visual part of this user control. The following is part of the code-behind for this user control:

    private void Page_Load(object sender, System.EventArgs e)
    {
        if (!Page.IsPostBack) 
        {
            IniBindData();
        }
    }

    private void ShowAllUsers_Click(object sender, System.EventArgs e)
    {
        DataAccess da = new DataAccess();
        DataGrid1.DataSource = da.GetAllCutomers();
        DataGrid1.DataBind();        
    }

    private void LetterList_Select(object source, 
       System.Web.UI.WebControls.RepeaterCommandEventArgs e)
    {
        DataAccess da = new DataAccess();
        // find the selected letter
        string letter = (string)e.CommandArgument;
        // show the name list with this initial 
        DataGrid1.DataSource = da.GetCustomersByLNameInitial(letter);
        DataGrid1.DataBind();
    }

    //    Called once at page first load
    void IniBindData()
    {
        DataAccess da = new DataAccess();
        LetterList.DataSource = da.GetCustomerLNameInitials();
        LetterList.DataBind();
            
        int n = LetterList.Items.Count;
        if (n > 0)
        {
            // try to find the first letter on the list
            string letter = ((LinkButton)
               (LetterList.Items[0].Controls[1])).CommandArgument;
                // show the Names with the first letter
            DataGrid1.DataSource = da.GetCustomersByLNameInitial(letter);
            DataGrid1.DataBind();
        }
    }

For the most part, the code is easy to understand. When this user control is first loaded, it calls IniBindData(), which gets the letter list, and binds it to the Repeater control to form the row of LinkButtons. If the list is not empty, this method also loads the DataGrid with the data rows of the first letter. When a LinkButton on the Repeater letter list is clicked, its event handler LetterList_Select is called, it will find out which letter is selected, then load the DataGrid with the rows under that latter. The user control code has no exception checks, unlike the real applications.

History

  • April 6, 2004, initial writing.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Jeff X. Chang
Web Developer
United States United States
No Biography provided

Comments and Discussions

 
QuestionWill this work with Linq to SQL? PinmemberJeff Frick7-Jan-09 10:17 
QuestionGuidance please PinmemberSrividhya Seetharaman18-Aug-08 9:45 
GeneralRe: Guidance please PinmemberSrividhya Seetharaman18-Aug-08 10:12 
QuestionSimple Newbie question Pinmembermljohnson1mo30-Aug-06 5:30 
GeneralSuggestion for DataAccess class Pinmemberonstottj7-Dec-05 9:07 
GeneralRe: Suggestion for DataAccess class PinmemberJeff X. Chang7-Dec-05 13:24 
GeneralThank You! Pinmemberohyeahbaby16-Feb-05 11:11 
Questionam i wrong? PinmemberoOomen23-Sep-04 4:31 
AnswerRe: am i wrong? PinmemberJeff X. Chang29-Sep-04 17:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 6 Apr 2004
Article Copyright 2004 by Jeff X. Chang
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid