Implementing Model-View-Presenter in ASP.NET

using System;
using System.ComponentModel;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.Common;
using System.Web;
using System.Text;
using SubSonic.Utilities;
using System.Collections.Generic;
using System.Security;


namespace SubSonic 
{
    public class Scaffold : Control, INamingContainer 
    {

        private string _manyToManyMap;
        private Button btnSave = new Button();
        private Button btnCancel = new Button();
        private Button btnDelete = new Button();
        private Button btnAdd = new Button();
        private Panel surroundingPanel = new Panel();
        private GridView grid = new GridView();
        private TableSchema.Table schema;
        private Label lblSorter = new Label();
        private string tableName = String.Empty;
        private string _editTableCssClass;
        private string _editTableItemCssClass;
        private string _editTableLabelCssClass;
        private string _buttonCssClass;
        private string _textBoxCssClass;
        private string _editTableItemCaptionCellCssClass;
        private bool _showScaffoldCaption = true;
        private string deleteConfirm = "Delete this record? This action cannot be undone...";
        private List<string> _hiddenGridColumnList = new List<string>();
        private List<string> _readOnlyColumnList = new List<string>();
        private const string SORT_DIRECTION = "SORT_DIRECTION";
        private const string ORDER_BY = "ORDER_BY";
        private const string SCAFFOLD_MODE = "SCAFFOLD_MODE";

        #region Properties
        [Bindable(true)]
        [Category("Data")]
        [DefaultValue("")]
        private string _providerName;
        public string ProviderName {
            get { return _providerName; }
            set { _providerName = value; }
        }
	


        [Bindable(true)]
        [Category("Data")]
        [DefaultValue("")]
        public string ManyToManyMap 
        {
            get { return _manyToManyMap; }
            set { _manyToManyMap = value; }
        }

        [Bindable(true)]
        [Category("Data")]
        [DefaultValue("")]
        public string TableName 
        {
            get { return tableName; }
            set { tableName = value; }
        }

        [Bindable(true)]
        [Category("Data")]
        [Description("A comma delimited list of column names which will not be displayed in the GridView.")]
        [DefaultValue("")]
        public string HiddenGridColumns
        {
            set {
                _hiddenGridColumnList.Clear();
                foreach (string columnName in Utility.Split(value))
                    _hiddenGridColumnList.Add(columnName.ToLower());
            }
        }

        [Bindable(true)]
        [Category("Data")]
        [Description("A comma delimited list of column names which are read only.")]
        [DefaultValue("")]
        public string ReadOnlyColumns
        {
            set {
                _readOnlyColumnList.Clear();
                foreach (string columnName in Utility.Split(value))
                    _readOnlyColumnList.Add(columnName.ToLower());
            }
        }

        private bool CanCreate {
            get { return _readOnlyColumnList.Count == 0; }
        }

        private bool CanDelete
        {
            get { return _readOnlyColumnList.Count == 0; }
        }

        [Bindable(true)]
        [Category("Behavior")]
        [DefaultValue("Delete this record? This action cannot be undone...")]
        public string DeleteConfirm 
        {
            get { return deleteConfirm; }
            set { deleteConfirm = value; }
        }

        [Bindable(true)]
        [Category("Display")]
        [Description("Sets the CSS class for the div surrounding the scaffold.")]
        [DefaultValue(ScaffoldCSS.WRAPPER)]
        public string CssClass
        {
            get { return surroundingPanel.CssClass; }
            set { surroundingPanel.CssClass = value; }
        }

        [Bindable(true)]
        [Category("Display")]
        [Description("Sets the skin for GridView.")]
        [DefaultValue("scaffold")]
        public string GridViewSkinID
        {
            get { return grid.SkinID; }
            set { grid.SkinID = value;}
        }

        [Bindable(true)]
        [Category("Display")]
        [Description("Sets the CSS class used by the HTML table in the edit form.")]
        [DefaultValue(ScaffoldCSS.EDIT_TABLE)]
        public string EditTableCssClass
        {
            get { return _editTableCssClass; }
            set { _editTableCssClass = value; }
        }

        [Bindable(true)]
        [Category("Display")]
        [Description("Sets the CSS class used by editable form field elements in the edit form.")]
        [DefaultValue(ScaffoldCSS.EDIT_ITEM)]
        public string EditTableItemCssClass
        {
            get { return _editTableItemCssClass; }
            set { _editTableItemCssClass = value; }
        }

        [Bindable(true)]
        [Category("Display")]
        [Description("Sets the CSS class used by the table label in the edit form.")]
        [DefaultValue(ScaffoldCSS.EDIT_TABLE_LABEL)]
        public string EditTableLabelCssClass
        {
            get { return _editTableLabelCssClass; }
            set { _editTableLabelCssClass = value; }
        }

        [Bindable(true)]
        [Category("Display")]
        [Description("Sets the CSS class used by all buttons.")]
        [DefaultValue(ScaffoldCSS.BUTTON)]
        public string ButtonCssClass
        {
            get { return _buttonCssClass; }
            set { _buttonCssClass = value; }
        }


        [Bindable(true)]
        [Category("Display")]
        [Description("Sets the CSS class used by all TextBox elements in the edit form.")]
        [DefaultValue(ScaffoldCSS.TEXT_BOX)]
        public string TextBoxCssClass
        {
            get { return _textBoxCssClass; }
            set { _textBoxCssClass = value; }
        }

        [Bindable(true)]
        [Category("Display")]
        [Description("Sets the CSS class used by the table cell surrounding edit item captions in the edit form.")]
        [DefaultValue(ScaffoldCSS.EDIT_ITEM_CAPTION)]
        public string EditTableItemCaptionCellCssClass
        {
            get { return _editTableItemCaptionCellCssClass; }
            set { _editTableItemCaptionCellCssClass = value; }
        }

        [Bindable(true)]
        [Category("Display")]
        [Description("If true, a scaffold caption will be shown")]
        public bool ShowScaffoldCaption
        {
            get { return _showScaffoldCaption; }
            set { _showScaffoldCaption = value; }
        }

        public string PrimaryKeyValue
        {
            get { return (string)ViewState["PrimaryKeyValue"]; }
            private set { ViewState["PrimaryKeyValue"] = value; }
        }

        public enum ScaffoldMode
        {
            List,
            Add,
            Edit
        }

        public ScaffoldMode Mode
        {
            get
            {
                if(ViewState[SCAFFOLD_MODE] == null)
                {
                    ViewState[SCAFFOLD_MODE] = ScaffoldMode.List;
                }
                return (ScaffoldMode)ViewState[SCAFFOLD_MODE];
            }
            private set
            {
                ViewState[SCAFFOLD_MODE] = value;
            }
        }
 
        private string Prefix(string param)
        {
            return Utility.PrefixParameter(param, schema.Provider);
        }

        public GridView GridView
        {
            get
            {
                return grid;
            }
        }
        
        #endregion

        /// <summary>
        /// Used to apply CSS class values to WebControls. Ensures that no empty classes are applied;
        /// </summary>
        /// <param name="control"></param>
        /// <param name="cssClass"></param>
        private static void ApplyCssClass(WebControl control, string cssClass)
        {
            if (!String.IsNullOrEmpty(cssClass))
            {
                control.CssClass = cssClass;
            }
        }

        /// <summary>
        /// Used to apply class attribute to HtmlControls. Ensures that no empty classes are applied;
        /// </summary>
        /// <param name="control"></param>
        /// <param name="cssClass"></param>
        private static void ApplyCssClass(HtmlControl control, string cssClass)
        {
            if (!String.IsNullOrEmpty(cssClass))
            {
                control.Attributes.Add("class", cssClass);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tbl"></param>
        /// <param name="text"></param>
        /// <param name="colspan"></param>
        private HtmlTableRow AddRow(HtmlTable tbl, string text, int colspan)
        {
            HtmlTableRow tr = new HtmlTableRow();
            tbl.Rows.Add(tr);

            HtmlTableCell td = new HtmlTableCell();
            tr.Cells.Add(td);
            ApplyCssClass(td, EditTableLabelCssClass);
            
            if (colspan > 0)
            {
                td.ColSpan = colspan;
            }
            td.InnerHtml = text;
            return tr;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tbl"></param>
        /// <param name="cellValue1"></param>
        /// <param name="control"></param>
        private HtmlTableRow AddRow(HtmlTable tbl, string cellValue1, Control control)
        {
            HtmlTableRow tr = new HtmlTableRow();
            tbl.Rows.Add(tr);
            
            HtmlTableCell td = new HtmlTableCell();
            tr.Cells.Add(td);
            
            //label
            ApplyCssClass(td, EditTableItemCaptionCellCssClass);
            td.InnerHtml = "<b>" + cellValue1 + "</b>";
            
            //control
            HtmlTableCell td2 = new HtmlTableCell();
            tr.Cells.Add(td2);
            ApplyCssClass(td, EditTableItemCssClass);
            td2.Controls.Add(control);
            return tr;
        }

        /// <summary>
        /// 
        /// </summary>
        protected override void CreateChildControls()
        {
            if(!String.IsNullOrEmpty(TableName))
            {
                schema = DataService.GetSchema(tableName, ProviderName, TableType.Table);
                //decide what to do here
                //if there is an ID passed in
                //show the editor, if not, show the grid

                surroundingPanel.ID = "pnlSurround";
                Controls.Add(surroundingPanel);
                surroundingPanel.Controls.Clear();
                //add in the control bar

                Label lblTitle = new Label();
                lblTitle.ID = "lblTitle";
                surroundingPanel.Controls.Add(lblTitle);
                lblTitle.Text = "<h2>" + schema.DisplayName + " Admin</h2>";
                lblTitle.Visible = ShowScaffoldCaption;

                //add in the button row
                Panel pnlButtons = new Panel();
                pnlButtons.ID = "pnlButtons";
                surroundingPanel.Controls.Add(pnlButtons);


                btnAdd.ID = "btnAdd";
                pnlButtons.Controls.Add(btnAdd);
                btnSave.ID = "btnSave";
                pnlButtons.Controls.Add(btnSave);
                btnCancel.ID = "btnCancel";
                pnlButtons.Controls.Add(btnCancel);
                btnDelete.ID = "btnDelete";
                pnlButtons.Controls.Add(btnDelete);

                foreach(Button button in pnlButtons.Controls)
                {
                    ApplyCssClass(button, ButtonCssClass);
                }

                btnDelete.OnClientClick = "return CheckDelete();";

                btnSave.Text = "Save";
                btnDelete.Text = "Delete";
                btnCancel.Text = "Return";
                btnAdd.Text = "Add";

                btnAdd.Click += new EventHandler(btnAdd_Click);
                btnSave.Click += new EventHandler(btnSave_Click);
                btnCancel.Click += new EventHandler(btnCancel_Click);
                btnDelete.Click += new EventHandler(btnDelete_Click);

                btnDelete.Visible = CanDelete;

                bool isAdd = (Mode == ScaffoldMode.Add);
                bool isEdit = (Mode == ScaffoldMode.Edit);
                bool isGrid = (Mode == ScaffoldMode.List);

                Panel pnlEditor = new Panel();
                pnlEditor.ID = "pnlEditor";
                surroundingPanel.Controls.Add(pnlEditor);

                CreateEditor(pnlEditor, isEdit);

                Panel pnlGrid = new Panel();
                pnlGrid.ID = "pnlGrid";
                surroundingPanel.Controls.Add(pnlGrid);

                grid.ID = "grid";
                pnlGrid.Controls.Add(grid);

                grid.Sorting += new GridViewSortEventHandler(grid_Sorting);
                grid.RowEditing += new GridViewEditEventHandler(grid_RowEditing);
                grid.AllowSorting = true;

                btnAdd.Visible = false;
                
                if(isGrid)
                {
                    btnAdd.Visible = CanCreate;
                    pnlEditor.Visible = false;
                    pnlGrid.Visible = true;
                    btnSave.Visible = false;
                    btnCancel.Visible = false;
                    btnDelete.Visible = false;
                    BindGrid(String.Empty);
                }

                if (isEdit)
                {
                    pnlEditor.Visible = true;
                    pnlGrid.Visible = false;
                    BindEditor(schema, PrimaryKeyValue);
                    btnSave.Visible = true;
                    btnCancel.Visible = true;
                    btnDelete.Visible = CanDelete;
                }

                if(isAdd)
                {
                    pnlEditor.Visible = true;
                    pnlGrid.Visible = false;
                    btnSave.Visible = true;
                    btnCancel.Visible = true;
                    btnDelete.Visible = false;
  
                }

                Label lblMessage = new Label();
                lblMessage.ID = "lblMessage";
                surroundingPanel.Controls.Add(lblMessage);

                lblSorter.Text = String.Empty;
                surroundingPanel.Controls.Add(lblSorter);
            }

            ViewState[SCAFFOLD_MODE] = Mode;
            if (Mode != ScaffoldMode.Edit)
                PrimaryKeyValue = "";
        }

        #region Event Handlers
        protected void grid_Sorting(object sender, GridViewSortEventArgs e)
        {
            string columnName = e.SortExpression;
            //rebind the grid
            if (ViewState[SORT_DIRECTION] == null || ((string)ViewState[SORT_DIRECTION]) == SqlFragment.ASC)
            {
                ViewState[SORT_DIRECTION] = SqlFragment.DESC;
            }
            else
            {
                ViewState[SORT_DIRECTION] = SqlFragment.ASC;
            }
            Mode = ScaffoldMode.List;
            BindGrid(columnName);
        }

        protected void btnCancel_Click(object sender, EventArgs e)
        {
            //HttpContext.Current.Response.Redirect(HttpContext.Current.Request.CurrentExecutionFilePath);
            Mode = ScaffoldMode.List;
            CreateChildControls();
        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                
                SaveEditor();
                //ShowMessage("<font color=\"ForestGreen\"><b>Record Saved</b></font>");
                //HttpContext.Current.Response.Redirect(HttpContext.Current.Request.CurrentExecutionFilePath);
                Mode = ScaffoldMode.List;
                CreateChildControls();
            }
            catch (DbException x)
            {
                ShowMessage("<font color=\"#990000\"><b>" + x.Message + "</b></font>");
            }

        }


        protected void btnDelete_Click(object sender, EventArgs e)
        {
            if (!this.CanDelete)
                throw new SecurityException(String.Format("This row can not be deleted as it has {0} read-only fields", _readOnlyColumnList.Count));

            string pk;
            if (schema.PrimaryKey.AutoIncrement)
            {
                pk = ((Label)FindControl("pkID")).Text;
            }
            else
            {
                pk = ((TextBox)FindControl("pkID")).Text;
            }

            Query qry = new Query(schema);
            qry.AddWhere(schema.PrimaryKey.ColumnName, pk);
            DataService.ExecuteQuery(qry.BuildDeleteCommand());

            Mode = ScaffoldMode.List;
            CreateChildControls();
            //HttpContext.Current.Response.Redirect(HttpContext.Current.Request.CurrentExecutionFilePath);
        }


        protected void btnAdd_Click(object sender, EventArgs e)
        {
            Mode = ScaffoldMode.Add;
            CreateChildControls();
            //HttpContext.Current.Response.Redirect(HttpContext.Current.Request.CurrentExecutionFilePath + "?id=0");
        }

        protected void grid_RowEditing(object sender, GridViewEditEventArgs e)
        {
            PrimaryKeyValue = grid.Rows[e.NewEditIndex].Cells[1].Text;
            Mode = ScaffoldMode.Edit;
            CreateChildControls();
        }

        #endregion

        private void ShowMessage(string message)
        {
            Label lblMessage = (Label)FindControl("lblMessage");
            if (lblMessage != null)
            {
                lblMessage.Text = message + " <br><i>" + DateTime.Now + "</i>";
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="orderBy"></param>
        private void BindGrid(string orderBy)
        {
            if (schema != null && schema.PrimaryKey != null)
            {
                Query query = new Query(schema);

                string sortColumn = null;
                if (!String.IsNullOrEmpty(orderBy))
                {
                    sortColumn = orderBy;
                }
                else if (ViewState[ORDER_BY] != null)
                {
                    sortColumn = (string)ViewState[ORDER_BY];
                }

                int colIndex = -1;

                if (!String.IsNullOrEmpty(sortColumn))
                {
                    ViewState.Add(ORDER_BY, sortColumn);
                    TableSchema.TableColumn col = schema.GetColumn(sortColumn);
                    if (col == null)
                    {
                        for (int i = 0; i < schema.Columns.Count; i++)
                        {
                            TableSchema.TableColumn fkCol = schema.Columns[i];
                            if (fkCol.IsForeignKey && !String.IsNullOrEmpty(fkCol.ForeignKeyTableName))
                            {
                                TableSchema.Table fkTbl = DataService.GetSchema(fkCol.ForeignKeyTableName, ProviderName, TableType.Table);
                                if (fkTbl != null)
                                {
                                    col = fkTbl.Columns[1];
                                    colIndex = i;
                                    break;
                                }
                            }
                        }
                    }
                    if (col != null && col.MaxLength < 2048)
                    {
                        if (ViewState[SORT_DIRECTION] == null || ((string)ViewState[SORT_DIRECTION]) == SqlFragment.ASC)
                        {
                            if (colIndex > -1)
                            {
                                query.OrderBy = OrderBy.Asc(col, SqlFragment.JOIN_PREFIX + colIndex);
                            }
                            else
                            {
                                query.OrderBy = OrderBy.Asc(col);
                            }
                            ViewState[SORT_DIRECTION] = SqlFragment.ASC;
                        }
                        else
                        {
                            if (colIndex > -1)
                            {
                                query.OrderBy = OrderBy.Desc(col, SqlFragment.JOIN_PREFIX + colIndex);
                            }
                            else
                            {
                                query.OrderBy = OrderBy.Desc(col);
                            }
                            ViewState[SORT_DIRECTION] = SqlFragment.DESC;
                        }
                    }
                }


                DataTable dt = query.ExecuteJoinedDataSet().Tables[0];
                grid.DataSource = dt;
                grid.AutoGenerateColumns = false;
                grid.Columns.Clear();
                grid.DataKeyNames = new string[] { schema.PrimaryKey.ColumnName };
                
                CommandField link = new CommandField();
                link.ShowEditButton = true;
                link.EditText = "Edit";

                

                //HyperLinkField link = new HyperLinkField();
                
                //link.Text = "Edit";
                //link.DataNavigateUrlFields = new string[] { schema.PrimaryKey.ColumnName };
                //link.DataNavigateUrlFormatString = HttpContext.Current.Request.CurrentExecutionFilePath + "?id={0}";
                grid.Columns.Insert(0, link);

                for (int i = 0; i < schema.Columns.Count; i++)
                {

                    BoundField field = new BoundField();
                    field.DataField = dt.Columns[i].ColumnName;
                    field.SortExpression = dt.Columns[i].ColumnName;
                    //field.SortExpression = Utility.QualifyColumnName(schema.Name, dt.Columns[i].ColumnName, schema.Provider);
                    field.HtmlEncode = false;
                    if (schema.Columns[i].IsForeignKey)
                    {
                        TableSchema.Table fkSchema;
                        if (schema.Columns[i].ForeignKeyTableName == null)
                        {
                            fkSchema = DataService.GetForeignKeyTable(schema.Columns[i], schema);
                        }
                        else
                        {
                            fkSchema = DataService.GetSchema(schema.Columns[i].ForeignKeyTableName, ProviderName, TableType.Table);
                        }
                        if (fkSchema != null)
                        {
                            field.HeaderText = fkSchema.DisplayName;
                        }
                    }
                    else
                    {
                        field.HeaderText = schema.Columns[i].DisplayName;
                    }

                    if (!Utility.IsAuditField(dt.Columns[i].ColumnName) && !_hiddenGridColumnList.Contains(dt.Columns[i].ColumnName.ToLower()))
                    {
                        grid.Columns.Add(field);
                    }
                }

                grid.DataBind();
            }
        }



        /// <summary>
        /// Special builder for many to many relational tables.
        /// </summary>
        /// <returns></returns>
        private HtmlTable CreateManyMapper()
        {
            HtmlTable tbl = new HtmlTable();
            tbl.Width = "600px";

            if (this.ShowScaffoldCaption)
                AddRow(tbl, "<h2>" + schema.DisplayName + " Map </h2>", 2);

            foreach (TableSchema.TableColumn col in schema.Columns)
            {
                //by convention, each key in the map table should be a foreignkey
                //if not, it's not good
                if (col.IsPrimaryKey)
                {
                    string fkTable;
                    fkTable = DataService.GetForeignKeyTableName(col.ColumnName, schema.Name, ProviderName);
                    Query qry = new Query(DataService.GetSchema(fkTable, ProviderName, TableType.Table));
                    DropDownList ddl = new DropDownList();
                    ddl.ID = col.ColumnName;
                    AddRow(tbl, fkTable, ddl);

                    IDataReader rdr = qry.ExecuteReader();
                    while (rdr.Read())
                    {
                        ddl.Items.Add(new ListItem(rdr[1].ToString(), rdr[0].ToString()));
                    }
                    rdr.Close();
                }
                else
                {
                    Control ctrl = GetEditControl(col);
                    AddRow(tbl, Utility.ParseCamelToProper(col.ColumnName), ctrl);
                }
            }
            return tbl;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private bool isManyToMany()
        {
            int keyCount = 0;
            bool bOut = false;

            foreach (TableSchema.TableColumn col in schema.Columns)
            {
                if (col.IsPrimaryKey)
                {
                    keyCount++;
                }
            }

            if (keyCount > 1)
            {
                bOut = true;
            }
            return bOut;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="isEdit"></param>
        /// <returns></returns>
        private HtmlTable CreateEditor(Control destination, bool isEdit)
        {
            //if this is a many to many, we need to construct it differently
            HtmlTable tbl = new HtmlTable();
            destination.Controls.Add(tbl);
            if (isManyToMany())
            {
                tbl = CreateManyMapper();
            }
            else
            {
                tbl.Width = "600px";

                if(this.ShowScaffoldCaption)
                    AddRow(tbl, "<h2>" + schema.DisplayName + " Editor</h2>", 2);

                foreach (TableSchema.TableColumn col in schema.Columns)
                {
                    Control ctrl = GetEditControl(col);
                    if (ctrl != null)
                    {
                        string label;
                        label = col.DisplayName;
                        AddRow(tbl, label, ctrl);
                        if(ctrl.GetType() == typeof(TextBox))
                        {
                            TextBox tbx = (TextBox)ctrl;
                            if (tbx.TextMode == TextBoxMode.MultiLine)
                            {
                                int efftectiveMaxLength = Utility.GetEffectiveMaxLength(col);
                                string remainingLength = (efftectiveMaxLength - tbx.Text.Length).ToString();
                                string maxLength = efftectiveMaxLength.ToString();

                                tbx.Attributes.Add("onkeyup", "return imposeMaxLength(event, this, " + maxLength + ", " + tbl.Rows.Count + ");");
                                tbx.Attributes.Add("onChange", "return imposeMaxLength(event, this, " + maxLength + ", " + tbl.Rows.Count + ");");
                                LiteralControl lc = new LiteralControl("<div style='padding: 2px;'><div style='float:left'>Characters Remaining:&nbsp;</div><div id=\"counter" + tbl.Rows.Count + "\" style=\"visibility:hidden\">" + remainingLength + "</div></div>");
                                tbx.Parent.Controls.Add(lc);
                            }
                        }
                    }
                }
                //need a primary key for many/many editing
                if (!String.IsNullOrEmpty(_manyToManyMap) && isEdit)
                {
                    AddManyToMany(isEdit, tbl);
                }
            }
            return tbl;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tbl"></param>
        /// <param name="keyID"></param>
        private void BindEditor(TableSchema.Table tbl, string keyID)
        {
            //get all the data for this row
            Query qry = new Query(tbl);
            qry.AddWhere(tbl.PrimaryKey.ColumnName, keyID);
            IDataReader rdr = qry.ExecuteReader();

            if (rdr.Read())
            {
                foreach (TableSchema.TableColumn col in tbl.Columns)
                {
                    if (col.IsPrimaryKey)
                    {
                        Control ctrl = FindControl("pkID");
                        if (ctrl != null)
                        {
                            string colValue;
                            colValue = rdr[col.ColumnName].ToString();
                            Type ctrlType = ctrl.GetType();
                            if(ctrlType == typeof(Label))
                            {
                                ((Label)ctrl).Text = colValue;
                            }
                            else if(ctrlType == typeof(DropDownList))
                            {
                                ((DropDownList)ctrl).SelectedValue = colValue;
                            }
                            else if(ctrlType == typeof(TextBox))
                            {
                                ((TextBox)ctrl).Text = colValue;
                            }
                        }
                    }
                    else
                    {
                        Control ctrl = FindControl(col.ColumnName);
                        if (ctrl != null)
                        {
                            Type ctrlType = ctrl.GetType();
                            if(ctrlType == typeof(TextBox))
                            {
                                TextBox tbx = ((TextBox)ctrl);
                                tbx.Text = rdr[col.ColumnName].ToString();
                            }
                            else if(ctrlType == typeof(CheckBox))
                            {
                                if(!col.IsNullable || (col.IsNullable && rdr[col.ColumnName] != DBNull.Value))
                                {
                                    ((CheckBox)ctrl).Checked = Convert.ToBoolean(rdr[col.ColumnName]);
                                }
                            }
                            else if(ctrlType == typeof(DropDownList))
                            {
                                ((DropDownList)ctrl).SelectedValue = rdr[col.ColumnName].ToString();
                            }
                            else if(ctrlType == typeof(CalendarControl))
                            {
                                DateTime dt;
                                if(DateTime.TryParse(rdr[col.ColumnName].ToString(), out dt))
                                {
                                    CalendarControl cal = (CalendarControl)ctrl;
                                    cal.SelectedDate = dt.Date;
                                }
                            }
                            else if(ctrlType == typeof(Label))
                            {
                                ((Label)ctrl).Text = rdr[col.ColumnName].ToString();
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="isEdit"></param>
        /// <param name="tbl"></param>
        private void AddManyToMany(bool isEdit, HtmlTable tbl)
        {
            string[] mmTables = _manyToManyMap.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

            if (mmTables.Length > 0)
            {
                foreach (string mmTableName in mmTables)
                {
                    TableSchema.Table mmTable = Query.BuildTableSchema(mmTableName, ProviderName);

                    //this table should have one or more primary keys
                    //one of these keys should, by convention, have the same name
                    //as the primary key of our main schema table
                    //need to get this key, then find it's table
                    foreach (TableSchema.TableColumn col in mmTable.Columns)
                    {
                        if (col.IsPrimaryKey && col.ColumnName.ToLower() != schema.PrimaryKey.ColumnName.ToLower())
                        {
                            //this is the key we need. Get the table for this key
                            string fTableName = DataService.GetForeignKeyTableName(col.ColumnName, mmTableName, ProviderName);

                            if (!String.IsNullOrEmpty(fTableName) && isEdit)
                            {
                                CheckBoxList chk = new CheckBoxList();
                                chk.ID = mmTableName;
                                //add the checkbox in
                                AddRow(tbl, Utility.ParseCamelToProper(mmTableName), chk);
                                chk.RepeatColumns = 2;

                                TableSchema.Table fTable = Query.BuildTableSchema(fTableName);
                                Query qry = new Query(fTable);
                                IDataReader rdr = qry.ExecuteReader();

                                while (rdr.Read())
                                {
                                    chk.Items.Add(new ListItem(rdr[1].ToString(), rdr[0].ToString()));
                                }

                                rdr.Close();
                                //now we need to query the map table, loop it, and check off the items
                                //that are in it

                                rdr = new Query(mmTable).AddWhere(schema.PrimaryKey.ColumnName, this.PrimaryKeyValue).ExecuteReader();
                                
                                //thanks to jcoenen for this!
                                while (rdr.Read()) {
                                    string fkID = rdr[fTable.PrimaryKey.ColumnName].ToString();
                                    foreach (ListItem item in chk.Items) {
                                        if (item.Value.ToString().ToLower().Equals(fkID.ToLower())) {
                                            item.Selected = true;
                                            break;
                                        }

                                    }

                                }
                                rdr.Close();

                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="mapTableName"></param>
        private void SaveManyToMany(string mapTableName)
        {
            //first, need to get the id of the other field
            TableSchema.Table fkTable = Query.BuildTableSchema(mapTableName);
            string fkField = String.Empty;
            foreach (TableSchema.TableColumn col in fkTable.Columns)
            {
                if (col.IsPrimaryKey && col.ColumnName.ToLower() != schema.PrimaryKey.ColumnName.ToLower())
                {
                    fkField = col.ColumnName;
                    break;
                }
            }

            if (!String.IsNullOrEmpty(fkField))
            {
                int pk = Convert.ToInt32(this.PrimaryKeyValue);
                //first, delete out all references in there
                //this MUST be done in a transaction!
                QueryCommandCollection transCollection = new QueryCommandCollection();


                Query qry = new Query(DataService.GetSchema(mapTableName, ProviderName, TableType.Table));
                qry.QueryType = QueryType.Delete;
                qry.AddWhere(schema.PrimaryKey.ColumnName, pk);

                transCollection.Add(qry.BuildDeleteCommand());

                //now, loop the check list, adding items in for each checked bit
                string sql = "INSERT INTO " + mapTableName + "(" + fkField + "," + schema.PrimaryKey.ColumnName + ") VALUES (" + Prefix("fk") + "," + Prefix("pk)");

                CheckBoxList chk = (CheckBoxList)FindControl(fkTable.Name);
                if (chk != null)
                {
                    foreach (ListItem item in chk.Items)
                    {
                        if (item.Selected)
                        {
                            QueryCommand cmd = new QueryCommand(sql, ProviderName);
                            cmd.Parameters.Add(Prefix("fk"), item.Value, DbType.Int32);
                            cmd.Parameters.Add(Prefix("pk"), pk);
                            transCollection.Add(cmd);
                        }
                    }
                }

                //execute
                DataService.ExecuteTransaction(transCollection);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        private void SaveEditor()
        {

            QueryCommand cmd = new QueryCommand(String.Empty, ProviderName);

            //gotta loop through here, create the proper command for this table
            //and execute
            //see if lblID is 0 or a value
            //if the primary key of the schema table is autoincrement, this will be a label
            string pk;

            //thanks to jcoenen for this!
            if (schema.PrimaryKey.AutoIncrement || schema.PrimaryKey.DataType == DbType.Guid)
                pk = ((Label)FindControl("pkID")).Text;
            else
                pk = ((TextBox)FindControl("pkID")).Text;

            if (Mode == ScaffoldMode.Edit)
            {
                cmd.CommandSql = BuildUpdateSql();
                cmd.Parameters.Add(Prefix(schema.PrimaryKey.ColumnName), pk);
            }
            else
            {
                cmd.CommandSql = BuildInsertSql();
                //if (!schema.PrimaryKey.AutoIncrement) { - GUID fix below
                if (!schema.PrimaryKey.AutoIncrement && schema.PrimaryKey.DataType != DbType.Guid)
                {
                    //have to put this in 
                    cmd.Parameters.Add(Prefix(schema.PrimaryKey.ColumnName), pk);
                }
            }

            if (schema != null)
            {
                bool isAdd = Mode == ScaffoldMode.Add;
                foreach (TableSchema.TableColumn col in schema.Columns)
                {
                    //pull the value from the controls
                    if (col.DataType != DbType.Binary && col.DataType != DbType.Byte && !col.IsPrimaryKey)
                    {
                        Control ctrl = FindControl(col.ColumnName);
                        object oVal = Utility.GetDefaultControlValue(col, ctrl, isAdd, true);

                        if (!col.AutoIncrement)
                            cmd.Parameters.Add(Prefix(col.ColumnName), oVal, col.DataType);
                    }
                }
            }

            //execute it
            DataService.ExecuteQuery(cmd);

            //save down any many/many bits
            if (!String.IsNullOrEmpty(_manyToManyMap))
            {
                string[] mmTables = _manyToManyMap.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

                foreach (string mmTableName in mmTables)
                {
                    SaveManyToMany(mmTableName);
                }
            }

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="col"></param>
        /// <returns></returns>
        private Control GetEditControl(TableSchema.TableColumn col)
        {
            WebControl cOut = null;
            string colName = col.ColumnName.ToLower();
            //use special care with the Primary Key
            if (col.IsPrimaryKey)
            {
                //don't want to edit an auto-increment
                if (col.AutoIncrement || col.DataType == DbType.Guid)
                {
                    Label lblPK = new Label();
                    lblPK.ID = "pkID";
                    cOut = lblPK;
                }
                else
                {
                    TextBox txtPK = new TextBox();
                    txtPK.ID = "pkID";
                    cOut = txtPK;
                }
            }
            else
            {
                if (col.IsForeignKey)
                {
                    DropDownList ddl = new DropDownList();
                    string fkTableName = DataService.GetForeignKeyTableName(col.ColumnName, col.Table.Name, ProviderName);

                    TableSchema.Table tbl = DataService.GetSchema(fkTableName, ProviderName, TableType.Table);
                    Query qry = new Query(tbl);
                    
                    qry.OrderBy = OrderBy.Asc(tbl.Columns[1].ColumnName);

                    IDataReader rdr = qry.ExecuteReader();

                    //load up the dropdown
                    //by convention the descriptor should be the second field

                    if (col.IsNullable)
                    {
                        ListItem liNull = new ListItem("(Not Specified)", String.Empty);
                        ddl.Items.Add(liNull);
                    }
                    
                    while (rdr.Read())
                    {
                        ListItem item = new ListItem(rdr[1].ToString(), rdr[0].ToString());
                        ddl.Items.Add(item);
                    }
                    rdr.Close();

                    
                    cOut = ddl;
                }
                else
                {
                    switch (col.DataType)
                    {
                        case DbType.Guid:    
                        case DbType.AnsiString:
                        case DbType.String:
                        case DbType.StringFixedLength:
                        case DbType.Xml:
                        case DbType.Object:
                        case DbType.AnsiStringFixedLength:
                            if (Utility.IsMatch(colName, ReservedColumnName.CREATED_BY) || Utility.IsMatch(colName, ReservedColumnName.MODIFIED_BY))
                            {
                                cOut = new Label();
                            }
                            else
                            {
                                TextBox t = new TextBox();
                                if (Utility.GetEffectiveMaxLength(col) > 250)
                                {
                                    t.TextMode = TextBoxMode.MultiLine;
                                    t.Columns = 60;
                                    t.Rows = 4;
                                }
                                else
                                {
                                    t.Width = Unit.Pixel(250);
                                    if (colName.EndsWith("guid"))
                                    {
                                        t.Text = Guid.NewGuid().ToString();
                                        t.Enabled = false;
                                    }
                                }
                                cOut = t;
                            }
                            break;
                            
                        case DbType.Binary:
                        case DbType.Byte:    
                            //do nothing
                            break;
                        case DbType.Boolean:
                            CheckBox chk = new CheckBox();
                            if (Utility.IsMatch(colName, ReservedColumnName.IS_ACTIVE))
                            {
                                chk.Checked = true;
                            }
                            if (Utility.IsMatch(colName, ReservedColumnName.DELETED) || Utility.IsMatch(colName, ReservedColumnName.IS_DELETED))
                            {
                                chk.Checked = false;
                            }
                            cOut = chk;
                            break;

                        case DbType.Date:
                        case DbType.Time:
                        case DbType.DateTime:
                            if (Utility.IsMatch(colName, ReservedColumnName.MODIFIED_ON) || Utility.IsMatch(colName, ReservedColumnName.CREATED_ON))
                            {
                                cOut = new Label();
                            }
                            else
                            {
                                cOut = new CalendarControl();
                            }
                            break;

                        case DbType.Int16:
                        case DbType.Int32:      
                        case DbType.UInt16:
                        case DbType.Int64:
                        case DbType.UInt32:
                        case DbType.UInt64:
                        case DbType.VarNumeric:
                        case DbType.Single:
                        case DbType.Currency:
                        case DbType.Decimal:
                        case DbType.Double:
                            TextBox tt = new TextBox();
                            tt.Width = Unit.Pixel(50);
                            //if (!this.isNew)
                            //tt.Text = this.GetColumnValue(col.ColumnName).ToString();
                            cOut = tt;
                            break;
                        default:
                            cOut = new TextBox();
                            break;
                    }
                }
                if (cOut != null)
                {
                    cOut.ID = col.ColumnName;
                }
            }
            if (cOut is TextBox)
            {
                TextBox tbx = (TextBox)cOut;
                ApplyCssClass(tbx, TextBoxCssClass);
                if (cOut.GetType() == typeof(TextBox)) //Not Redundant! CalendarControl is TextBox == true; myCalendarControl.GetType() == typeof(TextBox) == false!
                {
                    int maxLength = Utility.GetEffectiveMaxLength(col);
                    if(maxLength > 0)
                    {
                        tbx.MaxLength = maxLength;
                    }
                }
            }

            if (cOut != null && _readOnlyColumnList.Contains(colName))
                cOut.Enabled = false;

            return cOut;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private string BuildInsertSql()
        {
            string sql = "INSERT INTO " + schema.Name;
            string colList = String.Empty;
            string paramList = String.Empty;

            foreach (TableSchema.TableColumn col in schema.Columns)
            {
                if (col.DataType != DbType.Binary && col.DataType != DbType.Byte)
                {
                    //if (!col.AutoIncrement && col.ColumnName.ToLower()!="createdon" && col.ColumnName.ToLower()!="modifiedon") {
                    // HACK: GUID fix
                    if (!col.AutoIncrement && !(col.IsPrimaryKey && col.DataType == DbType.Guid))
                    {
                        colList += col.ColumnName + ",";
                        paramList += Prefix(col.ColumnName) + ",";
                    }
                }
            }

            colList = colList.Remove(colList.Length - 1, 1);
            paramList = paramList.Remove(paramList.Length - 1, 1);

            sql += "(" + colList + ") VALUES (" + paramList + ")";
            return sql;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private string BuildUpdateSql()
        {
            string sql = "UPDATE " + schema.Name + " SET ";

            foreach (TableSchema.TableColumn col in schema.Columns)
            {
                if (col.DataType != DbType.Binary && col.DataType != DbType.Byte)
                {
                    if (Utility.IsWritableColumn(col))
                    {
                        sql += col.ColumnName + " = " + Prefix(col.ColumnName) + ",";
                    }
                }
            }

            sql = sql.Remove(sql.Length - 1, 1);
            sql += " WHERE " + schema.PrimaryKey.ColumnName + " = " + Prefix(schema.PrimaryKey.ColumnName);
            return sql;
        }
    }
}