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. 

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. 

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). 

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: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: 

    <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. 
    <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: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): 
       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: 
      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. 
    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: 
    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: 
    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
关键字 the complete datagrid,alphabetic and customized paging
推荐.NET配套的通用数据层ORM框架：CYQ.Data 通用数据层框架