Letter/Alphanumeric Based Paging in ASP.NET

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;

namespace LetterBasedPaging {
  //----------------------------------------------------------------------------
  /// <summary>
  /// Web form that demonstrates how to implement letter-based paging in an
  ///   ASP.NET data grid.
  /// </summary>
  /// <remarks>
  /// <p><strong>Filename: </strong>Default.aspx.cs</p>
  /// <p><strong>Author: </strong>Joseph S. Keller</p>
  /// <p align="center"><i>Copyright � 2003 STANDPoint Development</i></p>
  /// </remarks>
  public class Default : System.Web.UI.Page {

    #region Web Form UI Control Declarations

    protected System.Web.UI.WebControls.DataGrid dgCustomers;
    protected System.Web.UI.WebControls.Label lblError;
    protected System.Web.UI.WebControls.Repeater rptLetters;

    #endregion

    #region Web Form Designer generated code
    
    override protected void OnInit(EventArgs e) {
      //
      // CODEGEN: This call is required by the ASP.NET Web Form Designer.
      //
      InitializeComponent();
      base.OnInit(e);
    }
		
    /// <summary>
    /// Required method for Designer support - do not modify
    /// the contents of this method with the code editor.
    /// </summary>
    private void InitializeComponent() {    
      this.rptLetters.ItemDataBound += new System.Web.UI.WebControls.RepeaterItemEventHandler(this.letters_ItemDataBound);
      this.rptLetters.ItemCommand += new System.Web.UI.WebControls.RepeaterCommandEventHandler(this.letters_ItemCommand);
      this.Load += new System.EventHandler(this.Page_Load);

    }
		
    #endregion

    #region Web Form Protected/Private Fields

    //--------------------------------------------------------------------------
    /// <summary>
    /// Connection string to the northwind database located on the localhost
    /// </summary>
    private const string CONNECTION_STRING = "data source=localhost;" +
      "initial catalog=Northwind;" +
      "integrated security=SSPI;" +
      "persist security info=False";

    //--------------------------------------------------------------------------
    /// <summary>
    /// The row filter used on the DataGrid.
    /// </summary>
    protected string _letterFilter;
    
    #endregion

    //--------------------------------------------------------------------------
    /// <summary>
    /// Code to initialize the web form goes here.
    /// </summary>
    private void Page_Load(object sender, System.EventArgs e) {

      //------------------------------------------------------------------------
      // Get information needed everytime the web form is processed
      
      // Get the letter we will use as a filter for paging.  If the value is 
      //  null set the value to "All", otherwise get the value previously saved 
      //  in the viewstate.
      object oFilter = ViewState[this.ToString() + "_letterFilter"];
      if (oFilter != null) _letterFilter = (string)oFilter;
      else _letterFilter = "All";
      
      //------------------------------------------------------------------------
      // Initialize web form controls, but don't reinitialize on post backs
      if (!IsPostBack) initControls();
    
    } // private void Page_Load(sender, e)

    //--------------------------------------------------------------------------
    /// <summary>
    /// Initializes all controls and data
    /// </summary>
    private void initControls () {
      // Bind the DataGrid control (for the first time)
      dgCustomers_Bind(true);

    } // private void initControls ()


    //--------------------------------------------------------------------------
    // Customer Data Grid Methods and Events
    //--------------------------------------------------------------------------

    //--------------------------------------------------------------------------
    /// <summary>
    /// Sets up DataGrid control source/options and binds it.
    /// </summary>
    private void dgCustomers_Bind (bool requery) {
      
      // Declares a variable that will store a referance to the DataTable we are 
      //  going to bind the data grid to.
      DataTable dt = null;

      //------------------------------------------------------------------------
      // Get the appropriate set of records records to view
      if ((Session[this.ToString() + "_CustomersData"] == null) || (requery)) {
        // This section of code is called if the database has not yet been 
        //  queried, or if the "requery" parameter was specified in the method
        //  call.

        // Create a connection to the database
        SqlConnection conn = new SqlConnection(CONNECTION_STRING);

        // Create a command that will get the records from the database
        SqlCommand cmd = new SqlCommand("SELECT CompanyName, " +
          "Address, " + 
          "City, " + 
          "Country " + 
          "FROM Customers", conn);

        // Create a data adapter that will be used to fill a data set with the
        //  data returned from the database.
        SqlDataAdapter da = new SqlDataAdapter(cmd);

        // Create a data set the will hold the results from the database.
        DataSet ds = new DataSet();

        try {
          // Fill the data set with the command results
          da.Fill(ds);

          // Set the data table we will use later on, to the first table 
          //  returned from the command
          dt = ds.Tables[0];
        
        }
        catch (SqlException ex) {
          // Display the error message to the user
          lblError.Text = ex.Message;
          
          // Return without doing anything else. (Note that the finally section
          //  will still be called)
          return;
        }
        finally {
          // Be sure to close the connection
          conn.Close();
        }

        // If we are here, we know the we have a valid set of data, otherwise an
        //  error would have been thrown, and this method would have returned.

        // Store a referance in the session to the data that we are binding the
        //  data grid to, so that we can use it on postbacks without having to
        //  requery the database
        Session[this.ToString() + "_CustomersData"] = dt;
      }
      else {
        // Get the previously queried data from the session
        dt = (DataTable)Session[this.ToString() + "_CustomersData"];
      }

      //------------------------------------------------------------------------
      // Implement paging based upon the lettering filter specified
      if (_letterFilter == "All")
        dt.DefaultView.RowFilter = string.Empty;
      else
        dt.DefaultView.RowFilter = "CompanyName LIKE '" + _letterFilter + "%'";
      
      //------------------------------------------------------------------------
      // Bind the data's default view to the grid
      dgCustomers.DataSource = dt.DefaultView;
      dgCustomers.DataBind();

      //------------------------------------------------------------------------
      // Bind the letters array to the repeater control
      letters_Bind();

    } // private void dgCustomers_Bind (bool requery)


    //--------------------------------------------------------------------------
    // Letters Repeater Methods and Events
    //--------------------------------------------------------------------------
    
    //--------------------------------------------------------------------------
    /// <summary>
    /// Sets up Repeater control source/options and binds it.
    /// </summary>
    private void letters_Bind () {

      // Declares a variable that will store a referance to the DataTable we are 
      //  going to bind the repeater control to.
      DataTable dt;

      //------------------------------------------------------------------------
      // Get the appropriate set of records to view/edit
      if (Session[this.ToString() + "_LettersData"] == null) {
        
        string[] letters = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", 
                             "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", 
                             "U", "V", "W", "X", "Y", "Z", "All"};

        // Create a new data table
        dt = new DataTable();

        // Create the scheme of the table
        dt.Columns.Add(new DataColumn("Letter",
          typeof(string)));

        // Populate the data table with the letter data
        for (int i = 0; i < letters.Length; i++) {
          DataRow dr = dt.NewRow();
          dr[0] = letters[i];
          dt.Rows.Add(dr);
        }

        // Store a referance to the newly create data tabel in the session for 
        //  use on post back.
        Session[this.ToString() + "_LettersData"] = dt;
      }
      else
        dt = (DataTable)Session[this.ToString() + "_LettersData"];

      //------------------------------------------------------------------------
      // Bind the data's default view to the grid
      rptLetters.DataSource = dt.DefaultView;
      rptLetters.DataBind();

    } // private void dgLetters_Bind ()

    
    //--------------------------------------------------------------------------
    /// <summary>
    /// Called when an item in the letters repeater control is data bound to a 
    /// source.
    /// </summary>
    private void letters_ItemDataBound(object sender, System.Web.UI.WebControls.RepeaterItemEventArgs e) {
      
      // Retrieve the row of data that is to be bound to the repeater
      DataRowView data = (DataRowView) e.Item.DataItem;
      
      // If the letter we are binding to the current repeater control item is
      //  the same as the one currently selected, than disable it so the user
      //  knows which one was selected.
      if ((string)data[0] == _letterFilter) {
        LinkButton lnkletter = (LinkButton) e.Item.FindControl("lnkletter");
        lnkletter.Enabled = false;
      }

    } // private void letters_ItemDataBound(sender, e)

    
    //--------------------------------------------------------------------------
    /// <summary>
    /// Called when a custom command in the Repeater control is executed.
    /// </summary>
    private void letters_ItemCommand(object source, System.Web.UI.WebControls.RepeaterCommandEventArgs e) {

      // Handle the "Filter" new record command
      if (e.CommandName == "Filter") {
        
        // Set the row filter to the new filter
        _letterFilter = (string)e.CommandArgument;

        // Remember the row filter for next time the form is loaded
        ViewState[this.ToString() + "_LetterFilter"] = _letterFilter;

        // Rebind the data in the data grid.  This will also update the letter
        //  links and disable the currently selected letter.
        dgCustomers_Bind(false);
      }

    } // private void letters_ItemCommand(source, e)


  } // public class Default

} // namespace LetterBasedPaging