ASP.NET Letter Paging Based on Data Content

• Download source files - 15.2 Kb

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.