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;
}
}
}