ONDotNet.com    
 Published on ONDotNet.com (http://www.ondotnet.com/)
 See this if you're having trouble printing code examples


ASP.NET Data Controls Part 1: DataGrids

by Wei-Meng Lee
02/24/2003

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.

Using the DataGrid Control

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.

Selecting a scheme for the DataGrid control
Figure 1. Selecting a scheme for the DataGrid control

Your DataGrid control should now look like this:

Applying a scheme to the DataGrid control
Figure 2. Applying a scheme to the DataGrid control

There are two ways in which you can configure the DataGrid control:

  1. Using the Property Builder
  2. Modifying the HTML codes manually

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):

The modified DataGrid control
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):

Employee ID Column

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.

Edit Column

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.

Using a PushButton for the Edit column
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:

Displaying the Update and Cancel button automatically
Figure 5. Displaying the Update and Cancel button automatically

First Name and Last Name Columns

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:

Clicking on the hyperlink allows rows to be sorted
Figure 6. Clicking on the hyperlink allows rows to be sorted

Delete Column

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.

Combining All of the Parts

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:

Completing the Sample

To complete the sample application, add the following controls, as shown in Figures 7 and 8:

Adding the various controls to the form
Figure 7. Adding the various controls to the form
Adding another DataGrid control 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.

Adding a SqlDataAdapter to the project
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:

Running the example application
Figure 10. Running the example application

Summary

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.