public EdmEntityObject Get(string key, ODataQueryOptions queryOptions)
{
var cxt = queryOptions.Context;
var entityType = cxt.ElementType as EdmEntityType;
if (this.PermissionCheck != null && !this.PermissionCheck(MethodType.Get, entityType.Name))
{
throw new UnauthorizedAccessException();
}
var keyDefine = entityType.DeclaredKey.First();
string cmdSql = "select {0} from [{1}] where [{2}]=@{2}";
var cmdTxt = string.Format(cmdSql
, queryOptions.ParseSelect()
, cxt.Path.Segments[0].ToString()
, keyDefine.Name);
EdmEntityObject entity = new EdmEntityObject(entityType);
using (DbAccess db = new DbAccess(this.ConnectionString))
{
db.ExecuteReader(cmdTxt,
(reader) =>
{
for (int i = 0; i < reader.FieldCount; i++)
{
reader.SetEntityPropertyValue(i, entity);
}
},
(par) => { par.AddWithValue("@" + keyDefine.Name, key.ChangeType(keyDefine.Type.PrimitiveKind())); },
CommandType.Text);
}
return entity;
}
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;
}