public static SemiTrimesterCommentModel Read(SemiTrimesterViewModel semiTrimester, StudentViewModel student)
{
SemiTrimesterCommentModel semiTrimesterComment = null;
using (SqlConnection connection = new SqlConnection(Settings.Default.SQLConnection))
{
connection.Open();
using (SqlCommand command = new SqlCommand($"SELECT * FROM [SemiTrimesterComment] WHERE IdStudent = {student.Id} AND IdSemiTrimester = {semiTrimester.Id}"
+ $" AND [Year] = {semiTrimester.Year}", connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
semiTrimesterComment = new SemiTrimesterCommentModel()
{
Id = (int)reader["Id"],
DivisionPrefectComment = (string)reader["DivisionPrefectComment"],
MainTeacherComment = (string)reader["MainTeacherComment"],
IdStudent = student.Id,
IdSemiTrimester = semiTrimester.Id,
Year = semiTrimester.Year,
};
}
}
}
}
return(semiTrimesterComment);
}
public static IEnumerable <SemiTrimesterViewModel> Read(int year, IEnumerable <PeriodViewModel> periods)
{
List <SemiTrimesterViewModel> semiTrimesters = new List <SemiTrimesterViewModel>();
using (SqlConnection connection = new SqlConnection(Settings.Default.SQLConnection))
{
connection.Open();
using (SqlCommand command = new SqlCommand($"SELECT * FROM [SemiTrimester] WHERE Year = {year}", connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
SemiTrimesterViewModel semiTrimester = new SemiTrimesterViewModel()
{
Year = (int)reader["Year"],
Id = (int)reader["Id"],
};
semiTrimester.Period1 = periods.FirstOrDefault(p => p.Id == (int)reader["idPeriod1"]);
if (!reader.IsDBNull(reader.GetOrdinal("IdPeriod2")))
{
semiTrimester.Period2 = periods.FirstOrDefault(p => p.Id == (int)reader["idPeriod2"]);
}
semiTrimester.Name = (string)reader["Name"];
semiTrimesters.Add(semiTrimester);
}
}
}
}
return(semiTrimesters.OrderBy(s => s.FromDate));
}
public static void Save(SemiTrimesterViewModel semiTrimester)
{
using (SqlConnection connection = new SqlConnection(Settings.Default.SQLConnection))
{
connection.Open();
using (SqlCommand command = new SqlCommand(string.Format("UPDATE [SemiTrimester] SET Name = '{0}', IdPeriod1 = {1}, IdPeriod2 = {2} WHERE [SemiTrimester].Id = {3} AND [SemiTrimester].[Year] = {4}",
semiTrimester.Name, semiTrimester.Period1.Id, semiTrimester.Period2 != null ? semiTrimester.Period2.Id.ToString() : "NULL", semiTrimester.Id, semiTrimester.Year), connection))
{
command.ExecuteNonQuery();
}
}
}
