private void AdditionalTables_Clicked(object sender, RoutedEventArgs e)
{
try
{
string[] exludeTables = this.ErdSegment.SegmentTables == null || this.ErdSegment.SegmentTables.Count == 0 ?
new string[] { } :
this.ErdSegment.SegmentTables.Select(t => t.TableName).ToArray();
SelectedTables selector = new SelectedTables(this.ErdSegment.IncludeInContextBuild.ToArray(), exludeTables);
bool?result = selector.ShowDialog();
if (!result.IsTrue())
{
return;
}
this.ErdSegment.IncludeInContextBuild.Clear();
this.ErdSegment.IncludeInContextBuild.AddRange(selector.SelectedModels());
this.Canvas_Changed(this, this.ErdSegment.IncludeInContextBuild);
}
catch (Exception err)
{
MessageBox.Show(err.InnerExceptionMessage());
}
}
/// <summary>
/// Selects from tables.
/// </summary>
/// <param name="tables">The tables.</param>
public void SelectFromTables(params string[] tables)
{
SelectedTables.Clear();
foreach (var table in tables)
{
SelectedTables.Add(table);
}
}
/// <inheritdoc />
public ISelectStringBuilder From(params string[] tableNames)
{
SelectedTables.Clear();
foreach (var table in tableNames)
{
SelectedTables.Add(table);
}
return(this);
}
private void CheckIfFieldIsSelected(String selectedTable)
{
for (int x = 0; x < chkListFields.Items.Count; x++)
{
if (SelectedTables.IsFieldSelected(selectedTable, chkListFields.Items[x].ToString()))
{
chkListFields.SetItemChecked(x, true);
}
}
}
/// <summary>
/// Checking to see if user has selected this table to be generated
/// </summary>
protected bool IsTableSelected(string tableName)
{
if (!OnlyReadSelectedItems)
{
return(true);
}
if (SelectedTables == null || SelectedTables.Count == 0)
{
return(false);
}
return(SelectedTables.Contains(tableName));
}
public void PutSelectedTable(string tableAlias, bool createNewPath)
{
if (SelectedTables == null)
{
SelectedTables = new List <List <string> >();
}
if (SelectedTables.Count == 0)
{
List <string> path = new List <string>();
if (!string.IsNullOrWhiteSpace(tableAlias))
{
path.Add(tableAlias);
}
SelectedTables.Add(path);
}
else
{
if (createNewPath)
{
List <string> path = new List <string>();
if (!string.IsNullOrWhiteSpace(tableAlias))
{
path.Add(tableAlias);
}
SelectedTables.Add(path);
}
else
{
int index = SelectedTables.Count - 1;
List <string> path = SelectedTables[index];
if (path == null)
{
path = new List <string>();
if (!string.IsNullOrWhiteSpace(tableAlias))
{
path.Add(tableAlias);
}
}
else
{
if (!string.IsNullOrWhiteSpace(tableAlias) && !BaseMarkupUtilities.IsExistOnList(path, tableAlias))
{
// path.Insert(0, tableAlias);
path.Add(tableAlias);
}
}
SelectedTables[index] = path;
}
}
}
private void chkListTables_ItemCheck(object sender, ItemCheckEventArgs e)
{
CheckedListBox currentBox = (CheckedListBox)sender;
if (e.NewValue.Equals(CheckState.Checked))
{
SelectedTables.CreateTableEntry(currentBox.Items[e.Index].ToString());
}
else if (e.NewValue.Equals(CheckState.Unchecked))
{
SelectedTables.RemoveTable(currentBox.Items[e.Index].ToString());
}
chkListFields.Tag = currentBox;
}
private void chkListFields_ItemCheck(object sender, ItemCheckEventArgs e)
{
CheckedListBox currentBox = (CheckedListBox)chkListFields.Tag;
CheckState tableChecked = currentBox.GetItemCheckState(currentBox.SelectedIndex);
if (e.NewValue.Equals(CheckState.Checked) && tableChecked == CheckState.Checked)
{
if (!SelectedTables.IsFieldSelected(currentBox.SelectedItem.ToString(), chkListFields.Items[e.Index].ToString()))
{
SelectedTables.AddTableField(currentBox.SelectedItem.ToString(), chkListFields.Items[e.Index].ToString());
}
}
else if (e.NewValue.Equals(CheckState.Unchecked) && tableChecked == CheckState.Checked)
{
SelectedTables.RemoveTableField(currentBox.SelectedItem.ToString(), chkListFields.Items[e.Index].ToString());
}
}
/// <inheritdoc />
public override string Build()
{
var sb = new StringBuilder("SELECT ");
// Output Distinct.
if (IsDistinct)
{
sb.Append("DISTINCT ");
}
// Output column names.
sb.Append(SelectedColumns.Any() ? string.Join(", ", SelectedColumns.Select(WrapVariable)) : "*");
// Output table names.
if (SelectedTables.Any())
{
sb.Append($" FROM {string.Join(", ", SelectedTables.Select(WrapVariable))}");
}
// Output joins.
if (JoinStatement.Any())
{
foreach (var clause in JoinStatement)
{
sb.AppendLine();
switch (clause.JoinType)
{
case JoinType.InnerJoin:
sb.Append("INNER JOIN ");
break;
case JoinType.LeftJoin:
sb.Append("LEFT JOIN ");
break;
case JoinType.RightJoin:
sb.Append("RIGHT JOIN ");
break;
default:
throw new ArgumentOutOfRangeException(nameof(clause.JoinType), $"MySql doesn't support {clause.JoinType} join type.");
}
sb.Append($"`{clause.ToTable}` ON ");
sb.Append(CreateComparisonClause(
$"{clause.ToTable}.{clause.ToColumn}",
clause.ComparisonOperator,
new SqlLiteral($"{clause.FromTable}.{clause.FromColumn}")));
}
}
// Output where statement.
if (WhereStatement.Any())
{
sb.AppendLine();
sb.Append($"WHERE {string.Join(" AND ", WhereStatement.Select(BuildWhereClauseString))}");
}
// Output GroupBy statement.
if (GroupByColumns.Count > 0)
{
sb.AppendLine();
sb.Append($"GROUP BY {string.Join(", ", GroupByColumns.Select(WrapVariable))}");
}
// Output OrderBy statement.
if (OrderByStatement.Any())
{
sb.AppendLine();
sb.Append($"ORDER BY {string.Join(", ", OrderByStatement.Select(BuildOrderByClauseString))}");
}
if (TakeRows.HasValue)
{
sb.AppendLine();
if (SkipRows.HasValue)
{
sb.Append($"LIMIT {SkipRows}, {TakeRows}");
}
else
{
sb.Append($"LIMIT {TakeRows}");
}
}
// Return the built query.
return(sb.ToString());
}
/// <summary>
/// Selects from table.
/// </summary>
/// <param name="table">The table.</param>
public void SelectFromTable(string table)
{
SelectedTables.Clear();
SelectedTables.Add(table);
}
/// <inheritdoc />
public override string Build()
{
var sb = new StringBuilder("SELECT ");
// Output Distinct.
if (IsDistinct)
{
sb.Append("DISTINCT ");
}
if (!SkipRows.HasValue && TakeRows.HasValue)
{
sb.Append($"TOP {TakeRows} ");
if (TopIsPercent)
{
sb.Append("PERCENT ");
}
}
// Output column names.
sb.Append(SelectedColumns.Any() ? string.Join(", ", SelectedColumns.Select(WrapVariable)) : "*");
// Output table names.
if (SelectedTables.Any())
{
sb.Append($" FROM {string.Join(", ", SelectedTables.Select(WrapVariable))}");
}
// Output joins.
if (JoinStatement.Any())
{
foreach (var clause in JoinStatement)
{
sb.AppendLine();
switch (clause.JoinType)
{
case JoinType.InnerJoin:
sb.Append("INNER JOIN ");
break;
case JoinType.OuterJoin:
sb.Append("OUTER JOIN ");
break;
case JoinType.LeftJoin:
sb.Append("LEFT JOIN ");
break;
case JoinType.RightJoin:
sb.Append("RIGHT JOIN ");
break;
}
sb.Append($"[{clause.ToTable}] ON ");
sb.Append(CreateComparisonClause(
$"{clause.ToTable}.{clause.ToColumn}",
clause.ComparisonOperator,
new SqlLiteral($"{clause.FromTable}.{clause.FromColumn}")));
}
}
// Output where statement.
if (WhereStatement.Any())
{
sb.AppendLine();
sb.Append($"WHERE {string.Join(" AND ", WhereStatement.Select(BuildWhereClauseString))}");
}
// Output GroupBy statement.
if (GroupByColumns.Count > 0)
{
sb.AppendLine();
sb.Append($"GROUP BY {string.Join(", ", GroupByColumns.Select(WrapVariable))}");
}
// TODO: Output having statement.
/*
* if (Having.ClauseLevels > 0)
* {
* // Check if a Group By Clause was set
* if (groupByColumns.Count == 0)
* {
* throw new Exception("Having statement was set without Group By");
* }
* if (buildCommand)
* {
* sb.Append(" HAVING " + Having.BuildWhereStatement(() => command));
* }
* else
* {
* sb.Append(" HAVING " + Having.BuildWhereStatement());
* }
* }
*/
// Output OrderBy statement.
if (OrderByStatement.Any())
{
sb.AppendLine();
sb.Append($"ORDER BY {string.Join(", ", OrderByStatement.Select(BuildOrderByClauseString))}");
// Works only in SQL Server 2012 and upper.
// TODO use BETWEEN if it require
if (SkipRows.HasValue)
{
sb.AppendLine();
sb.Append($"OFFSET {SkipRows} ROWS");
if (TakeRows.HasValue)
{
sb.AppendLine();
sb.Append($"FETCH NEXT {TakeRows} ROWS ONLY");
}
}
}
// Return the built query.
return(sb.ToString());
}
