One of the most powerful server controls in ASP.NET is no doubt the DataGrid control. The DataGrid control is a multi-column, data-bound ASP.NET server control. Using the DataGrid control, you can display records from a database using a variety of formats. You can also edit, update, and delete records from the database using the DataGrid control. In this first part of the ASP.NET server controls series, I will show you how to use the DataGrid control to develop compelling Web applications.
Let's learn to use the DataGrid control by building a simple example using Visual Studio .NET. For this example, I will illustrate using an ASP.NET Web application.
In your default Web form, add a DataGrid control. To change the format of the DataGrid control, right-click on it and select Auto-Format.... Choose the scheme that you like, such as the one shown in Figure 1.
 |
| Figure 1. Selecting a scheme for the DataGrid control |
Your DataGrid control should now look like this:
 |
| Figure 2. Applying a scheme to the DataGrid control |
There are two ways in which you can configure the DataGrid control:
In this article, I will illustrate using the second method, allowing readers who are not using Visual Studio .NET to easily follow the discussions in this article.
To modify the HTML codes behind the DataGrid control, switch to code view. Pay attention to the section on the <asp:DataGrid> element:
<asp:DataGrid
id="DataGrid1"
style="Z-INDEX: 101; LEFT: 24px; POSITION: absolute; TOP: 16px"
runat="server"
BorderColor="#DEBA84"
BorderStyle="None"
CellSpacing="2"
BorderWidth="1px"
BackColor="#DEBA84"
CellPadding="3">
<SelectedItemStyle Font-Bold="True"
ForeColor="White"
BackColor="#738A9C">
</SelectedItemStyle>
<ItemStyle ForeColor="#8C4510"
BackColor="#FFF7E7">
</ItemStyle>
<HeaderStyle Font-Bold="True"
ForeColor="White"
BackColor="#A55129">
</HeaderStyle>
<FooterStyle ForeColor="#8C4510"
BackColor="#F7DFB5">
</FooterStyle>
<PagerStyle HorizontalAlign="Center"
ForeColor="#8C4510"
Mode="NumericPages">
</PagerStyle>
</asp:DataGrid>
Modify the HTML codes for the DataGrid control as follows:
<asp:DataGrid
DataKeyField="emp_id"
AutoGenerateColumns="False"
id="DataGrid1"
runat="server"
Width="397px"
Height="231px"
BorderStyle="None"
BorderWidth="1px"
BorderColor="#DEBA84"
BackColor="#DEBA84"
CellPadding="3"
CellSpacing="2"
OnCancelCommand="cancel"
OnUpdateCommand="update"
OnEditCommand="edit"
AllowPaging="True"
OnPageIndexChanged="changepage"
PageSize="4"
AllowSorting="True"
OnSortCommand="sort"
OnDeleteCommand="Delete">
<FOOTERSTYLE BackColor="#F7DFB5" ForeColor="#8C4510"></FOOTERSTYLE>
<HEADERSTYLE BackColor="#A55129" ForeColor="White"
Font-Bold="True">
</HEADERSTYLE>
<PAGERSTYLE ForeColor="#8C4510" HorizontalAlign="Center"
Mode="NumericPages"></PAGERSTYLE>
<SELECTEDITEMSTYLE BackColor="#738A9C" ForeColor="White"
Font-Bold="True"></SELECTEDITEMSTYLE>
<ITEMSTYLE BackColor="#FFF7E7" ForeColor="#8C4510"></ITEMSTYLE>
<COLUMNS>
<asp:HyperLinkColumn
HeaderText="Employee ID"
DataTextField="emp_id"
DataNavigateUrlFormatString="Webform1.aspx?emp_id={0}"
DataNavigateUrlField="emp_id">
</asp:HyperLinkColumn>
<asp:EditCommandColumn
HeaderText="Edit"
EditText="Edit"
ButtonType="PushButton"
CancelText="Cancel"
UpdateText="Update">
</asp:EditCommandColumn>
<asp:BoundColumn
HeaderText="First Name"
DataField="fname"
SortExpression="ORDER BY fname">
</asp:BoundColumn>
<asp:BoundColumn
HeaderText="Last Name"
DataField="lname"
SortExpression="ORDER BY lname">
</asp:BoundColumn>
<asp:ButtonColumn
Text="Remove"
HeaderText="Delete"
ButtonType="PushButton"
CommandName="delete">
</asp:ButtonColumn>
</COLUMNS>
</asp:DataGrid>
After you have modified the code, your DataGrid should look like this (switch to design view):
 |
| Figure 3. The modified DataGrid control |
To customize each column in the DataGrid control, I use the <columns> element. I will explain how each column is created in the following sections (use Figure 3 as a reference):
To create a hyperlink column, use the <asp:HyperLinkColumn> element:
<asp:HyperLinkColumn
HeaderText="Employee ID"
DataTextField="emp_id"
DataNavigateUrlFormatString="Webform1.aspx?emp_id={0}"
DataNavigateUrlField="emp_id">
</asp:HyperLinkColumn>
The HeaderText attribute specifies the column header text.
The DataTextField attribute specifies the field (of the data source bound to this DataGrid control) whose value will be displayed in this column.
The DataNavigateUrlFormatString attribute indicates the URL pointed to by this column.
The "{0}" will be substituted with the value indicated by the data field specified in the DataNavigateUrlField attribute.
|
To display the Edit column, I use the <asp:EditCommandColumn> element.
<asp:EditCommandColumn
HeaderText="Edit"
EditText="Edit"
ButtonType="PushButton"
CancelText="Cancel"
UpdateText="Update">
</asp:EditCommandColumn>
The HeaderText attribute specifies the column header text.
The EditText attribute specifies the text to be displayed on the button itself. The ButtonType attribute specifies the type of button you want. Besides PushButton, you can also have LinkButton.
 |
Figure 4. Using a PushButton for the Edit column |
The CancelText and Update attributes specify the text of the buttons to display when the edit button is clicked:
 |
| Figure 5. Displaying the Update and Cancel button automatically |
The First Name and Last Name columns are created using the <asp:BoundColumn> element and are bound to a data source.
<asp:BoundColumn
HeaderText="First Name"
DataField="fname"
SortExpression="ORDER BY fname">
</asp:BoundColumn>
<asp:BoundColumn
HeaderText="Last Name"
DataField="lname"
SortExpression="ORDER BY lname">
</asp:BoundColumn>
The HeaderText attribute specifies the column header text.
The DataField attribute specifies the field (of the data source bound to this DataGrid control) whose value will be displayed in this column.
The SortExpression attribute contains the expression that you can use to sort the columns when the header text is clicked:
 |
| Figure 6. Clicking on the hyperlink allows rows to be sorted |
To implement the last column, I use the <asp:ButtonColumn> element.
<asp:ButtonColumn
Text="Remove"
HeaderText="Delete"
ButtonType="PushButton"
CommandName="delete">
</asp:ButtonColumn>
The <asp:ButtonColumn> element is similar to <asp:EditCommandColumn>, except that it does not automatically generate the Update and Cancel buttons. The CommandName attribute identifies this button as a delete button. It corresponds to the OnDeleteCommand attribute in the <asp:DataGrid> element.
Note that I have also added a few additional attributes to the <asp:DataGrid> element:
<asp:DataGrid
DataKeyField="emp_id"
AutoGenerateColumns="False"
id="DataGrid1"
runat="server"
Width="397px"
Height="231px"
BorderStyle="None"
BorderWidth="1px"
BorderColor="#DEBA84"
BackColor="#DEBA84"
CellPadding="3"
CellSpacing="2"
OnCancelCommand="cancel"
OnUpdateCommand="update"
OnEditCommand="edit"
AllowPaging="True"
OnPageIndexChanged="changepage"
PageSize="4"
AllowSorting="True"
OnSortCommand="sort"
OnDeleteCommand="delete_record">
Here are the important ones:
DataKeyField="emp_id": The key for this entire DataGrid control is the field emp_id.OnCancelCommand="cancel": When the user clicks the Cancel button, invokes the cancel() method.OnUpdateCommand="update": When the user clicks the Update button, invokes the update() method.OnEditCommand="edit": When the user clicks the Edit button, invokes the edit() method.AllowPaging="True": Allows the DataGrid control to display records in multiple pages.OnPageIndexChanged="changepage": Invokes the changepage() method when the user clicks on a page number.PageSize="4": Sets the size of the page to 4AllowSorting="True": Allows the rows to be sorted. OnSortCommand="sort": Invokes the sort() method when rows are to be sorted. OnDeleteCommand="delete_record": Invokes the delete_record() method when the Delete button is clicked.To complete the sample application, add the following controls, as shown in Figures 7 and 8:
 |
| Figure 7. Adding the various controls to the form |
 |
| Figure 8. Adding another DataGrid control to the form |
You also need to add a SqlDataAdapter control to your project. Configure the data adapter to connect to the local SQL Server 2000 database. I use the Employee table from the Pubs database.
 |
| Figure 9. Adding a SqlDataAdapter to the project |
|
Let's now write the code to perform all of the various functions that we have indicated in the DataGrid control.
First, we have the Page_Load() event:
Private Sub Page_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
'---sets the initial page size to be 4
If Session("page_size") Is Nothing Then _
Session("page_size") = 4
'---if loading for the first time,
'---set the page size and also the page index
If Not IsPostBack Then
txtPageSize.Text = Session("page_size")
DataGrid1.PageSize = txtPageSize.Text
DataGrid1.CurrentPageIndex = Session("curr_page")
Load_Data()
End If
'---displays the selected employee information---
If Request.QueryString("emp_id") <> "" Then
Dim ds As New DataSet
SqlDataAdapter1.SelectCommand.CommandText = _
"SELECT * FROM Employee WHERE emp_id='" & _
Request.QueryString("emp_id") & "' " & Session("sortBy")
SqlDataAdapter1.Fill(ds, "employee_record")
DataGrid2.DataSource = ds
DataGrid2.DataBind()
End If
End Sub
Here is the Load_Data() method, which loads the data from the database and binds it to the DataGrid control:
Private Sub Load_Data()
Dim ds As New DataSet
SqlDataAdapter1.SelectCommand.CommandText = _
"SELECT * FROM Employee " & Session("sortby")
SqlDataAdapter1.Fill(ds, "employee")
DataGrid1.DataSource = ds
DataGrid1.DataBind()
End Sub
The code for the Hide Names button:
Private Sub hidedetails_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles hidedetails.Click
'---hides the second, third and fourth column---
DataGrid1.Columns(1).Visible = False
DataGrid1.Columns(2).Visible = False
DataGrid1.Columns(3).Visible = False
End Sub
The code for the Show Names button:
Private Sub showdetails_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles showdetails.Click
'---displays the second, third and fourth column---
DataGrid1.Columns(1).Visible = True
DataGrid1.Columns(2).Visible = True
DataGrid1.Columns(3).Visible = True
End Sub
The code for the edit() method:
Public Sub edit(ByVal s As Object, _
ByVal e As DataGridCommandEventArgs)
'---sets the row to edit---
DataGrid1.EditItemIndex = e.Item.ItemIndex
Load_Data()
End Sub
The code for the cancel() method:
Public Sub cancel(ByVal s As Object, _
ByVal e As DataGridCommandEventArgs)
'---deselects the row to edit---
DataGrid1.EditItemIndex = -1
Load_Data()
End Sub
The code for the update() method:
Public Sub update(ByVal s As Object, _
ByVal e As DataGridCommandEventArgs)
Dim tbox As TextBox
Dim fname, lname, emp_id As String
'---retrieves the data from the row to be updated---
tbox = e.Item.Cells(2).Controls(0)
fname = tbox.Text
tbox = e.Item.Cells(3).Controls(0)
lname = tbox.Text
'---retrieves the key for the row---
emp_id = DataGrid1.DataKeys(e.Item.ItemIndex)
'---updates the database---
Dim sql As String = "UPDATE Employee SET fname='" & _
fname & "' , lname='" & lname & _
"' WHERE emp_id='" & emp_id & "'"
Dim conn As New SqlConnection("server=localhost; " & _
"user id =sa; password=;database=pubs")
Dim comm As New SqlCommand(sql, conn)
conn.Open()
comm.ExecuteNonQuery()
conn.Close()
DataGrid1.EditItemIndex = -1
Load_Data()
End Sub
The code for the sort() method:
Public Sub sort(ByVal s As Object, _
ByVal e As DataGridSortCommandEventArgs)
' session object is used to support paging
Session("sortby") = e.SortExpression
Load_Data()
End Sub
The code for the delete_record() method:
Public Sub delete_record(ByVal s As Object, _
ByVal e As DataGridCommandEventArgs)
'---retrieves the key for the row---
Response.Write(DataGrid1.DataKeys(e.Item.ItemIndex))
'---codes to delete row here----
'
'-------------------------------
End Sub
The code for the changepage() method:
Public Sub changepage(ByVal s As Object, _
ByVal e As DataGridPageChangedEventArgs)
'---displays the next page---
DataGrid1.CurrentPageIndex = e.NewPageIndex
Session("curr_page") = e.NewPageIndex
Load_Data()
End Sub
The code for the Change button:
Private Sub changepagesize_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles changepagesize.Click
'---changes the size of the page---
DataGrid1.PageSize = CLng(txtPageSize.Text())
Session("page_size") = DataGrid1.PageSize
Load_Data()
End Sub
Run this application and you should now be able to display the records in multiple pages. You can also edit and delete any rows you desire:
 |
| Figure 10. Running the example application |
The DataGrid is a very powerful and useful control, if you know how to use it properly. Don't be fooled by its appearance; you still need to write substantial code to make it work the way you want it to work. By if you take some time to trace the code and understand the documentation, it is really worth the effort!
Wei-Meng Lee (Microsoft MVP) http://weimenglee.blogspot.com is a technologist and founder of Developer Learning Solutions http://www.developerlearningsolutions.com, a technology company specializing in hands-on training on the latest Microsoft technologies.
Return to ONDotnet.com
Copyright © 2009 O'Reilly Media, Inc.