ASP.NET Web Component for editing SQL tables

/*
 * Code Copyright 2012 by Bill Frisbie
 * 
 * This code is licensed under COPL license.
 * Users are free to use this code, modify it and include it in other work, including works for resale.
 * Please include this notice in the code.
 * Author provides code 'as-is', and assumes no liability for any losses whatsoever arising from its use
 * Contact author at bfrisbie@optonline.net with any questions or suggestions.
 */
using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Common;

namespace BFCS.Data.Common
{
    /// <summary>
    /// Field that displays a dropdown box with a list of choices selected from a sql server cursor
    /// </summary>
    public class LookupTableAdapter : TableAdapter, ITableAdapter
    {
        DataConnection conn;      // connection used to open dropdown lookup list
        string sql;         // sql used to populate dropdown list

        
        /// <summary>
        /// Constructor: modifier value is a valid SQL string used to select 2 columns (at least)
        /// from a table or view to use in generating a dropdown list
        /// </summary>
        /// <param name="validator">DbValidator to use</param>
        /// <param name="ColumnName">Column being used</param>
        /// <param name="modifier">SQL statement that returns values to use to populate dropdown box</param>
        public LookupTableAdapter(DbValidator validator, string ColumnName, string modifier)
            : base(validator, ColumnName, null)
        {
            sql = modifier;
            conn = validator.Conn;
        }
        #region ITableField Members
        /// <summary>
        /// Given value retrieved from table, return display control for non-edit row
        /// </summary>
        /// <param name="value">retrieved value</param>
        /// <returns>Control used to display value</returns>
        /// <remarks>
        /// Null or empty string values are displayed as empty label.
        /// Method attempts to look up value in lookup recordset if recordset has at least 2 columns,
        /// Assumes second column is value and first is displayed value
        /// </remarks>
        public Control DisplayValue(object value)
        {
            Label label = new Label();
            label.Text = "&nbsp;";
            if (((value is DBNull)) || value != null)
                return label;           // don't process nulls further
            label.Text = value.ToString();      // default value if not found in lookup table
            try
            {
                DbValidator ListValidator = new DbValidator(sql, conn);
                if (!ListValidator.HasError && ListValidator.ColumnNames.Length > 1)
                {
                    string valueColumnName = ListValidator.ColumnNames[1];    // get name of column
                    string compareValue = value.ToString();        // raw version of what to search for
                    if (ListValidator.ValueString(valueColumnName, ref compareValue))   // ensure search value valid
                    {
                        string lookupSQL = sql + string.Format(" WHERE [{0}] = {1}",
                            valueColumnName, compareValue);
                        using (DbCommand cmd = conn.NewCommand(lookupSQL))
                        {
                            using (DbDataReader reader = cmd.ExecuteReader())
                            {
                                if (reader.Read())
                                {
                                    label.Text = reader[0].ToString();      // Assume display in col 0
                                }
                                reader.Close();
                            }
                            cmd.Dispose();
                        }
                    }
                }
            }
            catch (Exception)
            {
                label.Text = value.ToString();
            }
            return label;
        }

        public Control EditValue(object value)
        {
            return EditValue(value, false);
        }
        /// <summary>
        /// Return edit control for edit or append row
        /// </summary>
        /// <param name="value">value retrieved from table</param>
        /// <param name="Appending">flag: if set this is an append row, otherwise edit row</param>
        /// <returns>control to use</returns>
        /// <remarks>
        /// If editable, use DropDownList control, populate from modifier recordset
        /// Select entry that matches (ignore case, whitespace at beginning or end) the retrieved value
        /// </remarks>
        public Control EditValue(object value, bool Appending)
        {
            if ((IsKey && !Appending) || !IsWriteable)
            {
                return DisplayValue(value);
            }
            DropDownList list = new DropDownList();
            using (DbCommand cmd = conn.NewCommand(sql))
            {
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    int valueColumn = (reader.FieldCount == 1) ? 0 : 1;
                    while (reader.Read())
                    {
                        string valueString = reader[valueColumn].ToString();
                        ListItem item = new ListItem(reader[0].ToString(), valueString);
                        if (valueString.ToLower().Trim() == value.ToString().ToLower().Trim())
                            item.Selected = true;       // select matching column
                        list.Items.Add(item);
                    }
                    reader.Close();
                }
                cmd.Dispose();
            }
            return list;
        }

        public virtual string SaveValue(string setting, out string error)
        {
            error = "";
            return setting;
        }

        #endregion
    }
}