private void AppendPagingEnd(ISelectQuery query)
        {
            /*
                 with
                    __DATA as (SELECT...),
                    __COUNT as (select count(0) as _ROWCOUNT from __DATA)
                select * from __COUNT, __DATA
                order by s.SalesOrderID
                offset 0 rows fetch next 10 rows only*/

            _cmd.AppendLine(
                "),\n__COUNT AS (SELECT COUNT(0) AS __ROWCOUNT FROM __DATA)\nSELECT * FROM __COUNT, __DATA")
                .Append("ORDER BY ");

            if (query.OrderByColumns.Any())
            {
                var comma = "";
                foreach (var col in query.OrderByColumns)
                {
                    _cmd.AppendFormat("{0}__DATA.{1} {2}", comma, col.Column.Alias, col.Desc ? "DESC" : "ASC");
                    comma = ",";
                }
            }
            else
            {
                var fromCol = (query.SelectColumns.Values.FirstOrDefault(c => c.Meta.Identity && c.Table == query.From) ??
                               query.SelectColumns.Values.FirstOrDefault(c => c.Meta.Identity)) ??
                               query.SelectColumns.Values.First();

                _cmd.AppendFormat("__DATA.{0}", fromCol.Alias);
            }

            var offsetParam = new CommandParameter
            {
                Name = "pOffset",
                Value = (query.Page - 1) * query.PageSize,
                ParameterMeta = new ParameterMeta { DbType = DbType.Int32 }
            };

            var limitParam = new CommandParameter
            {
                Name = "pLimit",
                Value = query.PageSize,
                ParameterMeta = new ParameterMeta { DbType = DbType.Int32 }
            };

            _param.Add(offsetParam.Name, offsetParam);
            _param.Add(limitParam.Name, limitParam);

            _cmd.AppendLine()
                .Append("OFFSET @pOffset ROWS FETCH NEXT @pLimit ROWS ONLY");
        }
Esempio n. 2
0
 public CommandBuilder AddParameter(CommandParameter param)
 {
     _params.Add(param.Name, param);
     return this;
 }