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,
be included with these adapters.
Using the code
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"
ItemClass="td" style="width: 700px;"
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
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
CellSpacing are all available.
In addition, the
EditTable control provides a rich set of style properties detailed
The data displayed by the component is controlled by another set of properties described
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:
public partial class TableEditor : System.Web.UI.Page
protected void Page_Load(object Sender, EventArgs e)
Type pageType = Page.GetType();
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.
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.
|Add||Display a single Append row, with OK and Cancel buttons and all fields blank. OK button saves append row,
Cancel discards it|
|Edit||Change the selected row to an Edit row, with OK and Cancel buttons, as above|
|Delete||Request confirmation, then delete the current row, pulling up subsequent rows|
|Find||If a value is entered, attempt to position the table at or to nearest row with key column value matched
to entered string|
|Prev/Next||Page through the table data|
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
- It must implement the
- It must have an appropriate constructor, as described below
BFCS.Data.Common.ITableAdapter interface specifies methods for processing field data, as shown in the following extract:
public interface ITableAdapter
Control DisplayValue(object value);
Control EditValue(object value);
Control EditValue(object value, bool Appending);
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.
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:
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
that can be set with a named parameter setting. This permits passing of additional parameters, encoded into a string, for use by
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:
public MyTableAdapter(BFCS.Data.Common.DbValidator validator, string ColumnName)
: base(validator, ColumnName, typeof(String))
private string m_Modifiers;
public MyTableAdapter(BFCS.Data.Common.DbValidator validator, string ColumnName, string Modifiers)
: base(validator, ColumnName, typeof(String)
m_Modifiers = Modifiers;
Of course, you can provide both constructors. The component selects which one to use based on the presence or absence of
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
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
- 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
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.
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
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
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.
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
Appendix: EditTable Properties
EditTable Control Appearance Properties
|Style to apply to table as a whole|
|Predefined class to apply to table as a whole|
|Style to apply to table headline|
|Predefined class to apply to table headline|
|Style to apply to display row cells|
|Predefined class to apply to display row cells|
|Style to apply to selected edit row or append row|
|Predefined class to apply to selected edit row or append row|
|Style to apply to edit cell with a format error|
|Predefined class to apply to edit cell with a format error|
|Style to apply to table footer|
|Predefined class to apply to table footer|
EditTable Control Data Properties
|Name of data connection provider (e.g., System.Data.SqlClient)|
|Connection string for data source|
|Name of table to be modified|
|SQL SELECT statement used to retrieve table rows|
EditTable Control Behavior Properties
|Maximum rows of table to display|
|Flag: enable edit of rows|
|Flag: enable delete of rows|
|Flag: enable add of new row|
|Flag: enable FIND function|