7.4 DataGrid | Essential ASP.NET With Examples in C#

Of all the controls provided by ASP.NET, the DataGrid is by far the most complex, with literally hundreds of properties, methods , and events. Understanding the full set of features of this control is a daunting prospect. Instead of enumerating all the various options available for the DataGrid , this section presents some of the more commonly used techniques for presenting tabular data with the DataGrid . For a full reference of all the available styles and features, refer to the MSDN documentation ^[13] for the DataGrid class.

^[13] MSDN documentation is available through Visual Studio .NET or at http://msdn.microsoft.com.

To begin with, the DataGrid control displays tabular data from a data source. In its simplest usage, as shown earlier in Listing 7-1, you can use a DataGrid to quickly generate a table rendering of any data source. Its object model includes a collection of columns , a header, and a footer, all of which can be customized to create the appearance you need. Columns can be autogenerated from the underlying data source schema, where each column of the data source table generates a column in the grid. Alternatively, you can be explicit about which columns to display by disabling the AutoGenerateColumns property and adding BoundColumn s to the Columns collection. Figure 7-8 demonstrates a DataGrid with AutoGenerateColumns set to false and with several other style attributes to alter the appearance.

Figure 7-8. A DataGrid with Custom BoundColumns

graphics/07fig08.gif

7.4.1 DataGrid Paging

Paging of data is a common approach to giving clients control over how much data they want to view at a time. Instead of generating one giant table with all of the data, you can generate a table with a fixed number of rows and specify an interface to navigate forward and backward through the data source.

To enable paging on a DataGrid , you must set the AllowPaging property to true , and you must provide a handler for the PageIndexChanged event of the DataGrid . In the PageIndexChanged event handler, you need to set the CurrentPageIndex property of the DataGrid to the NewPageIndex passed in via the DataGridPageChangedEventArgs parameter to the handler. Without doing anything else, you will have a paged grid with a default page size of 10. However, for this default paging mechanism to work, you must supply the DataGrid with the complete set of records each time its DataBind method is called, because this is how it calculates how many pages to display. Unfortunately, although this gives the appearance of paging, it loses the main benefit of paging, which is to avoid retrieving all of the data with each access to the page. And even worse , this technique implicitly sends all of the data associated with the DataGrid through the __VIEWSTATE field across each post-back.

A more compelling alternative is to add a little more logic to your DataGrid and use its custom paging capabilities. You enable custom paging by setting the AllowCustomPaging property to true . It is then left up to you to manage the page count and page navigation by setting the VirtualItemCount property of the DataGrid to the total number if items it should display, and then when performing data binding, by determining what subset of the underlying data source to retrieve based on the current page index. Figure 7-9 shows a sample custom paging data grid in action.

Figure 7-9. Custom Paging DataGrid in Action

graphics/07fig09.gif

Listing 7-5 shows the page with the DataGrid declaration supporting custom paging (additional style attributes are not shown). Note that AllowCustomPaging has been set to true , and the PageSize has been explicitly specified, in addition to AllowPaging and the PageIndexChanged handler necessary for standard paging.

Listing 7-5 Page with a DataGrid Implementing Custom Paging

 <! File: DataGridPage.aspx > <%@ Page language="C#" Debug="True"       src="DataGridPage.aspx.cs" Inherits="DataGridPage" %> <html> <body> <form runat="server">   <asp:DataGrid id=_gd1           Width="90%"           Runat="server"  AllowCustomPaging='true'   AllowPaging='true'   OnPageIndexChanged="Grid_Change"   PageSize=10  /> </form> </body> </html>

Listing 7-6 shows the code-behind class that implements the logic behind the custom paging DataGrid . When this page is loaded for the first time, it queries the data source to discover how many items are in the collection to be displayed by the paged DataGrid . This number is assigned to the VirtualItemCount of the DataGrid so that it knows how many pages of data there are and can change the page navigation indicators appropriately. The total count of items is obtained via a private method called GetItemCount that issues a " SELECT COUNT(*) ..." query to discover the number of rows in the underlying table. The logic for the GetItemCount function varies from application to application depending on the underlying data source, but this is one common approach.

Perhaps the most important piece of logic in this class is provided by the BindGrid() method, used to populate the DataGrid whenever necessary. This function issues a query against the data source for precisely the number of rows necessary to display the current page. Again, the logic in this function varies from application to application. In this particular application, the ID field of the Employees table is a linear indexer, so to retrieve records 11 through 20, you can query the table for records whose IDs range from 11 through 20.

Finally, the Grid_Change method is our handler for the PageIndexChanged event of the DataGrid . In it, we assign the CurrentPageIndex property of the DataGrid to the incoming NewPageIndex of the DataGridPageChangedEventArgs parameter. We also calculate the beginning index of the underlying data source to be retrieved by taking the current page index (plus 1), multiplying it by the page size, and subtracting the result from the total VirtualItemCount . This starting index is saved in a field of our class called _startIndex , used during the BindGrid method to retrieve the correct rows from the underlying table.

Listing 7-6 Code-Behind Class Implementing Custom Paging Logic

 // File: DataGridPage.aspx.cs public class DataGridPage : Page {   private   int      _startIndex = 0;   protected DataGrid _gd1;   protected void Page_Load(object Src, EventArgs e)   {      if (!IsPostBack)      {        _gd1.VirtualItemCount = GetItemCount();        _startIndex = _gd1.VirtualItemCount-_gd1.PageSize;        BindGrid();      }   }   private int GetItemCount()   {     int count = 0;     SqlConnection conn =     new SqlConnection("server=.;uid=sa;pwd=;database=Test");     SqlCommand cmd =      new SqlCommand("SELECT COUNT(*) FROM Employees", conn);     try     {       conn.Open();       count = (int)cmd.ExecuteScalar();     }     finally { conn.Dispose(); }     return count;   }   private void BindGrid()   {     string select = "SELECT * FROM Employees WHERE ID > " +                     _startIndex + " AND ID <= " +                     (_startIndex + _gd1.PageSize) +                     " ORDER BY ID DESC";     SqlConnection conn =     new SqlConnection("server=.;uid=sa;pwd=;database=Test");     SqlCommand cmd = new SqlCommand(select, conn);     try     {       conn.Open();       IDataReader reader = cmd.ExecuteReader();       _gd1.DataSource = reader;       _gd1.DataBind();     }     finally { conn.Dispose(); }   }   protected void Grid_Change(object sender,                              DataGridPageChangedEventArgs e)   {     _gd1.CurrentPageIndex = e.NewPageIndex;     _startIndex = _gd1.VirtualItemCount -        ((_gd1.CurrentPageIndex+1) * _gd1.PageSize);     BindGrid();   } }

7.4.2 DataGrid Sorting

When most users see a grid with column headers on a Web page, they expect to be able to click the headers to sort the grid based on that column. If they click the header and nothing happens, they become frustrated and are less likely to visit that page again. To avoid this kind of disappointment, the DataGrid class supports sorting through the AllowSorting property and the SortCommand event. As with paging, the details of sorting are left up to you, but the DataGrid takes care of turning the column headers into hyperlinks and issuing the SortCommand event whenever one of the links is pressed. Figure 7-10 shows an example of a DataGrid that supports sorting in action.

Figure 7-10. DataGrid Supporting Sorting in Action

graphics/07fig10.gif

To add support for sorting to a DataGrid , you need to enable the AllowSorting property of the grid, and you need to associate a handler with the SortCommand . Enabling AllowSorting turns all your DataGrid headers into hyperlinks, which when clicked perform a post-back and fire the SortCommand event, passing in the sort expression associated with the clicked column. The sort expression of a column defaults to the column name , but you can change it by explicitly describing the column in your DataGrid declaration and assigning a string to the SortExpression property of the column. Listing 7-7 shows a sample page with a DataGrid that has sorting enabled.

Listing 7-7 Page with a DataGrid Supporting Sorting

 <! File: DataGridSort.aspx > <%@ Page language="C#" src="DataGridSort.aspx.cs"     Inherits="DataGridSort" %> <html> <body> <form runat="server">   <asp:datagrid id=_gd1                 Width="90%"                 Runat="server"  AllowSorting="True"   OnSortCommand="gd1_Sort"  /> </form> </body> </html>

The logic behind a sortable grid involves repopulating the grid with whatever the current sort expression selected by the user is. Listing 7-8 shows the code-behind page for a grid with sorting enabled. In this implementation, a local field named _sortExpression is maintained to determine what to pass into the ORDER BY clause of our query. The sort expression is set in the gd1_Sort method, our handler for the SortCommand event. The details of how the sorting of the data is performed may vary from application to application, but the core methods and sort expression management should stay the same.

Listing 7-8 Code-Behind Class Implementing DataGrid Sorting Logic

 // File: DataGridSort.aspx.cs public class DataGridSort : Page {   private   string   _sortExpression;   protected DataGrid _gd1;   protected void Page_Load(object Src, EventArgs e)   {      if (!IsPostBack)        BindGrid();   }   protected void gd1_Sort(object src,                           DataGridSortCommandEventArgs e)   {      _sortExpression = e.SortExpression;      BindGrid();   }   public void BindGrid()   {     string select = "SELECT * FROM Employees";     if (_sortExpression != null)       select +=  " ORDER BY " + _sortExpression;     SqlConnection conn =     new SqlConnection("server=.;uid=sa;pwd=;database=Test");     SqlCommand cmd = new SqlCommand(select, conn);     try     {       conn.Open();       IDataReader reader = cmd.ExecuteReader();       _gd1.DataSource=reader;       _gd1.DataBind();     }     finally { conn.Dispose(); }   } }

7.4.3 DataGrid Editing

In addition to paging and sorting, the DataGrid class supports editing of individual rows. Editing is supported through a property of the DataGrid named EditItemIndex . When this property is set to a non-negative integer, the row associated with that number is redisplayed using text input controls instead of just table cells , giving the user a chance to edit the values in the fields. This property is used in conjunction with the EditCommandColumn , which you place in the Columns collection of your DataGrid , where it renders an additional column in your table containing hyperlinks for the user to click to perform editing. This column also issues three server-side events for you to handle, the EditCommand , the CancelCommand , and the UpdateCommand , corresponding to the three hyperlinks that the user might press when working with your grid. Figure 7-11 shows a sample DataGrid page with editing support in action.

Figure 7-11. A DataGrid with Editing Support in Action

graphics/07fig11.gif

As with both paging and sorting, the DataGrid merely provides the shell for performing row updating. It is up to you to implement the internal details of responding to events and propagating the information back to your data source. To enable editing on a DataGrid , you first need to add to your grid's Columns collection the EditCommandColumn , which describes the appearance and behavior of the supplemental column that is added, indicating that individual rows can be edited. Next, you need to add server-side event handlers for the EditCommand , CancelCommand , and UpdateCommand events the grid issues when the corresponding hyperlinks are pressed. Listing 7-9 shows a sample page with a DataGrid that supports editing.

Listing 7-9 Page with a DataGrid Supporting Editing

 <! File: DataGridEdit.aspx > <%@ Page language="C#" src="DataGridEdit.aspx.cs"          Inherits="DataGridEdit" %> <html> <body> <form runat="server"> <asp:datagrid id="_gd1" runat=server   GridLines=None  OnEditCommand="gd1_Edit"   OnCancelCommand="gd1_Cancel"   OnUpdateCommand="gd1_Update"  >   <Columns>  <asp:EditCommandColumn EditText="Edit"   CancelText="Cancel"   UpdateText="Update"   ItemStyle-Wrap="false"   />  </Columns> </asp:datagrid> </form> </body> </html>

Once the DataGrid is declared with editing support, you have to build pieces of logic to make the grid truly editable. First, in your handler for the EditCommand event, you need to set the EditItemIndex property of your DataGrid class equal to the incoming ItemIndex property of the Item property on the DataGridCommandEventArgs parameter. When the grid renders back to the client after setting this property, it renders the columns for the selected row as text boxes instead of plain table cells, indicating that the user can edit the values. It also changes the EditCommandColumn to display two hyperlinks for that row: Update and Cancel . Second, in your handler for the CancelCommand event, you need to reset the EditItemIndex to -1 , indicating that none of the rows are currently being edited. This makes no row selected when the grid renders back to the client. Third, and most importantly, in your handler for the UpdateCommand event, you need to query the contents of the text boxes for the selected row and propagate back to your data source any changes made. The DataGridCommandEventArgs parameter passed into your UpdateCommand handler exposes the current row through its Item property, which in turn has an array of controls called Cells , which you can index to obtain the control in the column you need. An example of a page implementing this logic is shown in Listing 7-10.

Listing 7-10 Code-Behind Class Implementing DataGrid Editing Logic

 // File: DataGridEdit.aspx.cs public class DataGridEdit : Page {   protected DataGrid _gd1;   protected void Page_Load(object Src, EventArgs e)   {      if (!IsPostBack)        BindGrid();   }   public void gd1_Edit(object sender,                        DataGridCommandEventArgs e)   {      _gd1.EditItemIndex = e.Item.ItemIndex;      BindGrid();   }   public void gd1_Cancel(object sender,                          DataGridCommandEventArgs e)   {      _gd1.EditItemIndex = -1;      BindGrid();   }   public void BindGrid()   {     SqlConnection conn =     new SqlConnection("server=.;uid=sa;pwd=;database=Pubs");     SqlDataAdapter da =         new SqlDataAdapter("select * from Authors", conn);     DataSet ds = new DataSet();     da.Fill(ds, "Authors");     _gd1.DataSource=ds;     _gd1.DataBind();   }   public void gd1_Update(object sender,                          DataGridCommandEventArgs e)   {     string updateCmd = "UPDATE Authors SET au_lname = "+       "@vLname, au_fname = @vFname, phone = @vPhone, "+       "address = @vAddress, city = @vCity, state = " +       "@vState, zip = @vZip, contract = @vContract " +       "where au_id=@vId";     SqlConnection conn =     new SqlConnection("server=.;uid=sa;pwd=;database=pubs");     SqlCommand cmd = new SqlCommand(updateCmd, conn);     cmd.Parameters.Add("@vId",            ((TextBox)e.Item.Cells[1].Controls[0]).Text);     cmd.Parameters.Add("@vLname",            ((TextBox)e.Item.Cells[2].Controls[0]).Text);     cmd.Parameters.Add("@vFname",            ((TextBox)e.Item.Cells[3].Controls[0]).Text);     cmd.Parameters.Add("@vPhone",            ((TextBox)e.Item.Cells[4].Controls[0]).Text);     cmd.Parameters.Add("@vAddress",            ((TextBox)e.Item.Cells[5].Controls[0]).Text);     cmd.Parameters.Add("@vCity",            ((TextBox)e.Item.Cells[6].Controls[0]).Text);     cmd.Parameters.Add("@vState",            ((TextBox)e.Item.Cells[7].Controls[0]).Text);     cmd.Parameters.Add("@vZip",            ((TextBox)e.Item.Cells[8].Controls[0]).Text);     // The bool is stored in a textbox too, so parse it     // into a bool before passing in as a parameter     bool contract =     bool.Parse(((TextBox)e.Item.Cells[9].Controls[0]).Text);     cmd.Parameters.Add("@vContract", contract);     try     {       conn.Open();       cmd.ExecuteNonQuery();       _gd1.EditItemIndex = -1;     }     finally { conn.Dispose(); }     BindGrid();   } }