Click here to Skip to main content
15,867,835 members
Articles / Web Development / ASP.NET

ASP.NET Web Component for editing SQL tables

Rate me:
Please Sign up or sign in to vote.
5.00/5 (4 votes)
29 Nov 2012CPOL11 min read 42.7K   2.5K   24   4
ASP.NET c# component for editing SQL tables with plug-in column format adapter architecture.

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:

ASP.NET
//
// 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:

C#
//
// 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

Image 1

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.

AddDisplay a single Append row, with OK and Cancel buttons and all fields blank. OK button saves append row, Cancel discards it
EditChange the selected row to an Edit row, with OK and Cancel buttons, as above
DeleteRequest confirmation, then delete the current row, pulling up subsequent rows
FindIf a value is entered, attempt to position the table at or to nearest row with key column value matched to entered string
Prev/NextPage 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 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:

C#
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:

C#
[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:

C#
//
// 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

EditTable Control Appearance Properties

Property NameDescription
TableStyleStyle to apply to table as a whole
TableClassPredefined class to apply to table as a whole
HeadlineStyleStyle to apply to table headline
HeadlineClassPredefined class to apply to table headline
ItemStyleStyle to apply to display row cells
ItemClassPredefined class to apply to display row cells
SelectedStyleStyle to apply to selected edit row or append row
SelectedClassPredefined class to apply to selected edit row or append row
ErrorStyleStyle to apply to edit cell with a format error
ErrorClassPredefined class to apply to edit cell with a format error
FooterStyleStyle to apply to table footer
FooterClassPredefined class to apply to table footer

EditTable Control Data Properties

Property NameDescription
DataProviderName of data connection provider (e.g., System.Data.SqlClient)
ConnectionStringConnection string for data source
TableNameName of table to be modified
TableSQLSQL SELECT statement used to retrieve table rows

EditTable Control Behavior Properties

Property NameDescription
RowsPerPageMaximum rows of table to display
AllowEditFlag: enable edit of rows
AllowDeleteFlag: enable delete of rows
AllowAddFlag: enable add of new row
AllowFindFlag: enable FIND function

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Rovenet, Inc.
United States United States
Bill, who thinks of himself in the third person, has been programming since the dawn of time (1974) in a wide variety of hardware environments (dipswitch settings and paper tape in the beginning), languages (asm, forth, c, c++, c#, basic [visual and unvisualizable]) and industries (graphic arts, medical technology, commercial, website, mobile devices). Corporate clients include DHL, Pitney-Bowes and now-defunct medical equipment midget Q-Med. In his free time, which is all the time, he plays bluegrass guitar, body-boards the oceans of the world and bicycles through Southern California and eastern Long Island, NY.

Comments and Discussions

 
QuestionAbout DateTime Fields Question Pin
Chen Henry 202228-Apr-22 21:39
Chen Henry 202228-Apr-22 21:39 
BugDeletes row from page 1 when row on ANY page deleted - HELP! Pin
oldoak200030-Oct-13 10:13
oldoak200030-Oct-13 10:13 
Questiongreat project.. can we use it with Sql server Pin
Member 422609722-Jan-13 8:26
Member 422609722-Jan-13 8:26 
Hello,

i just found your project and its great, exactly what im looking for. I wonder if it works with Sql Server 2005 or 2008.

i will try by changing the connection string and will see from there.

thanks for the reply if you can.
BugForgot to implement Dispose() Pin
RovenetBill30-Nov-12 17:41
RovenetBill30-Nov-12 17:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.