public StudentsEnrollmentResponse GetStudentsEnrollment(string index)
{
string EnrollmentQuery = "SELECT stu.IndexNumber,stu.FirstName, stu.LastName,BirthDate, s.Name, e.Semester, e.StartDate" +
" FROM Student stu INNER JOIN Enrollment e on stu.IdEnrollment = e.IdEnrollment INNER JOIN" +
" Studies s on e.IdStudy = s.IdStudy WHERE IndexNumber = @index";
using (SqlConnection con = new SqlConnection(ConString))
using (SqlCommand com = new SqlCommand())
{
com.Connection = con;
com.CommandText = EnrollmentQuery;
com.Parameters.AddWithValue("index", index);
con.Open();
SqlDataReader dr = com.ExecuteReader();
var stEnrollment = new StudentsEnrollmentResponse();
if (!dr.HasRows)
{
return(null);
}
while (dr.Read())
{
stEnrollment.IndexNumber = dr["IndexNumber"].ToString();
stEnrollment.FirstName = dr["FirstName"].ToString();
stEnrollment.LastName = dr["LastName"].ToString();
stEnrollment.BirthDate = DateTime.Parse(dr["BirthDate"].ToString());
stEnrollment.Name = dr["Name"].ToString();
stEnrollment.Semester = int.Parse(dr["Semester"].ToString());
stEnrollment.StartDate = DateTime.Parse(dr["StartDate"].ToString());
}
return(stEnrollment);
}
}
public StudentsEnrollmentResponse StartEnrollStudent(EnrollStudentRequest request)
{
StudentsEnrollmentResponse response = null;
using (var con = new SqlConnection(ConString))
using (var com = new SqlCommand())
{
com.Connection = con;
con.Open();
var trans = con.BeginTransaction();
com.Transaction = trans;
try
{
//Czy studia istnieja?
com.CommandText = "SELECT IdStudy FROM Studies WHERE Name = @name";
com.Parameters.AddWithValue("name", request.Studies);
var dr = com.ExecuteReader();
if (!dr.Read())
{
trans.Rollback();
throw new EnrollmentException("Dane studia nie istnieją");
}
int idStudy = int.Parse(dr["IdStudy"].ToString());
dr.Close();
//Czy index jest unikalny?
com.CommandText = "SELECT IndexNumber FROM Student WHERE IndexNumber =@IndexNumber";
com.Parameters.AddWithValue("IndexNumber", request.IndexNumber);
dr = com.ExecuteReader();
if (dr.HasRows)
{
trans.Rollback();
throw new EnrollmentException("Podany numer indexu jest już używany");
}
dr.Close();
//Czy jest już taki wpis w tabel Enrollments o danym IdEnrollments dla stówdiów o IdStudy
com.CommandText = "SELECT IdEnrollment FROM Enrollment WHERE IdStudy =@IdStudy AND Semester = 1 ORDER BY StartDate DESC";
com.Parameters.AddWithValue("IdStudy", idStudy);
int idEnrollment;
dr = com.ExecuteReader();
DateTime startDate = DateTime.Now;
if (dr.HasRows)
{
idEnrollment = (int)dr["IdEnrollment"];
}
else
{
//dodajemy wpis do Enorllment
//sprawdzamy maxymalny wpis w IdEnrollment
dr.Close();
com.CommandText = "SELECT Max(IdEnrollment) AS Max FROM Enrollment";
dr = com.ExecuteReader();
if (dr.Read())
{
idEnrollment = (int)dr["Max"] + 1;
}
else
{
throw new EnrollmentException("Błąd w czytaniu max enrollment");
}
dr.Close();
com.CommandText = $"INSERT INTO Enrollment(IdEnrollment,Semester,IdStudy,StartDate) " +
$"VAUES (@IdEnrollment,@Semester,@IdStudy,@StartDate)";
com.Parameters.AddWithValue("IdEnrollment", idEnrollment);
com.Parameters.AddWithValue("Semester", "1");
com.Parameters.AddWithValue("IdStudy", idStudy);
com.Parameters.AddWithValue("StartDate", startDate);
com.BeginExecuteNonQuery();
}
//Dodajemy wpis do Students
com.CommandText = $"INSERT INTO Student(IndexNumber, FirstName,LastName,BirthDate, IdEnrollment)" +
$" VALUES ( @IndexNumber, @FirstName, @LastName, @Birthdate, @IdEnrollment)";
com.Parameters.AddWithValue("IndexNumber", request.IndexNumber);
com.Parameters.AddWithValue("FirstName", request.FirstName);
com.Parameters.AddWithValue("LastName", request.LastName);
com.Parameters.AddWithValue("BirthDate", request.BirthDate);
com.Parameters.AddWithValue("IdEnrollment", idEnrollment);
com.ExecuteNonQuery();
response = new StudentsEnrollmentResponse
{
IndexNumber = request.IndexNumber,
FirstName = request.FirstName,
LastName = request.LastName,
BirthDate = request.BirthDate,
Name = request.Studies,
Semester = 1,
StartDate = startDate
};
trans.Commit();
}
catch (SqlException e)
{
trans.Rollback();
return(null);
}
}
return(response);
}
