string BuildSqlQueryCmd(ExpandedNavigationSelectItem expanded, string condition)
{
string table = string.Format("[{0}]", expanded.NavigationSource.Name);
string cmdSql = "select {0} {1} from {2} {3} {4} {5} {6}";
string top = string.Empty;
string skip = string.Empty;
string fetch = string.Empty;
if (!expanded.CountOption.HasValue && expanded.TopOption.HasValue)
{
if (expanded.SkipOption.HasValue)
{
skip = string.Format("OFFSET {0} ROWS", expanded.SkipOption.Value); ;
fetch = string.Format("FETCH NEXT {0} ROWS ONLY", expanded.TopOption.Value);
top = string.Empty;
}
else
top = "top " + expanded.TopOption.Value;
}
var cmdtxt = string.Format(cmdSql
, top
, expanded.ParseSelect()
, table
, expanded.ParseWhere(condition, this.Model)
, expanded.ParseOrderBy()
, skip
, fetch);
return cmdtxt;
}
string BuildSqlQueryCmd(ExpandedNavigationSelectItem expanded, string condition)
{
string table = string.Format("[{0}]", expanded.NavigationSource.Name);
string cmdTxt = string.Empty;
if (!expanded.CountOption.HasValue && expanded.TopOption.HasValue)
{
if (expanded.SkipOption.HasValue)
{
cmdTxt = string.Format(
@"select t.* from(
select ROW_NUMBER() over ({0}) as rowIndex,{1} from {2} {3}
) as t
where t.rowIndex between {4} and {5}"
, expanded.ParseOrderBy()
, expanded.ParseSelect()
, table
, expanded.ParseWhere(condition, this.Model)
, expanded.SkipOption.Value + 1
, expanded.SkipOption.Value + expanded.TopOption.Value);
}
else
cmdTxt = string.Format("select top {0} {1} from {2} {3} {4}"
, expanded.TopOption.Value
, expanded.ParseSelect()
, table
, expanded.ParseWhere(condition, this.Model)
, expanded.ParseOrderBy());
}
else
{
cmdTxt = string.Format("select {0} from {1} {2} {3} "
, expanded.ParseSelect()
, table
, expanded.ParseWhere(condition, this.Model)
, expanded.ParseOrderBy());
}
return cmdTxt;
}
