Cutting Edge: Building Editing Capabilities into the SqlDataNavigator ASP.NET Control

Building Editing Capabilities into the SqlDataNavigator ASP.NET Control

Download the code for this article:Cutting0205.exe (50KB)

n last month's column I began an ambitious project: building a SQL Server™-specific DataNavigator control that supports two-way data binding. The control I'll present in this column, SqlDataNavigator, is just an extension of last month's DataNavigator. The SqlDataNavigator ASP.NET control described here is meant to be the Microsoft® .NET counterpart of the Data control—an old Visual Basic® control that caused its share of headaches. The control moves from one record to the next according to a given order and displays each data row using a dynamically generated template. Last month I focused on the DataNavigator control's architecture and tackled some programming issues related to connectivity and data display. This month, I'll add editing capabilities to the control, making SqlDataNavigator actually support the "writing" channel of .NET data binding.
      The SqlDataNavigator user interface includes a new toolbar with buttons to edit and delete the current row, plus a third button to insert a new blank record. I also added Boolean properties to let you control the availability of each of these features individually. Before discussing how the control's internal structure must be modified to allow for the new functions, let me say a few words on the topic of record-based paging.

Paging Record by Record

      The SqlDataNavigator control has a pager bar, similar to the MoveNext and MovePrevious methods exposed by the Data control, that you can use to move through records sequentially. There's a significant difference between the Data control, which was tailored for the ADO Recordset object, and the SqlDataNavigator control, which has been designed with ADO.NET and ASP.NET in mind. The SqlDataNavigator control (see Figure 1) has no underlying open connection or server-side cursor to easily move you to the next record. Right now, ADO.NET does not support server cursors. In addition, in this implementation of the SqlDataNavigator control, I have deliberately chosen to build all the logic necessary for data access into the control class. What I need is a flexible tool that can quickly set up an attractive, efficient user interface for SQL Server tables. Furthermore, once full support for two-way data binding has been implemented, you'll have a rather powerful tool for creating edit interfaces for virtually any SQL Server table. But how does the control get the current record, and what kind of logic moves it from one record to the next?
      The GetRecord method performs the paging. It locates the record that occupies the CurrentRecordIndex ordinal position in the current order. The control's pager bar updates the index stored in the CurrentRecordIndex property. GetRecord utilizes the actual content of the ConnectionString and TableName properties to set up the connection and run the command. The method returns a SqlDataReader object and leaves the connection open until the data reader content is completely processed.
      How can you retrieve row n in a SQL Server table sorting by a given field? And speaking more generally, how can you select all the rows that fit in page n given a certain page size? The answer lies in the following steps:

• Select the minimum number of records that will definitely include the ones that you need according to a given order. The minimum number of records can only be P?N where P is the one-based page number and N is the page size. The records you need are clearly the last in the resultset.
• From this set, discard all the records not included in the last N positions, where N is the size of the page you need.

      Setting the SearchKeyField property allows you to specify the field by which to order. You also need to indicate a primary key field to make sure that records with duplicates in the sort field are not mistaken for one another. Since the SqlDataNavigator control works with a page size of 1, the following T-SQL statements retrieves the record in the fifth position, sorting by lastname:

  SELECT * FROM 
  (SELECT TOP 5 * FROM Employees ORDER BY lastname) AS t1 
   WHERE NOT EXISTS 
  (SELECT * FROM (SELECT TOP 4 * FROM Employees ORDER BY lastname)
   AS t2 
WHERE t1.employeeid=t2.employeeid)

Notice that there are items in the code that represent parametric information such as the table name, sorting field, the key, and the record number to retrieve. If you need to retrieve the record in position N with a page size of 1, then you must discard the first N-1 records. Unfortunately, there's no way to write a command like this using SQL parameters. The reason is that the TOP and the ORDER BY clauses do not accept variable parameters. So I resorted to the following code for formatting placeholders:

  StringBuilder sb = new StringBuilder("");
sb.Append("SELECT * FROM (SELECT TOP {0} * FROM {1}
          ORDER BY {2}) AS t1 ");
sb.Append("WHERE NOT EXISTS (SELECT * FROM (SELECT TOP {3}
          * FROM {1} ");
sb.Append("ORDER BY {2}) AS t2 WHERE t1.{4}=t2.{4})");
strCmd = sb.ToString(); 

      The SQL Server-based .NET data provider deals with this code, and any other T-SQL code, in a relatively efficient manner. The SQL code is transmitted through the sp_executesql system procedure. For programming ease, you might want to consider using format placeholders in your code.

Adapting the Control's Structure

      At the foundation of the SqlDataNavigator user interface there is a highly customized DataGrid Web control. The DataGrid is already predisposed toward in-place editing, so making this feature show off the SqlDataNavigator control should not be really hard. To set a DataGrid to edit mode you normally add a special breed of column—the EditCommandColumn column type—and handle the events it fires upon clicking. The EditCommandColumn object allows you to specify the text for the links that will edit the row and then save or cancel any changes. Note that you don't strictly need such a column to set a grid to edit mode. What really matters is that you run a piece of code that properly sets the DataGrid's EditItemIndex property.
      In Figure 2, you can see an updated layout for the SqlDataNavigator control. There are two key enhancements over the structure I introduced last month. First, in addition to the record navigator and the search box, the pager bar also contains a small toolbar whose buttons allow for the three editing operations: insert, update, and delete.


Figure 2 SqlDataNavigator Control

      If you permit updates, you must also provide for buttons to save or cancel changes. Such controls are automatically provided by the DataGrid infrastructure as long as you define an edit command column. For the sake of consistency, you should maintain the interface and the working metaphor of the DataGrid control. The EditCommandColumn object mostly provides for a row-specific hyperlink to trigger the edit event. If the control's interface is capable of providing an alternate way of entering data in edit mode, then you can have an editable grid even without an EditCommandColumn, or you can keep such a column hidden from view.
      During the initialization step, you add two columns to the grid. One is a TemplateColumn object for displaying the contents of the record, while the second column is an EditCommandColumn object hidden by default. The idea is to let another piece of the interface fire the edit and when this happens, toggle the visibility of the edit column to make it show the standard Save and Cancel link buttons. Falling into this predefined flow is important because it saves you a lot of coding. You don't have to worry about the layout of the OK and Cancel buttons or about their handlers. The DataGrid does it for you and fires two tailor-made events when users click OK or Cancel.
      One of the buttons in the new toolbar can easily enable editing by just setting EditItemIndex to a non-negative index. Since the grid has exactly one item, this index must necessarily be 0. Figure 3 shows the initialization code of the grid that makes the SqlDataNavigator control. Notice that the EditCommandColumn object has the Visible property set to False, which hides it from view. Also notice that there's no handler for the EditCommand event. EditCommand is one of the standard events that the DataGrid control fires during its activity. In particular, EditCommand is raised when the user clicks on a link within the edit or when he or she clicks on any button within the grid that has a command name of edit. In response to the event, the programmer if set EditItemProperty to the correct index value to indicate which item should redraw in edit mode and refresh the view. Since I'm not putting the grid into edit mode using the column, I can more easily accomplish the same tasks elsewhere in the code if something happens to change the working mode of the grid. A more elegant approach would be to use a custom button, with a command name of edit. In this case, the grid would still fire an EditCommand event—a piece of the standard programming interface—though it would be unrelated to the EditCommandColumn.
      The code in Figure 4 creates the toolbar with buttons for insert, delete, and edit operations. Each button is governed by a Boolean property—AllowInsert, AllowEdit, and AllowDelete—which determines whether the button is enabled. Let's see what happens when you click on the Edit button.

Edit Implementation

      The structure of the handler that takes the edit command is pretty straightforward. The code sets up the control's interface to reflect the new working mode and then orders a data refresh:

  void OnEditCurrentRecord(Object sender, EventArgs e)
{
    SetupWorkingMode(WorkingMode.Edit);
    BindDataToGrid();
}

The feasible working modes for the control are defined in a custom enum object called WorkingMode:

  public enum WorkingMode : int
{
   View = 0,    
   Edit = 1, 
   Insert = 2    
}

      When the control is not in view mode (in other words, it's editing or inserting), the button bar is hidden from view to avoid abruptly halting ongoing operations. At the same time, the EditItemIndex must be 0 (which refers to the first item in the grid page) and the EditCommandColumn must be visible:

  void SetupWorkingMode(WorkingMode m)
{
  bool bIsInViewMode = 
    (m==WorkingMode.View);

  // Hide/Show the button bar 
  ShowButtonBox = bIsInViewMode;

  // Set the new working mode
  Mode = m;    

  m_grid.EditItemIndex = (bIsInViewMode ?-1 :0);
  m_grid.Columns[1].Visible = !bIsInViewMode; 
}

            Figure 5 shows the SqlDataNavigator control while editing a record. As you can see, the button bar is hidden while the EditCommandColumn is visible. This column features the Save and the Cancel buttons. Clicking on either of these two buttons would fire the standard pair of events—UpdateCommand and CancelCommand—for you to persist or cancel changes.
      So much for the editing infrastructure, but what about the edit template? By default, when a DataGrid control enters edit mode, only data-bound columns (class BoundColumn) are automatically rendered through textboxes. In this case, the internal grid control has just one column (plus the edit command column). Last month, I discussed how to dynamically build an HTML template for display purposes. This month I will build the template again to allow for editing.
      The essential code that outputs the record shown in Figure 5 is repeated here:

  TemplateColumn tc = new TemplateColumn();
tc.ItemTemplate = new DataNavigatorItemTemplate();
tc.EditItemTemplate = new DataNavigatorEditItemTemplate();

The class DataNavigatorEditItemTemplate inherits from ITemplate and binds to the data using editable controls like textboxes, dropdown lists, and checkboxes. In Figure 6 you see the outline of the code for the template class. The edit template creates a placeholder control and then handles its DataBinding event. When the placeholder gets bound, the class dynamically creates and renders an HTML table. The procedure looks similar to what happens for display, but with a few significant differences.
      As you can see in Figure 5, textboxes are used in lieu of labels. Later in this column, I'll discuss how to utilize dropdown lists and checkboxes if the data allows for it. Using textboxes poses a few modeling problems. For example, if the text is too long, you must switch to a multiline textbox; if you handle dates, then you should employ a short, simple format. But what if the field you're about to edit is set as auto-number or read-only? What if it does not accept null values? How do you handle a field that has binary contents?
      You need to know about the schema of the table you're editing. Unfortunately, you need to access schema information from within a template class that has been passed only the structure of the data item. This latter point represents just one instance of a more general problem. A template class is a class that is distinct from the control, yet it needs to access and read some of the configuration settings, among which are schema table, colors, and bindings. How do you pass all this information down to the template class? Well, the edit template class is a class whose programming interface is under the programmer's total control. The only requirement is that it has to implement the ITemplate interface. Nothing prevents you from adding public properties to pass all the information needed. You get schema information about a table by calling the GetSchemaTable method on the SqlDataReader object. GetSchemaTable returns a DataTable object whose rows evaluate to the table's columns. The columns of the schema table have predefined names like AllowDBNull, IsReadOnly, ColumnSize, IsPrimaryKey, and so on. The following code checks whether a given column is an identity column:

  DataRow r = SchemaTable.Rows[i];
bool bIsIdentity = (bool) r["IsIdentity"];

      Prior to refreshing the grid, you make sure that the template classes (both item and edit item templates) have been filled with all the configuration information they need:

  DataTable dtSchema = GetSchemaTable(); 
TemplateColumn tc = (TemplateColumn) 
    m_grid.Columns[0];
DataNavigatorEditItemTemplate dneit;
dneit = (DataNavigatorEditItemTemplate) 
    tc.EditItemTemplate;
•••
dneit.SchemaTable = dtSchema; 

      Carrying schema information in the body of the edit template class makes it easy for you to implement some cool features such as marking the field as required if it does not accept nulls, preventing changes on read-only fields, or using multiline controls if the text or the column size can exceed a certain length. In Figure 5 you can see some of these features in action. For example, asterisks mark fields where nulls are not allowed and the employeeid field, which is an identity column, is disabled.

Creating a Column Binding Context

      To improve the user's edit experience, you might want to configure each column individually. For example, you may want to pick up the value for that column from a lookup table or render a certain piece of content as a Boolean value. In such cases, the textbox is no longer the most suitable control for editing. You might also want to keep fields as read-only in your application or format them in a special way. For this purpose, I created a new data structure called DataBoundField (see Figure 7). This class describes how a field should be rendered for display and edit. The class represents the binding context for the column and indirectly adds a great deal of flexibility to the overall interface of the SqlDataNavigator control. For example, you can control the label text and the tooltip of the field and decide whether you want it to be displayed with a dropdown list or a checkbox. (More in a moment.) The SqlDataNavigator control exposes a DataBindings property that is an instance of the ListDictionary class:

  public ListDictionary DataBindings 
  {
      get {return (ListDictionary) 
          ViewState["DataBindings"];}
      set {ViewState["DataBindings"] = value;}
  }

      The contents of the property is persisted across multiple page requests. ASP.NET does not know, though, how to serialize the contents of the DataBoundField. If you want to be served the default way, just mark the class with the [Serializable] attribute. Beware, though, that this approach is not necessarily optimal and could lead to too much code being persisted. Check the MSDN documentation to explore alternative approaches, such as writing a type converter for the class.
      The following code shows you how to configure the user interface of the SqlDataNavigator control using bindings:

  DataBoundField d = new DataBoundField();
d.MultiLineRows = 5;
d.FormatText = "<i>{0}</i>";
d.LabelText = "Personal Notes";
d.ToolTipText ="Some personal notes";
data.DataBindings.Add("Notes", d);

First, you create a new DataBoundField object and set some of its properties. Then, add the object to the DataBindings collection using a key value that matches the field name. The control internally locates the item using the Contains method of the ListDictionary class and passes the name the column just read off the schema in a string. Since the Contains method is case-sensitive, you must pay attention to how you write the column name. A better approach would be to derive a custom dictionary object from DictionaryBase and make it work irrespective of the key case.


Figure 8 Configuring the UI

      Figure 8 shows the modifications in the user interface of a sample page. The Notes column now has a different label and tooltip that are user-defined. In addition, the value has been formatted to display with an italic font, and when in edit mode the Notes field will contain five rows of a multiline textbox instead of the default three lines.
      The DataBindings collection can do much more than I've described so far. For example, you can associate a data-bound list of choice when editing a given foreign-key field and resolve the key through an automatic JOIN when in view mode.
      The ReportsTo column of the Employees table (from the Northwind database) points to the employee ID of the boss. The following code snippet illustrates how to pick up his ID from the existing employees while editing and how to display the full name otherwise:

    DataBoundField d1;
  d = new DataBoundField("reportsto", 
      "employees", "lastname", "employeeid");
  d.LabelText = "Boss";
  d.FormatText = "The boss is <b>{0}</b>";
  d.ToolTipText = "Name of the boss";
  data.DataBindings.Add("ReportsTo", d);

      The alternate class constructor defines a lookup table for the field. You specify the name of the field to be mapped, the lookup table, and the fields to use to populate the dropdown list control for text and value. There's a bit of redundancy here as the field name appears both as the key of the collection item and as the BoundField member of the DataBoundField object. Figure 9 shows the user interface of the record being edited in this way; Figure 10 shows the ad hoc formatting when in view mode.


Figure 10 Ad Hoc Formatting

The SqlDataNavigator control automatically performs an internal query to retrieve the information in the user interface. You can make this particular aspect of programming more effective and flexible by firing a custom event to the page requesting the data. The class constructor used to look up on external tables sets the CtlType property of the DataBoundField to ControlType.DropDown:

  public enum ControlType : int
{
    TextBox = 0,
    DropDown = 1,
    CheckBox = 2
}

      If the data to render lends itself to representation as a binary type of information (yes/no, on/off, true/false), you can use a checkbox control instead of textboxes or lists. For example, in the Employees table, the ReportsTo column contains the ID of the boss. The column allows for nulls, meaning that the given employee does not report to anyone. Although not strictly Boolean, this piece of information can be adapted to display through a checkbox that answers the question: does he or she report to anyone? If the value of the column is greater than 0, the employee has a boss; otherwise, he or she does not report to anyone.


Figure 11 Displaying Binary Info

      Figure 11 shows the output of the following code:

  DataBoundField d = new DataBoundField();
d.CtlType = ControlType.CheckBox;
d.ToolTipText = "Shows whether the employee reports to someone";
d.LabelText = "Has a boss?";
data.DataBindings.Add("ReportsTo", d);

In view mode, the SqlDataNavigator control shows Yes/No text. In edit mode you have a checkbox whose text is always the true string. The value of the column is converted to a Boolean and the result determines whether or not the checkbox is checked. The control's code also ensures that any null values encountered are rendered as false:

    chk.Checked = false;
  if (!dbdr.IsDBNull(i))
    chk.Checked = Convert.ToBoolean(dbdr[i]);

      However, this is not necessarily a good approach and potentially leads to some data inconsistency. The SqlDataNavigator assumes that you use a checkbox-based representation of the data either if you have truly Boolean data or if you want to abstract over the data. In the latter case, though, you won't allow for editing. In situations in which you must handle buttons with three possible states (true, false, or nothing) you are better off adding a radio button list rather than using checkboxes.

Handling Null Values

      In effect, the optional presence of null values in some fields poses a few design issues that can be summarized in the following question: how do you let users set null values? In the SqlDataNavigator control I assume that if you edit through textboxes, you are going to enter non-empty strings. So if the textbox turns out to be empty at save time, the control sets that column to null. Columns rendered as checkboxes handle the null value as false, but what about dropdown lists? In this case, the control gets slightly smarter and recognizes the nullity as a special case. For example, the ReportsTo column contains the ID of the boss or null. In view mode, employees without bosses can simply be rendered with an empty label. What happens if you need to update the ReportsTo field to hold the value null?
      To deal with this issue, I decided to add an extra item to the dropdown list to denote the null value:

  if (!dbf.Required)
{
   DataTable dt = ds.Tables[0];
   DataRow rowNull = dt.NewRow();
   rowNull[dbf.DataTextField] = "<NULL>";
   ds.Tables[0].Rows.Add(rowNull);
}

If the field accepts nulls then you create a new row and add it to the source table of the dropdown list. To enhance the user interface, you set the Text property of the list item with any text that means NULL. Figure 12 shows how gracefully the SqlDataNavigator control handles the contents of the ReportsTo column.


Figure 12 Reports to Column

Inserting and Deleting Records

      The edit mechanism is by far the most complex part of the navigator, and it is the core engine of the editing capabilities of SqlDataNavigator. When you click to insert a new record, the control switches in insert mode and refreshes the grid. There are only a few minor differences between the edit and the insert mode:

  void OnInsertNewRecord(Object sender, EventArgs e)
{
   SetupWorkingMode(WorkingMode.Insert);
   BindDataToGrid();
}

      Actually, both the Insert and the Edit buttons trigger the same engine—the in-place editing feature of the underlying DataGrid. The idea is that the insertion acts as the update of the record currently displayed. When it comes to this, the SqlDataNavigator control detects the insert mode and adapts the user interface. For example, it clears out all the textboxes, sets a few of them to default values—the DefaultValue field of the DataBoundField class—and, more importantly, runs a different procedure to save the data. Figure 13 shows the typical insertion mask with some default values and automatic handling of auto-increment columns.


Figure 13 Typical Insertion Mask

      This way of working isn't problem-free, however. Just because the insert is implemented as a special editing of the current record, the SqlDataNavigator control is rather unusable with empty tables. To work around this problem, you can first count the number of records in the data source and add a fictitious row if the count is zero. Instead of connecting the grid to a SqlDataReader object, you use an in-memory DataTable object with just one blank row. When the user clicks Save, you then collect the data and run an INSERT statement.
      The Delete operation is even simpler. Click on the toolbar and the control arranges and executes a DELETE. To make your application cool, you can even add script to ask for confirmation:

  String js = "return confirm('Do you really want to 
                             delete the record?');";
btn.Attributes["onclick"] = js;

Upon creation of the Delete button, you just add an onclick item to the button's Attributes collection.

Finalizing the Update

      When the user chooses to save the changes he has made, he clicks on the Save button and the following code performs a number of possible operations:

  void OnUpdateCommand(Object sender, DataGridCommandEventArgs e)
{
   switch(Mode)
   {
      case WorkingMode.Edit:
         SaveCurrentRecordChanges();
     break;
         case WorkingMode.Insert:
         InsertCurrentRecord();
         SetVirtualItemCount();     
         break;
   }

   SetupWorkingMode(WorkingMode.View);
   BindDataToGrid();
}

      Depending on the working mode, the control updates the current record or inserts a new one. Both the INSERT and the UPDATE statements are built by concatenating text into a StringBuilder object. The values are extracted from the page using the Page.Request.Form collection and the textbox control's unique ID. If you snoop through the source code, you see that a lot of facilities such as the automatic duplication of single quotes are already implemented.
      The neat separation between the various states of the control makes it possible for clients to display the SqlDataNavigator control already in edit or insert mode. As the sample client WebForm demonstrates to you, the SqlDataNavigator control is a table-driven form for both editing and input that you can also customize to a certain extent.
      So much for beta stages; next month I'll discuss the final version of the SqlDataNavigator control with a slew of new features, among which will be the ability to import user-defined templates for view and edit modes. Until next month!

Send questions and comments for Dino to cutting@microsoft.com.