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");
}
public CommandBuilder AddParameter(CommandParameter param)
{
_params.Add(param.Name, param);
return this;
}