public static LPHSSession GetSessionBySchoolID(int? schoolIDNullable) { LPHSSession sessList = new LPHSSession(); string sqlQuery = @"SELECT S.SESSION_ID,SESSION_NAME,SESSION_START_DATE,SESSION_END_DATE,SESSION_CREATED_BY_SID FROM LPHSDB.SESSION_TBL S, LPHSDB.SCHOOL_SESSION SS WHERE S.SESSION_ID = SS.SCHOOL_SESSION AND SS.SCHOOL_ID = :schoolID"; using (new LogScope("LPHSSession.GetSessionBySchoolID")) { try { Database.Oracle.Execute("LPHS", (conn) => { using (OracleCommand cmd = new OracleCommand(sqlQuery, conn)) { int schoolID = schoolIDNullable.HasValue ? schoolIDNullable.Value : -1; cmd.Parameters.Add("schoolID", (int)schoolID); OracleDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { sessList = LPHSSession.ReadSession(reader); } } } conn.Close(); }); } catch (Exception ex) { Log.Write(ex, Log.Mask.Failure); throw; } } return sessList; }
public static void Update(LPHSSession session) { using (new LogScope("LPHSSession.Update")) { try { Database.Oracle.Execute("LPHS", (conn) => { using (OracleCommand command = new OracleCommand("UPDATE LPHSDB.SESSION_TBL SET SESSION_NAME = :Name, SESSION_START_DATE = :StartDate, SESSION_END_DATE = :EndDate WHERE SESSION_ID = :SessionID", conn)) { command.BindByName = true; command.Parameters.Add("Name", OracleDbType.Varchar2).Value = session.Name; command.Parameters.Add("StartDate", OracleDbType.Date).Value = session.StartDate; command.Parameters.Add("EndDate", OracleDbType.Date).Value = session.EndDate; command.Parameters.Add("SessionID", OracleDbType.Int64).Value = session.ID; foreach (OracleParameter p in command.Parameters) if (p.Value == null) p.Value = DBNull.Value; command.ExecuteNonQuery(); } }); } catch (Exception ex) { Log.Write(ex, Log.Mask.Failure); throw; } } }
private static LPHSSession ReadSession(OracleDataReader reader) { LPHSSession session = new LPHSSession(); session.ID = (int)reader.GetDecimal(0); session.Name = OracleDatabase.GetString(reader, 1, string.Empty); session.StartDate = OracleDatabase.GetDateTime(reader, 2, DateTime.Now); session.EndDate = OracleDatabase.GetDateTime(reader, 3, DateTime.Now); session.UserSid = OracleDatabase.GetString(reader, 4, string.Empty); return session; }
public static LPHSSession Create(LPHSSession session) { int sessionCreatedID = -1; using (new LogScope("LPHSSession.Create")) { try { Database.Oracle.Execute("LPHS", (conn) => { using (OracleCommand command = new OracleCommand(@" INSERT INTO LPHSDB.SESSION_TBL (SESSION_ID, SESSION_NAME, SESSION_START_DATE, SESSION_END_DATE, SESSION_CREATED_BY_SID) VALUES (LPHSDB.SEQ_SESSION_ID.NEXTVAL, :Name, :StartDate, :EndDate, :UserSid) RETURNING session_id INTO :lastID" , conn)) { command.BindByName = true; command.Parameters.Add("Name", OracleDbType.Varchar2).Value = session.Name; command.Parameters.Add("StartDate", OracleDbType.Date).Value = session.StartDate; command.Parameters.Add("EndDate", OracleDbType.Date).Value = session.EndDate; command.Parameters.Add("UserSid", OracleDbType.Varchar2).Value = session.UserSid; command.Parameters.Add("lastID", OracleDbType.Decimal, System.Data.ParameterDirection.Output); foreach (OracleParameter p in command.Parameters) if (p.Value == null) p.Value = DBNull.Value; command.ExecuteNonQuery(); session.ID = Convert.ToInt32(command.Parameters["lastID"].Value.ToString()); } }); } catch (Exception ex) { if(session.ID >0) LPHSSession.Delete(sessionCreatedID); Log.Write(ex, Log.Mask.Failure); throw; } //create session activity. if (session.ID > 0) { //ensure we always have a session value. session.SessionCopied = session.SessionCopied.HasValue ? session.SessionCopied.Value : 1; try { Database.Oracle.Execute("LPHS", (conn) => { using (OracleCommand command = new OracleCommand(@"INSERT INTO LPHSDB.ACTIVITY_SESSION ( ACTIVITY_ID, SESSION_ID, SUMTOTAL_ACTIVITY_ID, SUMTOTAL_URL) select distinct activity_id, " + session.ID.ToString() + @", SUMTOTAL_ACTIVITY_ID, SUMTOTAL_URL from lphsdb.activity_session where session_id = :sessionIDCopy" , conn)) { command.Parameters.Add("sessionIDCopy", OracleDbType.Decimal).Value = session.SessionCopied.Value; //command.Parameters.Add("sessionID", OracleDbType.Decimal).Value = session.ID; command.ExecuteNonQuery(); } }); } catch (Exception ex) { // LPHSSession.Delete(session.ID); Log.Write(ex, Log.Mask.Failure); throw; } } return session; } }