public void DeleteStudentAndLections(StudentId studId, LectionId lectionId)
{
string sql = "Delete from StudentAndLections " +
"Where studentId = @studentId and lectionId = @lectionId ";
try
{
using (var sqlConnection = new SqlConnection(_connectionString))
{
sqlConnection.Open();
using (SqlCommand command = new SqlCommand(sql, sqlConnection))
{
command.Parameters.AddWithValue("@studentId", studId.Id);
command.Parameters.AddWithValue("@lectionId", lectionId.Id);
command.ExecuteNonQuery();
}
}
}
catch (SqlException sqlException)
{
_logger.Log(string.Format("You have an error with sql:\n {0}", sqlException));
}
catch (ArgumentNullException argException)
{
_logger.Log(string.Format("You have a null argument:\n {0}", argException));
}
}
public void UpdateLection(LectionId lectionId, Lection lection)
{
if (lection == null)
{
_logger.Log(string.Format("You sent a null lection:\n {0}", nameof(NullReferenceException)));
}
string sql = "Update lection Set " +
"lectorId = @lectorId, lectionName = @lectionName, lectionData = @lectionData " +
"Where lectionId = @lectionId";
try
{
using (var sqlConnection = new SqlConnection(_connectionString))
{
sqlConnection.Open();
using (SqlCommand command = new SqlCommand(sql, sqlConnection))
{
command.Parameters.AddWithValue("@lectionId", lectionId.Id);
command.Parameters.AddWithValue("@lectorId", lection.lector.lectorId.Id);
command.Parameters.AddWithValue("@lectionName", lection.lectionName);
command.Parameters.AddWithValue("@lectionData", lection.lectionData);
command.ExecuteNonQuery();
}
}
}
catch (SqlException sqlException)
{
_logger.Log(string.Format("You have an error with sql:\n {0}", sqlException));
}
catch (ArgumentNullException argException)
{
_logger.Log(string.Format("You have a null argument:\n {0}", argException));
}
}
public Lection(LectionId lectionId, Lector lector, string name, DateTime dateTime)
{
this.lectionId = lectionId;
this.lector = lector;
lectionName = name;
lectionData = dateTime;
}
public Lection GetLecionById(LectionId lectionId)
{
var result = new Lection();
result.lector = new Lector();
string sql = string.Format("SELECT L.LectionId, L.LectionName, L.LectionData, " +
"LRS.FirstName, LRS.SecondName, LRS.Email, LRS.LectorId " +
"From Lection, Lectors " +
"INNER JOIN Lection L ON L.LectionId = L.LectionId " +
"INNER JOIN Lectors LRS ON L.LectorId = LRS.LectorId " +
"Where L.LectionId = @LectionId ");
try
{
using (var sqlConnection = new SqlConnection(_connectionString))
{
sqlConnection.Open();
using (SqlCommand command = new SqlCommand(sql, sqlConnection))
{
command.Parameters.AddWithValue("@LectionId", lectionId.Id);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
if (!Int32.TryParse(reader["LectionId"].ToString(), out result.lectionId.Id))
{
//TODO Add exception Name and Logging
throw new FormatException();
}
if (!Int32.TryParse(reader["LectorId"].ToString(), out result.lector.lectorId.Id))
{
//TODO Add exception Name and Logging
throw new FormatException();
}
result.lector.firstName = (string)reader["FirstName"];
result.lector.secondName = (string)reader["SecondName"];
result.lector.email = (string)reader["Email"];
result.lectionName = (string)reader["lectionName"];
result.lectionData = (DateTime)reader["lectionData"];
}
}
}
return(result);
}
catch (SqlException sqlException)
{
_logger.Log(string.Format("You have an error with sql:\n {0}", sqlException));
return(null);
}
catch (ArgumentNullException argException)
{
_logger.Log(string.Format("You have a null argument:\n {0}", argException));
return(null);
}
}
public void UpdateStudentAndLections(StudentId studId, LectionId lectionId, StudentAndLections sal)
{
if (sal == null)
{
_logger.Log(string.Format("You sent a null journal:\n {0}", nameof(NullReferenceException)));
}
string sql = "Update StudentAndLections Set " +
"lectionId = @lectionId, mark = @mark, presence = @presence, homework = @homework " +
"Where studentId = @studentId and lectionId = @lectionId";
try
{
using (var sqlConnection = new SqlConnection(_connectionString))
{
sqlConnection.Open();
using (SqlCommand command = new SqlCommand(sql, sqlConnection))
{
command.Parameters.AddWithValue("@studentId", studId.Id);
command.Parameters.AddWithValue("@lectionId", lectionId.Id);
command.Parameters.AddWithValue("@mark", sal.mark);
command.Parameters.AddWithValue("@presence", sal.presence);
command.Parameters.AddWithValue("@homework", sal.homework);
command.ExecuteNonQuery();
}
}
}
catch (SqlException sqlException)
{
_logger.Log(string.Format("You have an error with sql:\n {0}", sqlException));
}
catch (ArgumentNullException argException)
{
_logger.Log(string.Format("You have a null argument:\n {0}", argException));
}
}
public StudentAndLections GetStudentAndLectionsById(StudentId studId, LectionId lectionId)
{
var result = new StudentAndLections();
result.student = new Student();
result.lection = new Lection();
result.lection.lector = new Lector();
string sql = "SELECT sal.studentId,sal.lectionId,sal.mark,sal.presence,sal.homework, " +
"S.firstName, S.secondName, S.email, S.phoneNumber, " +
" L.lectionName,L.lectionData, " +
"LRS.firstName, LRS.secondName, LRS.lectorId, LRS.email " +
"From StudentAndLections sal " +
"JOIN Lection L ON sal.lectionId = L.lectionId " +
"LEFT JOIN Students S ON sal.studentId = S.studentId " +
"JOIN Lectors LRS ON L.lectorId = LRS.lectorId " +
"Where sal.studentId = @studentId and sal.lectionId = @lectionId ";
try
{
using (var sqlConnection = new SqlConnection(_connectionString))
{
sqlConnection.Open();
using (SqlCommand command = new SqlCommand(sql, sqlConnection))
{
command.Parameters.AddWithValue("@studentId", studId.Id);
command.Parameters.AddWithValue("@lectionId", lectionId.Id);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
if (!Int32.TryParse(reader[0].ToString(), out result.student.studentId.Id))
{
//TODO Add exception Name and Logging
throw new FormatException();
}
if (!Int32.TryParse(reader[1].ToString(), out result.lection.lectionId.Id))
{
//TODO Add exception Name and Logging
throw new FormatException();
}
if (!Int32.TryParse(reader[2].ToString(), out result.mark))
{
//TODO Add exception Name and Logging
throw new FormatException();
}
result.presence = (bool)reader[3];
result.homework = (bool)reader[4];
result.student.firstName = (string)reader[5];
result.student.secondName = (string)reader[6];
result.student.email = (string)reader[7];
result.student.phoneNumber = (string)reader[8];
result.lection.lectionName = (string)reader[9];
result.lection.lectionData = (DateTime)reader[10];
result.lection.lector.firstName = (string)reader[11];
result.lection.lector.secondName = (string)reader[12];
if (!Int32.TryParse(reader[13].ToString(), out result.lection.lector.lectorId.Id))
{
//TODO Add exception Name and Logging
throw new FormatException();
}
result.lection.lector.email = (string)reader[14];
}
}
}
}
catch (SqlException sqlException)
{
_logger.Log(string.Format("You have an error with sql:\n {0}", sqlException));
}
catch (ArgumentNullException argException)
{
_logger.Log(string.Format("You have a null argument:\n {0}", argException));
}
return(result);
}
