private List <string> GetParameterReady() { List <string> p = new List <string>(); p.Add(string.Join(",", SelectedColumns.Select(c => c.Name.ToUpper()).ToArray())); p.Add(string.Join(",", SelectedColumns.Select(c => c.Rem).ToArray())); p.Add(string.Join(",", SelectedColumns.Select(c => { if (c.IsContinuous) { return("0"); } else { return(c.KindCount.ToString()); } }).ToArray())); p.Add(string.Join(",", AndColumns.Select(c => c.Name.ToUpper()).ToArray())); p.Add(string.Join(",", AndColumns.Select(c => c.Rem).ToArray())); p.Add(string.Join(",", AndColumns.Select(c => { if (c.IsContinuous) { return("0"); } else { return(c.KindCount.ToString()); } }).ToArray())); return(p); }
private string BuildSelectClause() { return(string.Join(",", SelectedColumns.Select(s => { var tableName = (string.IsNullOrWhiteSpace(s.TableName) || s.TableName == FromTableName) ? FromTableAlias : s.TableName; return $"[{tableName}].[{s.ColumnName}] AS [{s.AliasName}]"; }))); }
private List <string> GetParameterReady() { List <string> p = new List <string>(); p.Add(string.Join(",", SelectedColumns.Select(c => c.Name.ToUpper()).ToArray())); p.Add(string.Join(",", SelectedColumns.Select(c => c.Rem).ToArray())); p.Add(string.Join(",", SelectedColumns.Select(c => c.KindCount).ToArray())); p.Add(FilterValue.ToString()); return(p); }
private List <string> GetParameterReady() { List <string> p = new List <string>(); p.Add(ResultColumn.Name.ToUpper()); p.Add(ResultColumn.Rem); p.Add(string.Join(",", SelectedColumns.Select(c => c.Name.ToUpper()).ToArray())); p.Add(string.Join(",", SelectedColumns.Select(c => c.Rem).ToArray())); p.Add(string.Join(",", SelectedColumns.Select(c => c.KindCount).ToArray())); p.Add(DataType_ClassMethod.ToString()); return(p); }
private List <string> GetParameterReady() { List <string> p = new List <string>(); p.Add(string.Join(",", SelectedColumns.Select(c => c.Name.ToUpper()).ToArray())); p.Add(string.Join(",", SelectedColumns.Select(c => c.Rem).ToArray())); p.Add(string.Join(",", SelectedColumns.Select(c => { if (c.IsContinuous) { return("0"); } else { return(c.KindCount.ToString()); } }).ToArray())); p.Add(jyfield.ToString()); p.Add(jzfield.ToString()); if (IsSelectGroup > 0) { p.Add("1"); p.Add(GroupingColumn.Name.ToUpper() + "|" + string.Join("|", GroupingColumn.GroupingTags.Select(c => GroupingColumn.Name.ToUpper() + "." + c.Index).ToArray())); //p.Add(GroupingColumn.Name.ToUpper()); p.Add(GroupingColumn.Rem + "|" + string.Join("|", GroupingColumn.GroupingTags.Select(c => " " + c.Index).ToArray())); //p.Add(GroupingColumn.Rem.ToUpper()); } else { p.Add("0"); } //var sbName = new List<string>(); //sbName.Add(GroupingColumn.Name.ToUpper()); //foreach (var item in GroupingTags) //{ // sbName.Add(GroupingColumn.Name + "." + item.Index); //} //var sbLabel = new List<string>(); //sbLabel.Add(GroupingColumn.Rem); //foreach (var item in GroupingTags) //{ // sbLabel.Add(" " + item.Name); //} //p.Add(string.Join("|", sbName)); //p.Add(string.Join("|", sbLabel)); return(p); }
private List <string> GetParameterReady() { List <string> p = new List <string>(); p.Add(string.Join(",", SelectedColumns.Select(c => c.Name.ToUpper()).ToArray())); p.Add(string.Join(",", SelectedColumns.Select(c => c.Rem).ToArray())); p.Add(string.Join(",", SelectedColumns.Select(c => c.KindCount).ToArray())); foreach (var item in RelativeColumns) { p.Add(item.DistroType); p.Add(item.LinkFunction); } p.Add(string.Join(",", RelativeColumns.Select(c => c.Name.ToUpper()).ToArray())); p.Add(string.Join(",", RelativeColumns.Select(c => c.Rem).ToArray())); p.Add(string.Join(",", RelativeColumns.Select(c => c.KindCount).ToArray())); p.Add(jyField.ToString()); return(p); }
private List <string> GetParameterReady() { List <string> p = new List <string>(); List <string> nameList = new List <string>(); foreach (var item in SelectedColumns) { nameList.Add(item.Name.ToUpper() + "|" + string.Join("|", item.GroupingTags.Select(c => item.Name.ToUpper() + "." + c.Index).ToArray())); } p.Add(string.Join("|", nameList.ToArray())); List <string> remList = new List <string>(); foreach (var item in SelectedColumns) { remList.Add(item.Rem + "|" + string.Join("|", item.GroupingTags.Select(c => " " + c.Index).ToArray())); } p.Add(string.Join("|", remList.ToArray())); //p.Add(string.Join(",", SelectedColumns.Select(c => c.Name.ToUpper()).ToArray())); //p.Add(string.Join(",", SelectedColumns.Select(c => c.Rem).ToArray())); p.Add(string.Join(",", SelectedColumns.Select(c => { if (c.IsContinuous) { return("0"); } else { return(c.KindCount.ToString()); } }).ToArray())); p.Add(rytxt); return(p); }
private List <string> GetParameterReady() { List <string> p = new List <string>(); p.Add(string.Join(",", SelectedColumns.Select(c => c.Name.ToUpper()).ToArray())); p.Add(string.Join(",", SelectedColumns.Select(c => c.Rem).ToArray())); p.Add(string.Join(",", SelectedColumns.Select(c => { if (c.IsContinuous) { return("0"); } else { return(c.KindCount.ToString()); } }).ToArray())); var sbName = new List <string>(); sbName.Add(GroupingColumn.Name.ToUpper()); foreach (var item in GroupingTags) { sbName.Add(GroupingColumn.Name.ToUpper() + "." + item.Index); } var sbLabel = new List <string>(); sbLabel.Add(GroupingColumn.Rem); foreach (var item in GroupingTags) { sbLabel.Add(" " + item.Name); } p.Add(string.Join("|", sbName)); p.Add(string.Join("|", sbLabel)); p.Add(bzField.ToString()); p.Add(jyField.ToString()); return(p); }
private List <string> GetParameterReady() { List <string> p = new List <string>(); p.Add(string.Join(",", SelectedColumns.Select(c => c.Name.ToUpper()).ToArray())); p.Add(string.Join(",", SelectedColumns.Select(c => c.Rem).ToArray())); p.Add(string.Join(",", SelectedColumns.Select(c => { if (c.IsContinuous) { return("0"); } else { return(c.KindCount.ToString()); } }).ToArray())); p.Add(GroupingColumn.Name.ToUpper()); p.Add(GroupingColumn.Rem); p.Add(scField.ToString()); p.Add(jqField.ToString()); p.Add(rowGroupSign.ToString()); if (rowGroupSign == 1) { p.Add(GroupingRowColumn.Name.ToUpper()); p.Add(GroupingRowColumn.Rem); } else { p.Add("NA"); p.Add("NA"); } return(p); }
/// <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()); }
/// <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()); }