ASP.NET Web Component for editing SQL tables

• Download source code - 101 KB
• Download demo - 46.6 KB

Introduction

Frustrated with the complications of using the packaged ASP.NET data components like DataGrid, and wanting more control over layout, field formatting and error detection, I decided to "roll my own". I wanted to be able to view a paged list of rows, edit or delete any row and add a new row, and have the component automatically handle a key column and different standard data types. I also wanted to be able to put in a drop list as an input with a minimum of custom coding.

As the project evolved, it got divided into two layers: one was a wrapper around System.Data.Common.DbConnection and utilities to construct SQL action statements with full format and error checking, and the other a GUI component for editing table rows. The first component can be of general use when manipulating SQL data tables in code, while the second was designed to be used with a web-based database maintenance site.

The next development cycle involved creation of a means of customizing the GUI at the column level, to allow different types of web controls to be used for editing and to provide custom formatting and parsing. An architecture of plug-in adapters was developed that provided standard behavior for common data types, and was also extensible at the project level for particular table columns. One common use of this mechanism is to provide a DropDown box as an input means, using either a values table or literal value list to populate the control. Client-based JavaScript validation can also be included with these adapters.

Using the code

The EditTable component is designed to be used like any other ASP.NET control. The web project should reference the EditTable project, and any pages should include a reference to the BFCS.Data.Common namespace. Your project Bin folder must contain Connection.dll, which is used internally by the EditTable class library.

Here is a sample markup snippet incorporating the control:

//
// Sample EditTable control markup
//
<@ Register assembly="EditTable" namespace="BFCS.Data.Common" tagprefix="data" %>
//
// Style definitions are used here for setting appearance properties. You can also use literal styles.
//
//
// EditTable control instance
//
<data:EditTable ID="EditTable1" runat="server" 
    RowsPerPage="12" ScrollBars="Auto" BorderStyle="None" 
    FooterClass="th" HeadlineClass="th" 
    ItemClass="td" style="width: 700px;" 
    TableClass="table" Visible="False" 
    onerror_changed="EditTable1_Error_Changed" 
    SelectedClass="tds" KeyHeadlineClass="thk" 
    ErrorClass="tdsError" CellSpacing="0">
</data:EditTable>

To be eligible for use with this control, a table must have a single key column, which may either be auto-generated or editable by the user. Multi-column keys and tables without a key column are not supported.

Component Properties, Methods and Events

The EditTable component is derived from System.Web.UI.WebControls.Table, which is a wrapper around a plain ol' HTML Table tag. This means that such properties as Border, CellPadding and CellSpacing are all available. In addition, the EditTable control provides a rich set of style properties detailed here.

The data displayed by the component is controlled by another set of properties described here. The properties used in this set define the database connection string and provider type (for Microsoft SQL server, for instance, this might be System.Data.SqlClient), the name of the table or view to be modified, and a SQL SELECT statement for selecting the actual columns to be displayed and/or filtering the rows. In the sample application, the connection values are read from the Web.Config file's ConnnectionStrings section, while the table name and SQL SELECT statement are constructed by selecting a table to edit from a predefined list.

In addition, there are behavior properties for switching on or off the ability to edit, delete and append rows and control whether to display initialization errors and the number of rows to display on a page.

The above properties are available on the design pane property sheet of Visual Studio. In addition, there is the CallingAssembly property which must be set in code (typically in a page's Page_Load event handler) as shown in the following example:

//
// Example of setting CallingAssembly property from Page_Load event handler
//
using System.Reflection;
using BFCS.Data.Common;

namespace EditTableDemo
{
    public partial class TableEditor : System.Web.UI.Page
    {
        protected void Page_Load(object Sender, EventArgs e)
        {
            Type pageType = Page.GetType();
            EditTable1.CallingAssembly = 
                Assembly.GetAssembly(pageType.BaseType == null || 
                pageType.BaseType == typeof(Page) ? pageType : pageType.BaseType);
//
// ...
//
        }

The purpose of this property is to inform the control of the assembly in which it is running. (Is there a simpler way to do this? Please let me know!) The reason for this is described in the next section, for detecting custom table adapters.

Finally there is a property, LastError, that, when read, produces the most recent error or group of errors detected in attempting to modify the table. When changed, this property fires the Error_Changed event, which your project can use to refresh an error display label. These errors may either be trapped before attempting to modify the table data, such as invalid string formats or out-of-limit values, or may be returned by the SQL provider itself, such as index violations.

The main method available to the calling code is Refresh which does what it says: retrieves the data again from the data store and re-displays the table.

User Interface

The table is displayed in a standard format, with the actual column names from the data source used to label the top row. Initially, all rows are displayed as 'display rows'. If enabled, each row has buttons for Edit and Delete functions on that row, and a single Add button appears at the top. If enabled, there is a Find field at the bottom, with a button to search for the entered value. Depending on the table size and position of the top row, there will be Prev and Next buttons for paging through the table.

Custom Table Column Adapters

There are all kinds of situations in which you might want to fine-tune the way an editor works with particular columns in a table. One common case is where a column contains a numeric code meaningless to a user, but there is a lookup table assigning prompt values to the valid codes - a natural case for a DropDown box edit control. You might want to use a DateTime control to display and edit only the time portion of the data. You might want to use a radio button control for a small number of exclusive choices. More simply, there might be a legal range of number values that must be enforced, or a format such as a tax ID or Social Security number that must be checked for valid format.

To handle these situations, I created an architecture of Table Column Adapters. The Connection project provides a standard set that are used by default for common SQL data types. For more specialized cases, your project defines its own adapters and assigns them to a particular table/column combination. Such adapters are typically written as classes within your web project. These classes must adhere to the following conditions:

• The class must be a descendent of the BFCS.Data.Common.TableAdapter class
• It must implement the BFCS.Data.Common.ITableAdapter interface
• It must have an appropriate constructor, as described below

The BFCS.Data.Common.ITableAdapter interface specifies methods for processing field data, as shown in the following extract:

public interface ITableAdapter
{
    /// Given a value retrieved from a data source, return a System.Web.Control to display it
    /// in a non-edit, non-append row
    Control DisplayValue(object value);
    /// Given a value retrieved from a data source, return a System.Web.Control to display it
    /// in an edit row
    Control EditValue(object value);
    /// Given a value retrieved from a data source, return a System.Web.Control to display it
    /// in an edit or append row. Appending flag informs routine that it is being used to
    /// display an append row; if false, it is an edit row.
    Control EditValue(object value, bool Appending);
    /// Given a value entered as a string, check for validity, modify as necessary to use
    /// in an action SQL statement. Set 'error' value to non-empty, non-null string if an error is detected
    /// in the supplied value.
    string SaveValue(string setting, out string error);
}

The first three methods generate a System.Web.UI.Control derived instance that will be inserted into the Table when it is populated prior to return. In the above, the term 'non-edit row' refers to a row that is displayed but not open for editing, while 'edit row' refers to a row as displayed after clicking the Edit button on the row, or when displaying an append row. The SaveValue method is used to parse entered data as returned during a PostBack by the edit control, check it for validity and, if necessary, re-format it for inclusion in a SQL action statement (INSERT INTO or UPDATE). If this routine detects an error in the submitted value, the out parameter error should be set with a user-readable error message describing the error and suggesting how to correct it.

A custom table column adapter appearing in a web application must be decorated with the BFCS.Data.Common.CustomTableAdapter attribute, as shown in the following example:

[CustomTableAdapter("Calendar", "StartTime")]

As the example shows, there are two mandatory arguments for the attribute. The first is the table name and the second is the name of the column to which the adapter will be applied. If the SAME adapter is to be applied to multiple columns, you must declare a separate class for each table/column combination, and have them inherit from the base adapter class.

For further fine-tuning of behavior of table column adapters, there is an optional String property Modifiers that can be set with a named parameter setting. This permits passing of additional parameters, encoded into a string, for use by the adapter.

Depending on whether or not the Modifiers argument appears in the decoration, the table column adapter class must provide a constructor following one of these templates:

//
// Constructor for table column adapter that does not use Modifiers argument in decorating attribute
// NOTE on third argument of base constructor: this argument is used for type checking only. If used,
// type passed should match the type of the column, after mapping. To disable the type checking, pass null.
// 
//
public MyTableAdapter(BFCS.Data.Common.DbValidator validator, string ColumnName)
: base(validator, ColumnName, typeof(String))
{
// ...
}
//
// Constructor for table column adapter that uses Modifiers argument
//
private string m_Modifiers;

public MyTableAdapter(BFCS.Data.Common.DbValidator validator, string ColumnName, string Modifiers)
: base(validator, ColumnName, typeof(String)
{
    m_Modifiers = Modifiers;        // Save for use by adapter
// ..
}

Of course, you can provide both constructors. The component selects which one to use based on the presence or absence of the Modifiers argument. Some uses of the Modifiers argument are:

• Define lower and upper bounds for numeric or date fields
• Declare literal list of legal values for a DropDown box, or
• Declare specification for a lookup table to supply values for a DropDown box
• Define a regular expression that must be satisfied by a text input

As mentioned above, the individual adapter code is responsible for interpreting and using the Modifiers value.

At runtime, if the calling page code sets the CallingAssembly property of the EditTable control as shown above, the control searches the defined types in the page assembly for those decorated with the CustomTableAdapter attribute, with properties matching the table and column being initialized. If a matching adapter is found, the component then searches for a constructor to generate an instance of the adapter that is attached to the column and used for moving data in and out of database storage, and for error checking.

Recapping the rules for use of custom table column adapters, they must:

• Derive from TableAdapter
• Implement the ITableAdapter interface appropriately
• Be decorated with the CustomTableAdapter attribute, with arguments indicating the table and column, and optionally any modifiers
• Provide appropriate constructor(s) depending on attribute arguments
• Be made visible by setting the EditTable control's CallingAssembly property

Under The Hood

This project encapsulates years of working with SQL and ASP.NET. The EditTable control source is a thorough, almost excruciating example of many features of custom control coding, while the Connection project encapsulates a lot of experience of trying to make working with SQL painless and bullet-proof.

The Connection project design is a layered implementation. The DataConnection class cleans up the usually messy business of establishing a database connection and dealing with problems and errors. It allows you to "Open" multiple times without complaining and defers closing the connection, if desired, in cases of nested routines. Simple methods are provided for generating data source agnostic versions of DataReader, Command and DataAdapter objects.

The DbValidator class retrieves the schema of a table and uses it in validating and, where necessary, reformatting string values for use with action SQL statements. The SQLStatement class, derived from DbValidator, is like a smart StringBuilder for SQL statements, checking validity and painlessly re-formatting string values to ensure that they execute, trapping many common problems (such as overlong strings, missing values for required columns and duplicate key settings) before trying to execute the SQL statement against the database provider.

The EditTable class illustrates a slew of concepts in control coding. ViewState is used internally to maintain position and other state for the control between postbacks. One of the trickiest features was getting events to fire correctly for dynamically entered button controls. The solution boils down to recreating the control tree AFTER postback, duplicating the control IDs for such controls, handling the events thereby detected, then RE-generating the control tree as modified by the action of the event handlers, and doing it all at the correct lifestyle phase of the Page control.

Appendix: EditTable Properties