DataGrid

DataGrid provides two ways of paging

1. Default Paging: Easy one, but it is suitable only with a small amount of data. Because all the data is retrieved for every page of data to be 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 a query to retrieve 'n' number of records each time. (example: Retreiving 1 to 10 records the first time to show in the first page, then retrieving 11-20 records for the next time to show on the second page and so on.)
   In SQL Server we have the ROW_NUMBER() function which assigns a serial number to rows; using this we can write a query to fullfill our need for custom paging, by passing a page number and a page size to the DataGrid as arguments.
   
   We need to retrieve the total count of rows to know how many pages we need to display all of the records.

1. Create an ASP.Net data grid with some columns, styles, properties, edit and delete button as shown below.
   
   Set DataKeyField to the 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)
   
   <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, total pages, letter values across postback and to display page number and TotalPages.
   
   <p>Page:&nbsp;<asp:Label ID="CurrentPage" runat="server">
   </asp:Label> &nbsp;of&nbsp;
   <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>&nbsp;
   <asp:LinkButton ID="Nextbtn" runat="server" CommandName="Next" Text="Next >"
   onclick="Nextbtn_Click" CausesValidation="False"></asp:LinkButton> </p>
   

1. On pageload bind the first specified number of rows. (In my example the pagesize is set to 5, ie 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);
   }
   
3. Sorting eventhandler: DataView has a sorting property; we can use that to sort columns, both in ascending and descending order.
   
4. 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();
   }
   
5. 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);
   }
   
6. When you are editing a row the textboxes for each column of a row havs a default width; you can set their width on Item_Databound event as shown 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);
   }
   }

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)