Using the DataGrid Control
Using the DataGrid ControlThe DataGrid control is the most feature-rich (and most complicated) control included with the ASP.NET framework. Like the Repeater and DataList controls, it enables you to format and display records from a database table. However, it has several advanced features, such as support for sorting and paging through records, which make it unique. You can display records in a DataGrid without using templates. You can simply bind a data source to the DataGrid , and it automatically displays the records. For example, the DataGrid in Listing 11.9 displays all the records from the Titles database table in a DataGrid (see Figure 11.8). Listing 11.9 DataGrid.aspx<%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim conPubs As SqlConnection Dim cmdSelect As SqlCommand conPUbs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) cmdSelect = New SqlCommand( "Select * From Titles", conPubs ) conPubs.Open() dgrdTitles.DataSource = cmdSelect.ExecuteReader() dgrdTitles.DataBind() conPubs.Close() End Sub </Script> <html> <head><title>DataGrid.aspx</title></head> <body> <asp:DataGrid ID="dgrdTitles" EnableViewState="False" Runat="Server" /> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 11.8. Displaying data with a DataGrid . By default, a DataGrid displays gridlines around its items. You can modify how the gridlines appear by setting the GridLines property; possible values are Both , Horizontal , None , or Vertical . For example, to completely disable GridLines , you would declare the DataGrid like this: <asp:DataGrid GridLines="None" Runat="Server" /> You also can control the cell spacing and cell padding of the cells in a DataGrid by modifying the DataGrid control's CellSpacing and CellPadding properties like this: <asp:DataGrid CellSpacing="20" CellPadding="20" Runat="Server" /> You can also specify a background image for a DataGrid by assigning the name of an image to the BackImageUrl property. The image is tiled behind the records the DataGrid displays. For example, the following DataGrid displays an image named Bricks.Gif in the background: <asp:DataGrid BackImageUrl="Bricks.Gif" Runat="Server" /> Finally, you can display or hide headers and footers for the columns in a DataGrid by enabling or disabling the ShowHeader and ShowFooter properties. The ShowHeader property has the value True by default, and the ShowFooter property has the value False by default. If you want to prevent column headers from being displayed, you would declare a DataGrid like this: <asp:DataGrid ShowHeader="False" Runat="Server" /> Creating Columns in a DataGrid ControlThe DataGrid control supports several column types. You can create columns to control how records are formatted or to display links for editing records. The DataGrid control supports the following five types of columns:
By default, a DataGrid simply displays all the columns from its data source. However, if you assign the value False to the DataGrid control's AutoGenerateColumns property, you can create each column individually and have more control over the formatting. Adding a BoundColumn to a DataGridThe default column used in a DataGrid is a BoundColumn . If you want to display only certain columns, but not all columns, from a data source, or you want to control the order in which the columns are displayed, you can explicitly declare one or more BoundColumn controls. The page in Listing 11.10, for example, displays only the Title and Price columns from the Titles table (see Figure 11.9). Listing 11.10 DataGridBoundColumns.aspx<%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim conPubs As SqlConnection Dim cmdSelect As SqlCommand conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) cmdSelect = New SqlCommand( "Select * From Titles", conPubs ) conPubs.Open() dgrdTitles.DataSource = cmdSelect.ExecuteReader() dgrdTitles.DataBind() conPubs.Close() End Sub </Script> <html> <head><title>DataGridBoundColumns.aspx</title></head> <body> <asp:DataGrid ID="dgrdTitles" AutoGenerateColumns="False" EnableViewState="False" Runat="Server"> <Columns> <asp:BoundColumn DataField="Title" /> <asp:BoundColumn DataField="Price" /> </Columns> </asp:DataGrid> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 11.9. Using BoundColumn controls. Notice that the DataGrid control's AutoGenerateColumns property is set to the value False . If you do not set this property to False , all the columns from the Titles table are displayed, and the Title and Price columns are displayed twice. The BoundColumns are declared inside the DataGrid control's <Columns> tag. This tag contains a BoundColumn for the Titles column and a BoundColumn for the Price column. The DataField property indicates the field from the data source to display in the BoundColumn . NOTE For performance reasons, you should select only the records that you intend to display in a page when retrieving records from a database. In other words, Select * is your enemy. You can format the output of a BoundColumn by assigning a format string to its DataFormatString property. This capability is useful, for example, when you need to display a currency or date/time value. To display the Price column with a currency format string, you would declare the BoundColumn like this: <asp:BoundColumn DataField="Price" DataFormatString="{0:c}"/> NOTE For more information on using format strings, see Chapter 24, "Working with Collections and Strings." Finally, you can control the appearance of the header and footer used for a BoundColumn by modifying the HeaderText , FooterText , and HeaderImageUrl properties. The HeaderText and FooterText properties enable you to display text at the top and bottom of a BoundColumn . The HeaderImageUrl enables you to display a picture for the header. The following DataList control, for example, displays the text Book Titles for the header and footer: <asp:DataGrid AutoGenerateColumns="False" EnableViewState="False" ShowFooter="True" Runat="Server"> <Columns> <asp:BoundColumn DataField="Title" HeaderText="Book Titles" FooterText="Book Titles" /> <asp:BoundColumn DataField="Price" DataFormatString="{0:c}"/> </Columns> </asp:DataGrid> Notice that the DataGrid control's ShowFooter property is assigned the value True . Before you can use the FooterText property, you must modify the ShowFooter property. The HeaderImageUrl property displays an image for a header. The image is not displayed in the background, so you cannot combine the HeaderImageUrl property with the HeaderText property to display both an image and text. If you want to display both an image and text in a header, you need to assign the HTML tags for an image to the HeaderText property like this: HeaderText="<img src=myImage.Gif>Titles" Following is a list of some of the important properties supported by a BoundColumn :
Adding a HyperLinkColumn to a DataGridIf you want to display links to other pages from your DataGrid , you can use a HyperLinkColumn . This type of column is useful in two situations. First, you can use a HyperLinkColumn when creating a master/detail form. You can display a set of master records in a DataGrid and use a HyperLinkColumn in the DataGrid to link to a separate page that contains detailed information. You also can use a HyperLinkColumn to display links to different pages at your Web site, or links to other Web sites. For example, you can use a HyperLinkColumn to display a list of useful ASP.NET Web sites. The page in Listing 11.11 could be used as the master page in a master/detail form. Listing 11.11 DataGridHyperLink.aspx <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim conPubs As SqlConnection Dim cmdSelect As SqlCommand conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) cmdSelect = New SqlCommand( "Select au_id, au_lname From Authors", conPubs ) conPubs.Open() dgrdAuthors.DataSource = cmdSelect.ExecuteReader() dgrdAuthors.DataBind() conPubs.Close() End Sub </Script> <html> <head><title>DataGridHyperLink.aspx</title></head> <body> <form Runat="Server"> <asp:DataGrid ID="dgrdAuthors" AutoGenerateColumns="False" EnableViewState="False" CellPadding="10" Runat="Server"> <Columns> <asp:BoundColumn HeaderText="Author" DataField="au_lname" /> <asp:HyperLinkColumn HeaderText="Details" DataNavigateUrlField="au_id" DataNavigateUrlFormatString="Details.aspx?id={0}" Text="view details" /> </Columns> </asp:DataGrid> </form> </body> </html> The C# version of this code can be found on the CD-ROM. The DataGrid in Listing 11.11 is bound to the Authors database table. The values from the author last name column ( au_lname ) are displayed by the BoundColumn , and links to a page with detailed author information are displayed by the HyperLinkColumn . Each hyperlink displayed by the HyperLinkColumn is displayed with the help of the DataNavigateUrlField , DataNavigateUrlFormatString , and Text properties. The DataNavigateUrlField and DataNavigateUrlFormatString properties create the URL for the link. In this case, the links point to a page named Details.aspx . The au_id column is passed to Details.aspx in a query string variable named ID . The Text property specifies the label for the link. In Listing 11.11, each link is simply labeled with the text view details . Following is a list of some of the important properties of a HyperLinkColumn :
Notice that you can use the DataTextField and DataTextFormatString to display different labels for each hyperlink. For example, the page in Listing 11.12 displays a list of hyperlinks to different Web sites that contain information on ASP.NET (see Figure 11.10). Listing 11.12 DataGridDataFormatString.aspx<%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim conMyData As SqlConnection Dim cmdSelect As SqlCommand conMyData = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=MyData" ) cmdSelect = New SqlCommand( "Select link_title, link_url From FavLinks", conMyData ) conMyData.Open() dgrdLinks.DataSource = cmdSelect.ExecuteReader() dgrdLinks.DataBind() conMyData.Close() End Sub </Script> <html> <head><title>DataGridDataFormatString.aspx</title></head> <body> <form Runat="Server"> <asp:DataGrid ID="dgrdLinks" AutoGenerateColumns="False" EnableViewState="False" ShowHeader="False" CellPadding="10" Runat="Server"> <Columns> <asp:HyperLinkColumn DataNavigateUrlField="link_url" DataTextField="link_title" /> </Columns> </asp:DataGrid> </form> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 11.10. Using the DataTextFormatString property. The HyperLinkColumn displays each link by using the DataNavigateUrlField and DataTextField properties. The DataNavigateUrlField property contains the URL of the link, and the DataTextField property contains the text label of the link. Adding a TemplateColumn to a DataGridIf you need more control over the formatting of a column displayed by a DataGrid , you can use a TemplateColumn , which enables you to control the formatting of a column through the following templates:
Imagine that you want to display a DataGrid that contains two columns. The first column displays author last names , and the second column displays more detailed information about the author (see Figure 11.11). The page in Listing 11.13 illustrates how you can create such a DataGrid by using a TemplateColumn . Listing 11.13 DataGridTemplate.aspx<%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim conPubs As SqlConnection Dim cmdSelect As SqlCommand conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) cmdSelect = New SqlCommand( "Select * From Authors", conPubs ) conPubs.Open() dgrdAuthors.DataSource = cmdSelect.ExecuteReader() dgrdAuthors.DataBind() conPubs.Close() End Sub </Script> <html> <head><title>DataGridTemplate.aspx</title></head> <body> <form Runat="Server"> <asp:DataGrid ID="dgrdAuthors" AutoGenerateColumns="False" EnableViewState="False" ShowHeader="False" CellPadding="10" Runat="Server"> <Columns> <asp:BoundColumn DataField="au_lname" /> <asp:TemplateColumn> <itemTemplate> <table> <tr> <td>Phone:</td> <td><%# Container.DataItem( "phone" )%></td> </tr> <tr> <td>City:</td> <td><%# Container.DataItem( "city" )%></td> </tr> </table> </itemTemplate> </asp:TemplateColumn> </Columns> </asp:DataGrid> </form> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 11.11. Using a TemplateColumn . In Listing 11.13, the TemplateColumn displays the phone and city fields from the Authors table. These two fields are formatted with an HTML table. Notice that templates work differently when used with a DataGrid than when used with either a Repeater or DataList control. When you create templates for a Repeater or DataList , the template formats rows of data displayed by the control. When a template is used with a DataGrid , in contrast, the template formats a single column, not the whole row. A TemplateColumn supports the following properties:
Adding a ButtonColumn to a DataGridYou can use a ButtonColumn to add command buttons to a DataGrid . You can use these command buttons to perform any action you wish. For example, you can use a ButtonColumn to create an Add To Cart button on a DataGrid . The page in Listing 11.14 illustrates how you can use a ButtonColumn to select rows in a DataGrid . When you click the Select! link displayed by the first ButtonColumn , the selected row appears with a light green background and bold text (see Figure 11.12). Listing 11.14 DataGridButtonColumn.aspx<%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load If Not IsPostBack Then Dim conPubs As SqlConnection Dim cmdSelect As SqlCommand conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) cmdSelect = New SqlCommand( "Select * From Titles", conPubs ) conPubs.Open() dgrdTitles.DataSource = cmdSelect.ExecuteReader() dgrdTitles.DataBind() conPubs.Close() End If End Sub Sub dgrdTitles_ItemCommand( s As Object, e As DataGridCommandEventArgs ) If e.CommandName="select" Then e.Item.BackColor = System.Drawing.Color.LightGreen e.Item.Font.Bold = True Else e.Item.BackColor = System.Drawing.Color.White e.Item.Font.Bold = False End If End Sub </Script> <html> <head><title>DataGridButtonColumn.aspx</title></head> <body> <form Runat="Server"> <asp:DataGrid ID="dgrdTitles" OnItemCommand="dgrdTitles_ItemCommand" AutoGenerateColumns="False" CellPadding="10" Runat="Server"> <Columns> <asp:BoundColumn HeaderText="Book Titles" DataField="Title" /> <asp:ButtonColumn CommandName="select" Text="Select!" /> <asp:ButtonColumn CommandName="unselect" Text="UnSelect!" /> </Columns> </asp:DataGrid> </form> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 11.12. Using a ButtonColumn . When you click the Select! button displayed by the first ButtonColumn , the subroutine associated with the DataGrid control's OnItemCommand property is executed. This subroutine, named dgrdTitles_ItemCommand , displays the background of the selected row in light green with bold text. When you click the UnSelect! button displayed by the second ButtonColumn , the item reverts to a white background and normal font weight. The dgrdTitles_ItemCommand subroutine distinguishes between the Select! and UnSelect! buttons with the help of the value of the buttons' CommandName properties. A ButtonColumn supports the following properties:
Adding an EditCommandColumn to a DataGrid ControlAn EditCommandColumn displays the most common editing commands (see Figure 11.13). You can use it to display Edit, Update, and Cancel buttons. It doesn't actually perform any editing, it just displays the user interface for editing a row. Figure 11.13. Using an EditCommandColumn . The DataGrid in Listing 11.15, for example, includes an EditCommandColumn . Listing 11.15 DataGridEditCommandColumn.aspx<%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load If Not IsPostBack Then BindDataGrid End If End Sub Sub BindDataGrid Dim conPubs As SqlConnection Dim cmdSelect As SqlCommand conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) cmdSelect = New SqlCommand( "Select * From Titles", conPubs ) conPubs.Open() dgrdTitles.DataSource = cmdSelect.ExecuteReader() dgrdTitles.DataBind() conPubs.Close() End Sub Sub dgrdTitles_EditCommand( s As Object, e As DataGridCommandEventArgs ) dgrdTitles.EditItemIndex = e.Item.ItemIndex BindDataGrid End Sub </Script> <html> <head><title>DataGridEditCommandColumn.aspx</title></head> <body> <form Runat="Server"> <asp:DataGrid ID="dgrdTitles" OnEditCommand="dgrdTitles_EditCommand" AutoGenerateColumns="False" CellPadding="10" Runat="Server"> <Columns> <asp:BoundColumn HeaderText="Book Titles" DataField="Title" /> <asp:EditCommandColumn EditText="Edit!" UpdateText="Update!" CancelText="Cancel!" /> </Columns> </asp:DataGrid> </form> </body> </html> The C# version of this code can be found on the CD-ROM. Exactly what an EditCommandColumn displays depends on the current value of the DataGrid control's EditItemIndex property, which selects a DataGrid item for editing. When an item is not selected for editing, the EditCommandColumn displays an Edit command. When an item is selected for editing, the EditCommandColumn displays Update and Cancel commands. You can select an item for editing by clicking the Edit command displayed by an EditCommandColumn . In Listing 11.15, when you click the Edit command link, the selectEdit subroutine executes and assigns the index number of the selected item to the EditItemIndex property. This results in the item being selected for editing. The EditCommandColumn supports the following properties:
Using Styles with a DataGridThe DataGrid control supports style objects that you can use to customize its appearance. Each style object is an instance of the TableItemStyle class, which has the following properties:
You can use any of these style properties with the AlternatingItemStyle , EditItemStyle , FooterStyle , HeaderStyle , ItemStyle , and SelectedItemStyle styles of the DataGrid control. When you set a style property while declaring a control, you use a hyphen ( - ) instead of the normal period ( . ) to indicate a property of an object. The page in Listing 11.16, for example, displays the header with a bold, Arial font and a yellow background. Every item is displayed with a 10-point Arial font. Alternate items are displayed with an Alice Blue background (see Figure 11.14). Listing 11.16 DataGridStyles.aspx<%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load If Not isPostBack Then Dim conPubs As SqlConnection Dim cmdSelect As SqlCommand conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) cmdSelect = New SqlCommand( "Select * From Titles", conPubs ) conPubs.Open() dgrdTitles.DataSource = cmdSelect.ExecuteReader() dgrdTitles.DataBind() conPubs.Close() End If End Sub </Script> <html> <head><title>DataGridStyles.aspx</title></head> <body> <form Runat="Server"> <asp:DataGrid ID="dgrdTitles" CellPadding="10" HeaderStyle-Font-Name="Arial" HeaderStyle-Font-Bold="True" HeaderStyle-BackColor="lightyellow" ItemStyle-Font-Name="Arial" ItemStyle-Font-Size="10pt" AlternatingItemStyle-BackColor="AliceBlue" Runat="Server" /> </form> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 11.14. Using styles with a DataGrid . In Listing 11.16, the style objects modify every item displayed by a DataGrid . You also can apply style objects to individual columns in a DataGrid by using different style objects with different columns. For example, the following DataGrid has different style objects applied to its two BoundColumns : <asp:DataGrid AutoGenerateColumns="False" CellPadding="10" Runat="Server" > <Columns> <asp:BoundColumn DataField="Title" HeaderText="Book Titles" HeaderStyle-Font-Name="Script" /> <asp:BoundColumn DataField="Price" HeaderText="Book Prices" HeaderStyle-Font-Name="Verdana" /> </Columns> </asp:DataGrid> When this DataGrid is displayed, the header text for Book Titles appears with a different font than the header text for Book Prices . Finally, you can apply style properties directly to a DataGrid . For example, if you need to change the appearance of the border of a DataGrid , you can set the BorderStyle property directly on the DataGrid like this: <asp:DataGrid BorderStyle="Dashed" BorderWidth="8px" BorderColor="Blue" Runat="Server" /> </asp:DataGrid> This DataGrid appears with a blue dashed border that is 8 pixels wide. The Font properties used with the Style object can be a little confusing at first because they are actually properties of the FontInfo class. (The TableItemStyle object has a property named Font that represents the FontInfo class.) The FontInfo class has the following properties:
Sorting Columns in a DataGrid ControlThe DataGrid control has built-in support for sorting columns. You can enable sorting for all the columns in a DataGrid or enable sorting for only particular columns. To enable sorting for all the columns in a DataGrid , set the AllowSorting property to True and associate a subroutine with the SortCommand event. The page in Listing 11.17 illustrates how you can do so (see Figure 11.15). Listing 11.17 DataGridSort.aspx<%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load If Not IsPostBack Then BindDataGrid( "Title" ) End If End Sub Sub BindDataGrid( strSortField As String ) Dim conPubs As SqlConnection Dim cmdSelect As SqlCommand conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) cmdSelect = New SqlCommand( "Select * From Titles Order By " & strSortField, conPubs ) conPubs.Open() dgrdTitles.DataSource = cmdSelect.ExecuteReader() dgrdTitles.DataBind() conPubs.Close() End Sub Sub dgrdTitles_SortCommand( s As Object, e As DataGridSortCommandEventArgs ) BindDataGrid( e.SortExpression ) End Sub </Script> <html> <head><title>DataGridSort.aspx</title></head> <body> <form Runat="Server"> <asp:DataGrid ID="dgrdTitles" AllowSorting="True" OnSortCommand="dgrdTitles_SortCommand" CellPadding="10" Runat="Server" /> </form> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 11.15. Sorting columns in a DataGrid . Because the AllowSorting property is enabled for the DataGrid in Listing 11.17, the header text for all the columns appears as hypertext links. When you click a link, the SortCommand event is raised. This event is associated with the dgrdTitles_SortCommand subroutine by the DataGrid control's OnSortCommand property. The dgrdTitles_SortCommand subroutine retrieves the name of the column selected for sorting and passes it to the BindDataGrid subroutine. The actual sorting is performed in the BindDataGrid subroutine. The records are sorted with the help of a SQL Order By clause. You can enable and disable sorting for particular columns by using the SortExpression property. Sorting on certain columns, such as ProductID , might not make any sense. For example, the DataGrid in Listing 11.18 enables you to sort the first two columns, but not the last (see Figure 11.16). Listing 11.18 DataGridSortExpression.aspx <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load If Not isPostBack Then BindDataGrid( "Title" ) End If End Sub Sub BindDataGrid( strSortField As String ) Dim conPubs As SqlConnection Dim cmdSelect As SqlCommand conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) cmdSelect = New SqlCommand( "Select * From Titles Order By " & strSortField, conPubs ) conPubs.Open() dgrdTitles.DataSource = cmdSelect.ExecuteReader() dgrdTitles.DataBind() conPubs.Close() End Sub Sub dgrdTitles_SortCommand( s As Object, e As DataGridSortCommandEventArgs ) BindDataGrid( e.SortExpression ) End Sub </Script> <html> <head><title>DataGridSortExpression.aspx</title></head> <body> <form Runat="Server"> <asp:DataGrid ID="dgrdTitles" AllowSorting="True" OnSortCommand="dgrdTitles_SortCommand" AutoGenerateColumns="False" CellPadding="10" Runat="Server"> <Columns> <asp:BoundColumn DataField="Title" HeaderText="Sort Titles" SortExpression="title"/> <asp:BoundColumn HeaderText="Sort Price" DataField="price" DataFormatString="{0:c}" SortExpression="price"/> <asp:BoundColumn DataField="Notes" /> </Columns> </asp:DataGrid> </form> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 11.16. Sorting only certain columns in a DataGrid . The first two BoundColumns have their SortExpression properties set. The first BoundColumn sorts the title database field, and the second BoundColumn sorts the price database field. Because no SortExpression appears for the BoundColumn named Notes , you cannot sort on this field. Paging Through Records in a DataGridThe DataGrid control has built-in support for paging through the records of a data source. Imagine, for example, that you want to display a product list that contains hundreds of records. You would not want to display all these records on a page at once. Instead, you would want to divide the records into multiple logical pages. You enable paging for a DataGrid by enabling the AllowPaging property and creating a subroutine to change the current page. There is one other important requirement for enabling paging. You can enable paging only if a DataGrid control's data source implements the ICollection interface. A DataReader doesn't implement this interface, so you must use a DataTable instead. NOTE DataTables and DataSets are discussed in the next chapter, Chapter 12, "Working with DataSets." The page in Listing 11.19 illustrates how you can add paging functionality to a DataGrid control (see Figure 11.17). Listing 11.19 DataGridPaging.aspx<%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Data" %> <Script Runat="Server"> Sub Page_Load If Not isPostBack Then BindDataGrid End If End Sub Sub BindDataGrid Dim conPubs As SqlConnection Dim dadTitles As SqlDataAdapter Dim dstTitles As DataSet conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) dadTitles = New SqlDataAdapter( "Select * From Titles Order By Title", conPubs ) dstTitles = New DataSet dadTitles.Fill( dstTitles ) dgrdTitles.DataSource = dstTitles dgrdTitles.DataBind() End Sub Sub dgrdTitles_PageIndexChanged( s As Object, e As DataGridPageChangedEventArgs ) dgrdTitles.CurrentPageIndex = e.NewPageIndex BindDataGrid End Sub </Script> <html> <head><title>DataGridPaging.aspx</title></head> <body> <form Runat="Server"> <asp:DataGrid ID="dgrdTitles" AllowPaging="True" PageSize="5" OnPageIndexChanged="dgrdTitles_PageIndexChanged" CellPadding="3" Runat="Server" /> </form> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 11.17. Enabling paging for a DataGrid . The DataGrid in Listing 11.19 displays five records at a time from the Titles database table. By clicking the < and > links displayed at the bottom of the DataGrid , you can move one page forward or backward. The number of records displayed in a page is determined by the DataGrid control's PageSize property. By default, this property has the value 10 . It is set to 5 in the listing to display fewer records per page. A new page is selected with the dgrdTitles_PageIndexChanged subroutine. This subroutine assigns the value of the NewPageIndex to the DataGrid control's CurrentPageIndex . The subroutine then rebinds the DataGrid to the data source, displaying the new page of records. You can control the appearance of the page links by modifying the properties of the DataGridPagerStyle style object. This style object supports all the properties of the TableItemStyle style object in addition to the following properties:
The DataGridPagerStyle object is exposed in a DataGrid through the PagerStyle property. For example, the following DataGrid displays page navigation links, using a list of numbers for each page, a coral background, and a bold Verdana font (see Figure 11.18): <asp:DataGrid id="dgrdTitles" AllowPaging="True" PageSize="5" OnPageIndexChanged="dgrdTitles_PageIndexChanged" CellPadding="3" PagerStyle-Mode="NumericPages" PagerStyle-Font-Name="Verdana" PagerStyle-Font-Bold="True" PagerStyle-BackColor="Coral" Runat="Server" /> Figure 11.18. Specifying PagerStyle properties. Implementing Custom PagingOne disadvantage of using the DataGrid control's built-in paging support is performance. When you use the method of paging through records discussed in the preceding section, all the records must be retrieved from the data source every time you navigate to a new page. So, if you are paging through a database table with 2 million records, those 2 million records must be retrieved into memory every time you move to a new page. In this section, you learn how to implement a custom paging solution to get around this limitation. Instead of retrieving every record to display each page, you retrieve only the records you need. The custom paging solution you examine here makes a strong assumption about the contents of the database table with which it is used. This approach works only with database tables that have a column that uniquely indexes each database row. For example, it works with a table that has an identity column when the identity column is not missing any values. CAUTION If certain values are missing from the identity column, the DataGrid displays fewer records for some pages than others. This custom paging approach is implemented in Listing 11.20. Listing 11.20 DataGridCustomPaging.aspx<%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Data" %> <Script Runat="Server"> Dim conNorthwind As SqlConnection Dim strSelect As String Dim intStartIndex As Integer Dim intEndIndex As Integer Sub Page_Load Dim cmdSelect As SqlCommand conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" ) If Not IsPostBack Then ' Get Total Pages strSelect = "Select Count(*) From Products" cmdSelect = New SqlCommand( strSelect, conNorthwind ) conNorthwind.Open() dgrdProducts.VirtualItemCount = ( cmdSelect.ExecuteScalar() / dgrdProducts.PageSize ) conNorthwind.Close() BindDataGrid End If End Sub Sub BindDataGrid Dim dadProducts As SqlDataAdapter Dim dstProducts As DataSet intEndIndex = intStartIndex + dgrdProducts.PageSize strSelect = "Select * From Products Where ProductID > @startIndex " _ & "And ProductID <= @endIndex Order By ProductID" dadProducts = New SqlDataAdapter( strSelect, conNorthwind ) dadProducts.SelectCommand.Parameters.Add( "@startIndex", intStartIndex ) dadProducts.SelectCommand.Parameters.Add( "@endIndex", intEndIndex ) dstProducts = New DataSet dadProducts.Fill( dstProducts ) dgrdProducts.DataSource = dstProducts dgrdProducts.DataBind() End Sub Sub dgrdProducts_PageIndexChanged( s As Object, e As DataGridPageChangedEventArgs ) intStartIndex = ( e.NewPageIndex * dgrdProducts.PageSize ) dgrdProducts.CurrentPageIndex = e.NewPageIndex BindDataGrid End Sub </Script> <html> <head><title>DataGridCustomPaging.aspx</title></head> <body> <form Runat="Server"> <asp:DataGrid ID="dgrdProducts" AllowPaging="True" AllowCustomPaging="True" PageSize="3" OnPageIndexChanged="dgrdProducts_PageIndexChanged" PagerStyle-Mode="NumericPages" CellPadding="3" Runat="Server" /> </form> </body> </html> The C# version of this code can be found on the CD-ROM. The page in Listing 11.20 enables you to page through the records in the Products database table three records at a time. (You can change the number of records displayed at a time by changing the value of the PageSize property.) A count of the total number of records in the Products table is retrieved in the Page_Load subroutine. This value is assigned to the VirtualItemCount property of the DataGrid . The DataGrid control uses this property when rendering the paging links. When you click a page number link, the dgrdProducts_PageIndexChanged subroutine is executed. This subroutine calculates the starting index of the record to display by multiplying the index of the selected page by the page size. The correct set of records is retrieved from the Products database table with this SQL statement: Select * From Products Where ProductID > @startIndex And ProductID <= @endIndex Order By ProductID" This Select statement retrieves all the records that have a ProductID greater than the startIndex and less than or equal to the endIndex . The endIndex is calculated by adding the startIndex to the value of the PageSize property of the DataGrid . The advantage of this approach to paging through records is that the only records retrieved from the database are the records displayed in the DataGrid . In other words, this approach works great with huge database tables. Selecting Rows in a DataGrid ControlYou can select a row in a DataGrid by modifying the value of the control's SelectedIndex property. This capability is useful, for example, when you want to display a menu of choices or create a master/detail form with a DataGrid . The page in Listing 11.21 contains two DataGrid controls. The first DataGrid lists categories and category descriptions, and the second DataGrid lists product information. When you select a category from the first DataGrid , the products in that category are displayed in the second DataGrid (see Figure 11.19). Listing 11.21 DataGridMasterDetail.aspx<%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load If Not IsPostBack Then BindMasterGrid End If End Sub Sub BindMasterGrid Dim conNorthwind As SqlConnection Dim cmdSelect As SqlCommand conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" ) cmdSelect = New SqlCommand( "Select * From Categories", conNorthwind ) conNorthwind.Open() dgrdCategories.DataSource = cmdSelect.ExecuteReader() dgrdCategories.DataBind() conNorthwind.Close() End Sub Sub BindDetailGrid( intCatID As Integer ) Dim conNorthwind As SqlConnection Dim strSelect As String Dim cmdSelect As SqlCommand conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" ) strSelect = "Select * From Products Where CategoryID=@catID" cmdSelect = New SqlCommand( strSelect, conNorthwind ) cmdSelect.Parameters.Add( "@catID", intCatID ) conNorthwind.Open() dgrdProducts.DataSource = cmdSelect.ExecuteReader() dgrdProducts.DataBind() conNorthwind.Close() End Sub Sub dgrdCategories_ItemCommand( s As Object, e As DataGridCommandEventArgs ) Dim intCatID As Integer intCatID = dgrdCategories.DataKeys( e.Item.ItemIndex ) dgrdCategories.SelectedIndex = e.Item.ItemIndex BindDetailGrid( intCatID ) End Sub </Script> <html> <head><title>DataGridMasterDetail.aspx</title></head> <body> <form Runat="Server"> <asp:DataGrid ID="dgrdCategories" OnItemCommand="dgrdCategories_ItemCommand" DataKeyField="CategoryID" AutoGenerateColumns="False" SelectedItemStyle-BackColor="LightGreen" ShowHeader="False" Runat="Server"> <Columns> <asp:TemplateColumn> <ItemTemplate> <asp:LinkButton Text='<%# Container.DataItem( "CategoryName" ) %>' Runat="Server"/> </ItemTemplate> </asp:TemplateColumn> <asp:BoundColumn DataField="Description" /> </Columns> </asp:DataGrid> <p> <asp:DataGrid ID="dgrdProducts" HeaderStyle-BackColor="yellow" Runat="Server" /> </form> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 11.19. Selecting DataGrid rows. When you select a category in the first DataGrid , the dgrdCategories_ItemCommand subroutine is executed. This subroutine retrieves the primary key associated with the selected category from the DataKeys collection. The primary key is passed to the BindDetailGrid subroutine to display the correct products for the selected category. Next, the subroutine assigns the index of the selected category to the SelectedIndex property of the DataGrid . The selected category appears with a light green background. Editing Items in a DataGrid ControlYou can edit items in a DataGrid by adding an EditCommandColumn to the DataGrid and associating subroutines with the DataGrid control's EditCommand , UpdateCommand , and CancelCommand events. The page in Listing 11.22, for example, enables you to edit records in the Products database table (see Figure 11.20). Listing 11.22 DataGridEditProducts.aspx <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Dim conNorthwind As SqlConnection Dim cmdSql As SqlCommand Dim strSql As String Sub Page_Load conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" ) If Not IsPostBack Then BindDataGrid End If End Sub Sub BindDataGrid cmdSql = New SqlCommand( "Select * From Products", conNorthwind ) conNorthwind.Open() dgrdProducts.DataSource = cmdSql.ExecuteReader() dgrdProducts.DataBind() conNorthwind.Close() End Sub Sub dgrdProducts_EditCommand( s As Object, e As DataGridCommandEventArgs ) dgrdProducts.EditItemIndex = e.Item.ItemIndex BindDataGrid End Sub Sub dgrdProducts_UpdateCommand( s As Object, e As DataGridCommandEventArgs ) Dim intProductID As Integer Dim txtProductName As TextBox Dim txtUnitPrice As TextBox Dim strProductName As String Dim decUnitPrice As Decimal intProductID = dgrdProducts.DataKeys( e.Item.ItemIndex ) txtProductName = e.Item.Cells( 1 ).Controls( 0 ) txtUnitPrice = e.Item.Cells( 2 ).Controls( 0 ) strProductName = txtProductName.Text decUnitPrice = txtUnitPrice.Text strSql = "Update Products Set ProductName=@ProductName, " _ & "UnitPrice=@UnitPrice Where ProductID=@ProductID" cmdSql = New SqlCommand( strSql, conNorthwind ) cmdSql.Parameters.Add( "@ProductName", strProductName ) cmdSql.Parameters.Add( "@UnitPrice", decUnitPrice ) cmdSql.Parameters.Add( "@ProductID", intProductID ) conNorthwind.Open() cmdSql.ExecuteNonQuery() conNorthwind.Close() dgrdProducts.EditItemIndex = -1 BindDataGrid End Sub Sub dgrdProducts_CancelCommand( s As Object, e As DataGridCommandEventArgs ) dgrdProducts.EditItemIndex = -1 BindDataGrid End Sub </Script> <html> <head><title>DataGridEditProducts.aspx</title></head> <body> <form Runat="Server"> <asp:DataGrid ID="dgrdProducts" OnEditCommand="dgrdProducts_EditCommand" OnUpdateCommand="dgrdProducts_UpdateCommand" OnCancelCommand="dgrdProducts_CancelCommand" DataKeyField="ProductID" AutoGenerateColumns="False" CellPadding="10" HeaderStyle-BackColor="Salmon" Runat="Server"> <Columns> <asp:BoundColumn HeaderText="Product ID" DataField="ProductID" ReadOnly="True" /> <asp:BoundColumn HeaderText="Product Name" DataField="ProductName" /> <asp:BoundColumn HeaderText="Price" DataField="UnitPrice" DataFormatString="{0:c}" /> <asp:EditCommandColumn EditText="Edit!" UpdateText="Update!" CancelText="Cancel!" /> </Columns> </asp:DataGrid> </form> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 11.20. Editing data with a DataGrid . When you select an item for editing, the dgrdProducts_EditCommand subroutine executes. This subroutine sets the value of the EditItemIndex property of the DataGrid . Any bound columns selected for editing automatically appear with TextBox controls. When a product is selected for editing in Listing 11.22, the BoundColumns for the ProductName and UnitPrice appear with TextBox controls. The ProductID column is excluded from editing because its ReadOnly property is set to the value True . When the Update button is clicked, the dgrdProducts_UpdateCommand subroutine executes. This subroutine retrieves the value of the ProductID associated with the row selected for editing by retrieving the value from the DataKeys collection. The values of the TextBox controls named ProductName and UnitPrice are retrieved with the following lines: txtProductName = e.Item.Cells( 1 ).Controls( 0 ) txtUnitPrice = e.Item.Cells( 2 ).Controls( 0 ) strProductName = txtProductName.Text decUnitPrice = txtUnitPrice.Text Each row of a DataGrid consists of cells, and each cell contains controls. The TextBox control named txtProductName is retrieved by grabbing the first control located in the second cell of the DataGrid . (The first cell contains the ProductID .) The TextBox control named txtUnitPrice is retrieved by grabbing the first control in the third cell. After the TextBox controls named txtProductName and txtUnitPrice have been retrieved, the current values of these TextBox controls can be assigned to the strProductName and decUnitPrice variables . After a product is updated in the database, the DataGrid control's EditItemIndex property is assigned the value -1 . This value unselects any item for editing in the DataGrid . Editing Items in a DataGrid Control with TemplatesThe approach followed in the preceding section for editing records in a DataGrid has a couple of significant limitations. These limitations are derived from the fact that you are using BoundColumns to automatically display TextBox controls to edit records. The first limitation is that you are forced to use only TextBox controls. You can't, for example, enable a user to select a value from a DropDownList control. Second, you cannot use Validation controls with the method of editing records described in the preceding section. For example, if you enter the string "apple" for the value of the TextBox control named txtUnitPrice , you receive an error message. And because you cannot use Validation controls, there's nothing preventing you from doing that. In this section, you examine a second method of updating records in a DataGrid . Here, you use a TemplateColumn , which has both an ItemTemplate and an EditTemplate , to update records. The page in Listing 11.23 demonstrates how you can use a TemplateColumn when updating records in a DataGrid (see Figure 11.21). Listing 11.23 DataGridEditTemplate.aspx<%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Dim conNorthwind As SqlConnection Dim cmdSql As SqlCommand Dim strSql As String Sub Page_Load conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" ) If Not IsPostBack Then BindDataGrid End If End Sub Sub BindDataGrid cmdSql = New SqlCommand( "Select * From Products", conNorthwind ) conNorthwind.Open() dgrdProducts.DataSource = cmdSql.ExecuteReader() dgrdProducts.DataBind() conNorthwind.Close() End Sub Sub dgrdProducts_EditCommand( s As Object, e As DataGridCommandEventArgs ) dgrdProducts.EditItemIndex = e.Item.ItemIndex BindDataGrid End Sub Sub dgrdProducts_UpdateCommand( s As Object, e As DataGridCommandEventArgs ) Dim intProductID As Integer Dim txtUnitPrice As TextBox Dim decUnitPrice As Decimal If IsValid Then intProductID = dgrdProducts.DataKeys( e.Item.ItemIndex ) txtUnitPrice = e.Item.FindControl( "txtUnitPrice" ) decUnitPrice = txtUnitPrice.Text strSql = "Update Products Set UnitPrice=@UnitPrice " _ & "Where ProductID=@ProductID" cmdSql = New SqlCommand( strSql, conNorthwind ) cmdSql.Parameters.Add( "@UnitPrice", decUnitPrice ) cmdSql.Parameters.Add( "@ProductID", intProductID ) conNorthwind.Open() cmdSql.ExecuteNonQuery() conNorthwind.Close() dgrdProducts.EditItemIndex = -1 BindDataGrid End If End Sub Sub dgrdProducts_CancelCommand( s As Object, e As DataGridCommandEventArgs ) dgrdProducts.EditItemIndex = -1 BindDataGrid End Sub </Script> <html> <head><title>DataGridEditTemplate.aspx</title></head> <body> <form Runat="Server"> <asp:DataGrid ID="dgrdProducts" OnEditCommand="dgrdProducts_EditCommand" OnUpdateCommand="dgrdProducts_Updatecommand" OnCancelCommand="dgrdProducts_CancelCommand" DataKeyField="ProductID" AutoGenerateColumns="False" CellPadding="10" HeaderStyle-BackColor="Salmon" Runat="Server"> <Columns> <asp:BoundColumn HeaderText="Product Name" DataField="ProductName" ReadOnly="True" /> <asp:TemplateColumn> <HeaderTemplate> Price </HeaderTemplate> <ItemTemplate> <%# Container.DataItem( "UnitPrice" ) %> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtUnitPrice" Text='<%# Container.DataItem( "UnitPrice" )%>' Runat="Server" /> <asp:RequiredFieldValidator ControlToValidate="txtUnitPrice" Display="Dynamic" Text="Required!" Runat="Server" /> <asp:CompareValidator ControlToValidate="txtUnitPrice" Display="Dynamic" Text="Must be Currency!" Operator="DataTypeCheck" Type="Currency" Runat="Server" /> </EditItemTemplate> </asp:TemplateColumn> <asp:EditCommandColumn EditText="Edit!" UpdateText="Update!" CancelText="Cancel!" /> </Columns> </asp:DataGrid> </form> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 11.21. Editing data with templates in a DataGrid . The DataGrid in Listing 11.23 enables you to edit its UnitPrice column, which is displayed by a TemplateColumn . The EditItemTemplate contains two Validation controls that require you to enter a value for UnitPrice and enter a currency value as the value. When the Update! link is clicked, the dgrdProducts_UpdateCommand subroutine executes and updates the value of the UnitPrice column in the underlying database table. Notice that the UnitPrice is retrieved from the EditItemTemplate with the help of the FindControl() method. |
EAN: 2147483647
Pages: 263