public static void Update(LPHSModule module) { using (new LogScope("LPHSModule.Update")) { try { String sql = @"UPDATE LPHSDB.MODULE SET MODULE_NAME = :name, MODULE_DESCRIPTION = :description, COURSE_ID = :courseID WHERE MODULE_ID = :moduleID"; Database.Oracle.Execute("LPHS", (conn) => { using (OracleCommand command = new OracleCommand(sql, conn)) { command.BindByName = true; command.Parameters.Add("moduleID", OracleDbType.Int64).Value = module.ID; command.Parameters.Add("name", OracleDbType.Varchar2).Value = module.Name; command.Parameters.Add("description", OracleDbType.Varchar2).Value = module.Description; command.Parameters.Add("courseID", OracleDbType.Int64).Value = module.CourseId; 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; } } }
/// <summary> /// Return list of all modules /// </summary> /// <author>David Williams</author> /// <date>12/7/2012</date> public static List<LPHSModule> List( int? schoolIDNullable) { int schoolID = schoolIDNullable.HasValue ? schoolIDNullable.Value : -1; using (new LogScope("LPHSModule.List")) { try { Dictionary<int, LPHSModule> modules = new Dictionary<int, LPHSModule>(); Database.Oracle.Execute("LPHS", (conn) => { OracleCommand command = new OracleCommand(@" SELECT m.module_id, m.module_name, m.module_description, a.activity_id, a.activity_name, a.activity_description, aa.sumtotal_activity_id, a.required, aA.sumtotal_url FROM LPHSDB.ACTIVITY a, LPHSDB.MODULE m, LPHSDB.SCHOOL_SESSION ss, LPHSDB.ACTIVITY_SESSION aa WHERE m.module_id = a.module_id AND ss.SCHOOL_ID = :schoolID AND aa.SESSION_ID = ss.SCHOOL_SESSION AND aa.ACTIVITY_ID = a.ACTIVITY_ID ORDER BY a.activity_id, m.module_id ", conn); command.Parameters.Add("schoolID", OracleDbType.Int64).Value = schoolID; using (var reader = command.ExecuteReader()) { while (reader.Read()) { int moduleid = (int)reader.GetDecimal(0); if (!modules.ContainsKey(moduleid)) { modules[moduleid] = new LPHSModule { ID = moduleid, Name = reader.GetString(1), Description = reader.IsDBNull(2) ? null : reader.GetString(2), Activities = new List<LPHSActivity>() }; } modules[moduleid].Activities.Add(new LPHSActivity { ModuleId = moduleid, ID = (int)reader.GetDecimal(3), Name = reader.GetString(4), Description = reader.IsDBNull(5) ? null : reader.GetString(5), SumTotalActivityID = reader.IsDBNull(6) ? null : reader.GetDecimal(6).ToString(), Required = OracleDatabase.GetBooleanFromString(reader, 7), // reader.IsDBNull(7) ? -1 : (int)reader.GetDecimal(6), ModuleName = OracleDatabase.GetString(reader, 1, string.Empty), SumTotalURL = OracleDatabase.GetString(reader, 8, null) }); } } }); return modules.Values.ToList(); } catch (Exception ex) { Log.Write(ex, Log.Mask.Failure); throw; } } }
/// <summary> /// Get single module with activities for this user. /// </summary> /// <param name="userId">The user id.</param> /// <author>David Williams</author> /// <date>12/7/2012</date> public static LPHSModule Get(int moduleId, int? schoolIDNullable) { int schoolID = schoolIDNullable.HasValue ? schoolIDNullable.Value : -1; using (new LogScope("LPHSModule.Get")) { try { LPHSModule lphsModule = new LPHSModule(); Database.Oracle.Execute("LPHS", (conn) => { string oracleQuery = @" SELECT m.module_id, m.module_name, m.module_description, a.activity_id, a.activity_name, a.activity_description, aa.sumtotal_activity_id, a.required, aA.sumtotal_url FROM LPHSDB.ACTIVITY a, LPHSDB.MODULE m, LPHSDB.SCHOOL_SESSION ss, LPHSDB.ACTIVITY_SESSION aa WHERE m.module_id = a.module_id AND ss.SCHOOL_ID = :schoolID AND aa.SESSION_ID = ss.SCHOOL_SESSION AND aa.ACTIVITY_ID = a.ACTIVITY_ID AND m.module_id = :moduleID ORDER BY a.activity_id, m.module_id "; OracleCommand command = new OracleCommand(oracleQuery, conn); command.BindByName = true; command.Parameters.Add("moduleId", (int)moduleId); command.Parameters.Add("SchoolId", (int)schoolID); using (var reader = command.ExecuteReader()) { if (reader.Read()) { // Initialize Module lphsModule = new LPHSModule() { ID = OracleDatabase.GetInt(reader, 0, 0), Name = OracleDatabase.GetString(reader, 1, string.Empty), Description = OracleDatabase.GetString(reader, 2, string.Empty), CourseId = OracleDatabase.GetIntNullable(reader, 3, null), Activities = new List<LPHSActivity>() }; // Add first activity to module lphsModule.Activities.Add(new LPHSActivity() { ID = OracleDatabase.GetInt(reader, 4, 0), Name = OracleDatabase.GetString(reader, 5, string.Empty), Description = OracleDatabase.GetString(reader, 6, null), SumTotalActivityID = reader.IsDBNull(7) ? null : reader.GetDecimal(7).ToString(), SumTotalURL = OracleDatabase.GetString(reader, 8, string.Empty) }); }; //while (reader.Read()) //{ // // Add additional activiites for this module // lphsModule.Activities.Add(new LPHSActivity() // { // ID = OracleDatabase.GetInt(reader, 2, 0), // Name = OracleDatabase.GetString(reader, 3, string.Empty), // Description = OracleDatabase.GetString(reader, 4, null), // SumTotalActivityID = reader.IsDBNull(5) ? null : reader.GetDecimal(5).ToString(), // SumTotalURL = OracleDatabase.GetString(reader, 6, string.Empty) // }); //} } }); return lphsModule; } catch (Exception ex) { Log.Write(ex, Log.Mask.Failure); throw; } } }