public Discipline FindByPk(string code, int graduationId)
{
Discipline discipline = null;
string query = "SELECT TOP 1 Period.Number as Period_Number, " +
"Graduation.Id as Graduation_Id, Graduation.Name as Graduation_Name, " +
"Discipline.Code as Discipline_Code, Discipline.Name as Discipline_Name, Discipline.TheorycClassesCount as Discipline_TheorycClassesCount, " +
"Discipline.PractiseClassesCount as Discipline_PractiseClassesCount, Discipline.NumberOfCredits as Discipline_NumberOfCredits, " +
"Discipline.Workload as Discipline_Workload, Discipline.ClockHours as Discipline_ClockHours " +
"FROM Discipline " +
"INNER JOIN Period ON Period.Number = Discipline.Period_Number AND Period.Graduation_id = Discipline.Period_Graduation_Id " +
"INNER JOIN Graduation ON Graduation.Id = Period.Graduation_Id " +
"WHERE Graduation.Id = @id AND Discipline.Code = @code";
SqlCommand cmd = new SqlCommand(query, Conn);
cmd.Parameters.Add(new SqlParameter("@id", graduationId));
cmd.Parameters.Add(new SqlParameter("@code", code));
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
discipline = EntityHydratorHelper.HydrateDiscipline(reader);
discipline.Period = EntityHydratorHelper.HydratePeriod(reader);
discipline.Period.Graduation = EntityHydratorHelper.HydrateGraduation(reader);
}
else
{
throw new EntityNotFoundException("Disciplina não encontrada");
}
return(discipline);
}
public Graduation FindByPkWithPeriodsAndDisciplines(int id)
{
Graduation graduation = null;
Period period = null;
Discipline discipline = null;
int periodNumber;
string query = "SELECT Period.Number as Period_Number, " +
"Graduation.Id as Graduation_Id, Graduation.Name as Graduation_Name, " +
"Discipline.Code as Discipline_Code, Discipline.Name as Discipline_Name, Discipline.TheorycClassesCount as Discipline_TheorycClassesCount, " +
"Discipline.PractiseClassesCount as Discipline_PractiseClassesCount, Discipline.NumberOfCredits as Discipline_NumberOfCredits, " +
"Discipline.Workload as Discipline_Workload, Discipline.ClockHours as Discipline_ClockHours " +
"FROM Graduation " +
"LEFT JOIN Period ON Period.Graduation_Id = Graduation.Id " +
"LEFT JOIN Discipline ON Discipline.Period_Graduation_Id = Period.Graduation_Id AND Discipline.Period_Number = Period.Number " +
"WHERE Graduation.Id = @id ORDER BY Period.Number ASC, Discipline.Code ASC";
SqlCommand cmd = new SqlCommand(query, Conn);
cmd.Parameters.Add(new SqlParameter("@id", id));
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
period = null;
discipline = null;
if (graduation == null)
{
graduation = EntityHydratorHelper.HydrateGraduation(reader);
}
periodNumber = -1;
if (int.TryParse(reader["Period_Number"].ToString(), out periodNumber))
{
period = graduation.Periods.SingleOrDefault(p => p.Number == periodNumber);
if (period == null)
{
period = EntityHydratorHelper.HydratePeriod(reader);
period.Graduation = graduation;
graduation.Periods.Add(period);
}
if (reader["Discipline_Code"].ToString().Length > 0)
{
discipline = EntityHydratorHelper.HydrateDiscipline(reader);
discipline.Period = period;
period.Discplines.Add(discipline);
}
}
}
if (graduation == null)
{
throw new EntityNotFoundException("Curso não encontrado");
}
return(graduation);
}
public Period FindByPkWithDisciplines(int graduationId, int periodNumber)
{
Period period = null;
Discipline discipline;
string query = "SELECT Period.Number as Period_Number, " +
"Graduation.Id as Graduation_Id, Graduation.Name as Graduation_Name, " +
"Discipline.Code as Discipline_Code, Discipline.Name as Discipline_Name, Discipline.TheorycClassesCount as Discipline_TheorycClassesCount, " +
"Discipline.PractiseClassesCount as Discipline_PractiseClassesCount, Discipline.NumberOfCredits as Discipline_NumberOfCredits, " +
"Discipline.Workload as Discipline_Workload, Discipline.ClockHours as Discipline_ClockHours " +
"FROM Period " +
"INNER JOIN Graduation ON Graduation.Id = Period.Graduation_id " +
"LEFT JOIN Discipline ON Discipline.Period_Number = Period.Number AND Discipline.Period_Graduation_Id = Period.Graduation_Id " +
"WHERE Period.Graduation_id = @graduation AND Period.Number = @period";
SqlCommand cmd = new SqlCommand(query, Conn);
cmd.Parameters.Add(new SqlParameter("@graduation", graduationId));
cmd.Parameters.Add(new SqlParameter("@period", periodNumber));
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (period == null)
{
period = EntityHydratorHelper.HydratePeriod(reader);
period.Graduation = EntityHydratorHelper.HydrateGraduation(reader);
}
if (reader["Discipline_Code"].ToString().Length > 0)
{
discipline = EntityHydratorHelper.HydrateDiscipline(reader);
discipline.Period = period;
period.Discplines.Add(discipline);
}
}
if (period == null)
{
throw new EntityNotFoundException("Período não encontrado");
}
return(period);
}