Beispiel #1
0
        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);
 }
Beispiel #5
0
 private string Functions(string tableBracket)
 {
     return(Function.ToString().ToLower() +
            "(" + Sqls.TableAndColumnBracket(
                tableBracket: tableBracket,
                columnBracket: ColumnBracket) + ")");
 }
Beispiel #6
0
 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 ");
         }
     }
 }
Beispiel #9
0
        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);
            }
        }
Beispiel #10
0
 private string Functions(Sqls.TableTypes tableType)
 {
     return(Function.ToString().ToLower() +
            "(" + Sqls.TableAndColumnBracket(
                tableName: TableName,
                tableType: tableType,
                columnBracket: ColumnBracket) + ")");
 }
Beispiel #11
0
 private string Sql_General(
     string columnBracket, Sqls.TableTypes tableType, int?commandCount)
 {
     return
         (Sqls.TableAndColumnBracket(
              tableName: TableName,
              tableType: tableType,
              columnBracket: columnBracket) +
          Operator +
          Variable(commandCount: commandCount));
 }
Beispiel #12
0
 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(), " ");
     }
 }
Beispiel #13
0
 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(), " ");
     }
 }
Beispiel #14
0
        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}");
                }
            }
        }
Beispiel #15
0
        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()));
        }
Beispiel #16
0
 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);
 }
Beispiel #17
0
 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()));
 }
Beispiel #18
0
 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);
                }
            }
        }