Walkthrough: Performing Bulk Updates to Rows Bound to a GridView Web Server Control

Article
12/04/2014

When editing is enabled for a GridView control, by default only one row can be edited at a time. This walkthrough shows you how to extend the functionality of the GridView control so that users can modify multiple rows and then save all changes by clicking a button. This walkthrough uses a SqlDataSource control to retrieve results from the data source and manage updates. The SqlDataSource control acts as the data source for the GridView control.

During this walkthrough, you will learn how to:

Connect to a SQL Server database in Microsoft Visual Web Developer.
Use the SqlDataSource control to manage data access.
Display data returned from the database in the GridView control.
Configure the GridView control to enable users to edit multiple rows at one time.

A full listing for the page that you create in this walkthrough is listed at the end of the topic.

Prerequisites

In order to complete this walkthrough, you will need:

Visual Web Developer (Visual Studio).
Microsoft Data Access Components (MDAC) version 2.7 or later.

If you are using Microsoft Windows XP or Windows Server 2003, you already have MDAC 2.7. However, if you are using Microsoft Windows 2000, you might need to upgrade the MDAC already installed on your computer. For more information, see Microsoft Data Access Components (MDAC) Installation in the MSDN library.
Access to the SQL Server Northwind database. If you need a copy of the Northwind database for SQL Server, see Installing Sample Databases in SQL Server 2005 Books Online.

Note

If you need information about how to log on to the computer running SQL Server, contact the server administrator.

Creating the Web Site

If you have already created a Web site in Visual Web Developer by completing Walkthrough: Creating a Basic Web Forms Page in Visual Studio, you can use that Web site and go to the next section. Otherwise, create a new Web site and page.

This walkthrough uses a Web site project. You could use a Web application project instead. For information about the difference between these Web project types, see Web Application Projects versus Web Site Projects in Visual Studio.

To create a file system Web site

Open Visual Web Developer.
On the File menu, click New, and then click Web Site. If you are using Visual Web Developer Express, on the File menu, click New Web Site.

The New Web Site dialog box appears.
Under Visual Studio installed templates, click ASP.NET Web Site.
In the Location list, click File System, and then enter the name of the folder where you want to keep the pages of your Web site.

For example, enter the folder name C:\WebSites\BulkUpdate.
In the Language list, click the programming language you prefer to work in.
Click OK.

Visual Web Developer creates the folder and a new page named Default.aspx.

Adding a Data Source for the GridView Control

To display data on an ASP.NET Web page, you need the following:

A connection to a data source such as a database.

In the following procedure, you will create a connection to the SQL Server Northwind database.
A data source control on the page, which interacts with the data source (the database) to read and write data.
A control on the page to display the data.

In the following procedure, you will display data in a GridView control. The GridView control will get its data from a SqlDataSource control.

To add a data source for the GridView control

Open or switch to the Default.aspx page.
Switch to Design view.
From the Data tab in the Toolbox, drag a SqlDataSource control onto the page.

If the SqlDataSource Tasks smart tag does not appear, right-click the SqlDataSource control and click Show Smart Tag.
In the SqlDataSource Tasks panel, click Configure Data Source.

The Configure Data Source wizard appears.
Click New Connection.

The Add Connection dialog box appears.
If required, do one of the following. The exact UI you see depends on what data connections you have made previously in Visual Web Developer.
- If the Data source list does not display Microsoft SQL Server (SqlClient), click Change, and in the Change Data Source dialog box, select Microsoft SQL Server.
- If the Choose Data Source dialog box appears instead of the Connection Properties dialog box, in the Data source list, select the type of data source you will use. For this walkthrough, the data source type is Microsoft SQL Server. In the Data provider list, click .NET Framework Data Provider for SQL Server, and then click Continue.
On the Add Connection page, in the Server name text box, enter the name of the computer running the SQL Server Northwind database.
Under Log on to the server, select the appropriate option to access the SQL Server database (integrated security or specific ID and password). If required, enter a user name and password.

Note

If you need information about how to log on to the computer running SQL Server, contact the server administrator.
If you entered a password, select the Save my password check box.
Click Select or enter a database name, and then enter Northwind.
Click Test connection, and when you are sure that it works, click OK.
In the Configure Data Source wizard, click Next.
Ensure that the Yes, save this connection as check box is selected.
Name the connection NorthwindConnectionString and then click Next.
In the Configure the Select Statement page, select Specify columns from a table or view.
In the Name list, select Employees.
Under Columns, select EmployeeID, LastName, and FirstName.
Click Advanced.
Select the Generate INSERT, UPDATE, and DELETE statements check box, and then click OK.
Click Next.
Click Finish.

Adding a GridView Control to Display Data

After you have configured a data source control to manage the data, you need a control on the page to display the data.

In the following procedure, you will display data in a GridView control. The GridView control will get its data from the SqlDataSource control that you added previously.

To enable users to edit the contents of all the GridView control at once (rather than editing just one row at a time), you must customize the GridView control. You will replace the default display elements in each column with editable elements, and then bind them to the data source. To accomplish this, you will create TemplateField columns. In the ItemTemplate for each TemplateField column, you will add a bound TextBox control where users can edit the data.

To add and configure a GridView control for displaying data

Ensure that you are in Design view for the Default.aspx page.
From the Data tab in the Toolbox, drag a GridView control onto the page.

In the GridView Tasks panel, in the Choose Data Source list box, select the SqlDataSource control you added previously, SqlDataSource1.
Select Enable Paging.
Click Edit Columns.

The Fields dialog box appears.
Under Selected fields, select FirstName.
Click Convert this field to a TemplateField.
Convert the LastName field to a template field, using the same steps that you did for the FirstName field. Do not convert the EmployeeId field, because it contains the primary key, and is therefore not editable.
Click OK.
In the GridView Tasks panel, click Edit Templates.
In the Display list, under FirstName, click EditItemTemplate.

The GridView control displays an editable area with the default layout for the FirstName column in edit mode.
Right-click the TextBox control in the EditItemTemplate and then click Copy.
Right-click the GridView control and then click Show Smart Tag.
In the Display list, under FirstName, click ItemTemplate.

The GridView control now displays the default layout for the FirstName columnin display mode.
Delete the existing Label control.
Right-click the ItemTemplate editable area, and then click Paste.

You have copied the TextBox control, including its data binding configuration, from the EditItemTemplate layout to the ItemTemplate layout.
Select the TextBox control and in the Properties window, set the (ID) property to FirstNameTextBox. This makes sure that the TextBox control that you pasted has a different ID than the one that you copied.
Set the MaxLength property to 10 (the maximum length of the field in the database).

This ensures that users do not enter more information than the FirstName field in the database is configured to hold.
Repeat the preceding nine steps, but this time modify the LastName template field. Replace the existing Label control with a TextBox copied from the EditItemTemplate layout, and set the ID property to LastNameTextBox.

For the MaxLength property of the TextBox control, specify a value of 20.
Right-click the GridView control, and then click End Template Editing.

Adding a Procedure to Perform Bulk Updates

After you have configured the GridView control to display editable data, you need to add code to perform the bulk update. In this section, you will do the following:

Add a Button control and in its Click handler, add code to bulk update the changes from each row of the GridView control.
Add a DataTable object that stores the original data values.
Add code to determine if a row has been modified. The current values displayed in the GridView control will be compared to the original values stored in the DataTable object. If one or more of the displayed values do not match the original value, the row will be updated in the database. Otherwise, the row will not be included in the bulk update.

To create a procedure for performing the bulk update

Switch to Design view.
Select the GridView control, and in the Properties window, click the Events button () to display the events for the GridView control.

In the RowDataBound field, type GridView1_RowDataBound and press ENTER.

Visual Web Developer creates an event handler for the RowDataBound event of the GridView control. The code will look like the following code example.

Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound

End Sub

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{

}

Replace the generated procedure with the following code (including the private variables).

Private tableCopied As Boolean = False 
Private originalDataTable As System.Data.DataTable

Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
    If e.Row.RowType = DataControlRowType.DataRow Then 
        If Not tableCopied Then
            originalDataTable = CType(e.Row.DataItem, System.Data.DataRowView).Row.Table.Copy()
            ViewState("originalValuesDataTable") = originalDataTable
            tableCopied = True 
        End If 
    End If 
End Sub

private bool tableCopied = false;
private System.Data.DataTable originalDataTable;

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
        if (!tableCopied)
        {
            originalDataTable = ((System.Data.DataRowView)e.Row.DataItem).Row.Table.Copy();
            ViewState["originalValuesDataTable"] = originalDataTable;
            tableCopied = true;
        }
}

The code runs whenever the GridView control is performing data binding. While the first row is binding, the code saves a copy of the original database values in a DataTable object, which is in turn stored in ViewState.

Switch to Design view.
From the Standard tab in the Toolbox, drag a Button control onto the page.
In the Properties window, click the Properties button () to display the properties for the Button control.
In the (ID) field, enter UpdateButton.
In the Text field, enter Update.
Click the Events button () to display events for the Button control.
In the Click field, type UpdateButton_Click and press ENTER.

Visual Web Developer creates an event handler for the Button control's Click event. The code will look like the following code example.
```
Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As EventArgs)

End Sub
```
```
protected void UpdateButton_Click(object sender, EventArgs e)
{

}
```

Replace the generated procedure with the following code.

Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles UpdateButton.Click
    originalDataTable = CType(ViewState("originalValuesDataTable"), System.Data.DataTable)

    For Each r As GridViewRow In GridView1.Rows
        If IsRowModified(r) Then GridView1.UpdateRow(r.RowIndex, False)
    Next 

    ' Rebind the Grid to repopulate the original values table.
    tableCopied = False
    GridView1.DataBind()
End Sub 

Protected Function IsRowModified(ByVal r As GridViewRow) As Boolean 
    Dim currentID As Integer 
    Dim currentLastName As String 
    Dim currentFirstName As String

    currentID = Convert.ToInt32(GridView1.DataKeys(r.RowIndex).Value)

    currentLastName = CType(r.FindControl("LastNameTextBox"), TextBox).Text
    currentFirstName = CType(r.FindControl("FirstNameTextBox"), TextBox).Text

    Dim row As System.Data.DataRow = _
        originalDataTable.Select(String.Format("EmployeeID = {0}", currentID))(0)

    If Not currentLastName.Equals(row("LastName").ToString()) Then Return True 
    If Not currentFirstName.Equals(row("FirstName").ToString()) Then Return True 

    Return False 
End Function

protected void UpdateButton_Click(object sender, EventArgs e)
{
    originalDataTable = (System.Data.DataTable)ViewState["originalValuesDataTable"];

    foreach (GridViewRow r in GridView1.Rows)
        if (IsRowModified(r)) { GridView1.UpdateRow(r.RowIndex, false); }

    // Rebind the Grid to repopulate the original values table.
    tableCopied = false;
    GridView1.DataBind();
}

protected bool IsRowModified(GridViewRow r)
{
    int currentID;
    string currentLastName;
    string currentFirstName;

    currentID = Convert.ToInt32(GridView1.DataKeys[r.RowIndex].Value);

    currentLastName = ((TextBox)r.FindControl("LastNameTextBox")).Text;
    currentFirstName = ((TextBox)r.FindControl("FirstNameTextBox")).Text;

    System.Data.DataRow row =
        originalDataTable.Select(String.Format("EmployeeID = {0}", currentID))[0];

    if (!currentLastName.Equals(row["LastName"].ToString())) { return true; }
    if (!currentFirstName.Equals(row["FirstName"].ToString())) { return true; }

    return false;
}

Note

The procedure performs a string comparison using the value in each editable TextBox control and the value stored in the cached DataTable object. If you have formatted the text in the TextBox control, the values might be equivalent, but their string representations will not match. For example, if you have formatted a DateTime value using the small date format ({0:d}), the value in the date TextBox control might be 3/2/2005. The string representation of the date value from the DataTable object would be 3/2/2005 12:00 AM. In these cases, you must add comparison logic that takes formats and localization settings into account.

The procedure iterates through the rows of the GridView control and calls the custom IsRowModified function for each row. The function compares the current row to the corresponding row in the DataTable object, and returns true if the row has changed. For any rows that have changed, the code in the button's Click handler calls the UpdateRow method of the GridView control.

Testing the Page

You can now run the page to test the code.

To test the page

Press CTRL+F5 to run the page.

The page appears in the browser. The GridView control displays the data rows from the Northwind Employees table in editable pages of data.
Modify some values.
Click Update.

The modified rows are updated in the database.
Close the browser.

Example

Description

The following example shows the source code for the page that you create in this walkthrough.

Code

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="VB_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="https://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

      <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" 
        DeleteCommand="DELETE FROM [Employees] WHERE [EmployeeID] = @EmployeeID" 
        InsertCommand="INSERT INTO [Employees] ([LastName], [FirstName]) VALUES (@LastName, @FirstName)" 
        SelectCommand="SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees]" 
        UpdateCommand="UPDATE [Employees] SET [LastName] = @LastName, [FirstName] = @FirstName WHERE [EmployeeID] = @EmployeeID">
        <DeleteParameters>
          <asp:Parameter Name="EmployeeID" Type="Int32" />
        </DeleteParameters>
        <UpdateParameters>
          <asp:Parameter Name="LastName" Type="String" />
          <asp:Parameter Name="FirstName" Type="String" />
          <asp:Parameter Name="EmployeeID" Type="Int32" />
        </UpdateParameters>
        <InsertParameters>
          <asp:Parameter Name="LastName" Type="String" />
          <asp:Parameter Name="FirstName" Type="String" />
        </InsertParameters>
      </asp:SqlDataSource>
      <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
        AutoGenerateColumns="False" DataKeyNames="EmployeeID" 
        DataSourceID="SqlDataSource1">
        <Columns>
          <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" 
            InsertVisible="False" ReadOnly="True" SortExpression="EmployeeID" />
          <asp:TemplateField HeaderText="LastName" SortExpression="LastName">
            <EditItemTemplate>
              <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("LastName") %>'></asp:TextBox>
            </EditItemTemplate>
            <ItemTemplate>
              <asp:TextBox ID="LastNameTextBox" runat="server" MaxLength="20" 
                Text='<%# Bind("LastName") %>'></asp:TextBox>
            </ItemTemplate>
          </asp:TemplateField>
          <asp:TemplateField HeaderText="FirstName" SortExpression="FirstName">
            <EditItemTemplate>
              <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("FirstName") %>'></asp:TextBox>
            </EditItemTemplate>
            <ItemTemplate>
              <asp:TextBox ID="FirstNameTextBox" runat="server" MaxLength="10" 
                Text='<%# Bind("FirstName") %>'></asp:TextBox>
            </ItemTemplate>
          </asp:TemplateField>
        </Columns>
      </asp:GridView>
      <asp:Button ID="UpdateButton" runat="server" Height="26px" Text="Update" />

    </div>
    </form>
</body>
</html>

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="https://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

      <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" 
        DeleteCommand="DELETE FROM [Employees] WHERE [EmployeeID] = @EmployeeID" 
        InsertCommand="INSERT INTO [Employees] ([LastName], [FirstName]) VALUES (@LastName, @FirstName)" 
        SelectCommand="SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees]" 
        UpdateCommand="UPDATE [Employees] SET [LastName] = @LastName, [FirstName] = @FirstName WHERE [EmployeeID] = @EmployeeID">
        <DeleteParameters>
          <asp:Parameter Name="EmployeeID" Type="Int32" />
        </DeleteParameters>
        <UpdateParameters>
          <asp:Parameter Name="LastName" Type="String" />
          <asp:Parameter Name="FirstName" Type="String" />
          <asp:Parameter Name="EmployeeID" Type="Int32" />
        </UpdateParameters>
        <InsertParameters>
          <asp:Parameter Name="LastName" Type="String" />
          <asp:Parameter Name="FirstName" Type="String" />
        </InsertParameters>
      </asp:SqlDataSource>
      <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
        AutoGenerateColumns="False" DataKeyNames="EmployeeID" 
        DataSourceID="SqlDataSource1" onrowdatabound="GridView1_RowDataBound">
        <Columns>
          <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" 
            InsertVisible="False" ReadOnly="True" SortExpression="EmployeeID" />
          <asp:TemplateField HeaderText="LastName" SortExpression="LastName">
            <EditItemTemplate>
              <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("LastName") %>'></asp:TextBox>
            </EditItemTemplate>
            <ItemTemplate>
              <asp:TextBox ID="LastNameTextBox" runat="server" MaxLength="20" 
                Text='<%# Bind("LastName") %>'></asp:TextBox>
            </ItemTemplate>
          </asp:TemplateField>
          <asp:TemplateField HeaderText="FirstName" SortExpression="FirstName">
            <EditItemTemplate>
              <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("FirstName") %>'></asp:TextBox>
            </EditItemTemplate>
            <ItemTemplate>
              <asp:TextBox ID="FirstNameTextBox" runat="server" MaxLength="10" 
                Text='<%# Bind("FirstName") %>'></asp:TextBox>
            </ItemTemplate>
          </asp:TemplateField>
        </Columns>
      </asp:GridView>
      <asp:Button ID="UpdateButton" runat="server" onclick="UpdateButton_Click" 
        Text="Update" />

    </div>
    </form>
</body>
</html>

Partial Class VB_Default
    Inherits System.Web.UI.Page

    Private tableCopied As Boolean = False 
    Private originalDataTable As System.Data.DataTable

    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then 
            If Not tableCopied Then
                originalDataTable = CType(e.Row.DataItem, System.Data.DataRowView).Row.Table.Copy()
                ViewState("originalValuesDataTable") = originalDataTable
                tableCopied = True 
            End If 
        End If 
    End Sub 

    Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles UpdateButton.Click
        originalDataTable = CType(ViewState("originalValuesDataTable"), System.Data.DataTable)

        For Each r As GridViewRow In GridView1.Rows
            If IsRowModified(r) Then GridView1.UpdateRow(r.RowIndex, False)
        Next 

        ' Rebind the Grid to repopulate the original values table.
        tableCopied = False
        GridView1.DataBind()
    End Sub 

    Protected Function IsRowModified(ByVal r As GridViewRow) As Boolean 
        Dim currentID As Integer 
        Dim currentLastName As String 
        Dim currentFirstName As String

        currentID = Convert.ToInt32(GridView1.DataKeys(r.RowIndex).Value)

        currentLastName = CType(r.FindControl("LastNameTextBox"), TextBox).Text
        currentFirstName = CType(r.FindControl("FirstNameTextBox"), TextBox).Text

        Dim row As System.Data.DataRow = _
            originalDataTable.Select(String.Format("EmployeeID = {0}", currentID))(0)

        If Not currentLastName.Equals(row("LastName").ToString()) Then Return True 
        If Not currentFirstName.Equals(row("FirstName").ToString()) Then Return True 

        Return False 
    End Function 

End Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    private bool tableCopied = false;
    private System.Data.DataTable originalDataTable;

    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
            if (!tableCopied)
            {
                originalDataTable = ((System.Data.DataRowView)e.Row.DataItem).Row.Table.Copy();
                ViewState["originalValuesDataTable"] = originalDataTable;
                tableCopied = true;
            }
    }

    protected void UpdateButton_Click(object sender, EventArgs e)
    {
        originalDataTable = (System.Data.DataTable)ViewState["originalValuesDataTable"];

        foreach (GridViewRow r in GridView1.Rows)
            if (IsRowModified(r)) { GridView1.UpdateRow(r.RowIndex, false); }

        // Rebind the Grid to repopulate the original values table.
        tableCopied = false;
        GridView1.DataBind();
    }

    protected bool IsRowModified(GridViewRow r)
    {
        int currentID;
        string currentLastName;
        string currentFirstName;

        currentID = Convert.ToInt32(GridView1.DataKeys[r.RowIndex].Value);

        currentLastName = ((TextBox)r.FindControl("LastNameTextBox")).Text;
        currentFirstName = ((TextBox)r.FindControl("FirstNameTextBox")).Text;

        System.Data.DataRow row =
            originalDataTable.Select(String.Format("EmployeeID = {0}", currentID))[0];

        if (!currentLastName.Equals(row["LastName"].ToString())) { return true; }
        if (!currentFirstName.Equals(row["FirstName"].ToString())) { return true; }

        return false;
    }
}

Next Steps

This walkthrough has illustrated how you can extend the functionality of the GridView control to enable updates of multiple rows of data on an ASP.NET Web page. You may also want to extend your application to enable users to add new data rows using a DetailsView or FormView control. You can also enable users to work with controls other than a TextBox control to edit values, such as a DropDownList control. For more information, see the following topics: