public static void Update(LPHSUserActivityResult userActivityResult) { Database.Oracle.Execute("LPHS", (conn) => { String oracleQuery = @" UPDATE LPHSDB.USER_ACTIVITY_RESULT SET ACTIVITY_STATUS = :status, ACTIVITY_DATE_COMPLETED = :dateCompleted, ACTIVITY_SCORE = :score WHERE (ACTIVITY_ID = :sumTotalActivityId) AND (USER_ID = :userId) "; using (OracleCommand command = new OracleCommand(oracleQuery.ToString(), conn)) { try { command.BindByName = true; command.Parameters.Add("userId", OracleDbType.Int32).Value = userActivityResult.UserId; command.Parameters.Add("sumTotalActivityId", OracleDbType.Int32).Value = userActivityResult.ActivityId; command.Parameters.Add("status", OracleDbType.Varchar2).Value = (int)userActivityResult.Status;// OracleDatabase.GetBooleanStringFromBooleanNullable(userActivityResult.Registered); command.Parameters.Add("dateCompleted", OracleDbType.Date).Value = userActivityResult.DateCompleted; command.Parameters.Add("score", OracleDbType.Double).Value = userActivityResult.Score; command.ExecuteNonQuery(); } catch (Exception ex) { Log.Write(ex, Log.Mask.Failure); return; } } }); }
//// Flattend view For RDLC grade report //public string ActivityName { get; set; } //public string UserName { get; set; } //public bool Completed { get { return (Status == LPHSUserActivityResultStatus.Completed); } } //public int ModuleId { get; set; } //public string ModuleName { get; set; } //public string UserDisplayName { get; set; } public static LPHSUserActivityResult Get(int userId, int activityId) { String oracleQuery = @" SELECT ACTIVITY_ID, USER_ID, ACTIVITY_STATUS, ACTIVITY_DATE_COMPLETED, ACTIVITY_SCORE, REGISTERED FROM LPHSDB.USER_ACTIVITY_RESULT WHERE USER_ID = :userId AND ACTIVITY_ID = :sumTotalActivityId "; LPHSUserActivityResult userActResult = new LPHSUserActivityResult(); Database.Oracle.Execute("LPHS", (conn) => { using (OracleCommand command = new OracleCommand(oracleQuery.ToString(), conn)) { command.BindByName = true; command.Parameters.Add("userId", userId); command.Parameters.Add("sumTotalActivityId", activityId); using (var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { if (reader.HasRows) { while (reader.Read()) { userActResult.ActivityId = OracleDatabase.GetInt(reader, 0, 0); userActResult.UserId = OracleDatabase.GetInt(reader, 1, 0); userActResult.Status = (LPHSUserActivityResultStatus)Convert.ToInt32((reader.IsDBNull(2) ? "0" : reader.GetString(2))); userActResult.DateCompleted = OracleDatabase.GetDateTimeNullable(reader, 3); userActResult.Score = OracleDatabase.GetIntNullable(reader, 4, null); userActResult.Registered = OracleDatabase.GetBooleanFromStringNullable(reader, 4, null); } } } } }); return userActResult; }