private string Sql_Sub( SqlContainer sqlContainer, SqlCommand sqlCommand, IEnumerable <string> left, Sqls.TableTypes tableType, int?commandCount) { var commandText = Sub.GetCommandText( sqlContainer: sqlContainer, sqlCommand: sqlCommand, prefix: "_sub", commandCount: commandCount); return(left != null ? left.Select(columnBracket => Sqls.TableAndColumnBracket( tableName : TableName, tableType : tableType, columnBracket : columnBracket) + Operator + "(" + commandText + ")") .Join(MultiColumnOperator) : Value == null ? "(" + commandText + ")" : "(" + commandText + Operator + Value + ")"); }
public void BuildCommandText( SqlContainer sqlContainer, SqlCommand sqlCommand, StringBuilder commandText, int pageSize, Sqls.TableTypes tableType, int?commandCount) { if (Count > 0) { commandText.Append( "order by ", this .GroupBy(o => o.ColumnBracket) .Select(o => o.FirstOrDefault()) .Select(o => o.Sql( sqlContainer: sqlContainer, sqlCommand: sqlCommand, tableBracket: Sqls.GetTableBracket(o.TableName), tableType: tableType)) .Join(), " "); if (pageSize != 0) { commandText.Append( $"offset {Parameters.Parameter.SqlParameterPrefix}Offset", commandCount.ToString(), $" rows fetch next {Parameters.Parameter.SqlParameterPrefix}PageSize", commandCount.ToString(), " rows only "); } } }
public void BuildCommandText( SqlContainer sqlContainer, SqlCommand sqlCommand, StringBuilder commandText, int?commandCount, bool distinct, int top) { commandText.Append("select "); Build_DistinctClause(commandText, distinct); Build_TopClause( commandText: commandText, top: top); if (this.Any()) { commandText.Append(this .Select(o => o.CommandText( sqlContainer: sqlContainer, sqlCommand: sqlCommand, tableBracket: Sqls.GetTableBracket(o.TableName), commandCount: commandCount)) .Join(), " "); } else { commandText.Append("* "); } RemoveAll(o => o.AdHoc); }
public string Sql( ISqlObjectFactory factory, SqlContainer sqlContainer, ISqlCommand sqlCommand, int?commandCount, string multiClauseOperator = " and ", bool select = false) { if (!select) { this.Where(o => o?.ColumnBrackets != null) .ForEach(where => where.ColumnBrackets = where.ColumnBrackets.Select(o => o.Split('.').Last()).ToArray()); } return(this.Where(o => o != null).Any(o => o.Using) ? Clause + this .Where(o => o != null) .Where(o => o.Using) .Select(o => o.Sql( factory: factory, sqlContainer: sqlContainer, sqlCommand: sqlCommand, tableBracket: Sqls.GetTableBracket(o.TableName), commandCount: commandCount, select: select)) .Join(multiClauseOperator) + " " : string.Empty); }
private string Functions(string tableBracket) { return(Function.ToString().ToLower() + "(" + Sqls.TableAndColumnBracket( tableBracket: tableBracket, columnBracket: ColumnBracket) + ")"); }
private string TableAndColumnBracket( string tableBracket, string columnBracket, bool select) { return(select ? Sqls.TableAndColumnBracket(tableBracket, columnBracket) : columnBracket); }
private string CommandText( ISqlObjectFactory factory, string tableBracket) { var columnBracket = Sqls.TableAndColumnBracket( tableBracket: tableBracket, columnBracket: ColumnBracket); switch (Function) { case Sqls.Functions.Count: return("count(*)"); case Sqls.Functions.Sum: return("sum(" + columnBracket + ")"); case Sqls.Functions.Min: return("min(" + columnBracket + ")"); case Sqls.Functions.Max: return("max(" + columnBracket + ")"); case Sqls.Functions.Avg: return($"avg({factory.Sqls.IsNull}({columnBracket}, 0))"); default: return(columnBracket); } }
public void BuildCommandText( StringBuilder commandText, int pageSize, Sqls.TableTypes tableType, int?commandCount) { if (Count > 0) { commandText.Append( "order by ", this .GroupBy(o => o.ColumnBracket) .Select(o => o.FirstOrDefault()) .Select(o => o.Sql( tableBracket: Sqls.GetTableBracket(o.TableName), tableType: tableType)) .Join(), " "); if (pageSize != 0) { commandText.Append( "offset @_Offset", commandCount.ToString(), " rows fetch next @_PageSize", commandCount.ToString(), " rows only "); } } }
private string CommandText(string tableBracket) { var columnBracket = Sqls.TableAndColumnBracket( tableBracket: tableBracket, columnBracket: ColumnBracket); switch (Function) { case Sqls.Functions.Count: return("count(*)"); case Sqls.Functions.Sum: return("sum(" + columnBracket + ")"); case Sqls.Functions.Min: return("min(" + columnBracket + ")"); case Sqls.Functions.Max: return("max(" + columnBracket + ")"); case Sqls.Functions.Avg: return("avg(" + columnBracket + ")"); default: return(columnBracket); } }
private string Functions(Sqls.TableTypes tableType) { return(Function.ToString().ToLower() + "(" + Sqls.TableAndColumnBracket( tableName: TableName, tableType: tableType, columnBracket: ColumnBracket) + ")"); }
private string Sql_General( string columnBracket, Sqls.TableTypes tableType, int?commandCount) { return (Sqls.TableAndColumnBracket( tableName: TableName, tableType: tableType, columnBracket: columnBracket) + Operator + Variable(commandCount: commandCount)); }
public void BuildCommandText(StringBuilder commandText) { if (this.Any()) { commandText.Append("group by ", this.Select(o => Sqls.TableAndColumnBracket( tableBracket: Sqls.GetTableBracket(o.TableName), columnBracket: o.ColumnBracket)) .Join(), " "); } }
public void BuildCommandText(StringBuilder commandText, Sqls.TableTypes tableType) { if (this.Any()) { commandText.Append("group by ", this.Select(o => Sqls.TableAndColumnBracket( tableName: o.TableName, tableType: tableType, columnBracket: o.ColumnBracket)) .Join(), " "); } }
public string Sql( ISqlObjectFactory factory, SqlContainer sqlContainer, ISqlCommand sqlCommand, string tableBracket, Sqls.TableTypes tableType) { var orderType = " " + OrderType.ToString().ToLower(); if (!Raw.IsNullOrEmpty()) { return(Raw); } else if (Sub != null) { return(Sql_Sub( factory: factory, sqlContainer: sqlContainer, sqlCommand: sqlCommand, orderType: orderType)); } else { string columnBracket = Sqls.TableAndColumnBracket( tableBracket: tableType == Sqls.TableTypes.NormalAndHistory ? string.Empty : tableBracket, columnBracket: ColumnBracket); switch (Function) { case Sqls.Functions.Count: case Sqls.Functions.Sum: case Sqls.Functions.Min: case Sqls.Functions.Max: return (Function.ToString().ToLower() + "(" + columnBracket + ")" + orderType); case Sqls.Functions.Avg: return($"avg({factory.Sqls.IsNull}({columnBracket}, 0)) {orderType}"); default: return(IsNullValue.IsNullOrEmpty() ? columnBracket + orderType : $"{factory.Sqls.IsNull}({columnBracket}, {IsNullValue}) {orderType}"); } } }
public static SqlWhereCollection SqlWhereLike( this SqlWhereCollection self, string searchText, params string[] clauseCollection) { var searchTextCollection = Sqls.SearchTextCollection(searchText); return(self.Add( name: "SearchText", value: searchTextCollection, raw: "(@SearchText#ParamCount#_#CommandCount# = '' or (" + clauseCollection.Join(" or ") + "))", _using: searchTextCollection.Any())); }
public string Sql( SqlContainer sqlContainer, SqlCommand sqlCommand, int?commandCount) { return(this.Any(o => o.Using) ? Clause + this .Where(o => o.Using) .Select(o => o.Sql( sqlContainer: sqlContainer, sqlCommand: sqlCommand, tableBracket: Sqls.GetTableBracket(o.TableName), commandCount: commandCount)) .Join(MultiClauseOperator) + " " : string.Empty); }
private string ReplacedSql( SqlContainer sqlContainer, SqlCommand sqlCommand, IEnumerable <string> left, Sqls.TableTypes tableType, int?commandCount) { return(left != null ? left.Select(columnBracket => Sqls.TableAndColumnBracket( tableName : TableName, tableType : tableType, columnBracket : columnBracket) + ReplacedRaw(commandCount : commandCount)) .Join(MultiColumnOperator) : Raw.Replace("#CommandCount#", commandCount.ToString())); }
private string Sql_General( string columnBracket, Sqls.TableTypes tableType, int?commandCount, IEnumerable <string> valueCollection) { return(valueCollection .Select((o, i) => Sqls.TableAndColumnBracket( tableName: TableName, tableType: tableType, columnBracket: columnBracket) + Operator + Variable( commandCount: commandCount, paramCount: i.ToString() + "_")) .Join(MultiParamOperator)); }
public string Sql( ISqlObjectFactory factory, SqlContainer sqlContainer, ISqlCommand sqlCommand, string tableBracket, Sqls.TableTypes tableType) { var orderType = " " + OrderType.ToString().ToLower(); if (Sub != null) { return(Sql_Sub( factory: factory, sqlContainer: sqlContainer, sqlCommand: sqlCommand, orderType: orderType)); } else { string columnBracket = Sqls.TableAndColumnBracket( tableBracket: tableType == Sqls.TableTypes.NormalAndHistory ? string.Empty : tableBracket, columnBracket: ColumnBracket); switch (Function) { case Sqls.Functions.Count: case Sqls.Functions.Sum: case Sqls.Functions.Min: case Sqls.Functions.Max: case Sqls.Functions.Avg: return (Function.ToString().ToLower() + "(" + columnBracket + ")" + orderType); default: return(columnBracket + orderType); } } }