The In-Place Editing Feature

Adding New Rows

You probably want your DataGrid controls to serve as a replacement for desktop worksheets. One feature we haven t discussed yet is adding rows dynamically. Before going any further, though, let me shed some light on the reasons that row insertion is not part of the DataGrid control s set of functionality.

The DataGrid control is already complex. It comes with a wide range of events that, properly hooked up, offer extensive functionality. Adding even more events would have led to an overly complex and unwieldy control.

There are no serious reasons to drop the traditional implementation of the insertion functionality. Enabling the user to click a button somewhere on a page so that an input form pops up ready to collect and submit data is still an excellent way of solving the insertion issue. The add-row facility for DataGrid controls, described later in the chapter, is simply another insertion option. I find it particularly suitable when I m working with tables that have a limited number of columns. In other words, it is great for tables that do not deserve a full-fledged update module. When all is said and done, an add-row DataGrid control is really just the Web counterpart of the grid pane that both the SQL Server Enterprise Manager and Microsoft Access use to show table rows.

Setting Up the Add-Row Feature

Your user wants to be able to click a button and add a new blank row to the data grid (that is possibly already in edit mode), continue the editing process, and save any changes entered. While the user is working, you want to keep the underlying data store and the grid s Items collection perfectly synchronized.

The link button that enables the add-row functionality can be placed anywhere in the page. It makes sense to place it within the grid for example, in the footer. To place the button in the footer, set the grid s ShowFooter property to true. Next, define a footer template for one of your templated columns. Finally, hook up the ItemCreated event and remove some cells from the footer row. (This technique was fully demonstrated in Chapter 3.)

The footer template might look like the following:

<footertemplate> <asp:linkbutton runat="server" OnClick="AddNewRow" Text="Add new row..." Enabled='<%# IsLastPage() %>' /> </footertemplate>

The link button is not enabled all the time; the DataGrid control can be made of several pages, but only on the last page can the user click the button to add a new row. Figure 4-12 shows this link button activated only on the last page. You can easily rework this decision in your implementation. (Of course, this implementation does not need to be changed and remains fully functional if you use grids without pagination.)

private bool IsLastPage() { // CurrentPageIndex is 0-based... if (grid.CurrentPageIndex+1 == grid.PageCount) return true; return false; }

The IsLastPage function returns true or false, depending on whether the user is positioned on the last page.

Figure 4-12

The link that lets you add a new row is active only on the last page of the grid.

Adding Blank Rows

To add a row, a new DataRow object must be appended to the DataTable object that the grid is bound to. To wed seamless development and performance, the insertion must be done in memory first, and then submitted to the database server only when the changes have been committed by the user. I highly recommend that the DataTable object be cached locally to avoid accessing the database too many times. However, this aspect of the coding has more to do with the overall structure of the page than with the details of the add-row feature. As a general guideline, bear in mind that cached is better. In the code that follows, I used the Session object to cache the DataSet object that is used to populate the grid. Refer to the discussion in Chapter 2 under Using the Session Object to understand the pros and the cons of this choice.

public void AddNewRow(Object sender, EventArgs e) { // Grab the dataset DataSet ds = (DataSet) Session["MyData"]; DataTable dt = ds.Tables["MyTable"]; // Add a blank row DataRow dr = dt.NewRow(); dt.Rows.Add(dr); // If needed, assign default values // dr["column_name"] = ... // Update the in-memory DataSet. This step is needed to // refresh the grid properly, given the structure of the // UpdateView method. Session["MyData"] = ds; // Index of the new item in the page: last +1 int nNewItemIndex = grid.Items.Count; // If the page is full, move to next page. In this // case, first item. if (nNewItemIndex >= grid.PageSize) { grid.CurrentPageIndex ++; nNewItemIndex = 0; } // Turn edit mode on for the newly added row grid.EditItemIndex = nNewItemIndex; // Refresh the grid UpdateView(); }

After a new blank row is generated and added to the DataTable object, you update the cached copy of the data. This allows the grid to refresh properly, given the structure of the UpdateView method.

private void UpdateView() { DataSet ds = (DataSet) Session["MyData"]; grid.DataSource = ds.Tables["MyTable"]; grid.DataBind(); }

Notice that in this sample code, the UpdateView method works by retrieving the DataSet from Session. As shown in Figure 4-13, any changes must be saved to become effective.

Figure 4-13

Changes must be saved before they are applied.

Switching to Edit Mode

Let s see how to set the newly added row so that it works in edit mode automatically. You can activate edit mode for any row in the current page of the grid by setting the EditItemIndex property. The index of the new row by design is the last row in the page. Considering that the index is 0-based, the actual number of items in a page is provided by the following expression:

int nNewItemIndex = grid.Items.Count;

But a subtle problem arises. What if the last page is full and adding a new row violates the grid s page size setting? In this case, a new page must be added containing only the new row.

if (nNewItemIndex >= grid.PageSize) { // The new row is the first of the new page grid.CurrentPageIndex ++; nNewItemIndex = 0; } // Turn edit mode on for the newly added row grid.EditItemIndex = nNewItemIndex;

You don t have to worry about the new page. It will be generated automatically by the DataGrid control during the execution of the DataBind method, which is invoked by UpdateView. Properly setting the CurrentPageIndex and EditItemIndex properties is critical. Their values will determine the page displayed and the item rendered in edit mode. Figure 4-14 illustrates this example.

Figure 4-14

A new editable row has been added to the DataGrid control.

Updating the Table and the Grid

At this point, the DataGrid control has a new blank row. If you want to fill certain fields with default values, you can do that after creating the row. Your next step is to remove the row if editing is canceled or save the changes to the database if changes are committed. To understand how to accomplish this step, let s review how the internal state of a DataSet object changes when you add, delete, or modify rows.

The DataSet Object Commit Model

The DataSet, DataTable, and DataRow objects maintain a local cache of changes. When a row is modified, deleted, or added, its state changes to one of the values of the DataRowState enumeration. (See the .NET Framework documen tation for details.) Similarly, when a row is added, modified, or deleted from a table, the internal state of the table is altered, resulting in pending changes for the affected rows. When a table in a DataSet object has pending changes, the whole state of the DataSet object is modified to show pending changes. For all these objects, you detect changes by calling the HasChanges method. This method is overloaded to let you ask for specific changes for example, only insertions or deletions. The GetChanges method returns a table with all the current changes.

Pending changes can be either accepted or rejected. You can do that at any level:

  • If you accept or reject changes for a DataSet object, all changes in all tables are processed.

  • If you accept or reject changes for a DataTable object, all rows in the table are processed.

  • If you accept or reject changes for a single row, only that row is processed.

Accepting a pending change means that the row (changes always involve a row) updates are committed to the table. Rejecting a pending change rolls back the state of the table, and the table appears as though the change never occurred. By default, a data adapter returns a DataSet object in a committed state, that is, with no pending changes.

When you add a new row, you alter the state of the DataSet object, which now has one pending change. This change can be either rejected or accepted and is automatically handled by the DataGrid control and the data container objects. What you have to do is simply call the proper method at the proper time.

Canceling the Update

If the user cancels editing, the newly added row must be removed from the grid. You can easily accomplish this by using the RejectChanges method on the DataRow object.

public void CancelCommand(Object sender, DataGridCommandEventArgs e) { // Reset the edit mode for the current item grid.EditItemIndex = -1; // Reject changes on the last added row DataSet ds = (DataSet) Session["MyData"]; DataTable dt = ds.Tables["MyTable"]; DataRow drLast = dt.Rows[dt.Rows.Count-1]; if (drLast.RowState == DataRowState.Added) { drLast.RejectChanges(); // Move to previous page if the rejected row is the // only row in the current page if (grid.Items.Count == 1) grid.CurrentPageIndex--; } // Refresh the grid UpdateView(); }

You retrieve the DataRow object just added and then reject it. If the row is the only one in the current page, an error would occur because the CurrentPageIndex property points to an empty page. To prevent this error, you move back to the previous page. After the pending change is rejected, the edit item index is reset to -1, refreshing the grid and restoring a consistent situation.

Saving Changes with SQL Statements

Your solution for saving changes can differ according to the update mechanism you choose direct SQL statements or a batch update. Let s consider direct SQL statements for now. For SQL statements, you don t need to accept the pending change. In fact, after you update the underlying database, reloading the data set gives you an up-to-date table of records. So you can reject the change, execute the update, and then reload the data from the source. This approach ensures that your set of rows is a true snapshot of the database and takes into account any server-side changes due to triggers or auto-increment fields.

As discussed earlier in this chapter, to update the database, you can employ a stored procedure that returns output parameters for fields that are subject to server-side modifications. In this case, you might want to execute the update command first, then update the new row with data returning from the database, and finally accept the changes.

In the UpdateCommand event handler, you should determine whether you have to update a row or insert a new one. You can use the HasChanges method to see whether the last row in the table has been added. The full source code for the AddRows.aspx application is available on the companion CD.

DataTable dt = ds.Tables["MyTable"]; DataRow drLast = dt.Rows[dt.Rows.Count-1]; if (drLast.RowState == DataRowState.Added) { // Drop the last row from memory (if added) drLast.RejectChanges(); // Build the INSERT command string StringBuilder sb1 = new StringBuilder(""); sb1.Append("INSERT Employees (firstname, lastname, "); sb1.Append("titleofcourtesy, title, country) VALUES("); sb1.Append("@sFirstName, @sLastName, @sTitle, @sPosition, @sCountry)"); cmd.CommandText = sb1.ToString(); 

Once again, this approach works fine only when you are not using batch update. Notice that I don t explicitly add a value for the employeeid field because it is marked as an identity column whose values are automatically generated by SQL Server. Figure 4-15 shows how the add-row feature works.

Figure 4-15

The add-row feature lets the user add and edit a new row.

Saving Changes with Batch Updates

Batch update is an ADO.NET feature that lets you add, delete, and update records in memory and then submit all the changes in a single shot to the database server. A batch update is particularly useful in low-contention scenarios in which the likelihood of conflicting changes namely that the field you are going to update has been already updated since your last read is relatively low. (For a more detailed discussion of the pros and cons of ADO.NET implementation of the batch update, see Chapter 7 and Microsoft ADO.NET (Core Reference) by David Sceppa, Microsoft Press, 2002.)

If you support batch update in your application, chances are good that, at any time, you have several pending changes in addition to the one generated when the last row was added. For example, the list of pending changes could include previously added rows as well as deleted or updated rows. In this case, you should handle RejectChanges with extreme care.

To cancel an added row, you extract the last row from the DataTable object and reject only that row. To save the contents of a newly added row, you modify only the row with the data read from the input controls and then refresh the grid. You should not accept changes, however. This is a fundamental issue for the batch update. When you call the data adapter s Update method to submit changes to the server, only pending changes are considered. Accepting changes by using AcceptChanges will, at all levels (DataSet, DataTable, and DataRow), clear the pending state of changes so that no changes are sent to the server for updating.

A batch update presents another subtle problem you have to cope with. If the table has an auto-increment field (for example, an identity column such as employeeid in the Northwind s Employees table), how can you learn its true value? This value is generated by the database, and you cannot know it until you execute an update command on the server. So when you insert a new row in the grid, and you use a batch update in the page, what value will you be placing in the auto-increment field? Well, your only solution is to execute a physical insertion in the database. There are a few workarounds, though.

Normally, leaving the field blank does not pose practical problems, but you and your users have to accept the fact that the grid contents are not synchronized with what will become the database contents. To mark this pending state, you can fill the auto-increment field with a negative value and draw the row in a special fashion. The altered appearance of the row suggests to users the volatile nature of the changes. The value you use in the grid does not affect the update operation because that field will not be part of the update. Leaving an auto-increment field blank creates an error when an in-memory primary key is based on the column with that field. Note that in-memory indexes on a DataTable object have to be set explicitly through the PrimaryKeys collection. If this isn t done and if the DataTable object with the AddWithKey option isn t loaded, you can safely leave a key field blank. AddWithKey is one possible value for the data adapter object s MissingSchemaAction property.

note

You could work around the intricacies of auto-increment columns with GUID columns. In this case, the value is guaranteed to be unique throughout the table. Additionally, you don t need to rely on SQL Server to generate the value. One slight drawback is that a GUID column is 16 bytes long.

To discover the value assigned to an auto-increment field without reloading the data set after an update, you can use a stored procedure with an output parameter. If knowing this value in advance is really vital for your application, however, you can try the following:

  1. Insert an empty row in the database from the UpdateCommand handler.

  2. Get the auto-increment value, for example, by using the output parameter of an ad-hoc stored procedure.

  3. Accept the changes for the corresponding row in the grid.

  4. Modify the row with the auto-increment value.

  5. Submit the changes.

Accepting the changes and then modifying at least one field will modify the status of the row from Added to Modified. In this way, the data adapter object s Update method will try to submit the in-memory contents of the row to the database rather than insert the row.

tip

You can also try to guess the next auto-increment value for a table using the IDENT_CURRENT T-SQL function. This function returns the last identity value generated for a specified table in any session and any scope. The next value is the last one plus the identity increment for the column usually 1. Of course, you don t obtain a reliable value because the return value is subject to insertions that might occur in the meantime. You obtain the last identity for a table with the following code:

SELECT IDENT_CURRENT('table_name')



Building Web Solutions with ASP. NET and ADO. NET
Building Web Solutions with ASP.Net and ADO.NET
ISBN: 0735615780
EAN: 2147483647
Year: 2002
Pages: 75
Authors: Dino Esposito

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net