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: </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;
}
}
}