Click here to Skip to main content
Click here to Skip to main content
Articles » Web Development » ASP.NET » General » Downloads
 
Add your own
alternative version

Implementing Model-View-Presenter in ASP.NET

, 17 Nov 2007
Three implementations of Model-View-Presenter in ASP.NET 2.0.
MVPSampleApp.zip
MVP.SampleApp
Lib
Microsoft.Practices.EnterpriseLibrary.Common.dll
Microsoft.Practices.EnterpriseLibrary.Data.dll
MySql.Data.dll
nunit.framework.dll
Rhino.Mocks.dll
Model
Data
Interfaces
Properties
Presentation
Presentation.Tests
Properties
Interfaces
Properties
SubSonic
ActiveRecord
Builder
CodeGeneration
Templates
CodeLanguage
Configuration
Controls
Calendar
lang
skin
active-bg.gif
calendar.gif
dark-bg.gif
hover-bg.gif
menuarrow.gif
normal-bg.gif
rowhover-bg.gif
status-bg.gif
title-bg.gif
today-bg.gif
Resources
DataProviders
Properties
Sql Tools
SubSonic.snk
Sugar
WebApp
App_Data
Views
SQL2000SampleDb.zip
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;
        }
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

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

About the Author

Alex Mueller
Web Developer
United States United States
No Biography provided

| Advertise | Privacy | Mobile
Web02 | 2.8.140718.1 | Last Updated 17 Nov 2007
Article Copyright 2007 by Alex Mueller
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid