A Library of Self-validating Database-aware Input Controls






A Library of Self-validating Database-aware Input Controls

By Prakash Nadkarni, MD


Building an interface to a relational database is one of the ASP.NET programmer s most common tasks. Despite ASP.NET 2.0 s advances, the database application developer s productivity still lags considerably behind that provided by the best client-server (two-tier) development tools. The framework described here, WebEAV, is intended to accelerate database development tasks by supporting generation of Web forms for data browsing and entry from descriptions of the tables with which end-users interact. While the entire framework is too large to be described fully here (the complete source code, DLL, and HTML Help documentation are available for download; see end of article for details), I ll discuss some of the framework s custom controls that simplify code generation, and the common database-interaction scenarios they address. These controls can be used by themselves in your applications without the metadata if you specify the properties of each control manually.


The Strengths of Client-Server Development Tools

Microsoft Access and Sybase PowerBuilder provided the inspiration for the WebEAV framework. While admittedly non-scalable as a standalone database management system (DBMS), Access provides a powerful rapid-prototyping environment for database applications. Its system tables go beyond standard high-end DBMSes to store metadata, such as:

  • The caption used to label the column when shown to the user (as opposed to the column s internal name).
  • Additional validation-related information, such as input mask, and error message shown if validation fails.
  • How a column should be presented: e.g., as a textbox, listbox, or pull-down menu (combobox). For textboxes, you can define data-type-specific display formats. For list/combo boxes, you can specify a list of permissible values, or a SQL query that fetches one or more columns of data from another table. You can compose such a query visually, sometimes setting a relationship between two tables as a side-effect.


When you generate a form based on a table with Access, the fields inherit from the corresponding column definitions. Consequently, simply getting the table/column definitions and inter-table relationships right goes a very long way in creating a robust prototype interface. The ability to perform query-by-form (where the criteria for individual columns contain SQL sub-expressions) and standard form-based data manipulation with basic validation does not require any code. This works seamlessly, even when Access is used as a front-end, via ODBC, to a high-end DBMS. By contrast, the FormView templates generated using ASP.NET 2.0 and Visual Studio 2005 require extensive modification if you want to validate the user s input.


While entirely codeless development in ASP.NET is still a long way off, the WebEAV framework attempts to minimize repetitive design and programming efforts.


In high-end DBMSes, data presentation is regarded as the application developer s responsibility so the system tables do not store presentation metadata. PowerBuilder works around this by defining special extended-attribute metadata tables. It is fairly straightforward to replicate such functionality by creating two tables: one for table definitions and one for column definitions within a table. For a given DBMS, you can bootstrap these tables contents by querying system tables/views, or through an object layer such as ADOX. (It is considerably trickier to synchronize schema and metadata automatically as the schema evolves in response to user needs: Microsoft SQL Server 2005 s ability to define data definition language triggers may provide some relief in this regard.) You then define the presentation metadata manually: a forms-based interface eases metadata editing. Some of the metadata details will become clear as the custom controls and their properties are discussed.


The Custom Controls

The custom controls discussed below share common properties related to database mapping, appearance, default values, and non-null validation, with each control having additional properties related to its function:

  • TextBoxPlus: This inherits from ASP:TextBox, with additional properties related to validation.
  • ListBoxPlus: This inherits from ASP:ListBox, and can appear either as a DropDownList or ListBox.
  • ComboOrRadioList: This inherits from ListBoxPlus, but has a couple of additional properties that allow it to appear as either a RadioButtonList or DropDownList.
  • BoolListbox: This also inherits from ListBoxPlus, but is stripped down to provide an interface to a Boolean column in a database table (e.g., SQL Server s Bit data type). It shows only the values True/Yes, False/No, and blank, the last corresponding to a database NULL. (The checkbox, which can have only two states, checked and unchecked, is not suitable to represent the three-valued logic that databases support.) I won t discuss it further, other than to note that it is a convenience control that requires very few properties to be set before use.


When describing the controls individually, I ll explain the motivation behind the creation of each, then describe the properties that help them serve their intended purposes. The open-source software that currently uses these controls (a clinical data-management system) supports several production installations that use either of two different production DBMSes: Oracle and Microsoft SQL Server. To avoid building essentially two separate applications and worrying about subtle differences in SQL dialects, the framework and controls rely on the System.Data.ODBC namespace. However, the utility classes that perform database access are separate from the controls, and you can modify the top-level declarations to use a different namespace if you wish. One of the utility classes addresses case-sensitivity in string comparisons (Oracle is case-sensitive, SQL Server is not) when generating SQL for database searches.


The common properties shared by the controls are:

  • DatabaseTableName and DatabaseColumnName: These properties, if specified, provide the mapping to a physical database column.
  • DataType: This enumeration is one of: String, Integer, Real, Date, Boolean, Long Text, or Binary. (None of the above controls use the last, which corresponds to data such as images.) I ve stayed away from DBMS-specific data types, and this enumeration reflects the semantics of the column with respect to input validation, as well as the SQL that is generated e.g., single quotes around strings and the ODBC escapes (e.g., {ts yyyy-mm-dd} ) for date-time values. The data type integer typically maps to the longest integer-based data type supported by the DBMS. If you wish to map to other data types, e.g., Int16, you may add to this enumeration. There are also two special data types related to the ListBoxPlus-derived controls. For the type Choice , the value of each item in the list is an Integer, while the text is of type String. For the type Choice_String , both value and text are of type String. Designating these data types impacts SQL generation.
  • Mandatory: (Boolean) If True, the control s value cannot be left blank.
  • DefaultValue: The default value.
  • ErrMsgText: Text that is generated and displayed dynamically in case of an error either by the developer at design time or by the user during run time.
  • IsValid: (Boolean):If False, it means this control has failed validation, which is fired by the control s Validate method. As with the ASP.NET Validation controls, these controls inject browser script into the page to perform basic data-type, range, and regular-expression validation through the onBlur event. To take advantage of browser caching, the script code is written to disk the first time the application loads, then referenced with the