Recipe 7.5 Editing and Updating Data in a Web Forms DataGrid

Problem

You need to edit complex data using a DataGrid control and update the database with the changes made.

Solution

Bind the results of a database query to a DataGrid control and update the database with changes made in the DataGrid by configuring the appropriate properties and events.

The schema of table TBL00705 used in this solution is shown in Table 7-7.

Table 7-7. TBL0705 schema

Column name	Data type	Length	Allow nulls?
Id	int	4	No
IntField	int	4	Yes
StringField	nvarchar	50	Yes

The Web Forms page sample code defines the DataGrid control with the four columns that it contains—Edit or Update/Cancel button, Delete button, Id field, IntField field, StringField field—and the two templates controlling the appearance of data depending on whether the column is being edited: EditItemTemplate or ItemTemplate. The static Eval( ) method of the DataBinder class is used to fill the field values in each template. The Container.DataItem specifies the container argument for the method which, when used in a data grid, resolves to DataGridItem.DataItem. The code for the Web Forms page is shown in Example 7-9.

Example 7-9. File: ADOCookbookCS0705.aspx

<asp:DataGrid id="dataGrid"
    style="Z-INDEX: 102; LEFT: 16px; POSITION: absolute; TOP: 56px"
    runat="server" Width="576px" AutoGenerateColumns="False" PageSize="5"
    AllowPaging="True" AllowSorting="True">
    <AlternatingItemStyle BackColor="#FFFF99">
    </AlternatingItemStyle>
    <HeaderStyle Font-Bold="True">
    </HeaderStyle>
    <Columns>
        <asp:EditCommandColumn ButtonType="LinkButton"
            UpdateText="Update" CancelText="Cancel" EditText="Edit">
            <HeaderStyle Width="35px">
            </HeaderStyle>
        </asp:EditCommandColumn>
        <asp:ButtonColumn Text="Delete" CommandName="Delete">
            <HeaderStyle Width="50px">
            </HeaderStyle>
        </asp:ButtonColumn>
        <asp:BoundColumn DataField="Id" ReadOnly="True"
            HeaderText="ID">
        </asp:BoundColumn>
        <asp:TemplateColumn HeaderText="Int Field">
            <ItemTemplate>
                <asp:Label runat="server"
                    Text='<%# DataBinder.Eval(Container,
                    "DataItem.IntField") %>'>
                </asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox runat="server" id="intFieldTextBox"
                    Text='<%# DataBinder.Eval(Container,
                    "DataItem.IntField") %>'>
                </asp:TextBox>
            </EditItemTemplate>
        </asp:TemplateColumn>
        <asp:TemplateColumn HeaderText="String Field">
            <ItemTemplate>
                <asp:Label runat="server"
                    Text='<%# DataBinder.Eval(Container,
                    "DataItem.StringField") %>'>
                </asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox runat="server"
                    id="stringFieldTextBox"
                    Text='<%# DataBinder.Eval(Container,
                    "DataItem.StringField") %>'>
                </asp:TextBox>
            </EditItemTemplate>
        </asp:TemplateColumn>
    </Columns>
</asp:DataGrid>
<asp:Label id="Label1"
    style="Z-INDEX: 105; LEFT: 16px; POSITION: absolute; TOP: 248px"
    runat="server" Font-Bold="True">
    New Record:
</asp:Label>
<TABLE id="Table1" style="Z-INDEX: 103; LEFT: 16px; WIDTH: 336px;
    POSITION: absolute; TOP: 272px; HEIGHT: 82px"
    cellSpacing="1" cellPadding="1" width="336" border="0">
    <TR>
        <TD style="WIDTH: 87px">ID:</TD>
        <TD>
            <INPUT id="idTextBox" style="WIDTH: 56px; HEIGHT: 22px"
                type="text" size="4" name="Text1" runat="server">
        </TD>
    </TR>
    <TR>
        <TD style="WIDTH: 87px">
            Int Field:
        </TD>
        <TD>
            <INPUT id="intFieldTextBox" style="WIDTH: 56px; HEIGHT: 22px"
                type="text" size="4" name="Text2" runat="server">
       </TD>
    </TR>
    <TR>
        <TD style="WIDTH: 87px">
            String Field:
        </TD>
        <TD>
            <INPUT id="stringFieldTextBox"
                style="WIDTH: 240px; HEIGHT: 22px" type="text" size="34"
                name="Text3" runat="server">
        </TD>
    </TR>
</TABLE>
<asp:Button id="insertButton"
    style="Z-INDEX: 104; LEFT: 16px; POSITION: absolute; TOP: 360px"
    runat="server" Text="Insert">
</asp:Button>

The code-behind file contains seven event handlers and three methods:

Page.Load

Calls the CreateDataSource( ) method and binds data to the Web Forms DataGrid, if the page is being loaded for the first time.

CreateDataSource( )

This method fills a DataTable with the TBL0705 table and stores the DataTable to a Session variable to cache the data source for the DataGrid.

UpdateDataSource( )

This method creates a DataAdapter and uses it with updating logic generated by a CommandBuilder to update the data source with changes made to the cached DataTable. The updated DataTable is stored to the Session variable used to cache the data source for the DataList.

BindDataGrid( )

This method gets the cached data from the Session variable and binds its default view to the DataGrid.

DataGrid.CancelCommand

Sets the index of the item being edited to -1 to cancel any current editing and calls BindDataGrid( ) to refresh the grid.

DataGrid.DeleteCommand

Finds and deletes the specified row from the data cached in the Session variable and calls the UpdateDataSource( ) method to persist the change back to the data source. BindDataGrid( ) is called to refresh the grid.

DataGrid.EditCommand

Sets the index of the item being edited to the index of the row corresponding to the Edit button. This puts that row into edit mode and calls BindDataGrid( ) to refresh the grid.

DataGrid.UpdateCommand

Finds and updates the specified row in the data cached in the Session variable and calls the UpdateDataSource( ) method to persist the change back to the data source. BindDataGrid( ) is called to refresh the grid.

Insert Button.Click

Inserts a new row into the data cached in the Session variable and calls the UpdateDataSource( ) method to persist the change back to the data source. BindDataGrid( ) is called to refresh the grid.

DataGrid.PageIndexChanged

Sets index of the item being edited is to -1 and calls BindDataGrid( ) to refresh the grid.

The C# code for the code-behind is shown in Example 7-10.

Example 7-10. File: ADOCookbookCS0705.aspx.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient; 

private const String TABLENAME = "TBL0705"; 

//  . . . 

private void Page_Load(object sender, System.EventArgs e)
{
    if(!Page.IsPostBack)
    {
        dataGrid.DataSource = CreateDataSource( );
        dataGrid.DataKeyField = "Id";
        dataGrid.DataBind( );
    }

private DataTable CreateDataSource( )
{
    DataTable dt = new DataTable(TABLENAME);

    // Create the DataAdapter and fill the table using it.
    SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM " + TABLENAME +
        " ORDER BY Id",
        ConfigurationSettings.AppSettings["DataConnectString"]);
    da.Fill(dt);
    da.FillSchema(dt, SchemaType.Source);

    // Store data in session variable to store data between
    // posts to server.
    Session["DataSource"] = dt;

    return dt;
}

private DataTable UpdateDataSource(DataTable dt)
{
    // Create a DataAdapter for the update.
    SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM " + TABLENAME +
        " ORDER BY Id",
        ConfigurationSettings.AppSettings["DataConnectString"]);
    // Create a CommandBuilder to generate update logic.
    SqlCommandBuilder cb = new SqlCommandBuilder(da);

    // Update the data source with changes to the table.
    da.Update(dt);

    // Store updated data in session variable to store data between
    // posts to server.
    Session["DataSource"] = dt;

    return dt;
}

private void BindDataGrid( )
{
    // Get the data from the session variable.
    DataView dv = ((DataTable)Session["DataSource"]).DefaultView;

    // Bind the data view to the data grid.
    dataGrid.DataSource = dv;
    dataGrid.DataBind( );
}

private void dataGrid_CancelCommand(object source,
    System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
    // Set the index of the item being edited out of range.
    dataGrid.EditItemIndex = -1;

    BindDataGrid( );
}

private void dataGrid_DeleteCommand(object source,
    System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
    // Get the data from the session variable.
    DataTable dt = (DataTable)Session["DataSource"];

    // Get the ID of the row to delete.
    int id = (int)dataGrid.DataKeys[e.Item.ItemIndex];

    // Delete the row from the table.
    dt.Rows.Find(id).Delete( );

    // Update the data source with the changes to the table.
    UpdateDataSource(dt);

    BindDataGrid( );
}

private void dataGrid_EditCommand(object source,
    System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
    // Set the index of the item being edited to the selected item.
    dataGrid.EditItemIndex = e.Item.ItemIndex;

    BindDataGrid( );
}

private void dataGrid_UpdateCommand(object source,
    System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
    // Get the data from the session variable.
    DataTable dt = (DataTable)Session["DataSource"];

    // Get the ID of the row to update.
    int id = (int)dataGrid.DataKeys[e.Item.ItemIndex];

    // Get the DataRow to update using the ID.
    DataRow dr = dt.Rows.Find(id);

    // Get the column values for the current record from the DataList.
    dr["IntField"] = 
        Int32.Parse(((TextBox)e.Item.FindControl("intFieldTextBox")).Text);
    dr["StringField"] =
        ((TextBox)e.Item.FindControl("stringFieldTextBox")).Text;

    // Update the data source with the changes to the table.
    UpdateDataSource(dt);

    // Set the index of the item being edited out of range.
    dataGrid.EditItemIndex = -1;

    BindDataGrid( );
}

private void insertButton_Click(object sender, System.EventArgs e)
{
    // Get the data from the session variable.
    DataTable dt = (DataTable)Session["DataSource"];

    // Add the new row.
    DataRow dr = dt.NewRow( );

    dr["Id"] = Int32.Parse(idTextBox.Value);
    dr["IntField"] = Int32.Parse(intFieldTextBox.Value);
    dr["StringField"] = stringFieldTextBox.Value;

    dt.Rows.Add(dr);

    // Update the data source with the changes to the table.
    UpdateDataSource(dt);

    // Clear the controls used to add the record.
    idTextBox.Value = "";
    intFieldTextBox.Value = "";
    stringFieldTextBox.Value = "";

    BindDataGrid( );
}

private void dataGrid_PageIndexChanged(object source,
    System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
    // Update the current page for the data grid.
    dataGrid.CurrentPageIndex = e.NewPageIndex;

    BindDataGrid( );
}

Discussion

While Recipe 7.4 looks at the fundamentals of binding and displaying data using a Web Forms DataGrid control, this recipe shows how to delete, edit, change, and insert data into the DataGrid control and how to update the data source with the changes made.

By default, the DataGrid displays tabular data in read-only mode. With in-place editing configured, the runtime DataGrid displays two additional link button columns—Edit and Delete—for each row. When the Delete button is clicked, the row is deleted from the data source for the data grid. If the Edit button is clicked, it is replaced with Update and Cancel buttons, and the row is put into edit mode where text boxes appear in the cells allowing the values for the row to be edited. When the Cancel button is pressed, the row returns to the default appearance with an Edit button. When Update is pressed, the data source is updated with the changes made to the row and the row returns to the default appearance.

Unlike the Windows Forms DataGrid control, the Web Forms control does not automatically support in-place editing, and so a bit of code is required. Follow these steps to set up a DataGrid for in-place editing:

These steps prepare the DataGrid for in-place editing. Event handlers still need to be added for DataGrid events to enable in-place editing. Table 7-9 describes the events and associated generic event handling code.

Table 7-8. DataGrid event handler responses

Event	Handler response
CancelCommand	Cancel edit mode for the row being edited by setting EditItemIndex = -1
DeleteCommand	Delete the row
EditCommand	Put the row into edit mode by setting the EditItemIndex
UpdateCommand	Extract changes made from the in-place editing controls, update the data source with the changes, and cancel the edit mode for the row by setting EditItemIndex = -1

The example code for the solution shows actual implementations for these handlers.

The Web Forms DataGrid does not automatically support batch updates. To batch the updates, persist the changes to the Session variable with the following code when each change is made, rather than calling the UpdateDataSource( ) method:

// Store updated data in session variable to store data between
// posts to server.
Session["DataSource"] = dt;

Then call the UpdateDataSource( ) method when you want to update the data source with all changes made.

The Web Forms DataGrid does not support inserting records. The example shows a way to insert records outside of the DataGrid and resynchronize the DataGrid.