static string BuildSqlQueryCmd(ODataQueryOptions options, string target = "")
{
var cxt = options.Context;
string table = target;
if (string.IsNullOrEmpty(target))
table = string.Format("[{0}]", cxt.Path.Segments[0].ToString());
string cmdSql = "select {0} {1} from {2} {3} {4} {5} {6}";
string top = string.Empty;
string skip = string.Empty;
string fetch = string.Empty;
if (options.Count == null && options.Top != null)
{
if (options.Skip != null)
{
skip = string.Format("OFFSET {0} ROWS", options.Skip.RawValue); ;
fetch = string.Format("FETCH NEXT {0} ROWS ONLY", options.Top.RawValue);
top = string.Empty;
}
else
top = "top " + options.Top.RawValue;
}
var cmdtxt = string.Format(cmdSql
, top
, options.ParseSelect()
, table
, options.ParseWhere()
, options.ParseOrderBy()
, skip
, fetch);
return cmdtxt;
}
static string BuildSqlQueryCmd(ODataQueryOptions options, string target = "")
{
var cxt = options.Context;
string table = target;
if (string.IsNullOrEmpty(target))
table = string.Format("[{0}]", cxt.Path.Segments[0].ToString());
string cmdTxt = string.Empty;
if (options.Count == null && options.Top != null)
{
if (options.Skip != null)
{
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}"
, options.ParseOrderBy()
, options.ParseSelect()
, table
, options.ParseWhere()
, options.Skip.Value + 1
, options.Skip.Value + options.Top.Value);
}
else
cmdTxt = string.Format("select top {0} {1} from {2} {3} {4}"
, options.Top.RawValue
, options.ParseSelect()
, table
, options.ParseWhere()
, options.ParseOrderBy());
}
else
{
cmdTxt = string.Format("select {0} from {1} {2} {3} "
, options.ParseSelect()
, table
, options.ParseWhere()
, options.ParseOrderBy());
}
return cmdTxt;
}