larsbloch
10/5/2017 - 6:01 PM
ASPComp_DBTableEditor
using System;
using System.Collections.Generic;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;
public class DatabaseTableInfo
{ //v1.0
Helper_SQLServerData dbCallHelper;
string tableName;
string overrideTableName = "";
string headlineUnderDescription = "";
bool showHeadlineBanner = true;
List<string> keys = new List<string>();
string identityColumn;
List<ObjColumnWidth> columnWidths = new List<ObjColumnWidth>();
List<ObjColumnName> columnNames = new List<ObjColumnName>();
List<ObjColumnAllowNull> columnAllowNulls = new List<ObjColumnAllowNull>();
List<string> hiddenColumns = new List<string>();
List<string> readOnlyColumns = new List<string>();
DataTable table;
List<int> rowsChanged = new List<int>();
List<int> rowsDeleted = new List<int>();
int standardWidth = 150;
int standardHeight = 18;
bool allowDelete = false;
bool allowAddRow = false;
bool allowUpdate = true;
System.Web.UI.Page resetter;
Table mainTable = new Table();
int maxRowdecimals = 8;
int maxColumndecimals = 3;
int amountRowsWithoutNew = 0;
string uniqueControlIDValue = "";
string mainColor = "#27ae60";
public DatabaseTableInfo(string serverName, string database, string tableName, List<string> keys, System.Web.UI.Page resetter, string identityColumn)
{
dbCallHelper = new Helper_SQLServerData(serverName, database);
this.tableName = tableName;
this.keys = keys;
this.allowDelete = false;
this.resetter = resetter;
this.identityColumn = identityColumn;
}
public DatabaseTableInfo(string serverName, string database, string tableName, List<string> keys, System.Web.UI.Page resetter)
{
dbCallHelper = new Helper_SQLServerData(serverName, database);
this.tableName = tableName;
this.keys = keys;
this.allowDelete = false;
this.resetter = resetter;
this.identityColumn = "";
}
#region settings
/// <summary>
/// "User ID=username; Password=password;"
/// </summary>
public void SetUserNameAndPasswordForDB(string value)
{
dbCallHelper.SettingUsernamePassword(value);
}
public void SetUserNameAndPasswordForDB(string username, string password)
{
dbCallHelper.SettingUsernamePassword("User ID=" + username + "; Password=" + password + ";");
}
/// <summary>
/// Use the Column name as it is, in the table
/// If width is less than 1 the default size (150) will be used
/// IF alterntiveText = string.empty the column name from sql will be used
/// </summary>
public void SetColumnOptions(string columnName, string alternativeText, int width, bool hideColumn, bool readonlyColumn)
{
if (alternativeText != string.Empty)
{
ObjColumnName objColumnName = new ObjColumnName();
objColumnName.ColumnName = columnName;
objColumnName.NewColumnName = alternativeText;
columnNames.Add(objColumnName);
}
//Set width if not 0
if (width > 0)
{
ObjColumnWidth columnWidth = new ObjColumnWidth();
columnWidth.ColumnName = columnName;
columnWidth.ColumnWidth = width;
columnWidths.Add(columnWidth);
}
if (hideColumn)
hiddenColumns.Add(columnName);
if (readonlyColumn)
readOnlyColumns.Add(columnName);
}
public void SettingColor(string hexColorValue)
{
mainColor = hexColorValue;
}
public void SetHeadlineUnderDescription(string text)
{
headlineUnderDescription = text;
}
public void SetOverrideHeadline(string text)
{
overrideTableName = text;
}
public void SetAddUniqueControls(string uniqueStringValue)
{
uniqueControlIDValue = uniqueStringValue;
}
public void SetShowHeadlineBanner(bool value)
{
showHeadlineBanner = value;
}
public void SetAllowDelete(bool value)
{
allowDelete = value;
}
public void SetAllowAddRow(bool value)
{
allowAddRow = value;
}
public void SetAllowUpdates(bool value)
{
allowUpdate = value;
}
#endregion
public void CreatePage(Panel control)
{
mainTable = new Table();
mainTable.CssClass = "Table";
mainTable.ID = "MainTable" + uniqueControlIDValue;
CreatePart1(control); //Headline and save button
CreatePart2(control); //ColumnHeaders
//all the data
int i = 0;
if (allowAddRow)
{
foreach (DataColumn d in table.Columns)
d.AllowDBNull = true;
table.Rows.Add();
}
foreach (DataRow r in table.Rows)
{
//
// Add the row to the table
//
TableRow tableRow = new TableRow();
tableRow.Height = standardHeight;
tableRow.Width = standardWidth;
tableRow.CssClass = "TableRow";
//
// add delete button if enabled
//
if (allowDelete)
{
TableCell delCell = new TableCell();
delCell.CssClass = "TableCell";
delCell.Height = standardHeight;
if (i >= amountRowsWithoutNew)
{
Button insertFromExcel = new Button();
insertFromExcel.Click += InsertFromExcel_Click;
delCell.Controls.Add(insertFromExcel);
}
else
{
CheckBox delCheck = new CheckBox();
delCheck.ID = "DelChec" + GetStringRowNumber(i) + uniqueControlIDValue;
delCheck.CheckedChanged += DelCheck_CheckedChanged;
delCell.Controls.Add(delCheck);
}
tableRow.Cells.Add(delCell);
}
//
// loop all rows
//
for (int k = 0; k < table.Columns.Count; k++)
{
DataColumn loopDataColumn = table.Columns[k];
if (ColumnIsHidden(loopDataColumn.ColumnName) == false)
{
TableCell tableCell = new TableCell();
tableCell.CssClass = "TableCell";
tableCell.Height = standardHeight;
tableCell.Width = standardWidth;
string IDName = "Datarow" + GetStringRowNumber(i) + "Column" + GetStringColumnNumber(k) + uniqueControlIDValue;
//If datatype is booleand
if (loopDataColumn.DataType.Name == "Boolean")
{
CheckBox cb = new CheckBox();
cb.CssClass = "TableCheckbox";
if (i < amountRowsWithoutNew) //If it is NOT the last row
cb.CheckedChanged += T_TextChanged;
cb.ID = IDName;
if (IsKey(loopDataColumn.ColumnName) || IsReadOnlyColumn(loopDataColumn.ColumnName))
{
if (i >= amountRowsWithoutNew && loopDataColumn.ColumnName.ToUpper() != identityColumn.ToUpper())
cb.Enabled = false;
else
cb.Enabled = true;
}
ObjColumnWidth objColumnWidth = columnWidths.Find(cw => cw.ColumnName.ToUpper() == table.Columns[k].ColumnName.ToUpper());
if (objColumnWidth != null)
{
tableCell.Width = objColumnWidth.ColumnWidth;
}
else
tableCell.Width = standardWidth;
if (r[k].ToString().ToUpper() == "TRUE")
cb.Checked = true;
else
cb.Checked = false;
tableCell.Controls.Add(cb);
}
else
{
if (loopDataColumn.ColumnName.ToUpper() == "STRINGTN")
{
DropDownList d = new DropDownList();
d.Items.Add("Hest");
d.Items.Add("Køer");
tableCell.Controls.Add(d);
}
else
{
TextBox t = new TextBox();
if (loopDataColumn.DataType.Name == "DateTime")
{
if (r.Field<object>(k) != null)
{
DateTime dt = r.Field<DateTime>(k);
t.Text = dt.ToString("yyyy-MM-dd hh:mm:ss.fff");
}
}
else
t.Text = r[k].ToString();
t.ID = IDName;
t.CssClass = "TableTextBox";
if (i < amountRowsWithoutNew) //If it is NOT the last row
t.TextChanged += T_TextChanged;
t.Height = standardHeight;
t.Width = new Unit("95%");
if (loopDataColumn.DataType.Name.ToUpper() == "INT32" || loopDataColumn.DataType.Name.ToUpper() == "INT64")
t.TextMode = TextBoxMode.Number;
if (IsKey(loopDataColumn.ColumnName) || IsReadOnlyColumn(loopDataColumn.ColumnName))
{
if (i >= amountRowsWithoutNew && loopDataColumn.ColumnName.ToUpper() != identityColumn.ToUpper())
t.ReadOnly = false;
else
t.ReadOnly = true;
}
ObjColumnWidth objColumnWidth = columnWidths.Find(cw => cw.ColumnName.ToUpper() == table.Columns[k].ColumnName.ToUpper());
if (objColumnWidth != null)
{
tableCell.Width = objColumnWidth.ColumnWidth;
}
else
tableCell.Width = standardWidth;
tableCell.Controls.Add(t);
//\d{4}-\d{2}-\d{2}
//\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$
//\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}.\d{3}$
//Add Validators to textboxes
//https://msdn.microsoft.com/en-us/library/system.data.datacolumn.datatype(v=vs.110).aspx
if (table.Columns[k].DataType.Name == "DateTime")
{//Adding regular expresisons to datetime textboxes validates the input so it fits sql servers
string iString = i.ToString();
RegularExpressionValidator rev = new RegularExpressionValidator();
rev.ID = "Validater" + i + "Column" + k + uniqueControlIDValue;
rev.ValidationExpression = @"\d{4}-\d{2}-\d{2}$|\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$|\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}.\d{3}$";
rev.SetFocusOnError = true;
rev.ErrorMessage = "Invalid datetime format (yyyy-mm-dd OR yyyy-mm-dd tt-mm-ss OR yyyy-mm-dd tt-mm-ss.mmm";
rev.ControlToValidate = IDName;
rev.Display = ValidatorDisplay.Dynamic;
control.Controls.Add(rev);
}
}
}
tableRow.Cells.Add(tableCell);
}
}
// rev.ControlToValidate = textBoxID
//TablePanel.Controls.Add(new RegularExpressionValidator)
mainTable.Rows.Add(tableRow);
i++;
}
control.Controls.Add(mainTable);
}
private void InsertFromExcel_Click(object sender, EventArgs e)
{
}
private void CreatePart1(Panel control)
{//This is the headline part and the save button part
Panel headLinePanel = new Panel();
headLinePanel.CssClass = "TableHeadlinePanel";
if (showHeadlineBanner)
{
Label tableHeadline = new Label();
if (overrideTableName == "")
tableHeadline.Text = tableName;
else
tableHeadline.Text = overrideTableName;
tableHeadline.CssClass = "TableHeadline";
headLinePanel.Controls.Add(tableHeadline);
headLinePanel.BackColor = System.Drawing.ColorTranslator.FromHtml(mainColor);
if (headlineUnderDescription != "")
{
headLinePanel.Controls.Add(new LiteralControl("<br />"));
Label tableHeadlineDescription = new Label();
tableHeadlineDescription.Text = headlineUnderDescription;
tableHeadlineDescription.CssClass = "TableHeadlineUnderDescription";
headLinePanel.Controls.Add(tableHeadlineDescription);
}
headLinePanel.Controls.Add(new LiteralControl("<br />"));
control.Controls.Add(headLinePanel);
control.Controls.Add(new LiteralControl("<br />"));
}
Button updateChanges = new Button();
updateChanges.Text = "Save changes";
updateChanges.CssClass = "Button";
updateChanges.Click += UpdateChanges_Click;
updateChanges.BackColor = System.Drawing.ColorTranslator.FromHtml(mainColor);
control.Controls.Add(updateChanges);
control.Controls.Add(new LiteralControl("<br />"));
control.Controls.Add(new LiteralControl("<br />"));
table = dbCallHelper.GetDataFromSQL("Select * FROM " + tableName);
amountRowsWithoutNew = table.Rows.Count;
foreach(DataColumn d in table.Columns)
{
ObjColumnAllowNull columnAllowNull = new ObjColumnAllowNull();
columnAllowNull.ColumnName = d.ColumnName;
columnAllowNull.AllowNull = d.AllowDBNull;
columnAllowNulls.Add(columnAllowNull);
}
}
private void CreatePart2(Panel control)
{//this method contains the column headers
TableRow tableHeaderRow = new TableRow();
tableHeaderRow.Height = standardHeight;
tableHeaderRow.Width = standardWidth;
tableHeaderRow.CssClass = "TableRow";
//Small not enabled deleted button
if (allowDelete)
{
Button delBut = new Button();
delBut.CssClass = "DelButton";
delBut.Text = "Slet";
delBut.Enabled = false;
TableCell delCell = new TableCell();
delCell.CssClass = "TableCell";
delCell.Height = standardHeight;
delCell.Controls.Add(delBut);
tableHeaderRow.Cells.Add(delCell);
}
//Add all the header objects
int j = 0;
foreach (DataColumn c in table.Columns)
{
if (ColumnIsHidden(c.ColumnName) == false)
{
TableCell tableCell = new TableCell();
tableCell.CssClass = "TableCell";
tableCell.Height = standardHeight;
tableCell.Width = standardWidth;
TextBox t = new TextBox();
t.Text = GetHeaderName(c.ColumnName);
if (c.DataType.Name.ToUpper() == "STRING")
t.ToolTip = "Type: " + c.DataType.Name + "(" + c.MaxLength + ")" + " nullable: " + ColumnAllowNulls(c.ColumnName);
else
t.ToolTip = "Type: " + c.DataType.Name + " nullable: " + ColumnAllowNulls(c.ColumnName);
t.ReadOnly = true;
t.ID = "Column" + j + uniqueControlIDValue;
t.CssClass = "TableHeader";
ObjColumnWidth objColumnWidth = columnWidths.Find(cw => cw.ColumnName.ToUpper() == c.ColumnName.ToUpper());
if (objColumnWidth != null)
{
t.Width = objColumnWidth.ColumnWidth;
tableCell.Width = objColumnWidth.ColumnWidth;
}
else
{
tableCell.Width = standardWidth;
t.Width = standardWidth;
}
tableCell.Controls.Add(t);
tableHeaderRow.Cells.Add(tableCell);
}
j++;
}
mainTable.Rows.Add(tableHeaderRow);
}
#region Events
private void DelCheck_CheckedChanged(object sender, EventArgs e)
{
CheckBox cb = (CheckBox)sender;
if (cb.Checked)
{
int row = GetRowNumberFromCID(cb.ID);
if (rowsDeleted.Exists(r => r == row) == false)
rowsDeleted.Add(row);
}
}
private void T_TextChanged(object sender, EventArgs e)
{
Control c = sender as Control;
int row = GetRowNumberFromCID(c.ID);
if (rowsChanged.Exists(r => r == row) == false)
rowsChanged.Add(row);
}
private void UpdateChanges_Click(object sender, EventArgs e)
{
List<string> updates = new List<string>();
Button b = sender as Button;
#region foreach changed row
foreach (int i in rowsChanged)
{
string updateColumnSql = "";
string whereColumnSql = "";
string updateSQL = "UPDATE " + tableName + " ";
DataRow r = table.Rows[i];
for (int j = 0; j < table.Columns.Count; j++)
{
DataColumn dataColumn = table.Columns[j];
if (IsKey(dataColumn.ColumnName))
{
if (whereColumnSql == "")
whereColumnSql += " WHERE ";
else
whereColumnSql += " AND ";
if (dataColumn.DataType.Name == "String" || dataColumn.DataType.Name == "DateTime")
whereColumnSql += table.Columns[j].ColumnName + " = '" + r[j].ToString() + "'";
else if (dataColumn.DataType.Name == "Boolean")
{
if (r[j].ToString().ToUpper() == "TRUE")
whereColumnSql += table.Columns[j].ColumnName + " = 1";
else
whereColumnSql += table.Columns[j].ColumnName + " = 0";
}
else
whereColumnSql += table.Columns[j].ColumnName + " = " + r[j].ToString();
}
else
{
if (updateColumnSql == "")
updateColumnSql += " SET ";
else
updateColumnSql += " , ";
if (dataColumn.DataType.Name == "String" || dataColumn.DataType.Name == "DateTime")
updateColumnSql += table.Columns[j].ColumnName + " = '" + GetControlValue(i, j) + "'";
else if (dataColumn.DataType.Name == "Boolean")
{
if (GetControlValue(i, j) == "TRUE")
updateColumnSql += table.Columns[j].ColumnName + " = 1";
else
updateColumnSql += table.Columns[j].ColumnName + " = 0";
}
else
updateColumnSql += table.Columns[j].ColumnName + " = " + GetControlValue(i, j);
}
}
updateSQL += updateColumnSql + whereColumnSql;
updates.Add(updateSQL);
}
#endregion
#region foreach deleted row
foreach (int i in rowsDeleted)
{
DataRow r = table.Rows[i];
string whereColumnSql = "";
for (int j = 0; j < table.Columns.Count; j++)
{
DataColumn dataColumn = table.Columns[j];
if (IsKey(dataColumn.ColumnName))
{
if (whereColumnSql == "")
whereColumnSql += " WHERE ";
else
whereColumnSql += " AND ";
if (dataColumn.DataType.Name == "String" || dataColumn.DataType.Name == "DateTime")
whereColumnSql += table.Columns[j].ColumnName + " = '" + r[j].ToString() + "'";
else if (dataColumn.DataType.Name == "Boolean")
{
if (r[j].ToString().ToUpper() == "TRUE")
whereColumnSql += table.Columns[j].ColumnName + " = 1";
else
whereColumnSql += table.Columns[j].ColumnName + " = 0";
}
else
whereColumnSql += table.Columns[j].ColumnName + " = " + r[j].ToString();
}
}
string deleteStatement = "DELETE FROM " + tableName + " " + whereColumnSql;
updates.Add(deleteStatement);
}
#endregion
#region Add the new row
string newRowValuesSQL = "";
string newRowinsertSQL = "INSERT INTO " + tableName;
//Check if the "new row is filled out
bool newRowIsFilled = false;
for (int j = 0; j < table.Columns.Count; j++)
{
if (GetControlType(amountRowsWithoutNew, j) != "CHECKBOX")
{
string value = GetControlValue(amountRowsWithoutNew, j); //controls is zero based. so the amount of rows corresponds to the last row
if (value != "")
{
newRowIsFilled = true;
break;
}
}
}
if (newRowIsFilled)
{
for (int j = 0; j < table.Columns.Count; j++)
{
DataColumn dataColumn = table.Columns[j];
if (dataColumn.ColumnName.ToUpper() != identityColumn.ToUpper())
{
string ControlValue = GetControlValue(amountRowsWithoutNew, j); //controls is zero based. so the amount of rows corresponds to the last row
if (ControlValue == "")
{//if we have an empty field
if (dataColumn.DataType.Name.ToUpper() == "INT32" || dataColumn.DataType.Name.ToUpper() == "INT64")
{//Check for empty values if int fields are not set
if (ColumnAllowNulls(dataColumn.ColumnName))
ControlValue = "NULL";
else
ControlValue = "0";
}
else if (dataColumn.DataType.Name.ToUpper() == "STRING" || dataColumn.DataType.Name.ToUpper() == "DATETIME")
{
if (ColumnAllowNulls(dataColumn.ColumnName))
ControlValue = "NULL";
else
ControlValue = "'" + ControlValue + "'"; ;
}
}
else
{
if (dataColumn.DataType.Name.ToUpper() == "STRING" || dataColumn.DataType.Name.ToUpper() == "DATETIME" || dataColumn.DataType.Name.ToUpper() == "BOOLEAN")
{
ControlValue = "'" + ControlValue + "'";
}
}
if (newRowValuesSQL == "")
newRowValuesSQL = " Values(" + ControlValue;
else
newRowValuesSQL += ", " + ControlValue;
}
}
newRowValuesSQL += ")";
newRowinsertSQL += newRowValuesSQL;
updates.Add(newRowinsertSQL);
}
#endregion
dbCallHelper.RunSQLList(updates);
resetter.Response.Redirect(resetter.Request.Url.AbsoluteUri, false);
}
#endregion
#region HelperMethods
private string GetControlValue(int row, int column)
{
string value = "";
foreach (Control c in TableControls())
{
if (c.ID != null && c.ID.StartsWith("Datarow") && row == GetRowNumberFromCID(c.ID) && column == GetColumnNumberFromCID(c.ID))
{
if (c is TextBox)
{
TextBox t = c as TextBox;
value = t.Text;
}
else if (c is CheckBox)
{
CheckBox t = c as CheckBox;
if (t.Checked == true)
value = "TRUE";
else
value = "FALSE";
}
break;
}
}
return value;
}
private string GetControlType(int row, int column)
{
string value = "";
foreach (Control c in TableControls())
{
if (c.ID != null && c.ID.StartsWith("Datarow") && row == GetRowNumberFromCID(c.ID) && column == GetColumnNumberFromCID(c.ID))
{
if (c is TextBox)
value = "TEXTBOX";
else if (c is CheckBox)
value = "CHECKBOX";
break;
}
}
return value;
}
private List<Control> TableControls()
{
List<Control> returnValue = new List<Control>();
foreach (TableRow r in mainTable.Controls)
{
foreach (TableCell c in r.Controls)
{
foreach (Control control in c.Controls)
returnValue.Add(control);
}
}
return returnValue;
}
private bool IsKey(string columnName)
{
if (keys.Exists(k => k.ToUpper() == columnName.ToUpper()) || columnName.ToUpper() == identityColumn.ToUpper())
return true;
else
return false;
}
private bool ColumnAllowNulls(string columnName)
{
ObjColumnAllowNull obj = columnAllowNulls.Find(C => C.ColumnName.ToUpper() == columnName.ToUpper());
return obj.AllowNull;
}
private bool IsReadOnlyColumn(string columnName)
{
//If allow update = false then all rows are read only
if (allowUpdate == false || readOnlyColumns.Exists(k => k.ToUpper() == columnName.ToUpper()))
return true;
else
return false;
}
private string GetStringRowNumber(int number)
{
string returnNumber = number.ToString();
returnNumber = returnNumber.PadLeft(maxRowdecimals, '0');
return returnNumber;
}
private string GetStringColumnNumber(int number)
{
string returnNumber = number.ToString();
returnNumber = returnNumber.PadLeft(maxColumndecimals, '0');
return returnNumber;
}
private int GetRowNumberFromCIDBUTTON(string controlID)
{
return Convert.ToInt32(controlID.Substring(6, maxRowdecimals));
}
private int GetRowNumberFromCID(string controlID)
{
return Convert.ToInt32(controlID.Substring(7, maxRowdecimals));
}
private int GetColumnNumberFromCID(string controlID)
{//DataColumnXXXXXColumnXXX
// 7 X 6 X
return Convert.ToInt32(controlID.Substring(7 + maxRowdecimals + 6, maxColumndecimals));
}
private string GetHeaderName(string columnName)
{
string returnValue = columnName;
ObjColumnName objColumnName = columnNames.Find(c => c.ColumnName.ToUpper() == columnName.ToUpper());
if (objColumnName != null)
returnValue = objColumnName.NewColumnName;
return returnValue;
}
private bool ColumnIsHidden(string columnName)
{
if (hiddenColumns.Exists(k => k.ToUpper() == columnName.ToUpper()))
return true;
else
return false;
}
#endregion
}
public class ObjColumnWidth
{
public string ColumnName;
public int ColumnWidth;
}
public class ObjColumnName
{
public string ColumnName;
public string NewColumnName;
}
public class ObjColumnAllowNull
{
public string ColumnName;
public bool AllowNull;
}