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; }