Click here to Skip to main content
15,868,016 members
Articles / Web Development / ASP.NET
Article

The Complete Datagrid (Alphabetic and Customized Paging )

Rate me:
Please Sign up or sign in to vote.
4.43/5 (3 votes)
23 Jul 2011CPOL3 min read 34.6K   1.4K   17   1
Editing, Deleting, Sorting, Alphabetic and Customized paging all in one
image001_small.jpg

Introduction

Datagrid: A data bound list control that displays the items from data source in a table.

The DataGrid control allows you to select, sort, and edit these items.

In almost all projects we do need control with such functionalities. So I had worked on least functionalities of data grid.

This article explains editing, deleting, sorting, custom paging and alphabetical paging features with an example.

The complete source code is available for downloading.

Datagrid provides two ways of paging:

  1. Default Paging: Easy one, but it is suitable only with small amount of data because all data retrieved for each and every page of data is shown.
    Retrieving 1000 records to show only 100 records each time is not feasible.
  2. One more way of paging is custom paging to overcome the disadvantages of default paging.

We have to write query to retrieve ‘n’ number of records each time. (Example: Retreiving 1 to 10 records first time to show in first page, retrieving 11-20 records on next time to show on second page so on.)

In SQL Server, we have ROW_NUMBER() function which assigns serial number to rows. Using this, we can write query to fulfill our need for custom paging by passing page number and page size of datagrid as arguments.

We need to retrieve total count of rows to know how many pages we need to display all records.

Query to retrieve ‘n’ records.

SQL
int StartID = ((pagenum - 1) * pagesize) + 1;

int EndID = (pagenum) * pagesize;

  SELECT TOP 5 * FROM( SELECT ProductID,Name,Price,ROW_NUMBER() _
  OVER (ORDER BY ProductID) AS ROW_NUMBER FROM Product) AS a _
  WHERE ROW_NUMBER >= " + StartID + " and ROW_NUMBER <=" + EndID + " order by Name

Function to retrieve total count of records.

SQL
Select count(*) from Product

Think that you want paging and need to display data based on alphabet you select (example: you want to retrieve all product names starts with ‘a’ after clicking on alphabet ‘A’ and want to show only 5 products on each page).

The above queries need to modify like below (by passing selected alphabet also).

C#
int StartID = ((pagenum - 1) * pagesize) + 1;

         int EndID = (pagenum) * pagesize;
alpha = slectedLetter + "%";

SELECT TOP 5 * FROM( SELECT ProductID,Name,Price,ROW_NUMBER() _
OVER (ORDER BY ProductID) AS ROW_NUMBER FROM Product where Name like '"+alpha+"') _
AS a WHERE ROW_NUMBER >= " + StartID + " and ROW_NUMBER <=" + EndID + " 

Select count(*) from Product where Name like '" + alpha + "'

Now you came to know the logic(query)to retrieve ’n’ number of records each time to achieve custom and alphabet paging.

Let’s see an example.

  1. Create an ASP.NET datagrid with some columns, styles, properties, edit and delete button as below.

    Set DataKeyField to primary key of your table.

    ASP.NET
    <asp:DataGrid  runat="server" ID="dgProductN" AllowCustomPaging="True"
      Width="100%"
              AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False"
              PageSize="5"
              onsortcommand="dgProductN_SortCommand"
              oncancelcommand="dgProductN_CancelCommand"
              ondeletecommand="dgProductN_DeleteCommand"
              oneditcommand="dgProductN_EditCommand"
              onupdatecommand="dgProductN_UpdateCommand" DataKeyField="ProductID"
              CellPadding="4" ForeColor="#333333" GridLines="None"
              onitemdatabound="dgProductN_ItemDataBound">
             <AlternatingItemStyle BackColor="White" Width="10px" />
          <Columns>
    
                <asp:BoundColumn DataField="Name" HeaderText="Name"
                SortExpression="Name"></asp:BoundColumn>
                <asp:BoundColumn DataField="Price" HeaderText="Price"
                SortExpression="Price"></asp:BoundColumn>
                <asp:EditCommandColumn EditText="Edit" CancelText="Cancel"
                UpdateText="Update" CausesValidation="false"
                ItemStyle-Width="10px" Visible="true" HeaderText="Edit">
                </asp:EditCommandColumn>
               <asp:ButtonColumn Text="Delete" CommandName="Delete"
          CausesValidation="false"
                  HeaderText="Delete"></asp:ButtonColumn>
           </Columns>
             <EditItemStyle BackColor="#CCFFCC" HorizontalAlign="Left" Width="10px" />
             <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
             <HeaderStyle BackColor="#507CD1" BorderStyle="Solid" BorderWidth="1px"
                 ForeColor="White" Font-Bold="True" />
             <ItemStyle BackColor="#EFF3FB" Width="10px" />
             <PagerStyle Visible="False" BackColor="#2461BF" ForeColor="White"
                 HorizontalAlign="Center" />
             <SelectedItemStyle BackColor="#D1DDF1" Font-Bold="True"
          ForeColor="#333333" />
       </asp:DataGrid>
    
  2. Add a set of LinkButtons that correspond to the letters in the alphabet. When clicked, the DataGrid shows only records beginning with that letter (using Data repeater).

    For more information, refer to this article:

    HTML
    <table>
            <tr>
            <td>
            <asp:Repeater  ID="rptr" runat="server" onitemcommand="rptr_ItemCommand" 
                    onitemdatabound="rptr_ItemDataBound">
            <ItemTemplate>
            <asp:LinkButton ID="linkalpha" runat="server" CommandName="Filter" 
            CausesValidation="false" CommandArgument='<%# DataBinder.Eval
            (Container, "DataItem.Letter")%>'>
            <%# DataBinder.Eval(Container, "DataItem.Letter")%></asp:LinkButton>
            </ItemTemplate>
            </asp:Repeater>
            </td>
            </tr>
            </table>
  3. Add labels to hold page number, Totalpages, alphabet values across postback and to display page number, Total Pages.
    ASP.NET
    <p>Page: <asp:Label ID="CurrentPage" runat="server">
    </asp:Label>  of 
            <asp:Label ID="TotalPages" runat="server"></asp:Label>
                <asp:Label ID="lblalpha" runat="server" Visible="False"></asp:Label> 
  4. Add ‘<prev’ and ‘next>’ link buttons.
    ASP.NET
    <asp:LinkButton runat="server" CommandName="Prev" Text="< Prev" ID="PrevPage" 
                onclick="PrevPage_Click" CausesValidation="False" 
            ></asp:LinkButton> 
        <asp:LinkButton ID="Nextbtn" runat="server" CommandName="Next" Text="Next >" 
                onclick="Nextbtn_Click" CausesValidation="False"></asp:LinkButton>

    In code behind:

    1. On pageload, bind first ‘n' number rows. (In my example, I had set pagesize to 5, i.e. retrieving five records each time):
      C#
      public int _currentPageNumber = 1;
         public string alpha = "%";
           protected void Page_Load(object sender, EventArgs e)
             {
                 pagesize = dgProductN.PageSize;
                 if (!IsPostBack)
                 {
                         BindfiveProducts(_currentPageNumber, pagesize, alpha);
      
                 }
             }
      
      public void BindfiveProducts(int pagenum, int pagesize,string alpha)
             {
                 int StartID = ((pagenum - 1) * pagesize) + 1;
                 int EndID = (pagenum) * pagesize;
                 DataSet ds = product.GetFiveProductsFilterByAlphabets
                 (StartID, EndID,alpha);
                 _totalRecords = product.GetTotalRowsFilterByAlpha(alpha);
                 dt = ds.Tables[0];
                 dgProductN.DataSource = ds;
                 dgProductN.DataBind();
                 Session["Data"] = ds;
                 lblalpha.Text = alpha;
                 CurrentPage.Text = _currentPageNumber.ToString();
                int rem = _totalRecords % dgProductN.PageSize;
               if (rem == 0) { _totalPages = _totalRecords / dgProductN.PageSize; }
                else { _totalPages = (_totalRecords / dgProductN.PageSize) + 1; }
                TotalPages.Text = _totalPages.ToString();
      
                 if (_currentPageNumber == 1)
                 {
                     PrevPage.Enabled = false;
                     if (_totalPages > 1)
                         Nextbtn.Enabled = true;
                     else
                         Nextbtn.Enabled = false;
                 }
                 else
                 {
                     PrevPage.Enabled = true;
                     if (_currentPageNumber == _totalPages)
                         Nextbtn.Enabled = false;
                     else
                         Nextbtn.Enabled = true;
                 }
                 letters_Bind();
             }
      
    2. For Edit, Update and Cancel event handlers as follows:
      C#
      protected void dgProductN_EditCommand
      	(object source, DataGridCommandEventArgs e)
              {
                  dgProductN.EditItemIndex = e.Item.ItemIndex;//make the row data 
      						//as  editable
                 _currentPageNumber = int.Parse(CurrentPage.Text);
                  alpha = lblalpha.Text;
                  BindfiveProducts(_currentPageNumber, pagesize, alpha);
               }
       
      protected void dgProductN_DeleteCommand
      		(object source, DataGridCommandEventArgs e)
              {
                  int id=(int)dgProductN.DataKeys
      		[(int)e.Item.ItemIndex];	//Get primary key to delete 
                  				//corresponding record
                  product.DeleteProduct(id);
                   alpha = lblalpha.Text;
                  _currentPageNumber = int.Parse(CurrentPage.Text);
                  BindfiveProducts(_currentPageNumber, pagesize, alpha);
              }
      protected void dgProductN_CancelCommand
      		(object source, DataGridCommandEventArgs e)
              {
                  dgProductN.EditItemIndex = -1;
                  alpha = lblalpha.Text;
                  BindfiveProducts(_currentPageNumber, pagesize, alpha);
              }
  5. Sorting eventhandler: DataView has sorting property; we can use that to sort columns both in ascending and descending order.
    C#
    protected void dgProductN_SortCommand(object source, DataGridSortCommandEventArgs e)
    {
        DataSet ds = (DataSet)Session["Data"];
        DataView dv = new DataView(ds.Tables["Product"]);
        if ((numberDiv % 2) == 0)
            dv.Sort = e.SortExpression + " " + "ASC";
        else
            dv.Sort = e.SortExpression + " " + "DESC";
        numberDiv++;
        dgProductN.DataSource = dv;
        dgProductN.DataBind();
    }
  6. Previous and Next Event Handlers:
    C#
    protected void dgProductN_EditCommand(object source, DataGridCommandEventArgs e)
    {
        dgProductN.EditItemIndex = e.Item.ItemIndex;
        _currentPageNumber = int.Parse(CurrentPage.Text);
        alpha = lblalpha.Text;
        BindfiveProducts(_currentPageNumber, pagesize, alpha);
    }
     
    protected void dgProductN_CancelCommand(object source, DataGridCommandEventArgs e)
    {
         dgProductN.EditItemIndex = -1;
         alpha = lblalpha.Text;
         BindfiveProducts(_currentPageNumber, pagesize, alpha);
    }
  7. When you edit a row, the textboxes for each column of rows having default width, you can set their width on Item_Databound event as below:
    C#
    protected void dgProductN_ItemDataBound(object sender, DataGridItemEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.EditItem)
        {
            TextBox txtPID = (TextBox)e.Item.Cells[0].Controls[0];
            txtPID.Width = Unit.Pixel(60);
            TextBox txtPN = (TextBox)e.Item.Cells[1].Controls[0];
            txtPN.Width = Unit.Pixel(60);            
        }
    }

The complete source code is available for download from the link at the top of this article.

In the example:

  1. DataGridPaging.aspx shows Datagrid only with custom paging.
  2. CustomizePaging.aspx shows Datagrid with editing, deleting and custom paging features.
  3. DataGrid.aspx shows Datagrid with editing, deleting, alphabetic and custom paging features (Complete Grid all in one)?

History

  • 21st July, 2011: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
krishna_goluguri25-Jul-11 2:59
krishna_goluguri25-Jul-11 2:59 

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

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