private void onDB2VM(SqlDataReader reader, LearnHistoryUIViewModel vm) { Int32 idx = 0; vm.HID = reader.GetInt32(idx++); vm.UserID = reader.GetString(idx++); vm.UserDisplayAs = reader.GetString(idx++); vm.ObjectID = reader.GetInt32(idx++); vm.ObjectName = reader.GetString(idx++); vm.LearnDate = reader.GetDateTime(idx++); if (!reader.IsDBNull(idx)) { vm.Comment = reader.GetString(idx++); } else { ++idx; } if (!reader.IsDBNull(idx)) { vm.CreatedBy = reader.GetString(idx++); } else { ++idx; } if (!reader.IsDBNull(idx)) { vm.CreatedAt = reader.GetDateTime(idx++); } else { ++idx; } if (!reader.IsDBNull(idx)) { vm.UpdatedBy = reader.GetString(idx++); } else { ++idx; } if (!reader.IsDBNull(idx)) { vm.UpdatedAt = reader.GetDateTime(idx++); } else { ++idx; } }
public async Task <IActionResult> Get([FromQuery] Int32 hid, Int32 top = 100, Int32 skip = 0) { if (hid <= 0) { return(BadRequest("No Home Inputted")); } BaseListViewModel <LearnHistoryUIViewModel> listVm = new BaseListViewModel <LearnHistoryUIViewModel>(); SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; String queryString = ""; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; String usrName = ""; String scopeFilter = String.Empty; try { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; //var scopeObj = HIHAPIUtility.GetScopeClaim(this, HIHAPIConstants.LearnHistoryScope); //scopeFilter = HIHAPIUtility.GetScopeSQLFilter(scopeObj.Value, usrName); } catch { return(BadRequest("Not valid HTTP HEAD: User and Scope Failed!")); } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } try { queryString = this.getSQLString(true, top, skip, scopeFilter, hid); using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, hid, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } cmd = new SqlCommand(queryString, conn); reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { listVm.TotalCount = reader.GetInt32(0); break; } } reader.NextResult(); if (reader.HasRows) { while (reader.Read()) { LearnHistoryUIViewModel vm = new LearnHistoryUIViewModel(); onDB2VM(reader, vm); listVm.Add(vm); } } } } catch (Exception exp) { System.Diagnostics.Debug.WriteLine(exp.Message); strErrMsg = exp.Message; if (errorCode == HttpStatusCode.OK) { errorCode = HttpStatusCode.InternalServerError; } } finally { if (reader != null) { reader.Dispose(); reader = null; } if (cmd != null) { cmd.Dispose(); cmd = null; } if (conn != null) { conn.Dispose(); conn = null; } } if (errorCode != HttpStatusCode.OK) { switch (errorCode) { case HttpStatusCode.Unauthorized: return(Unauthorized()); case HttpStatusCode.NotFound: return(NotFound()); case HttpStatusCode.BadRequest: return(BadRequest(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(listVm, setting)); }
public async Task <IActionResult> Get(String sid) { if (String.IsNullOrEmpty(sid)) { return(BadRequest("No data is inputted")); } String usrName = ""; String scopeFilter = String.Empty; try { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; //var scopeObj = HIHAPIUtility.GetScopeClaim(this, HIHAPIConstants.LearnHistoryScope); //scopeFilter = HIHAPIUtility.GetScopeSQLFilter(scopeObj.Value, usrName); } catch { return(BadRequest("Not valid HTTP HEAD: User and Scope Failed!")); } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } LearnHistoryUIViewModel vm = new LearnHistoryUIViewModel(); SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; String queryString = ""; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; try { vm.ParseGeneratedKey(sid); queryString = this.getSQLString(false, null, null, String.Empty, null); using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, vm.HID, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } cmd = new SqlCommand(queryString, conn); cmd.Parameters.AddWithValue("@HID", vm.HID); cmd.Parameters.AddWithValue("@USERID", vm.UserID); cmd.Parameters.AddWithValue("@OBJECTID", vm.ObjectID); cmd.Parameters.AddWithValue("@LEARNDATE", vm.LearnDate); reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { onDB2VM(reader, vm); // It should return one entry only! // Nevertheless, ensure the code only execute once in API layer to keep toilence of dirty DB data; break; } } else { errorCode = HttpStatusCode.NotFound; throw new Exception(); } } } catch (Exception exp) { System.Diagnostics.Debug.WriteLine(exp.Message); strErrMsg = exp.Message; if (errorCode == HttpStatusCode.OK) { errorCode = HttpStatusCode.InternalServerError; } } finally { if (reader != null) { reader.Dispose(); reader = null; } if (cmd != null) { cmd.Dispose(); cmd = null; } if (conn != null) { conn.Dispose(); conn = null; } } if (errorCode != HttpStatusCode.OK) { switch (errorCode) { case HttpStatusCode.Unauthorized: return(Unauthorized()); case HttpStatusCode.NotFound: return(NotFound()); case HttpStatusCode.BadRequest: return(BadRequest(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } // Only return the meaningful object var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(vm, setting)); }