private static LPHSSchool ReadSchool(OracleDataReader reader) { LPHSSchool school = new LPHSSchool(); school.ID = (int)reader.GetDecimal(0); school.Name = OracleDatabase.GetString(reader, 1, null); // reader.IsDBNull(1) ? null : reader.GetString(1), school.Address1 = OracleDatabase.GetString(reader, 2, null); // reader.IsDBNull(2) ? null : reader.GetString(2), school.Address2 = OracleDatabase.GetString(reader, 3, null); // reader.IsDBNull(3) ? null : reader.GetString(3), school.Address3 = OracleDatabase.GetString(reader, 4, null); //reader.IsDBNull(4) ? null : reader.GetString(4), school.City = OracleDatabase.GetString(reader, 5, null); // reader.IsDBNull(5) ? null : reader.GetString(5), school.State = OracleDatabase.GetString(reader, 6, null); // reader.IsDBNull(6) ? null : reader.GetString(6), school.Zip = OracleDatabase.GetString(reader, 7, null); // reader.IsDBNull(7) ? null : reader.GetString(7), school.CoordinatorID = OracleDatabase.GetIntNullable(reader, 8, null); school.Active = OracleDatabase.GetBooleanFromString(reader, 9); school.RegionID = OracleDatabase.GetInt(reader, 10, -1); school.SessionID = OracleDatabase.GetInt(reader, 11, -1); return school; }
public static LPHSSchool Create(LPHSSchool school) { using (new LogScope("LPHSSchool.Create")) { try { Database.Oracle.Execute("LPHS", (conn) => { using (OracleCommand command = new OracleCommand(@" DECLARE SCHOOL_VAL LPHSDB.SCHOOL.SCHOOL_ID%TYPE; BEGIN INSERT INTO LPHSDB.SCHOOL (SCHOOL_ID, SCHOOL_NAME, SCHOOL_ADDRESS_1, SCHOOL_ADDRESS_2, SCHOOL_ADDRESS_3, SCHOOL_CITY, SCHOOL_STATE, SCHOOL_ZIP_CODE, SCHOOL_COORDINATOR_USER_ID, ACTIVE, REGION_ID) VALUES (LPHSDB.SEQ_SCHOOL_ID.NEXTVAL, :Name, :Address1, :Address2, :Address3, :City, :State, :Zip, :Coordinator, :Active, :RegionID) RETURNING school_id INTO SCHOOL_VAL; INSERT INTO LPHSDB.SCHOOL_SESSION (SCHOOL_ID, SCHOOL_SESSION) VALUES(SCHOOL_VAL, :SessionID) RETURNING SCHOOL_VAL into :lastID; END;" , conn)) { //conn.BeginTransaction(); command.BindByName = true; command.Parameters.Add("Name", OracleDbType.Varchar2).Value = school.Name; command.Parameters.Add("Address1", OracleDbType.Varchar2).Value = school.Address1; command.Parameters.Add("Address2", OracleDbType.Varchar2).Value = school.Address2; command.Parameters.Add("Address3", OracleDbType.Varchar2).Value = school.Address3; command.Parameters.Add("City", OracleDbType.Varchar2).Value = school.City; command.Parameters.Add("State", OracleDbType.Varchar2).Value = school.State; command.Parameters.Add("Zip", OracleDbType.Varchar2).Value = school.Zip; command.Parameters.Add("Coordinator", OracleDbType.Int64).Value = school.CoordinatorID.Value; command.Parameters.Add("RegionID", OracleDbType.Int64).Value = school.RegionID; command.Parameters.Add("Active", OracleDbType.Varchar2).Value = OracleDatabase.GetBooleanStringFromBoolean(school.Active); command.Parameters.Add("SessionID", OracleDbType.Int64).Value = school.SessionID; 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(); school.ID = Convert.ToInt32(command.Parameters["lastID"].Value.ToString()); } }, true); return school; } catch (Exception ex) { Log.Write(ex, Log.Mask.Failure); throw; } } }
public static void Update(LPHSSchool school) { using (new LogScope("LPHSSchool.Update")) { try { Database.Oracle.Execute("LPHS", (conn) => { // :schoolid should be a parameter... not sure why that isn't working using (OracleCommand command = new OracleCommand("UPDATE LPHSDB.SCHOOL SET SCHOOL_NAME = :Name, SCHOOL_ADDRESS_1 = :Address1, SCHOOL_ADDRESS_2 = :Address2, SCHOOL_ADDRESS_3 = :Address3, SCHOOL_CITY = :City, SCHOOL_STATE = :State, SCHOOL_ZIP_CODE = :Zip, SCHOOL_COORDINATOR_USER_ID = :Coordinator, ACTIVE = :Active, REGION_ID = :RegionID WHERE SCHOOL_ID = :SchoolID", conn)) { command.BindByName = true; //command.Parameters.Add("SchoolID", OracleDbType.Int64).Value = school.ID; command.Parameters.Add("Name", OracleDbType.Varchar2).Value = school.Name; command.Parameters.Add("Address1", OracleDbType.Varchar2).Value = school.Address1; command.Parameters.Add("Address2", OracleDbType.Varchar2).Value = school.Address2; command.Parameters.Add("Address3", OracleDbType.Varchar2).Value = school.Address3; command.Parameters.Add("City", OracleDbType.Varchar2).Value = school.City; command.Parameters.Add("State", OracleDbType.Varchar2).Value = school.State; command.Parameters.Add("Zip", OracleDbType.Varchar2).Value = school.Zip; command.Parameters.Add("Coordinator", OracleDbType.Int64).Value = school.CoordinatorID.HasValue ? (object)school.CoordinatorID.Value : DBNull.Value; command.Parameters.Add("RegionID", OracleDbType.Int64).Value = school.RegionID; //command.Parameters.Add("SessionID", OracleDbType.Int64).Value = school.SessionID; command.Parameters.Add("Active", OracleDbType.Varchar2).Value = OracleDatabase.GetBooleanStringFromBoolean(school.Active); command.Parameters.Add("SchoolID", OracleDbType.Int64).Value = school.ID; foreach (OracleParameter p in command.Parameters) if (p.Value == null) p.Value = DBNull.Value; command.ExecuteNonQuery(); OracleCommand cmd = new OracleCommand("UPDATE LPHSDB.SCHOOL_SESSION SET SCHOOL_SESSION = :SessionID where SCHOOL_ID = :SchoolID "); cmd.Parameters.Add("SessionID", OracleDbType.Int64).Value = school.SessionID; cmd.Parameters.Add("SchoolID", OracleDbType.Int64).Value = school.ID; cmd.Connection = conn; cmd.ExecuteNonQuery(); } }); } catch (Exception ex) { Log.Write(ex, Log.Mask.Failure); throw; } } }
public static LPHSSchool Get(int schoolId) { using (new LogScope("LPHSSchool.Get")) { try { LPHSSchool school = new LPHSSchool(); StringBuilder oracleSQL = new StringBuilder(); oracleSQL.Append(" SELECT LPHSDB.SCHOOL.SCHOOL_ID, SCHOOL_NAME, SCHOOL_ADDRESS_1, SCHOOL_ADDRESS_2, SCHOOL_ADDRESS_3, SCHOOL_CITY, "); oracleSQL.Append(" SCHOOL_STATE, SCHOOL_ZIP_CODE, SCHOOL_COORDINATOR_USER_ID, ACTIVE, REGION_ID, SCHOOL_SESSION "); oracleSQL.Append(" FROM LPHSDB.SCHOOL, LPHSDB.SCHOOL_SESSION "); oracleSQL.Append(" WHERE LPHSDB.SCHOOL.SCHOOL_ID = LPHSDB.SCHOOL_SESSION.SCHOOL_ID "); oracleSQL.Append(" AND SCHOOL.SCHOOL_ID = :schoolid"); Database.Oracle.Execute("LPHS", (conn) => { using (OracleCommand command = new OracleCommand(oracleSQL.ToString(), conn)) { command.BindByName = true; command.Parameters.Add("schoolid", schoolId); using (var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (reader.Read()) { school = ReadSchool(reader); } } } }); return school; } catch (Exception ex) { Log.Write(ex, Log.Mask.Failure); throw; } } }