From the June 2002 issue of MSDN Magazine.

he SqlDataNavigator control that I have worked on in the last two installments of Cutting Edge is a Web control that supports two-way data binding. The control features a form-based user interface to display all the contents of a table row. The form's data-bound elements are surrounded by a toolbar with navigation buttons and links for operations such as insertion, deletion, and update. Figure 1 shows the control. Last month, I extended it to support joined fields in both view and edit mode. As a result, foreign key fields display the contents of an external field and let you get the foreign key value from a dropdown list. The control takes care of all the code necessary to set up this kind of machinery, so you only have to specify the name of the external table and the fields involved.


Figure 1 SqlDataNavigator

      In this column, I'd like to address some of the user feedback I've received while building the control. In particular, I'll discuss how to bind the foreign key driven dropdown list to more complex expressions that involve multiple fields (like lastname, firstname instead of simply lastname). Next, I'll create tri-state checkboxes to cover situations in which a logically Boolean field can contain NULL values. Last month I discussed how to attach some client script code to the Delete button to prompt for confirmation. Now I'll go one step further and make this text context-sensitive. Finally, I'll discuss the two most wanted enhancements: custom events to simplify the synchronization of the control's detail data with a master menu of records and a custom layout for both view and edit modes.

Expression-based Columns

      Some ASP.NET controls let you bind properties to field names. A typical example is the DropDownList control, whose DataTextField and DataValueField properties are bound to field names for the Text and Value attributes of the underlying HTML tag. Suppose that you want to fill a dropdown list with employee's names. Typically, you would use the employee ID for Value and a concatenation of first and last name for Text. In doing so, you realize that the programming interface of all ASP.NET data-bound controls permits you to bind only one data source field, not an expression. You can work around this limitation in two ways: either modify your query to return a custom expression-based column, or you create an extra in-memory DataColumn object and set its Expression property to any expression you want. Notice that the DataColumn's Expression property supports the same syntax for expression-based columns as SQL Server™:

  Dim dt As DataTable = New DataTable()
•••
adapter.Fill(dt)
dt.Columns.Add("FullName", _
               GetType(string), _ 
               "lastname + ', ' + firstname")
theDropDownList.DataSource = dt
theDropDownList.DataTextField = "Name"
  

      I'll explain how you can integrate this with the SqlDataNavigator control. Last month, I defined the DataBoundField class to let you customize the look of a given field. The class has properties like TableName, BoundField, DataTextField, and DataValueField, which you use to declare the external table and fields to look up in order to display the contents of the bound field. I'll add a new Expression string property and modify one of the constructors to accommodate this (shown in C#):

  public DataBoundField(String boundField, String table,
   String expr, String textField, String valueField)
   {
      SetDefaults();
      CtlType = ControlType.DropDown;
      BoundField = boundField;
      TableName = table;
      DataTextField = textField;
      Expression = expr;
      DataValueField = valueField;
   }
  

The DataTextField member is now merely the name of the foreign display field, while Expression points to the actual column expression to retrieve. If Expression is empty, then DataTextField is seen as both the name and the expression for the field to fetch. Figure 2 shows the C# code that the SqlDataNavigator uses to take into account any column expression for foreign key fields that need special rendering.


Figure 3 NULL Value

Figure 3 demonstrates the new feature in action after executing the following code:

  DataBoundField d1;
d1 = new DataBoundField("ReportsTo", 
                        "Employees", 
                        "LastName + ', ' + FirstName", 
                        "Employee", "EmployeeId"); 
d1.LabelText = "Boss"; 
d1.FormatText = "Reports to <b>{0}</b>"; 
d1.ToolTipText = "Name of the boss"; 
data.DataBindings.Add("ReportsTo", d1); 
  

      The new column is called Employee and is used both as the input for the FormatText and to populate the visible portion of the dropdown list. Notice in Figure 3 that the first option available is "None of these." Last month, I discussed how to add an extra item symbolizing the NULL value if the column allows for NULLs. Another minor enhancement in this version is the ability to display text for NULL. The NullText property applies to all the fields displayed and not to a particular one. To be able to customize the null text for each field, add a NullText property to the DataBoundField class and modify the control's rendering code accordingly.

Tri-state Checkboxes

      Often you have database fields that can contain a Boolean value or nothing. How do you cope with this? In view mode, things are not that complicated. You just check the field value against NULL and leave the field empty if needed. If you're using a data reader, then the IsDBNull method tells you whether the value at a given column position is null:

  if (!dbdr.IsDBNull(i))
{
   buf = dbdr[i].ToString();
   if (dbf.FormatText != "")
      buf = String.Format(dbf.FormatText, dbdr[i]);
}
else
   buf = "";
  

      In edit mode, things get a bit more problematic because you must explicitly provide the user with a way to set the NULL value. For this reason, a classic two-state checkbox is not sufficient.
      A tri-state checkbox is a group of three radio buttons, two of which represent the false and the true states and a third which stands for the undefined state. You indicate that a given field must be rendered with a tri-state checkbox using a new entry in the ControlType enum I introduced last month:

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

None of the standard ASP.NET controls works as a tri-state checkbox, but implementing one is easy. It's simply an instance of a RadioButtonList control that spans horizontally and contains three child list items:

  RadioButtonList radio = new RadioButtonList();
radio.RepeatDirection = RepeatDirection.Horizontal;
radio.RepeatLayout = RepeatLayout.Flow; 
  

      Figure 4 shows the code in SqlDataNavigator that creates a tri-state control. The text for each state is found in the BooleanText and UndefinedText properties of the DataBoundField class. In particular, BooleanText points to an enum value representing possible ways to render Boolean information: Yes/No, On/Off, True/False.
      To demonstrate the tri-state checkbox in action, I added an extra column to the test database. For demo purposes, I'm using a custom SQL Server database called BWSLib that is a stripped-down version of Northwind. The new column, ReportsToFuller, holds True if the employee reports to Mr. Fuller, False if she reports to someone else, and NULL if the employee has no direct boss.


Figure 5 Reports to Fuller

The following code produces the output in Figure 5:

  DataBoundField dbf = new DataBoundField();
dbf.CtlType = ControlType.TriStateBox;
dbf.UndefinedText = "Has no boss";
dbf.BooleanText = BoolFormat.YesNo;
dbf.LabelText = "Reports to Fuller?";
data.DataBindings.Add("ReportsToFuller", dbf);
  

      Notice the value assigned to each child radio button in Figure 4. The ListItem object that represents True has a Value property of 1, whereas the value of the False object is 0. You could choose any other value for the undefined state. In my implementation, I decided to use the empty string in such a way that it falls in the same code branch that already handles NULL values for textboxes or dropdown lists. The following code retrieves the value of the currently selected radio button:

  String buf = 
Page.Request.Form[strTristateUniqueID];
  

The value is 0 or 1 if a Boolean button is selected and it's the empty string if NULL is the value to store in the database. As I explained in the previous columns, to retrieve the current postback value for dynamically created controls you must explicitly resort to Page.Request. While Page.Request is the only tool you have to retrieve postback values, the ASP.NET runtime normally shields you from these details. Interestingly, the IDs of the controls are not always the IDs you set. Controls embedded in other controls have a fully qualified, colon-separated ID. For this reason, always use the control's UniqueID property with Page.Request. This becomes particularly important when your controls are grouped in a container control. The value of a textbox with an ID of "TheTextBox" is stored in a Request slot with the same name if the control is hosted directly in an ASPX page. The ID changes if you move the same control in an ASCX file. For example, if the ASCX has an ID of "ThePagelet," the fully qualified ID for the original textbox will be ThePagelet:TheTextBox.

Context-sensitive Delete Text

      The SqlDataNavigator control allows you to delete the currently displayed record. Before performing the deletion, the control requires confirmation. This is accomplished with some simple JScript® on the client:

  String js = "return confirm('Do you want to proceed?";
btnDelete.Attributes["onclick"] = js;
  

      Of course, it would be nice to have the message indicate which entry is being deleted. If you had a Delete button for each row of a list-bound control, you could intercept the ItemCreated event and set the item's Attributes property.
      The SqlDataNavigator control creates the Delete button during the pager bar's ItemCreated event. But you can't access the underlying data item then. For that you have to wait for the ItemDataBound event, which comes immediately after ItemCreated:

  void OnItemDataBound(Object sender, DataGridItemEventArgs e)
{
   ListItemType lit = e.Item.ItemType;
   if (lit == ListItemType.Item && AllowDelete)
      SetScriptForDeleteButton((DbDataRecord) e.Item.DataItem);
}
  

      When the unique item in the page is being bound to data, your code attaches some script code to the Delete button (see Figure 6). The text for the confirm method of the HTML window object is determined by looking at two new properties of the SqlDataNavigator control: DeleteTextFormatString and DeleteTextParams. The former represents the text to display, defaults to a generic confirmation question, and may contain placeholders for database fields. For example:

  You are about to delete the employee\n\n"{0}"\n\n
Do you want to proceed?
  

      The DeleteTextParams is an array of field names that gets passed to String.Format to fill all the placeholders in the DeleteTextFormatString property. If not NULL, the field names in the array are expanded to the actual values that those fields have in the current row and then used to fill the message string.


Figure 7 Bye-bye Employee

The following code creates the dialog box shown in Figure 7:

  ArrayList a = new ArrayList();
a.Add("Name");
data.DeleteTextFormatString = deleteText;
data.DeleteTextParams = a;
  

      If you want to use newline characters or any other characters that need to be escaped, you'll have problems. The JScript newline character is \n, just like in C#. When your C#-based ASP.NET page writes HTML code to the output stream the \n character is treated as a newline character. As a result, the JScript code has an unterminated string constant and fails. To allow C#-based stream objects to write text verbatim, prefix the literal string with the @ symbol:

  String js = @"Deleting employee\n\n""{0}""\n\nConfirm?"
  

Notice that any quote symbol must be doubled to appear correctly in the final output. If you read the string from a file or never treat it as a literal, this is not necessary. If you use Visual Basic® instead, you don't need to handle this because \n means nothing special to the compiler whatsoever.
      The dialog box that displays the message is completely browser dependent. In Figure 8 you can see how the control looks under an old version of Netscape Communicator. There was no way for you to change the button captions, title bar, or other elements of the dialog box. On the other hand, the script uses the window. confirm method as part of the HTML Script Object Model whose actual implementation is left to the browser. If you want strict control of the script dialog boxes you could always resort to a modal HTML dialog whose interface is based on an HTML page. Use the window.showModalDialog method to show it.

Keep in Sync with Events

      The SqlDataNavigator control lets you provide a detail view with very little effort. It works in conjunction with another control acting as the master view, enabling you to select a row on the master and use the navigator control to show the details. If you like this idea better than the static user interface shown so far, read on. The data navigator control also allows you to move between records sequentially and by key or position. So what's the position of the master when the details view moves to a new record? Ideally, the master should update its current selection to reflect the position change in the navigator. How could you implement this without using events? Well, the SqlDataNavigator control exposes two events: CurrentChanged and DataChanged. The former fires whenever the navigator moves to a new record. The DataChanged event fires whenever the underlying database is updated through the data navigator control after a DELETE, UPDATE, or INSERT operation has been performed:

  public event DataNavigatorChangedEventHandler CurrentChanged;
public event DataNavigatorUpdatedEventHandler DataChanged;
  

      Because both events need to pass custom data to the client, they need specialized delegates:

  public delegate void DataNavigatorChangedEventHandler(
  Object sender, DataNavigatorChangedEventArgs e);
public delegate void DataNavigatorUpdatedEventHandler(
  Object sender, DataNavigatorUpdatedEventArgs e);
  

      Both of the event data structures inherit from EventArgs and add just one or two new data members:

  public class DataNavigatorChangedEventArgs : EventArgs
{
   public int Position;     
}
public class DataNavigatorUpdatedEventArgs : EventArgs
{
   public UpdateType OperationType;     
   public Object KeyValue;            
}
  

      The Position member contains the ordinal position of the new record in the data set. Of course, this only works as long as the master and the navigator follow the same order. OperationType returns an integer value denoting the type of operation that occurred (insert, delete, or update). Finally, KeyValue is the value of the search field in the newly added record. The search field in the SqlDataNavigator control is the field you use to perform direct queries. This field is not necessarily the primary key. For example, in an Employees table, the search field is more likely to be LastName than EmployeeId. Notice that the value returned by KeyValue is not completely reliable because you might choose a search field that is subject to triggers or auto-increment features. If you use it, make sure you check against NULL. The following code shows how the control fires the CurrentChanged event:

  DataNavigatorChangedEventArgs dncea;
dncea = new DataNavigatorChangedEventArgs();
dncea.Position = CurrentRecordIndex;
OnCurrentChanged(dncea);
  

      A client ASP.NET page registers handlers for custom events in exactly the same manner as for system events:

  <expo:SqlDataNavigator runat="server" id="data" 
    OnCurrentChanged="CurrentChanged"
    OnDataChanged="DataChanged" />
  

      Figure 9 shows a typical event handler that synchronizes the content of a master DataGrid with the newly selected record in the navigator. As you can see, the code calculates the page that contains the new record. In doing so, I use the integer division operator (/). In C#, this operator returns the integer portion of the result of the division and truncates any fractional portion. In Visual Basic it rounds the results up or down. To get only the integral portion of the dividend in Visual Basic, use the \ operator (backslash). Figure 10 shows the sample application that illustrates a master menu and the navigator working in sync.

Custom Layouts

      You can customize the layout of the SqlDataNavigator control. Two new properties, ItemTemplatePage and EditTemplatePage, provide for user-defined ASCX files that will be used in lieu of the standard user interface. The SqlDataNavigator is now more flexible than last month, as you can see in Figure 11.


Figure 11 Improved SqlDataNavigator

Basically, the templated column of the constituent grid has two options to render the data row contents. The column's template is hosted by a PlaceHolder control. What really changes is the contents of the Controls collection of this unique PlaceHolder control. If ItemTemplatePage or EditTemplatePage are empty, the place holder contains an instance of a dynamically created Table control with a column for the field name and a column for the corresponding values. If ItemTemplatePage or EditTemplatePage point to valid URLs, then the place holder is simply an added instance of the specified ASCX control, as shown here:

  PlaceHolder ph = (PlaceHolder) sender;
Page thisPage = ((Control)sender).Page;
if (ItemTemplatePage != String.Empty)
{
  UserControl uc;
  uc = (UserControl) thisPage.LoadControl(ItemTemplatePage);
  ph.Controls.Add(uc);
  return;
}  
  

      Notice that this code runs within the instance of the template class you assigned to the column's ItemTemplate property. In other words, this code is part of the SqlDataNavigator class and executes when the template class processes its DataBinding event. That's why you need some extra code to grab the reference to the hosting page.
      Is there any special requirement on the user control you use to render the contents of the current data row? As long as you employ it in view mode, the only requirement is that it must have obtained access to the underlying data item. This can be achieved in a number of ways. The easiest is to use DataBinder.Eval and data binding expressions, like so:

  <%# DataBinder.Eval(container, "fieldname") %>
  

      The challenge is determining the right container. The ASCX control is ultimately hosted by a DataGrid control so the container is an object of type DataGridItem. The object's Container property, though, returns the first level container, which is not necessarily the DataGridItem. In fact, if you happen to put the SqlDataNavigator control in a parent ASCX control (as I did in the sample application) then Container returns just the container, that is, the outer ASCX. What you really need here is the naming container. The following code snippet shows a better way to initialize the user control:

  public void Page_Load(Object sender, EventArgs e)
{
   DataGridItem dgi = (DataGridItem) this.NamingContainer;
   DbDataRecord dbdr = (DbDataRecord) dgi.DataItem;
   // Initialize the ASCX using dbdr["fieldname"]
}
  

      Bear in mind that DbDataRecord is the structure that represents an item of data accessed through a data reader object. To be able to use it, import the System.Data.Common namespace. In the sample application in Figure 12, I've given each data-bound control the same ID as the data column. This is arbitrary, so feel free to change it if you don't like the idea. The SqlDataNavigator control allows you to set view and edit templates individually so you can have a customized view but rely on the standard interface for editing:

  data.ItemTemplatePage = "nw_empview.ascx;
  

      In edit mode, the requirements for the ASCX control are a bit stricter. For example, different actions are required for editing records and adding new ones. In addition, SqlDataNavigator and the child control must agree on the means by which the navigator retrieves the updates. Normally, a user control inherits from the base class UserControl, which lacks specialized functionality. But a more advanced set of functions is needed here. Figure 13 shows the code for a new base class: DataNavigatorLayoutControl. The property Row spares you the details of retrieving the naming container in the ASCX source code, while IsNewRecord lets the ASCX know whether or not the user is expected to insert a new record or update an existing one. From the ASCX's perspective, IsNewRecord dictates whether the fields have to be initialized with current or default values:

  if (EditTemplatePage != String.Empty)
{
   DataNavigatorLayoutControl dnlc;
   dnlc = (DataNavigatorLayoutControl) 
          thisPage.LoadControl(EditTemplatePage);
   dnlc.IsNewRecord = IsNewRecord;
   dnlc.Row = dbdr;
   dnlc.ID = "EditTemplate";
   ph.Controls.Add(dnec);
   return;
}
  

      How can you make an ASCX user control inherit from a given class? First of all, the base class must be available as an assembly. You can take the code in Figure 13 and compile it into to a separate DLL or leave that source code in the same project as SqlDataNavigator. So the same assembly would contain the Web control as well as the base class for its customizable layout. Just as any other ASP.NET page, an ASCX control can inherit from a non-default base class if you set the Inherits attribute in the @Control directive. For example:

  <%@ Control Inherits="BWSLib.DataNavigatorLayoutControl"
 Language="C#" %>
  

In this way, the initialization code seen in Figure 12 can be rewritten like this:

  public void Page_Load(Object sender, EventArgs e)
{
   // Initialize the ASCX using Row["fieldname"]
   EmployeeId.Text = Row["employeeid"].ToString();
   •••
}
  

      How can the data navigator control retrieve the updated data prior to saving changes? The assumption I made here is that all fields in the table are updateable and bound to a Web control whose ID matches the column name. However, identity fields and fields marked as read-only through a DataBoundField object are not updateable.
      The assumption about IDs makes sense for the standard template in which the navigator itself decides for the IDs. If you happen to use a custom layout then you might want to use custom IDs and employ more complex controls to enter or edit data and apply any sort of logic. For this reason, when it comes to adding or updating a data row, the SqlDataNavigator control asks the custom layout whether a certain field must participate in the update and, if this is the case, what its value should be. Methods IsUpdateable and GetFieldValue do just this. By default, GetFieldValue makes a call to Request.Form to retrieve the actual value of the given field. The correct ID to use is determined internally. To accomplish its tasks, GetFieldValue makes use of the protected method GetControlValue, which returns the current value (via Request.Form) for any control ID you specify. This method can be employed in the ASCX to combine the current values of multiple controls to produce the new value for a database field. For example, suppose you use a custom user control to enter a date. This control is made of textboxes for month, day, and year. The ID of the overall control is, say, TheDateBox.
      Figure 14 shows the source code you might want to employ in a custom ASCX to retrieve the date from a particular combination of controls. Notice that IsUpdateable and GetFieldValue must be declared as overrides. Finally, notice that although the SqlDataNavigator is entirely written in C# it can easily handle a layout written in Visual Basic. Thankfully, language is unimportant in .NET and the ASCX can be a Visual Basic class that inherits from a base class (DataNavigatorLayoutControl) written in C#.

A Realistic Example

      In Figure 15 you can see quite a complex application that mimics the structure and the behavior of Windows® Explorer. The topmost strip contains a menu you use to get a table name. The selected table is then loaded and some of its fields populate a side menu forming the master view of the application. You scroll on the vertical menu and select an item. In the right pane, an instance of the SqlDataNavigator control lets you drill down into the details of the record. Any change is immediately reflected in the vertical menu, as a result of the events fired by the navigator. All this infrastructure is built into an all-encompassing ASCX control that takes any information from an XML file. The config file specifies the name and connection string of each table, and other information to configure the SqlDataNavigator control, including templates and search fields. To install the sample application, you simply need to copy the SqlDataNavigator assembly in the BIN folder of your Web application and point the browser to the ASPX page (see ReadMe.txt in the code download for this column). I suppose it's unnecessary to say that the control has been tested only with version 1.0 of the Framework and Microsoft® Internet Explorer 6.0. Have fun, and remember to send me your feedback!

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