Beispiel #1
0
        public override string SelectSql(SqlTableNameWithAlias tableName, IEnumerable <SqlExpressionWithAlias> columns, string sqlWhere, IEnumerable <SqlJoinDefinition> joins = null, string sqlSortExpression = null, int?start = null, int?numRecords = null, string afterSelect = null)
        {
            if (start == null && numRecords == null)
            {
                return(base.SelectSql(tableName, columns, sqlWhere, joins, sqlSortExpression));
            }

            if (start == null)
            {
                return(base.SelectSql(tableName, columns, sqlWhere, joins, sqlSortExpression, afterSelect: "TOP " + numRecords));
            }

            string subQueryAlias = SqlNameGenerator.NextTableAlias();
            string rowNumAlias   = SqlNameGenerator.NextFieldAlias();

            int end = (numRecords == null ? int.MaxValue : (numRecords.Value + start - 1)).Value;

            SqlExpressionWithAlias rowNumExpression = new SqlExpressionWithAlias("row_number() over (order by " + sqlSortExpression + ")", rowNumAlias);

            string[] parts =
            {
                "select",
                subQueryAlias + ".*",
                "from",
                "(",
                base.SelectSql(tableName,columns.Union(new[]  { rowNumExpression }), sqlWhere, joins),
                ")",
                "as",
                subQueryAlias,
                "where",
                rowNumAlias,
                "between",
                start.ToString(),
                "and",
                end.ToString(),
                "order by",
                rowNumAlias
            };

            return(string.Join(" ", parts));
        }
Beispiel #2
0
        public override string SelectSql(SqlTableNameWithAlias tableName, IEnumerable <SqlExpressionWithAlias> columns, string sqlWhere, IEnumerable <SqlJoinDefinition> joins = null, string sqlSortExpression = null, int?start = null, int?numRecords = null, string afterSelect = null)
        {
            if (start == null && numRecords == null)
            {
                return(base.SelectSql(tableName, columns, sqlWhere, joins, sqlSortExpression));
            }

            if (start == null)
            {
                return(base.SelectSql(tableName, columns, sqlWhere, joins, sqlSortExpression, numRecords: numRecords));
            }

            string sql = base.SelectSql(tableName, columns, sqlWhere, joins, sqlSortExpression, numRecords: numRecords);

            if (start.Value > 1)
            {
                sql += " OFFSET " + (start - 1);
            }

            return(sql);
        }
Beispiel #3
0
        public override string DeleteSql(SqlTableNameWithAlias tableName, string sqlWhere, IEnumerable <SqlJoinDefinition> joins)
        {
            if (joins != null && joins.Any())
            {
                string sql = "delete from " + QuoteTable(tableName.TableName) + (tableName.Alias != null ? (" " + tableName.Alias + " ") : "");

                sql += "using " + string.Join(",", joins.Select(join => QuoteTable(join.Right.Schema.MappedName) + " " + join.Right.Alias));

                if (sqlWhere != null)
                {
                    sql += $" where ({sqlWhere})";
                }

                sql += " and ";

                sql += string.Join(" and ", joins.Select(join => QuoteField(join.Left.Alias + "." + @join.Left.Field.MappedName) + "=" + QuoteField(join.Right.Alias + "." + @join.Right.Field.MappedName)));

                return(sql);
            }

            return("delete from " + QuoteTable(tableName.TableName) + (tableName.Alias != null ? (" " + tableName.Alias + " ") : "") + (sqlWhere != null ? (" where " + sqlWhere) : ""));
        }