Data Binding in Complex Web Forms Controls
The DataList and DataGrid controls are complex data-bound controls. These controls are capable of displaying multiple columns of a data source. The DataList and DataGrid controls share some common features. Before discussing each control, we'll discuss these common features.
Understanding the BaseDataList Class
Before discussing the DataList and DataGrid controls, let's take a look at the BaseDataList class. The BaseDataList class is an abstract base class for the DataList and DataGrid controls. Because it's an abstract base class, the functionality defined in this class can only be used through its derived classes. The class hierarchy is as follows:
System.Object System.Web.UI.Control System.Web.UI.WebControls.WebControl System.Web.UI.WebControls.BaseDataList System.Web.UI.WebControls.DataGrid System.Web.UI.WebControls.DataList
Table 16-5 describes the BaseDataList class properties.
| PROPERTY | DESCRIPTION |
|---|---|
| CellPadding | This is the space between a cell and its border. Both get and set. |
| CellSpacing | This is the space between two cells. Both get and set. |
| DataKeyField | This is the key field in the data source. Both get and set. |
| DataKeys | This is the collection of data keys in the form of DataKeyCollection. Read only. |
| DataMember | This is the data member in a multimember data source. Both get and set. |
| DataSource | This is the data source. Both get and set. |
| GridLines | This is the border between the cells is displayed if true; otherwise false. Both get and set. |
| HorizontalAlign | This is the horizontal alignment of a data listing control within its container. |
Using the DataKeyCollection Class
The DataKeyCollection object represents a collection of the key fields in a data source. Both the DataList and DataGrid controls provide access to the key fields through their DataKeyField properties.
The Count property returns the number of items in the collection. The IsReadOnly property returns true if the items in the collection are read only. If they aren't read only, the value of this property returns false.
The IsSynchronized property returns true if the collection is thread safe; otherwise it returns false.
The Item property gets the item from the collection at the specified index, and the SyncRoot property returns the object used to synchronize access to the collection.
The DataKeyCollection class provides two methods: CopyTo and GetEnumerator. The CopyTo method copies the contents of a collection to an array, and the GetEnumerator method creates an IEnumerator-implemented object that contains all key fields in the collection.
Using the DataList Web Server Control
The DataList control represents a data-bound list control that displays items using templates. You can create a DataList control by editing an .aspx file or by using the VS .NET IDE.
Before discussing DataList in more detail, let's look at a simple example. Listing 16-12 binds and displays data in a DataList control from the Categories table of the Northwind database.
Listing 16-12: Data Binding in a DataList Control
 |
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <HTML> <BODY> <font color="#006699" size="4" face="verdana"> DataList Server Control Sample </font> <script language="VB" runat="server"> Sub Page_Load(sender As Object, e As EventArgs) Dim conn As SqlConnection Dim adapter As SqlDataAdapter dim connectionString = _ "Data Source=MCB;Initial Catalog=Northwind;user id=sa;password=;" conn = new SqlConnection(connectionString) conn.Open() dim sql = "SELECT * FROM Categories" adapter = new SqlDataAdapter(sql, conn) Dim ds As Dataset = new DataSet() adapter.Fill(ds) dtList.DataSource = ds dtList.DataBind() End Sub </script> <P> <ASP:DataList RepeatColumns="1" RepeatDirection="Horizontal" runat="server"> <HeaderTemplate> <FONT face="verdana" color="#cc3333" size="3"> <B>DataList Control Header</B></FONT> </HeaderTemplate> <FooterTemplate> <FONT face="verdana" color="#996600" size="1"> DataList Control footer </FONT> </FooterTemplate> <ItemTemplate> <FONT face="verdana" size="2"> <BR> <B>Category ID: </B> <%# DataBinder.Eval(Container.DataItem, "CategoryID") %> <BR> <B>Category Name: </B> <%# DataBinder.Eval(Container.DataItem, "CategoryName")%> <BR> <B>Description: </B> <%# DataBinder.Eval(Container.DataItem, "Description") %> <BR> <B>Picture: </B> <%# DataBinder.Eval(Container.DataItem, "Picture") %> <P> <DIV></DIV> </FONT> </ItemTemplate> </ASP:DataList> </P> </BODY> </HTML>
|
The output of Listing 16-12 looks like Figure 16-7.
Figure 16-7: Data binding in a DataList control
Setting the DataList Properties
The DataList control is pretty useful and provides members to format the control's contents. Before discussing how to format a DataList control's contents, let's look at the DataList control properties. From these properties, you'll have an idea what a DataList control offers. Table 16-6 describes the DataList class properties.
| PROPERTY | DESCRIPTION |
|---|---|
| AlternatingItemStyle | This represents the style of alternating items. Read only. |
| AlternatingItemTemplate | This represents the template for alternating items. Both get and set. |
| EditItemIndex | This represents the index number of the selected item. Both get and set. |
| EditItemStyle | This represents the style of the item being edited. Read only. |
| EditItemTemplate | This represents the template for the items being edited. Both get and set. |
| ExtractTemplateRows | This represents the whether rows are extracted and displayed. Both get and set. |
| FooterStyle | This returns the style of the footer. |
| FooterTemplate | This represents the template for the footer. Both get and set. |
| GridLines | This represents grid lines: horizontal, vertical, both, or none. |
| HeaderStyle | This represents the style of the header. Both get and set. |
| HeaderTemplate | This represents the template for the header. Both get and set. |
| Items | This returns the collection of DataListItem objects. |
| ItemStyle | This returns the style of items. |
| ItemTemplate | This represents the template for DataList items. Both get and set. |
| RepeatColumns | This represents the represents the number of columns to display in a DataList. Both get and set. If you want to display 5 columns in a DataList, set it to 5. |
| RepeatDirection | The determines whether the DataList control displays vertically or horizontally. |
| RepeatLayout | This represents if the control is displayed in a table or flow layout. |
| SelectedIndex | This represents the index of the selected item. Both get and set. |
| SelectedItem | This returns selected item. |
| SelectedItemStyle | This returns the style of the selected item. |
| SelectedItemTemplate | This represents the template for the selected item. |
| SeparatorStyle | This represents the style of the border. |
| SeparatorTemplate | This represents the template for the border. |
| ShowFooter | If true, this shows the footer; otherwise it hides the footer. Both get and set. |
| ShowHeader | If true, this shows the header; otherwise it hides the header. Both get and set. |
| Tip | You can set most of the control properties at design-time as well as at runtime. To create and set a DataList control at design-time, just drag a control from the Toolbox to a page and use the Properties window to set its properties. |
Setting Multiple Columns, Grid Lines, and Alternating Rows
In Listing 16-12, you filled data from the Categories table and that data was listed in a single column. What if you want to display more than one column in a row? You can do that by setting the control's RepeatColumns property. You can even set the direction in which the control displays. The following code sets a DataList control to display five columns in a row in a horizontal direction with grid lines:
<ASP:DataList RepeatColumns="5" RepeatDirection="Horizontal" GridLines="Both" runat="server">
You can set the style of the alternating rows by using AlternatingItemTemplate. Listing 16-13 sets the format of alternating items in the controls.
Listing 16-13: Setting a DataList Control's Properties
|
<AlternatingItemTemplate> <FONT face="verdana" color="green" size="2"> <BR> <B>Category ID: </B> <%# DataBinder.Eval(Container.DataItem, "CategoryID") %> <BR> <B>Category Name: </B> <%# DataBinder.Eval(Container.DataItem, "CategoryName")%> <BR> <B>Description: </B> <%# DataBinder.Eval(Container.DataItem, "Description") %> <BR> <B>Picture: </B> <%# DataBinder.Eval(Container.DataItem, "Picture") %> <P> <DIV></DIV> </FONT> </AlternatingItemTemplate>
|
The output of this program looks like Figure 16-8. As you can see from this figure, each row of the DataList control has five columns with grid lines and alternating styles.
Figure 16-8: Multiple columns in a DataList control
Setting the Style and Templates of a DataList Control
As you saw in Table 16-6, the DataList class provides many style properties for different portions of the control. These properties are AlternatingItemStyle, EditItemStyle, FooterItemStyle, HeaderItemStyle, ItemStyle, SelectedItemStyle, and SeparatorStyle. Using these properties, you can set the style of the respective portion of the control.
In addition to the styles, you can hide or show the DataList control headers and footers by using the ShowHeader and ShowFooter properties. You can even set the layout of the DataList control by using the RepeatLayout property, which takes a value of the RepeatLayout enumeration, which has two values: Table and Flow. The Table layout is responsible for displaying items in a table. The Flow layout is responsible for displaying items without a table.
The DataList control supports many templates, even more than the DataGrid control. The templates supported by the DataList control are AlternatingItemTemplate, EditItemTemplate, FooterTemplate, HeaderTemplate, ItemTemplate, SelectedItemTemplate, and SeparatorTemplate. Using these templates you can set or get the format of different portions of the DataList.
| Note | See the "Understanding Web Server Control Templates" section for more details about templates. |
Listing 16-14 sets the styles and templates of a DataList control and its portions manually. This code shows you how you can use templates. You can give them whatever format you want.
Listing 16-14: Setting a DataList Control's Styles
|
<asp:DataList id=DataList1 runat="server" GridLines="Both" DataSource="<%# DataSet11 %>" DataKeyField="CategoryID" DataMember="Categories" RepeatDirection="Horizontal" RepeatColumns="5" Font-Size="X-Small" Font-Names="Verdana" ForeColor="Navy" BackColor="LightGray" BorderColor="Black"> <SelectedItemStyle ForeColor="#FF0033" BackColor="#CCCC99"> </SelectedItemStyle> <EditItemStyle Font-Size="10pt" Font-Names="Verdana" Font-Bold="True" BackColor="#FFE0C0"> </EditItemStyle> <AlternatingItemStyle ForeColor="#006600" BackColor="Silver"> </AlternatingItemStyle> <ItemStyle ForeColor="Black" BackColor="GhostWhite"> </ItemStyle> <FooterStyle Font-Size="8pt" Font-Names="Tahoma" ForeColor="White" BackColor="Teal"> </FooterStyle> <HeaderStyle Font-Size="X-Small" Font-Names="Verdana" Font-Bold="True" ForeColor="White" BackColor="#003399"> </HeaderStyle> </asp:DataList>
|
Using DataKeys
The DataKeys property of the BaseDataList class returns a collection of primary keys available in a data source. The DataKeys property accesses the key values of each row in a data listing control. The key field basically represents the primary key column value in a data source, which retrieves the data of other columns in a row based on the primary key. The DataKeyField property assigns the primary key of a data source.
What's the use of DataKeyField after setting its value to a primary key column of a data source? Well, let's say you want to retrieve, delete, and update a row in a data source bound to a DataList control. The only way to track down a row is through DataKeyField. DataKeyField is used in a handler for an event, such as ItemCommand or DeleteCommand, as part of an update query string to revise a specific record in the data source. So, DataFieldKey helps the update query string identify the appropriate row in a data source to modify.
The following code sets the DataKeyField property to the CategoryID column:
<ASP:DataList RepeatColumns="5" RepeatDirection="Horizontal" GridLines="Both" DataKeyField="CategoryID" ........... >
Understanding DataList Events
The DataList control supports events and provides method to raise events when items are being bound, edited, deleted, and created.
The ItemCreated event occurs when an item is created in the control. The event handler of the ItemCreated event receives an argument of type DataListItemEventArgs, which contains the data related to the event. You can use the OnItemCreated method to raise this event.
The ItemDataBound event occurs when an item is data bound to the control. You use this event to access the data item before it'll be displayed in the control. You can use the OnItemDataBound method to raise the ItemDataBound event.
A DataList control can have Update, Delete, and Cancel buttons, which can be used to update, delete, and cancel data in a DataList control, respectively. The ItemCommand event occurs when any button is clicked.
The CancelCommand event occurs when the Cancel button is clicked in a DataList control. You can use the OnCancelCommand method to raise this event.
The EditCommand event occurs when the Edit button of DataList is clicked. You can use the OnEditCommand method to raise this event.
The DeleteCommand event occurs when the Delete button of DataList is clicked. You can use the OnDeleteCommand method to raise this event.
The UpdateCommand event occurs when the Edit button of DataList is clicked. You can use the OnUpdateCommand method to raise this event.
The event handler of the ItemCreated, CancelCommand, EditCommand, DeleteCommand , and UpdateCommand events receive an argument of type DataListCommandEventArgs, which contains data related to this event. DataListCommandEventArgs provides information, which includes the argument for the command, the name of the command, the source of the command, and the item containing the command source in the control.
Listing 16-15 adds the Update, Delete, Cancel, and Edit command event handlers by using the OnUpdateCommand, OnDeleteCommand, OnCancelCommand, and OnEditCommand methods.
Listing 16-15: Adding Update, Delete, Cancel, and Edit Command Handlers
|
<asp:datalist runat="server" CellPadding="4" GridLines="Horizontal" RepeatColumns="5" OnUpdateCommand="UpdateCommandHandler" OnDeleteCommand="DeleteCommandHandler" OnCancelCommand="CancelCommandHandler" OnEditCommand="EditCommandHandler" DataKeyField="CategoryID" BackColor="WhiteSmoke" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" ForeColor="Black" Font-Names="Verdana" Font-Size="8pt">
|
Listing 16-16 shows the code written on the event handlers for these commands.
Listing 16-16: The Code for the Update, Delete, Cancel, and Edit Command Handlers
|
Sub EditCommandHandler(sender as object, _ args as DataListCommandEventArgs) End Sub Sub CancelCommandHandler(sender as object, _ args as DataListCommandEventArgs) End Sub Sub DeleteCommandHandler(sender as object, _ args as DataListCommandEventArgs) End Sub Sub UpdateCommandHandler(sender as object, _ args as DataListCommandEventArgs) End Sub
|
Editing, Updating, and Deleting Through a DataList Control
To see the real-world uses of DataKeys, templates, and DataList event handlers of a DataList control, let's write a useful editable DataList application. This application will display data from the Categories table of the Northwind database. The application will also allow you to edit, cancel, and delete data through the DataList control itself.
Actually writing an editable DataList control is little complex. So, you'll create a DataList by directly editing the code in an .aspx file. Once the DataList control is ready, you'll fill data to it and add event handlers for the Edit, Update, Cancel, and Delete buttons.
First, you create a DataList control and set its properties. You also make calls to the OnUpdateCommand, OnDeleteCommand, OnCancelCommand, and OnEditCommand methods, which raise the update command, delete command, cancel command, and edit command, respectively. The DataList control code is as follows:
<asp:datalist runat="server" CellPadding="4" RepeatDirection="Horizontal" GridLines="Horizontal" RepeatColumns="4" OnUpdateCommand="UpdateCommandHandler" OnDeleteCommand="DeleteCommandHandler" OnCancelCommand="CancelCommandHandler" OnEditCommand="EditCommandHandler" DataKeyField="CategoryID" BackColor="WhiteSmoke" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" ForeColor="Black" Font-Names="Verdana" Font-Size="8pt">
Second, format and set a layout for the control. For this example, set the header, footer, selected item, item, alternating item, and edit item templates. On ItemTemplate and AlternatingItemTemplate, add a LinkButton control with a CommandName of Edit, which means it'll be responsible for raising the EditCommand event. The following code adds an Edit link button to the items of a DataList control:
<asp:LinkButton Text="Edit" CommandName="Edit" RunAt="server"> </asp:LinkButton>
The code for ItemTemplate and AlternatingItemTemplate is as follows:
<ItemTemplate> <b>Category ID:</b> <%# Container.DataItem("CategoryID") %> <br> <b>Category Name:</b> <%# Container.DataItem("CategoryName") %> <br> <b>Description:</b> <%# Container.DataItem("Description") %> <br> <asp:LinkButton Text="Edit" CommandName="Edit" RunAt="server"> </asp:LinkButton> </ItemTemplate> <AlternatingItemTemplate> <font face="verdana" color="green" size="1"><b>Category ID:</b> <%# Container.DataItem("CategoryID") %> <br> <b>Category Name:</b> <%# Container.DataItem("CategoryName") %> <br> <b>Description:</b> <%# Container.DataItem("Description") %> <br> <asp:LinkButton Text="Edit" CommandName="Edit" RunAt="server" NAME="Linkbutton1"> </asp:LinkButton> </font> </AlternatingItemTemplate> As you can see from the previous code, you simply display the contents of the Categories table and, in the end, add the Edit link button. You also set the header and footer styles of the control.
EditItemTemplate is another template that plays a major role in creating an editable DataList control. This is the template where you set the types of control being used when a DataList control is in edit mode. When a DataList control is in edit mode, you want to allow users to edit the CategoryName and Description columns of the Categories table. To do so, you need to create TextBox controls so the user can edit new values. Besides the TextBox controls, you need to allow options to save changes, cancel changes, and delete an item from the DataList. The following code snippet adds TextBox controls and link buttons to the edited item of the DataList control:
<EditItemTemplate> <b>Category Name:</b> <br> <asp:TextBox Text='<%# Container.DataItem("CategoryName") %>' Runat="server" Font-Names="Verdana" Font-Size="8pt" BorderStyle="Groove" /> <br> <b>Description</b> <br> <asp:TextBox Text='<%# Container.DataItem("Description") %>' Runat="server" Font-Names="Verdana" Font-Size="8pt" BorderStyle="Groove" /> <br> <asp:LinkButton Text="Cancel" CommandName="cancel" Runat="server" /> <asp:LinkButton Text="Delete" CommandName="delete" Runat="server" /> <asp:LinkButton Text="Update" CommandName="update" Runat="server" /> </EditItemTemplate> The next step is to load data to the control. You create a method called FillDataList, which binds data to the DataList control based on the items specified in ItemTemplate and AlternatingItemTemplate, as shown earlier.
You call the FillDataList method on the Page_Load event, as shown in the following code:
Sub Page_Load If not IsPostBack FillDataList() End If End Sub
Now you write the EditCommand event handler, where you set EditItemIndex of the DataList control to the ItemIndex of DataListCommandEventArgs.Item returned by the EditCommand handler. After setting EditItemIndex of DataList, you refill data to the control by calling the FillDataList method, as shown in the following code:
Sub EditCommandHandler(sender as object, _ args as DataListCommandEventArgs) dtList.EditItemIndex = args.Item.ItemIndex FillDataList() End Sub
On the CancelCommand event handler, you simply set EditItemIndex of DataList to -1 and rebind the DataList control:
Sub CancelCommandHandler(sender as object, _ args as DataListCommandEventArgs) dtList.EditItemIndex = -1 FillDataList() End Sub
Writing code for the UpdateCommand and DeleteCommand handlers is little tricky. You probably can guess that on the UpdateCommand handler, you need to find out the index of the current item by using DataList.DataKeys, which returns the PrimaryKey column's value. Once you know the PrimaryKey, you can easily find out which record you want to update or delete. You can simply construct a SqlCommand and execute it.
The DeleteCommand event handler is as follows:
dim strId as string = dtList.DataKeys(args.Item.ItemIndex) dim sql = "DELETE Categories WHERE CategoryID=@catID" dim cmd as SqlCommand = new SqlCommand(sql, conn) cmd.Parameters.Add("@catID", strId) conn.Open() cmd.ExecuteNonQuery() conn.Close() dtList.EditItemIndex = -1 FillDataList() As you can see from the previous code, you simply get the CategoryID value for the edited item of the DataList control. Once you have the CategoryID value, you delete all records that match the CategoryID by constructing and executing a DELETE statement.
On the UpdateCommand event handler, you need to get the values of the CategoryName and Description columns and then construct and execute an UPDATE statement, as shown in the following code:
dim strId as string = dtList.DataKeys(args.Item.ItemIndex) dim strCatName as TextBox = args.Item.FindControl("catNameTextBox") dim strDesc as TextBox = args.Item.FindControl("desTextBox") dim sql = "UPDATE Categories SET CategoryName=@catName, " & _ " Description=@desc WHERE CategoryID=@catID" dim cmd as SqlCommand = new SqlCommand(sql, conn) cmd.Parameters.Add("@catID", strId) cmd.Parameters.Add("@catName", strCatName.Text.ToString()) cmd.Parameters.Add("@desc", strDesc.Text.ToString()) conn.Open() cmd.ExecuteNonQuery() conn.Close() dtList.EditItemIndex = -1 FillDataList() | Note | You also set the EditItemIndex of DataList to -1 and call the FillDataList method to refill the DataList control. |
Finally, Listing 16-17 shows the complete code.
Listing 16-17: Editable DataList Control
|
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <HTML> <HEAD> <title>Editable DataList</title> <script runat="server"> Sub Page_Load If not IsPostBack FillDataList() End If End Sub Sub FillDataList() Dim conn As SqlConnection Dim adapter As SqlDataAdapter dim connectionString = _ "Data Source=MCB;Initial Catalog=Northwind;user id=sa;password=;" conn = new SqlConnection(connectionString) conn.Open() dim sql = "SELECT * FROM Categories" dim cmd as SqlCommand = new SqlCommand(sql, conn) dim reader as SqlDataReader = cmd.ExecuteReader() dtList.DataSource = reader dtList.DataBind() reader.Close() conn.Close() End Sub Sub EditCommandHandler(sender as object, _ args as DataListCommandEventArgs) ' code here End Sub Sub CancelCommandHandler(sender as object, _ args as DataListCommandEventArgs) ' code here End Sub Sub DeleteCommandHandler(sender as object, _ args as DataListCommandEventArgs) ' code here End Sub Sub UpdateCommandHandler(sender as object, _ args as DataListCommandEventArgs) ' code here End Sub </script> </HEAD> <body> <form runat="server"> <asp:datalist runat="server" CellPadding="4" RepeatDirection="Horizontal" GridLines="Horizontal" RepeatColumns="4" OnUpdateCommand="UpdateCommandHandler" OnDeleteCommand="DeleteCommandHandler" OnCancelCommand="CancelCommandHandler" OnEditCommand="EditCommandHandler" DataKeyField="CategoryID" BackColor="WhiteSmoke" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" ForeColor="Black" Font-Names="Verdana" Font-Size="8pt"> <SelectedItemStyle Font-Bold="True" ForeColor="White" BackColor="#CC3333"> </SelectedItemStyle> <HeaderTemplate> <FONT face="verdana" color="#FFFFFF" size="3"> <B>Editable DataList</B></FONT> </HeaderTemplate> <EditItemStyle BackColor="#FFC080"></EditItemStyle> <FooterTemplate> <FONT face="verdana" color="#996600" size="1"> DataList Control footer </FONT> </FooterTemplate> <ItemTemplate> ' item template code here </ItemTemplate> <AlternatingItemTemplate> ' alternative item template code here </AlternatingItemTemplate> <FooterStyle ForeColor="Black" BackColor="#C0FFC0"> </FooterStyle> <HeaderStyle Font-Bold="True" ForeColor="#C04000" BackColor="#FF8000"> </HeaderStyle> <EditItemTemplate> ' code here </EditItemTemplate> </asp:datalist></form> </body> </HTML>
|
If you run the application, the output looks like Figure 16-9.
Figure 16-9: Editable DataList control
Now if you click an Edit link button, the item changes to editable form, which looks like Figure 16-10.
Figure 16-10: A DataList control in editable mode
The Update link button saves the changes, the Cancel link button ignores the changes and keeps the original values, and the Delete link button deletes the item.
Using the VS .NET IDE to Set a DataList's Properties
The VS .NET IDE provides limited support to the DataList control. There are two common ways to set a DataList control's properties. First, you can use the Properties windows; second, you can use the Property Builder. You call the Property Builder by right-clicking and selecting Property Builder or by clicking the Property Builder link at the bottom of the Properties window, as shown in Figure 16-11.
Figure 16-11: The Properties window of a DataList control
The first page of the Property Builder is the General page. On this page, you can set DataKeyField and other properties of the control. The Show Header and Show Footer check boxes indicate whether the header and footer will be displayed in the control. In the Repeat Layout section, you can set the Columns, Direction, and Layout properties.
The second page of the Property Builder is the Format page. On this page, you can set the format of the control, header, footer, items, and separators. From this page, you can set the properties such as background color, foreground colors, font, and alignment of each portion of the DataList control. You can even set the format of items, alternating items, selecting items, and edit mode items from this page.
The third page of the Property Builder is the Border Format page, which allows you to set the format of the DataList border. The properties on this page are cell padding, cell spacing, grid lines, border color, and border width.
Using the DataGrid Web Server Control
In Chapter 14, you learn about some DataGrid functionality. It discussed using data binding, setting a control's properties and styles using the Properties window as well as the Property Builder, writing event handlers, and implementing paging in a DataGrid control.
The DataGrid control's functionality doesn't end there; it provides much more than that. In this section, you'll see the rest of the functionality. You'll work in the HTML View because it's easy to understand and modify.
| Caution | If you skipped Chapter 14, you should go back and read the "DataGrid and DataList Controls" section. |
The following code creates a DataGrid control programmatically:
<ASP:DataGrid runat="server"> </ASP:DataGrid>
Similar to the DataList control, the DataGrid control provides a DataSource property, which binds data to the control. Once you have set the DataSource property, you call the DataBind method. Listing 16-18 reads data from the Categories table of Northwind and binds the filled DataSet to the DataGrid control.
Listing 16-18: Data Binding in a DataGrid Control
|
<%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Data" %> <HTML> <script language="VB" runat="server"> Sub Page_Load(sender As Object, e As EventArgs) If Not IsPostBack Then FillDataGrid() End If End SUb Sub FillDataGrid() Dim conn As SqlConnection Dim adapter As SqlDataAdapter dim connectionString = _ "Data Source=MCB;Initial Catalog=Northwind;" & _ "user id=sa;password=;" conn = new SqlConnection(connectionString) conn.Open() dim sql = "SELECT * FROM Categories" adapter = new SqlDataAdapter(sql, conn) Dim ds As Dataset = new DataSet() adapter.Fill(ds) dtGrid.DataSource = ds dtGrid.DataBind() End Sub </script> <body> <ASP:DataGrid runat="server"> </ASP:DataGrid> </body> </HTML>
|
The output of Listing 16-18 generates a page that looks like Figure 16-12.
Figure 16-12: Data binding in a DataGrid control
Setting DataGrid Styles
Besides setting a DataGrid control's properties at design-time, you can always set a DataGrid control's properties (including style properties) from the .aspx page by simply editing the HTML code using the VS .NET IDE or a text editor.
The DataGrid control provides properties that can set the style of its various parts. The DataGrid style properties are AlternatingItemStyle, HeaderStyle, FooterStyle, EditItemStyle, ItemStyle, and SelectedItemStyle, which represent styles for alternating items, headers, footers, edit items, items, and selected items, respectively.
Each of these style properties is a type of the TableItemStyle class, which inherits from the Style class. The TableItemStyle class represents the style of properties for an element of a control. Table 16-7 describes the TableItemStyle class properties.
| PROPERTY | DESCRIPTION |
|---|---|
| BackColor | Background color of the item. |
| BorderColor | Border color of the item. |
| BorderStyle | Border style of the item. It can be Dashed, Dotted, Double, Groove, Inset, None, Notset, Outset, Ridge, or Solid. |
| BorderWidth | Width of the border. |
| CssStyle | Cascading style of the item. |
| Font | FontInfo, which defines how the font will be displayed. |
| ForeColor | Foreground color of the item. |
| Height | Height of the item. |
| HorizontalAlign | Horizontal alignment of the item. |
| VerticalAlign | Vertical alignment of the item. |
| Width | Width of the item. |
| Wrap | If true, items will be wrapped; otherwise not. |
Listing 16-19 adds some colors to the DataGrid control by setting its ItemStyle, HeaderStyle, FooterStyle, AlternatingItemStyle, and EditItemStyle properties.
Listing 16-19: Setting a DataGrid Control's Style Properties
|
<ASP:DataGrid HeaderStyle-BackColor=#003366 HeaderStyle-Font-Bold=True HeaderStyle-Font-Name=verdana HeaderStyle-ForeColor=white HeaderStyle-Font-Size=10 ItemStyle-BackColor=black ItemStyle-Font-Name=verdana ItemStyle-Font-Size=8 ItemStyle-ForeColor=#ffcc33 AlternatingItemStyle-BackColor=LightSteelBlue AlternatingItemStyle-ForeColor=black FooterStyle-BackColor=#ffff99 FooterStyle-Font-Name=tahoma FooterStyle-Font-Size=6 FooterStyle-Font-Italic=True EditItemStyle-BackColor=red EditItemStyle-Font-Size=8 EditItemStyle-ForeColor=#ccffff ShowHeader=True ShowFooter=True runat="server">
|
Figure 16-13 shows the new DataGrid control.
Figure 16-13: The DataGrid control with the new styles
Understanding a DataGrid's Columns
The DataGrid control provides control and manages a DataGrid's columns in different ways. You can manage a DataGrid control's columns at design-time as well as at runtime. The Columns property of the DataGrid control is the gateway to accessing and managing columns in a DataGrid at runtime. The Columns property represents a collection of DataGridColumn objects.
Using the AutoGenerateColumns Property
So far you've seen that if you bind a DataGrid control to a data source, the control displays all the columns available in a data source in the same order as they are defined in the data source. The AutoGenerateColumns property controls this behavior of the DataGrid control. If you want to change the order of the columns, or you want to customize a DataGrid control, you must set the AutoGenerateColumns property to false.
Actually, setting AutoGenerateColumns property to true automatically binds columns for all fields in the data source. Each field of the data source is rendered as a column of DataGrid. The BoundColumn class represents a bound column of a DataGrid control.
Using the DataGridColumn Class
The DataGridColumn class represents a column of a DataGrid control. A DataGrid control can have different kind of columns such as bound columns, button columns, edit command columns, hyperlink columns, and button columns. The BoundColumn, ButtonColumn, EditCommandColumn, HyperlinkColumn, and TemplateColumn classes represent the bound columns, button, edit command, hyperlink, and template column, respectively. The DataGridColumn class is the base class for all column classes.
The DataGridColumn class provides properties that can format a DataGrid column's header and footers. These properties are FooterStyle, FooterText, HeaderImageUrl, HeaderStyle, and HeaderText. The ItemStyle property gets the style of a column's items. The SortExpression property represents the name of the field or expressions, which are used in the OnSortCommand method when a column sorts its items.
Customizing and Rearranging a DataGrid's Columns
As you saw earlier, the DataGrid control generates a column for each column of a table in the data source in the same order as they're defined in the data source. But what if you don't want to display all the columns of a data source? One way to do this is to use a SELECT statement with the columns you want. There may be occasions when you want to control the column's display at runtime. For example, you may want to change the default order of the columns.
To allow the custom view and selection of columns, the first thing you need to do is disable the AutoGenerateColumns property:
AutoGenerateColumns=False
Now, because the AutoGenerateColumns property is false, you have to generate all the columns you want to display in a DataGrid control. You use BoundColumn for adding data-bound columns to the DataGrid control. You can use the Columns property of DataGrid and bind the ASP:BoundColumn.DataField property to the data source. Listing 16-20 displays the Description, CategoryName, and CategoryID columns of the Categories table in the same order.
Listing 16-20: Rearranging a DataGrid Column's Sequence
|
<ASP:DataGrid AutoGenerateColumns=False EnableViewState=False runat="server"> <Columns> <ASP:BoundColumn DataField="Description" /> <ASP:BoundColumn DataField="CategoryName" /> <ASP:BoundColumn DataField="CategoryID" /> </Columns> </ASP:DataGrid>
|
The BoundColumn property provides the DataFormatString property, which can format the data of a column. You can even set the format of the BoundColumn header and footers. For this example, set the header and footer text for the columns by using the HeaderText and FooterText properties of BoundColumn. Listing 16-21 shows the final code.
Listing 16-21: Formatting BoundColumn's Header and Footers
|
<Columns> <ASP:BoundColumn DataField="Description" HeaderText="Description" FooterText="Category Description"/> <ASP:BoundColumn DataField="CategoryName" HeaderText="Category Name" FooterText="Name of Category"/> <ASP:BoundColumn DataField="CategoryID" HeaderText="ID" FooterText="Category ID"/> </Columns>
|
Figure 16-14 shows the updated DataGrid control.
Figure 16-14: DataGrid with BoundColumns
Calling DataGrid Events
The DataGrid events play a major role in DataGrid customization. In this section, you'll learn about the DataGrid events, which you'll use in later examples. Table 16-8 describes the DataGrid control events.
| EVENT | DESCRIPTION |
|---|---|
| CancelCommand | The Cancel button tells a DataGrid control not to save the changes made to the items. This event occurs when a Cancel button in a DataGrid is clicked. The OnCancelCommand method raises this event. The OnCancelCommand allows you to write a custom event handler for the CancelCommand, where you can get the data about a command. The CancelCommand event handler provides an argument of DataGridCommandArgs, which gets the data associated with a command. |
| DeleteCommand | The Delete button deletes an item of a DataGrid control. This event occurs when a Delete button is clicked. The OnDeleteCommand method raises this event. The OnDeleteCommand allows you to write a custom event handler for the DeleteCommand, where you can get the data about a command. The DeleteCommand event handler provides an argument of DataGridCommandArgs, which gets the data associated with a command. |
| EditCommand | The Edit button edits a DataGrid control's items. This event occurs when the Edit button is clicked. The OnEditCommand method raises this event. The OnEditCommand allows you to write a custom event handler for the EditCommand, where you can get the data about a command. The EditCommand event handler provides an argument of DataGridCommandArgs, which gets the data associated with a command. |
| UpdateCommand | The Update button saves the changes made to a DataGrid. This event occurs when the Update button is clicked. The OnUpdateCommand method raises this event. The OnUpdateCommand allows you to write a custom event handler for the UpdateCommand, where you can get the data about a command. The UpdateCommand event handler provides an argument of DataGridCommandArgs, which gets the data associated with a command. |
| ItemCommand | This event occurs when any button on a DataGrid is clicked. The OnItemCommand method raises this event. Similar to previous event handler, the OnItemCommand event also provides an argument of DataGridCommandArgs, which gets the data associated with a command. |
| ItemCreated | This event occurs when the items of the control are created. The OnItemCreated method raises this event. This method also provides an argument of DataGridCommandArgs, which gets the data associated with a command. |
| ItemDataBound | This event occurs when an item is bound to a DataGrid control. The OnItemDataBound method raises this event. The ItemDataBound event handler provides an argument of DataGridItemEventArgs, which contains data related to this event. |
| PageIndexChanged | This event occurs when a paging selection of a DataGrid control is clicked. Chapter 14 covers this event in it "Paging in the DataGrid Control" section. |
| SortCommand | The DataGrid control has default built-in functionality for sorting. By writing a few lines of code, you can add the sorting functionality to a DataGrid control. First of all, you need to set the AllowSorting property of the DataGrid to true. Second of all, you need to write a SortCommand event handler. The SortCommand event handler provides an argument of DataGridSortCommandEventArgs, which returns data related to the sort command such as the column being used to sort the data. |
Using the DataGridCommandEventArgs Class
This class provides data for the CancelCommand, DeleteCommand, EditCommand, ItemComand, and UpdateCommand events. The event handler of these events takes an argument of DataGridCommandEventArgs, which can be used to get the data about a command.
The CommandArgument property returns an argument for the command. The CommandName property returns the name of the command, which can be set by using a column's CommandName property. The CommandSource property returns the source of the command, and the Item property returns the item of the DataGrid that contains the command.
Using a DataGrid Control with Button Columns
The ButtonColumn class represents a button column. A button column can have two types: a push button or a link button. The ButtonType property of ButtonColumn represents the type of button.
The CommandName property is a string that represents the command to perform when the button is clicked. It's used to handle the button click event handler. The DataGridCommandEventArgs's CommandName property returns the CommandName of a column.
The DataTextField property binds a data source field to a button column, and DataTextFormatString represents the format of the column, which will be displayed in a DataGrid. The Text property represents the text of a column.
The following code creates two button columns:
<Columns> <ASP:ButtonColumn CommandName="Yes" ButtonType=LinkButton Text="Select"> </ASP:ButtonColumn> <ASP:ButtonColumn CommandName="No" ButtonType=LinkButton Text="UnSelect"> </ASP:ButtonColumn> </Columns>
The next step is to handle the button click event. The following code adds an ItemCommand event handler to a DataGrid control:
<ASP:DataGrid OnItemCommand="dtGridItem" runat="server">
Now you'll change the format of the row clicked in a DataGrid control. Listing 16-22 shows the event handler of the button click. As you can see from this listing, you find out what command name was clicked and set the row's format accordingly.
Listing 16-22: The ButtonColumn Click Event Handler
|
Sub dtGridItem(obj as Object, args as DataGridCommandEventArgs) If (args.CommandName = "Yes") Then args.Item.BackColor = System.Drawing.Color.Gray args.Item.ForeColor = System.Drawing.Color.White args.Item.Font.Name = "tahoma" Else args.Item.BackColor = System.Drawing.Color.Black args.Item.ForeColor = System.Drawing.Color.LightSteelBlue args.Item.Font.Name = "verdana" End If End Sub
|
This DataGrid control looks like Figure 16-15 with the button columns. When you click the Select or UnSelect link button, you see the changes in the selected row.
Figure 16-15: A DataGrid control with button columns
Listing 16-23 shows the complete code.
Listing 16-23: DataGrid with Button Columns
|
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <script language="VB" runat="server"> Sub Page_Load(sender As Object, e As EventArgs) If Not IsPostBack Then FillDataGrid() End If End SUb Sub FillDataGrid() Dim conn As SqlConnection Dim adapter As SqlDataAdapter dim connectionString = _ "Data Source=MCB;Initial Catalog=Northwind;" & _ "user id=sa;password=;" conn = new SqlConnection(connectionString) conn.Open() dim sql = "SELECT * FROM Categories" adapter = new SqlDataAdapter(sql, conn) Dim ds As Dataset = new DataSet() adapter.Fill(ds) dtGrid.DataSource = ds dtGrid.DataBind() End Sub Sub dtGridItem(obj as Object, args as DataGridCommandEventArgs) If (args.CommandName = "Yes") Then args.Item.BackColor = System.Drawing.Color.Gray args.Item.ForeColor = System.Drawing.Color.White args.Item.Font.Name = "tahoma" Else args.Item.BackColor = System.Drawing.Color.Black args.Item.ForeColor = System.Drawing.Color.LightSteelBlue args.Item.Font.Name = "verdana" End If End Sub </script> <body> <form runat="server"> <ASP:DataGrid HeaderStyle-BackColor="#003366" HeaderStyle-Font-Bold="True" HeaderStyle-Font-Name="verdana" HeaderStyle-ForeColor="white" HeaderStyle-Font-Size="10" ItemStyle-BackColor="black" ItemStyle-Font-Name="verdana" ItemStyle-Font-Size="8" ItemStyle-ForeColor="#ffcc33" AlternatingItemStyle-BackColor="LightSteelBlue" AlternatingItemStyle-ForeColor="black" FooterStyle-BackColor="#ffff99" FooterStyle-Font-Name="tahoma" FooterStyle-Font-Size="6" FooterStyle-Font-Italic="True" EditItemStyle-BackColor="red" EditItemStyle-Font-Size="8" EditItemStyle-ForeColor="#ccffff" ShowHeader="True" ShowFooter="True" OnItemCommand="dtGridItem" runat="server"> <Columns> <ASP:ButtonColumn CommandName="Yes" ButtonType=LinkButton Text="Select"> </ASP:ButtonColumn> <ASP:ButtonColumn CommandName="No" ButtonType=LinkButton Text="UnSelect"> </ASP:ButtonColumn> </Columns> </ASP:DataGrid> </form> </body> </html>
|
Using a DataGrid Control with Template Columns
Templates are one of the ways to customize DataGrid columns. The TemplateColumn class represents a template column in a DataGrid control. A template column allows you to customize a column's layout.
Similar to other column classes, this class also inherits from the DataGridColumn class. Besides the functionality provided by DataGridColumn, this class provides properties to set the layout of header, footer, item, and edit items of the control. The HeaderTemplate, FooterTemplate.ItemTemplate, and EditItemTemplate properties provide the custom appearance of the header, footer, items, and items that are being edited (respectively).
For example, Listing 16-24 creates a TemplateColumn and sets its ItemTemplate, HeaderTemplate, and FooterTemplates properties.
Listing 16-24: Using Template Columns
|
<Columns> <ASP:TemplateColumn> <ItemTemplate> <FONT face="verdana" size="2"> <BR> <B>Category ID: </B> <%# DataBinder.Eval(Container.DataItem, "CategoryID") %> <BR> <B>Category Name: </B> <%# DataBinder.Eval(Container.DataItem, "CategoryName")%> <BR> <B>Description: </B> <%# DataBinder.Eval(Container.DataItem, "Description") %> <BR> </FONT> </ItemTemplate> <HeaderTemplate> <font face="verdana" size="3"> DataGrid with Template Columns </font> </HeaderTemplate> <FooterTemplate> <font face="verdana" size="2">DataGrid Footer </font> </FooterTemplate> </ASP:TemplateColumn> </Columns>
|
Figure 16-16 shows the result of using template columns.
Figure 16-16: Using template columns
Listing 16-25 shows the complete code.
Listing 16-25: Using Template Columns in a DataGrid Control
|
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <script language="VB" runat="server"> Sub Page_Load(sender As Object, e As EventArgs) If Not IsPostBack Then FillDataGrid() End If End SUb Sub FillDataGrid() Dim conn As SqlConnection Dim adapter As SqlDataAdapter dim connectionString = _ "Data Source=MCB;Initial Catalog=Northwind;" & _ "user id=sa;password=;" conn = new SqlConnection(connectionString) conn.Open() dim sql = "SELECT * FROM Categories" adapter = new SqlDataAdapter(sql, conn) Dim ds As Dataset = new DataSet() adapter.Fill(ds) dtGrid.DataSource = ds dtGrid.DataBind() End Sub </script> <body> <form runat="server"> <ASP:DataGrid HeaderStyle-BackColor="#003366" HeaderStyle-Font-Bold="True" HeaderStyle-Font-Name="verdana" HeaderStyle-ForeColor="white" HeaderStyle-Font-Size="10" ItemStyle-BackColor="black" ItemStyle-Font-Name="verdana" ItemStyle-Font-Size="8" ItemStyle-ForeColor="#ffcc33" AlternatingItemStyle-BackColor="LightSteelBlue" AlternatingItemStyle-ForeColor="black" FooterStyle-BackColor="#ffff99" FooterStyle-Font-Name="tahoma" FooterStyle-Font-Size="6" FooterStyle-Font-Italic="True" EditItemStyle-BackColor="red" EditItemStyle-Font-Size="8" EditItemStyle-ForeColor="#ccffff" ShowHeader="True" ShowFooter="True" AutoGenerateColumns="False" runat="server"> <Columns> <ASP:TemplateColumn> <ItemTemplate> <FONT face="verdana" size="2"> <BR> <B>Category ID: </B> <%# DataBinder.Eval(Container.DataItem, "CategoryID") %> <BR> <B>Category Name: </B> <%# DataBinder.Eval(Container.DataItem, "CategoryName")%> <BR> <B>Description: </B> <%# DataBinder.Eval(Container.DataItem, "Description") %> <BR> </FONT> </ItemTemplate> <HeaderTemplate> <font face="verdana" size="3"> DataGrid with Template Columns </font> </HeaderTemplate> <FooterTemplate> <font face="verdana" size="2">DataGrid Footer </font> </FooterTemplate> </ASP:TemplateColumn> </Columns> </ASP:DataGrid> </form> </body> </html>
|
Creating an Editable DataGrid
Writing an editable DataGrid is one more useful feature of a DataGrid control. You allow users to add, delete, edit, and save changes through a DataGrid control. Adding data to a DataGrid is pretty simple; you don't need to use any special code. You simply use some TextBox controls to get input from the user, construct a SQL statement, execute the SQL statement, and refill the DataGrid control. When you need to delete some data, you can simply find out the primary key of the row you want to delete, construct a SQL statement, execute the statement, and refill the DataGrid.
Editing, in other words, is no more difficult than adding and deleting the data. Unlike the Windows Forms DataGrid control, the ASP.NET DataGrid control doesn't have an edit mode. To make an ASP.NET DataGrid control editable, you need to add TextBox controls and place them as the DataGrid items. Once the user is done editing, you need to provide a mechanism to read the data from the TextBox controls and then construct an UPDATE SQL statement, execute it, and refill the DataGrid with new data.
So, the first thing you need to do is to provide some buttons for the DataGrid control, which can set a DataGrid in editable mode. You can do this with the help of EditCommand columns. The EditCommand column adds an Edit button to the DataGrid control. When you click the Edit button, it changes the control's current cells into TextBox controls and provides Update and Cancel buttons.
The EditCommandColumn class represents an EditCommand column. This class provides properties including ButtonType, CancelText, EditText, and UpdateText. The ButtonType property represents whether the buttons are hyperlink buttons or push buttons. The CancelText, EditText, and UpdateText properties represent the text of the Cancel, Edit, and Update buttons. For example, the following code sets the EditCommandColumn properties:
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Save" CancelText="Cancel" EditText="Edit"> <ItemStyle Font-Names="verdana" ForeColor="White" BackColor="Red"> </ItemStyle> </asp:EditCommandColumn>
The DataGrid control with an EditCommandColumn looks like Figure 16-17.
Figure 16-17: DataGrid with EditCommandColumn
If you click the Edit button, the current row of the DataGrid will have TextBox controls and the Edit button will be replaced with Save and Cancel buttons, as shown in Figure 16-18.
Figure 16-18: DataGrid with Save and Cancel options
| Note | You can change the text of the Update and Cancel buttons by setting the EditText, UpdateText, and CancelText properties. |
After setting the EditCommandColumn properties, you need to write the button click event handlers. The OnCancelCommand, OnUpdateCommand, and OnEditCommand methods of the DataGrid control are responsible for raising the Cancel, Update, and Edit button click events, respectively. The following code adds event handlers for these commands:
OnCancelCommand="dtGridCancel" OnUpdateCommand="dtGridUpdate" OnEditCommand="dtGridEdit" DataKeyField="CategoryID"
Now you need to write the code for these command handlers. Editing in a DataGrid control works in a similar way to the DataList control. Listing 16-26 shows the complete code of an editable DataGrid.
Listing 16-26: Editable DataGrid
|
<%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Data" %> <HTML> <HEAD> <title>Editable DataGrid Sample</title> <script language="VB" runat="server"> Shared ConnectionString As String = "user id=sa;password=;" & _ "Initial Catalog=Northwind;" & _ "Data Source=MCB;" Shared conn As SqlConnection = Nothing Shared adapter As SqlDataAdapter = Nothing Shared sql As String = Nothing Shared cmd as SqlCommand = Nothing Shared ds as DataSet = Nothing Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here If Not IsPostBack Then FillDataGrid() End If End Sub ' FillDataGrid method Private Sub FillDataGrid() conn = New SqlConnection(ConnectionString) If conn.State <> ConnectionState.Closed Then conn.Open() End If sql = "SELECT * FROM Categories" ds = New DataSet() adapter = New SqlDataAdapter(sql, conn) adapter.Fill(ds) dtGrid.DataSource = ds.Tables(0) dtGrid.DataBind() If conn.State = ConnectionState.Open Then conn.Close() End If End Sub ' EditCommand event handler Sub dtGridEdit(obj as Object, args as DataGridCommandEventArgs) dtGrid.EditItemIndex = args.Item.ItemIndex FillDataGrid() End Sub ' UpdateCommand Event handler Sub dtGridUpdate(obj as Object, args as DataGridCommandEventArgs) Dim catId as Integer Dim strName as String Dim strDes as String Dim catNameTextBox as TextBox Dim desTextBox as TextBox ' Read text from text boxes catId = dtGrid.DataKeys(args.Item.ItemIndex) catNameTextBox = args.Item.Cells(1).Controls(0) desTextBox = args.Item.Cells(2).Controls(0) strName = catNameTextBox.Text strDes = desTextBox.Text ' Construct an UPDATE SQL statement sql = "UPDATE Categories SET CategoryName=@catName, " & _ " Description=@desc WHERE CategoryID=@id" ' Construct a SqlCommand cmd = new SqlCommand(sql, conn) ' Add parameters to the command with the values ' read from text boxes and category ID cmd.Parameters.Add("@id", catId) cmd.Parameters.Add("@catName", strName) cmd.Parameters.Add("@desc", strDes) ' Open the connection conn.Open() ' Execute SQL statement cmd.ExecuteNonQuery() ' Close the connection conn.Close() ' Close editing mode of DataGrid dtGrid.EditItemIndex = -1 ' Refill DataGrid with the updated data FillDataGrid() End Sub ' CancelCommand Event handler Sub dtGridCancel(obj as Object, args as DataGridCommandEventArgs) dtGrid.EditItemIndex = -1 FillDataGrid() End Sub ' Sort Event handler Sub dtGridSort(obj as Object, args as DataGridSortCommandEventArgs) Dim sortExpr as String ' Open the connection If conn.State <> ConnectionState.Closed Then conn.Open() End If ' Get the column from SortCommand Event Args sortExpr = args.SortExpression ' Create a SELECT Statement with ORDER BY the expression sql = "SELECT * FROM Categories ORDER BY " + sortExpr ds = New DataSet() adapter = New SqlDataAdapter(sql, conn) adapter.Fill(ds) dtGrid.DataSource = ds.Tables(0) dtGrid.DataBind() If conn.State = ConnectionState.Open Then conn.Close() End If End Sub </script> </HEAD> <BODY> <font face="verdana" size="4">Editable DataGrid Sample</font> <br> <form runat="server"> <ASP:DataGrid HeaderStyle-BackColor="#003366" HeaderStyle-Font-Bold="True" HeaderStyle-Font-Name="verdana" HeaderStyle-ForeColor="white" HeaderStyle-Font-Size="10" ItemStyle-BackColor="black" ItemStyle-Font-Name="verdana" ItemStyle-Font-Size="8" ItemStyle-ForeColor="#ffcc33" AlternatingItemStyle-BackColor="LightSteelBlue" AlternatingItemStyle-ForeColor="black" FooterStyle-BackColor="#ffff99" FooterStyle-Font-Name="tahoma" FooterStyle-Font-Size="6" FooterStyle-Font-Italic="True" EditItemStyle-BackColor="red" EditItemStyle-Font-Size="8" EditItemStyle-ForeColor="#ccffff" ShowHeader="True" ShowFooter="True" OnCancelCommand="dtGridCancel" OnUpdateCommand="dtGridUpdate" OnEditCommand="dtGridEdit" DataKeyField="CategoryID" AutoGenerateColumns="False" AllowSorting="True" OnSortCommand="dtGridSort" runat="server"> <FooterStyle Font-Size="6pt" Font-Names="tahoma" Font-Italic="True" BackColor="#FFFF99"></FooterStyle> <HeaderStyle Font-Size="10pt" Font-Names="verdana" Font-Bold="True" ForeColor="White" BackColor="#003366"> </HeaderStyle> <EditItemStyle Font-Size="8pt" ForeColor="#CCFFFF" BackColor="Red"> </EditItemStyle> <AlternatingItemStyle ForeColor="Black" BackColor="LightSteelBlue"> </AlternatingItemStyle> <ItemStyle Font-Size="8pt" Font-Names="verdana" ForeColor="#FFCC33" BackColor="Black"> </ItemStyle> <Columns> <asp:BoundColumn DataField="CategoryID" ReadOnly="True" HeaderText="ID" FooterText="Category ID"> </asp:BoundColumn> <asp:BoundColumn DataField="CategoryName" HeaderText="Category Name" FooterText="Name of Category"> </asp:BoundColumn> <asp:BoundColumn DataField="Description" HeaderText="Description" FooterText="Category Description"> </asp:BoundColumn> <asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Save" CancelText="Cancel" EditText="Edit"> <ItemStyle Font-Names="verdana" ForeColor="White" BackColor="Red"> </ItemStyle> </asp:EditCommandColumn> </Columns> </ASP:DataGrid> </form> </BODY> </HTML> |
Sorting and Searching in a DataGrid
The DataGrid control provides partial built-in support for sorting. The AllowSorting property's true value makes DataGrid columns link buttons, which can sort a column's values. The OnSortCommand method raises the sort event of the control. The following code activates the sorting in a DataGrid:
<ASP:DataGrid AllowSorting="True" OnSortCommand="dtGridSort" runat="server"> </ASP:DataGrid>
The OnSortCommand handler provides sorting data as the name of the column by using DataGridSortCommandEventArgs.SortExpression, as shown in the following code:
' Sort Event handler Sub dtGridSort(obj as Object, args as DataGridSortCommandEventArgs) Dim sortExpr as String sortExpr = args.SortExpression FillDataGrid(sortExpr) End Sub
| Note | The default sorting order is ascending. If you want to provide descending order, you need to construct an ORDER BY query with a DESCENDING option. |
Once you know the column, you can simply construct an ORDER BY query and refill the data in the control. Listing 16-27 shows the complete code for sorting in a DataGrid control.
Listing 16-27: Implementing Sorting in a DataGrid Control
|
<%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Data" %> <HTML> <HEAD> <title>Editable DataGrid Sample</title> <script language="VB" runat="server"> Shared ConnectionString As String = "user id=sa;password=;" & _ "Initial Catalog=Northwind;" & _ "Data Source=MCB;" Shared conn As SqlConnection = Nothing Shared adapter As SqlDataAdapter = Nothing Shared sql As String = Nothing Shared cmd as SqlCommand = Nothing Shared ds as DataSet = Nothing Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here If Not IsPostBack Then FillDataGrid("") End If End Sub ' FillDataGrid method Private Sub FillDataGrid(str as String) conn = New SqlConnection(ConnectionString) If conn.State <> ConnectionState.Closed Then conn.Open() End If if str = "" Then sql = "SELECT * FROM Categories" Else sql = "SELECT * FROM Categories ORDER BY " + str End If ds = New DataSet() adapter = New SqlDataAdapter(sql, conn) adapter.Fill(ds) dtGrid.DataSource = ds.Tables(0) dtGrid.DataBind() If conn.State = ConnectionState.Open Then conn.Close() End If End Sub ' Sort Event handler Sub dtGridSort(obj as Object, args as DataGridSortCommandEventArgs) Dim sortExpr as String sortExpr = args.SortExpression FillDataGrid(sortExpr) End Sub </script> </HEAD> <BODY> <font face="verdana" size="4">Sorting in DataGrid</font> <br> <form runat="server" > <ASP:DataGrid AllowSorting="True" OnSortCommand="dtGridSort" runat="server"> </ASP:DataGrid> </form> </BODY> |
| Caution | You can't use the text, ntext, and image data types in an ORDER BY clause. |
To implement searching in a DataGrid control, you need to read the name of the column and a value for which you're searching. After that you can simply construct a SELECT..WHERE clause and refill the data. If you don't want to sort and read data from a data source, you can simply create a DataView control from a data source using a DataSet, apply sort and filter criteria on a DataView, and bind the DataView to the DataGrid control. (See the DataGrid and "Implementing Search in a DataGrid" sections of Chapter 7 for more information.) Sorting and searching works in both the controls in the same way because it's done through ADO.NET. The only difference is the data binding.
EAN: 2147483647
Pages: 214
- Structures, Processes and Relational Mechanisms for IT Governance
- Measuring and Managing E-Business Initiatives Through the Balanced Scorecard
- Measuring ROI in E-Commerce Applications: Analysis to Action
- Technical Issues Related to IT Governance Tactics: Product Metrics, Measurements and Process Control
- The Evolution of IT Governance at NB Power