///<summary>Fills the grid with the contents of the corresponding wiki list table in the database.
///After filling the grid, FilterGrid() will get invoked to apply any advanced search options.</summary>
private void FillGrid()
{
_listColumnHeaders = WikiListHeaderWidths.GetForList(WikiListCurName);
_table = WikiLists.GetByName(WikiListCurName);
if (_table.Rows.Count > 0 && _listColumnHeaders.Count != _table.Columns.Count) //if these do not match, something happened to be desynched at the right moment.
{
WikiListHeaderWidths.RefreshCache();
_table = WikiLists.GetByName(WikiListCurName);
_listColumnHeaders = WikiListHeaderWidths.GetForList(WikiListCurName);
if (_listColumnHeaders.Count != _table.Columns.Count) //if they still do not match, one of them did not get synched correctly.
{
MsgBox.Show(this, "Unable to open the wiki list.");
return;
}
}
gridMain.BeginUpdate();
gridMain.ListGridColumns.Clear();
gridMain.ListGridColumns.AddRange(_listColumnHeaders.Select(x => new GridColumn(x.ColName, x.ColWidth)));
gridMain.ListGridRows.Clear();
gridMain.ListGridRows.AddRange(_table.Select().Select((x, index) => new GridRow(x.ItemArray.Select(y => y.ToString()).ToArray())
{
Tag = index
}));
gridMain.Title = WikiListCurName;
gridMain.EndUpdate();
FilterGrid();
}
private void FormWikiListHeaders_Load(object sender, EventArgs e)
{
List <WikiListHeaderWidth> listTableHeadersShallow = WikiListHeaderWidths.GetForList(_wikiListCurName);
_listTableHeaders = listTableHeadersShallow.Select(x => x.Copy()).ToList();
FillGrid();
}
/// <summary></summary>
private void FillGridOld()
{
gridOld.BeginUpdate();
gridOld.ListGridColumns.Clear();
gridOld.ListGridRows.Clear();
if (gridMain.GetSelectedIndex() < 0)
{
gridOld.EndUpdate();
return;
}
if (string.IsNullOrEmpty(gridMain.SelectedTag <WikiListHist>()?.ListHeaders))
{
gridMain.SelectedGridRows[0].Tag = WikiListHists.SelectOne(gridMain.SelectedTag <WikiListHist>()?.WikiListHistNum ?? 0);
}
Dictionary <string, int> dictColWidths = WikiListHeaderWidths.GetFromListHist(gridMain.SelectedTag <WikiListHist>())
.ToDictionary(x => x.ColName, x => x.ColWidth);
using (DataTable table = new DataTable()) {
using (StringReader sr = new StringReader(gridMain.SelectedTag <WikiListHist>().ListContent))
using (XmlReader xmlReader = XmlReader.Create(sr)) {
try {
table.ReadXml(xmlReader);
}
catch (Exception) {
MsgBox.Show(this, "Corruption detected in the Old Revision table. Partial data will be displayed. Please call us for support.");
gridOld.EndUpdate();
return;
}
}
gridOld.ListGridColumns.AddRange(
table.Columns.OfType <DataColumn>().Select(x => new GridColumn(x.ColumnName, dictColWidths.TryGetValue(x.ColumnName, out int width)?width:100)));
gridOld.ListGridRows.AddRange(table.Select().Select(x => new GridRow(x.ItemArray.Select(y => y.ToString()).ToArray())));
}
gridOld.EndUpdate();
}
private void butColumnAdd_Click(object sender, EventArgs e)
{
if (!Security.IsAuthorized(Permissions.WikiListSetup)) //gives a message box if no permission
{
return;
}
SetIsEdited();
WikiLists.AddColumn(WikiListCurName);
_table = WikiLists.GetByName(WikiListCurName);
_listColumnHeaders = WikiListHeaderWidths.GetForList(WikiListCurName);
FillGrid();
}
private void butAdvSearch_Click(object sender, EventArgs e)
{
List <WikiListHeaderWidth> colHeaderWidths = WikiListHeaderWidths.GetForList(WikiListCurName);
FormWikiListAdvancedSearch FormWLAS = new FormWikiListAdvancedSearch(colHeaderWidths);
//FormWLAS.ShowDialog();
if (FormWLAS.ShowDialog() == DialogResult.OK)
{
_arraySearchColIdxs = FormWLAS.SelectedColumnIndices;
FillGrid();
}
ActiveControl = textSearch;
}
/// <summary></summary>
private void FillGridCur()
{
gridCur.BeginUpdate();
gridCur.ListGridColumns.Clear();
gridCur.ListGridRows.Clear();
Dictionary <string, int> dictColWidths = WikiListHeaderWidths.GetForList(ListNameCur).ToDictionary(x => x.ColName, x => x.ColWidth);
using (DataTable table = WikiLists.GetByName(ListNameCur)) {
gridCur.ListGridColumns.AddRange(
table.Columns.OfType <DataColumn>().Select(x => new GridColumn(x.ColumnName, dictColWidths.TryGetValue(x.ColumnName, out int width)?width:100)));
gridCur.ListGridRows.AddRange(table.Select().Select(x => new GridRow(x.ItemArray.Select(y => y.ToString()).ToArray())));
}
gridCur.EndUpdate();
}
/// <summary></summary>
private void FillGridOld()
{
List <WikiListHeaderWidth> colHeaderWidths = new List <WikiListHeaderWidth>();
_tableOld = new DataTable();
if (gridMain.GetSelectedIndex() > -1)
{
colHeaderWidths = WikiListHeaderWidths.GetFromListHist(_listWikiListHists[gridMain.GetSelectedIndex()]);
using (XmlReader xmlReader = XmlReader.Create(new StringReader(_listWikiListHists[gridMain.GetSelectedIndex()].ListContent))) {
try {
_tableOld.ReadXml(xmlReader);
}
catch (Exception) {
MsgBox.Show(this, "Corruption detected in the Old Revision table. Partial data will be displayed. Please call us for support.");
}
}
}
gridOld.BeginUpdate();
gridOld.Columns.Clear();
ODGridColumn col;
for (int c = 0; c < _tableOld.Columns.Count; c++)
{
int colWidth = 100; //100 = default value in case something is malformed in the database.
foreach (WikiListHeaderWidth colHead in colHeaderWidths)
{
if (colHead.ColName == _tableOld.Columns[c].ColumnName)
{
colWidth = colHead.ColWidth;
break;
}
}
col = new ODGridColumn(_tableOld.Columns[c].ColumnName, colWidth, false);
gridOld.Columns.Add(col);
}
gridOld.Rows.Clear();
ODGridRow row;
for (int i = 0; i < _tableOld.Rows.Count; i++)
{
row = new ODGridRow();
for (int c = 0; c < _tableOld.Columns.Count; c++)
{
row.Cells.Add(_tableOld.Rows[i][c].ToString());
}
gridOld.Rows.Add(row);
gridOld.Rows[i].Tag = i;
}
gridOld.EndUpdate();
}
private void butHistory_Click(object sender, EventArgs e)
{
FormWikiListHistory FormWLH = new FormWikiListHistory();
FormWLH.ListNameCur = WikiListCurName;
FormWLH.ShowDialog();
if (!FormWLH.IsReverted)
{
return;
}
//Reversion has already saved a copy of the current revision.
_wikiListOld = WikiListHists.GenerateFromName(WikiListCurName, Security.CurUser.UserNum);
_table = WikiLists.GetByName(WikiListCurName);
_listColumnHeaders = WikiListHeaderWidths.GetForList(WikiListCurName);
FillGrid();
_isEdited = false;
IsNew = false;
}
private void butColumnEdit_Click(object sender, EventArgs e)
{
if (!Security.IsAuthorized(Permissions.WikiListSetup)) //gives a message box if no permission
{
return;
}
FormWikiListHeaders FormWLH = new FormWikiListHeaders(WikiListCurName);
FormWLH.ShowDialog();
if (FormWLH.DialogResult != DialogResult.OK)
{
return;
}
SetIsEdited();
_table = WikiLists.GetByName(WikiListCurName);
_listColumnHeaders = WikiListHeaderWidths.GetForList(WikiListCurName);
FillGrid();
}
private void butColumnRight_Click(object sender, EventArgs e)
{
if (!Security.IsAuthorized(Permissions.WikiListSetup)) //gives a message box if no permission
{
return;
}
if (gridMain.SelectedCell.X == -1)
{
return;
}
SetIsEdited();
Point pointNewSelectedCell = gridMain.SelectedCell;
pointNewSelectedCell.X = Math.Min(gridMain.Columns.Count - 1, pointNewSelectedCell.X + 1);
WikiLists.ShiftColumnRight(WikiListCurName, _table.Columns[gridMain.SelectedCell.X].ColumnName);
_table = WikiLists.GetByName(WikiListCurName);
_listColumnHeaders = WikiListHeaderWidths.GetForList(WikiListCurName);
FillGrid();
gridMain.SetSelected(pointNewSelectedCell);
}
private void FormWikiListEdit_Load(object sender, EventArgs e)
{
if (!WikiLists.CheckExists(WikiListCurName))
{
IsNew = true;
WikiLists.CreateNewWikiList(WikiListCurName);
}
_table = WikiLists.GetByName(WikiListCurName);
_wikiListOld = WikiListHists.GenerateFromName(WikiListCurName, Security.CurUser.UserNum);
if (_wikiListOld == null)
{
_wikiListOld = new WikiListHist();
}
//Fill _columnHeaders
_listColumnHeaders = WikiListHeaderWidths.GetForList(WikiListCurName);
radioButHighlight.Checked = true;
radioButFilter.Checked = false;
FillGrid();
ActiveControl = textSearch; //start in search box.
}
private void butColumnDelete_Click(object sender, EventArgs e)
{
if (!Security.IsAuthorized(Permissions.WikiListSetup)) //gives a message box if no permission
{
return;
}
if (gridMain.SelectedCell.X == -1)
{
MsgBox.Show(this, "Select cell in column to be deleted first.");
return;
}
if (!WikiLists.CheckColumnEmpty(WikiListCurName, _table.Columns[gridMain.SelectedCell.X].ColumnName))
{
MsgBox.Show(this, "Column cannot be deleted because it contains data.");
return;
}
SetIsEdited();
WikiLists.DeleteColumn(WikiListCurName, _table.Columns[gridMain.SelectedCell.X].ColumnName);
_table = WikiLists.GetByName(WikiListCurName);
_listColumnHeaders = WikiListHeaderWidths.GetForList(WikiListCurName);
FillGrid();
}
/// <summary></summary>
private void FillGridCur()
{
List <WikiListHeaderWidth> listColHeaderWidths = WikiListHeaderWidths.GetForList(ListNameCur);
_tableCur = WikiLists.GetByName(ListNameCur);
gridCur.BeginUpdate();
gridCur.Columns.Clear();
ODGridColumn col;
for (int c = 0; c < _tableCur.Columns.Count; c++)
{
int colWidth = 100; //100 = default value in case something is malformed in the database.
foreach (WikiListHeaderWidth colHead in listColHeaderWidths)
{
if (colHead.ColName == _tableCur.Columns[c].ColumnName)
{
colWidth = colHead.ColWidth;
break;
}
}
col = new ODGridColumn(_tableCur.Columns[c].ColumnName, colWidth, false);
gridCur.Columns.Add(col);
}
gridCur.Rows.Clear();
ODGridRow row;
for (int i = 0; i < _tableCur.Rows.Count; i++)
{
row = new ODGridRow();
for (int c = 0; c < _tableCur.Columns.Count; c++)
{
row.Cells.Add(_tableCur.Rows[i][c].ToString());
}
gridCur.Rows.Add(row);
gridCur.Rows[i].Tag = i;
}
gridCur.EndUpdate();
}
/// <summary></summary>
private void FillGrid()
{
List <WikiListHeaderWidth> colHeaderWidths = WikiListHeaderWidths.GetForList(WikiListCurName);
gridMain.BeginUpdate();
gridMain.Columns.Clear();
ODGridColumn col;
for (int c = 0; c < Table.Columns.Count; c++)
{
int colWidth = 100; //100 = default value in case something is malformed in the database.
foreach (WikiListHeaderWidth colHead in colHeaderWidths)
{
if (colHead.ColName == Table.Columns[c].ColumnName)
{
colWidth = colHead.ColWidth;
break;
}
}
col = new ODGridColumn(Table.Columns[c].ColumnName, colWidth, false);
gridMain.Columns.Add(col);
}
gridMain.Rows.Clear();
ODGridRow row;
for (int i = 0; i < Table.Rows.Count; i++)
{
row = new ODGridRow();
for (int c = 0; c < Table.Columns.Count; c++)
{
row.Cells.Add(Table.Rows[i][c].ToString());
}
gridMain.Rows.Add(row);
}
gridMain.EndUpdate();
gridMain.Title = WikiListCurName;
}
private void butOK_Click(object sender, EventArgs e)
{
if (_gridCurIndex > 0)
{
_listTableHeaders[_gridCurIndex].PickList = string.Join("\r\n", _listComboOptions);
}
//Set primary key to correct name-----------------------------------------------------------------------
gridMain.Rows[0].Cells[0].Text = _wikiListCurName + "Num"; //prevents exceptions from occuring when user tries to rename PK.
//Validate column names---------------------------------------------------------------------------------
for (int i = 0; i < gridMain.Rows.Count; i++) //ODGridCell colNameCell in gridMain.Rows[0].Cells){
{
if (Regex.IsMatch(gridMain.Rows[i].Cells[0].Text, @"^\d"))
{
MsgBox.Show(this, "Column cannot start with numbers.");
return;
}
if (Regex.IsMatch(gridMain.Rows[i].Cells[0].Text, @"\s"))
{
MsgBox.Show(this, "Column names cannot contain spaces.");
return;
}
if (Regex.IsMatch(gridMain.Rows[i].Cells[0].Text, @"\W")) //W=non-word chars
{
MsgBox.Show(this, "Column names cannot contain special characters.");
return;
}
}
//Check for reserved words--------------------------------------------------------------------------------
for (int i = 0; i < gridMain.Rows.Count; i++) //ODGridCell colNameCell in gridMain.Rows[0].Cells){
{
if (DbHelper.isMySQLReservedWord(gridMain.Rows[i].Cells[0].Text))
{
MessageBox.Show(Lan.g(this, "Column name is a reserved word in MySQL") + ":" + gridMain.Rows[i].Cells[0].Text);
return;
}
//primary key is caught by duplicate column name logic.
}
//Check for duplicates-----------------------------------------------------------------------------------
List <string> listColNamesCheck = new List <string>();
for (int i = 0; i < gridMain.Rows.Count; i++) //ODGridCell colNameCell in gridMain.Rows[0].Cells){
{
if (listColNamesCheck.Contains(gridMain.Rows[i].Cells[0].Text))
{
MessageBox.Show(Lan.g(this, "Duplicate column name detected") + ":" + gridMain.Rows[0].Cells[i].Text);
return;
}
listColNamesCheck.Add(gridMain.Rows[i].Cells[0].Text);
}
//Validate column widths---------------------------------------------------------------------------------
for (int i = 0; i < gridMain.Rows.Count; i++) //ODGridCell colNameCell in gridMain.Rows[0].Cells){
{
if (Regex.IsMatch(gridMain.Rows[i].Cells[1].Text, @"\D")) // "\D" matches any non-decimal character
{
MsgBox.Show(this, "Column widths must only contain positive integers.");
return;
}
if (gridMain.Rows[i].Cells[1].Text.Contains("-") ||
gridMain.Rows[i].Cells[1].Text.Contains(".") ||
gridMain.Rows[i].Cells[1].Text.Contains(",")) //inlcude the comma for international support. For instance Pi = 3.1415 or 3,1415 depending on your region
{
MsgBox.Show(this, "Column widths must only contain positive integers.");
return;
}
}
//save values to List<WikiListHeaderWidth> TableHeaders
for (int i = 0; i < _listTableHeaders.Count; i++)
{
_listTableHeaders[i].ColName = PIn.String(gridMain.Rows[i].Cells[0].Text);
_listTableHeaders[i].ColWidth = PIn.Int(gridMain.Rows[i].Cells[1].Text);
}
//Save data to database-----------------------------------------------------------------------------------
try {
WikiListHeaderWidths.UpdateNamesAndWidths(_wikiListCurName, _listTableHeaders);
}
catch (Exception ex) {
MessageBox.Show(ex.Message); //will throw exception if table schema has changed since the window was opened.
DialogResult = DialogResult.Cancel;
}
DataValid.SetInvalid(InvalidType.Wiki);
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, EventArgs e)
{
if (_gridCurCell.Y > 0 && _gridCurCell.Y < _listTableHeaders.Count) //no pick list option for the PK col
{
_listTableHeaders[_gridCurCell.Y].PickList = string.Join("\r\n", _listStringPick);
}
#region Validation
List <string> listColNames = new List <string>();
for (int i = 1; i < gridMain.ListGridRows.Count; i++) //start with index 1, first col is PK
{
string colName = gridMain.ListGridRows[i].Cells[0].Text;
string colWidth = gridMain.ListGridRows[i].Cells[1].Text;
#region Validate Column Widths
if (Regex.IsMatch(colWidth, @"\D")) // "\D" matches any non-decimal character
{
MsgBox.Show(this, "Column widths must only contain positive integers.");
return;
}
//inlcude the comma for international support. For instance Pi = 3.1415 or 3,1415 depending on your region
if (new[] { '-', '.', ',' }.Any(x => colWidth.Contains(x)))
{
MsgBox.Show(this, "Column widths must only contain positive integers.");
return;
}
#endregion Validate Column Widths
#region Validate Column Names
if (listColNames.Contains(colName))
{
MessageBox.Show(Lan.g(this, $"Duplicate column name detected") + ": " + colName);
return;
}
listColNames.Add(colName);
if (i == 0) //don't check PK column name, since it can't be changed
{
continue;
}
if (Regex.IsMatch(colName, @"^\d"))
{
MsgBox.Show(this, "Column cannot start with numbers.");
return;
}
if (Regex.IsMatch(colName, @"\s"))
{
MsgBox.Show(this, "Column names cannot contain spaces.");
return;
}
if (Regex.IsMatch(colName, @"\W")) //W=non-word chars
{
MsgBox.Show(this, "Column names cannot contain special characters.");
return;
}
//Check for reserved words--------------------------------------------------------------------------------
if (DbHelper.isMySQLReservedWord(colName))
{
MessageBox.Show(Lan.g(this, "Column name is a reserved word in MySQL") + ": " + colName);
return;
}
#endregion Validate Column Names
}
#endregion Validation
#region Update _listTableHeaders
for (int i = 0; i < _listTableHeaders.Count; i++)
{
if (i > 0) //don't allow renaming the first column, it's the PK
{
_listTableHeaders[i].ColName = PIn.String(gridMain.ListGridRows[i].Cells[0].Text);
}
_listTableHeaders[i].ColWidth = PIn.Int(gridMain.ListGridRows[i].Cells[1].Text);
}
#endregion Update _listTableHeaders
#region Try Update DB
try {
WikiListHeaderWidths.UpdateNamesAndWidths(_wikiListCurName, _listTableHeaders);
}
catch (Exception ex) {
MessageBox.Show(ex.Message); //will throw exception if table schema has changed since the window was opened.
DialogResult = DialogResult.Cancel;
}
#endregion Try Update DB
DataValid.SetInvalid(InvalidType.Wiki);
DialogResult = DialogResult.OK;
}
private void FormWikiListHeaders_Load(object sender, EventArgs e)
{
ListTableHeaders = WikiListHeaderWidths.GetForList(WikiListCurName);
FillGrid();
}
