/// <summary> /// Get an LPHS Activity by id /// </summary> /// <param name="sumTotalActivityId">The activity id.</param> /// <returns></returns> /// <author>David Williams</author> /// <date>12/6/2012</date> public static LPHSActivity Get(int activityId, int? schoolIDNullable) { int schoolID = schoolIDNullable.HasValue ? schoolIDNullable.Value : -1; using (new LogScope("LPHSModule.Get")) { try { String oracleQuery = @"SELECT A.ACTIVITY_ID, A.ACTIVITY_NAME, A.ACTIVITY_DESCRIPTION, A.MODULE_ID, s.SUMTOTAL_ACTIVITY_ID, s.SUMTOTAL_URL, A.REQUIRED FROM LPHSDB.ACTIVITY A INNER JOIN LPHSDB.ACTIVITY_SESSION s ON S.ACTIVITY_ID = A.ACTIVITY_ID INNER JOIN LPHSDB.SCHOOL_SESSION SS ON SS.SCHOOL_ID = :schoolID AND SS.SCHOOL_SESSION = s.SESSION_ID WHERE A.ACTIVITY_ID = :activityID "; LPHSActivity activity = new LPHSActivity() { ID = activityId }; Database.Oracle.Execute("LPHS", (conn) => { using (OracleCommand command = new OracleCommand(oracleQuery.ToString(), conn)) { command.BindByName = true; command.Parameters.Add("activityID", activityId); command.Parameters.Add("schoolID", schoolID); //command.Parameters.Add("sessionID", sessionID); using (var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (reader.Read()) { activity.Name = OracleDatabase.GetString(reader, 1, string.Empty); activity.Description = OracleDatabase.GetString(reader, 2, string.Empty); activity.ModuleId = OracleDatabase.GetInt(reader, 3, 0); activity.SumTotalActivityID = reader.IsDBNull(4) ? null : reader.GetDecimal(4).ToString(); activity.SumTotalURL = reader.IsDBNull(5) ? null : reader.GetString(5); activity.Required = OracleDatabase.GetBooleanFromString(reader, 6); } } } }); return activity; } catch (Exception ex) { Log.Write(ex, Log.Mask.Failure); throw; } } }
public static LPHSActivity Create(LPHSActivity activity) { using (new LogScope("LPHSModule.Create")) { try { return activity; } catch (Exception ex) { Log.Write(ex, Log.Mask.Failure); throw; } } }
public static void Update(LPHSActivity activity) { using (new LogScope("LPHSActivity.Update")) { try { String sql = @"UPDATE LPHSDB.ACTIVITY A SET A.ACTIVITY_NAME = :name, A.ACTIVITY_DESCRIPTION = :description, A.MODULE_ID = :moduleID, A.SUMTOTAL_ACTIVITY_ID = :sumtotalActivityID, A.SUMTOTAL_URL = :sumtotalURL, A.REQUIRED = :required WHERE A.ACTIVITY_ID = :activityID"; Database.Oracle.Execute("LPHS", (conn) => { using (OracleCommand command = new OracleCommand(sql, conn)) { command.BindByName = true; command.Parameters.Add("activityID", OracleDbType.Int64).Value = activity.ID; command.Parameters.Add("name", OracleDbType.Varchar2).Value = activity.Name; command.Parameters.Add("description", OracleDbType.Varchar2).Value = activity.Description; command.Parameters.Add("moduleID", OracleDbType.Int64).Value = activity.ModuleId; command.Parameters.Add("sumtotalActivityID", OracleDbType.Varchar2).Value = activity.SumTotalActivityID; command.Parameters.Add("sumtotalURL", OracleDbType.Varchar2).Value = activity.SumTotalURL; command.Parameters.Add("required", OracleDbType.Varchar2).Value = activity.Required ? Constants.TRUE : Constants.FALSE; foreach (OracleParameter parm in command.Parameters) if (parm.Value == null) parm.Value = DBNull.Value; command.ExecuteNonQuery(); } }); } catch (Exception ex) { Log.Write(ex, Log.Mask.Failure); throw; } } }