The Complete DataGrid (Editing, Deleting, Sorting, Alphabetic And Customized Paging All in One)

DataGrid

A DataGrid is a data bound list control that displays items in a table from a data source.

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

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

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

Suppose you want paging and to display data based on the letter you select. (Example: you want to retrieve all product names that start with "a" and after clicking on the letter "a" you want to show only 5 products on each page.)

The above queries need to be modified like below (by passing the selected letter 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 have come to know the logic (query) to retrieve a specified number of records each time to achieve custom and alphabet paging.
Let's see an example.

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>
   

In Code behind

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);
   }
   }

The complete source code is available for downloading.

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)