public Enrollment EnrollStudent(EnrollStudentRequest request)
{
using (var connection = new SqlConnection(CONNECTION_STRING))
using (var command = new SqlCommand()) {
command.Connection = connection;
connection.Open();
var transaction = connection.BeginTransaction();
command.Transaction = transaction;
command.CommandText = "SELECT IdStudy FROM Studies WHERE Name = @studies";
command.Parameters.AddWithValue("studies", request.Studies);
var reader = command.ExecuteReader();
if (!reader.Read())
{
return(null);
}
var idStudy = Int32.Parse(reader["IdStudy"].ToString());
reader.Close();
var enrollment = new Enrollment();
command.CommandText = "SELECT e.IdEnrollment, e.Semester, e.IdStudy, e.StartDate FROM Enrollment e INNER JOIN Studies s ON s.IdStudy = e.IdStudy WHERE s.Name = @studyname";
command.Parameters.AddWithValue("studyname", request.Studies);
reader = command.ExecuteReader();
if (reader.Read())
{
enrollment.IdEnrollment = Int32.Parse(reader["IdEnrollment"].ToString());
enrollment.Semester = Int32.Parse(reader["Semester"].ToString());
enrollment.IdStudy = Int32.Parse(reader["IdStudy"].ToString());
enrollment.StartDate = DateTime.Parse(reader["StartDate"].ToString());
}
else
{
var idEnrollement = 1;
reader.Close();
command.CommandText = "SELECT MAX(IdEnrollement) + 1 FROM Enrollment";
reader = command.ExecuteReader();
if (reader.Read())
{
idEnrollement = Int32.Parse(reader[0].ToString());
}
enrollment.IdEnrollment = idEnrollement;
enrollment.Semester = 1;
enrollment.IdStudy = idStudy;
enrollment.StartDate = DateTime.Now;
command.CommandText = "INSERT INTO Enrollment (IdEnrollment, Semester, IdStudy, StartDate) " +
"VALUES (@idenrollment, @semester, @idstudy, @startdate)";
command.Parameters.AddWithValue("idenrollment", enrollment.IdEnrollment);
command.Parameters.AddWithValue("semester", enrollment.Semester);
command.Parameters.AddWithValue("idstudy", enrollment.IdStudy);
command.Parameters.AddWithValue("startdate", enrollment.StartDate.Date);
command.ExecuteNonQuery();
}
reader.Close();
command.CommandText = "SELECT 1 FROM Student s WHERE s.IndexNumber = @indexnumber";
command.Parameters.AddWithValue("indexnumber", request.IndexNumber);
reader = command.ExecuteReader();
if (reader.Read())
{
reader.Close();
transaction.Rollback();
return(null);
}
reader.Close();
command.CommandText = "INSERT INTO Student (IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) " +
"VALUES (@index, @firstname, @lastname, @birthdate, @idenrollement)";
command.Parameters.AddWithValue("index", request.IndexNumber);
command.Parameters.AddWithValue("firstname", request.FirstName);
command.Parameters.AddWithValue("lastname", request.LastName);
command.Parameters.AddWithValue("birthdate", request.BirthDate);
command.Parameters.AddWithValue("idenrollement", enrollment.IdEnrollment);
command.ExecuteNonQuery();
transaction.Commit();
return(enrollment);
}
}
public EnrollStudentResponse EnrollStudent(EnrollStudentRequest request)
{
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand())
{
command.Connection = connection;
connection.Open();
command.CommandText = "SELECT IdStudy FROM studies WHERE name=@name";
command.Parameters.AddWithValue("name", request.Studies);
var read = command.ExecuteReader();
if (!read.Read())
{
throw new Exception("No such studies");
}
int idStudies = (int)read["IdStudy"];
read.Close();
command.CommandText = "SELECT * FROM Student WHERE IndexNumber=@index";
command.Parameters.AddWithValue("index", request.IndexNumber);
read = command.ExecuteReader();
if (read.Read())
{
throw new Exception("IndexNumber not unique");
}
read.Close();
command.CommandText = "SELECT * FROM enrollment WHERE Semester=@semester AND IdStudy=@idStudy";
command.Parameters.AddWithValue("idStudy", idStudies);
command.Parameters.AddWithValue("semester", 1);
int idEnroll = 1;
DateTime dateTime = DateTime.Now;
read = command.ExecuteReader();
if (!read.Read())
{
read.Close();
command.CommandText = "SELECT MAX(IdEnrollment) as maximus from enrollment";
read = command.ExecuteReader();
if (read.Read())
{
idEnroll = (int)read["maximus"] + 1;
}
read.Close();
Console.WriteLine(idEnroll);
command.CommandText = "INSERT INTO Enrollment(IdEnrollment,Semester,IdStudy,StartDate) VALUES(@id, @sem, @idstud, @date)";
command.Parameters.AddWithValue("@id", idEnroll);
command.Parameters.AddWithValue("@sem", 1);
command.Parameters.AddWithValue("@idstud", idStudies);
command.Parameters.AddWithValue("@date", dateTime);
}
else
{
idEnroll = (int)read["IdEnrollment"];
dateTime = (DateTime)read["StartDate"];
}
read.Close();
command.CommandText = "INSERT INTO Student(IndexNumber,FirstName,LastName,BirthDate,IdEnrollment) VALUES(@indx, @firna, @laname, @date, @idEnrol)";
command.Parameters.AddWithValue("indx", request.IndexNumber);
command.Parameters.AddWithValue("firna", request.FirstName);
command.Parameters.AddWithValue("laname", request.LastName);
command.Parameters.AddWithValue("date", request.DateOfBirth);
command.Parameters.AddWithValue("idEnrol", idEnroll);
command.ExecuteNonQuery();
return(new EnrollStudentResponse()
{
IdEnroll = idEnroll,
Semester = 1,
Study = idStudies,
Date = dateTime
});
}
}
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);
}
public Enrollment EnrollStudent(EnrollStudentRequest request)
{
var st = new Student();
st.LastName = request.LastName;
st.FirstName = request.FirstName;
st.BirthDate = request.BirthDate;
st.Studies = request.Studies;
st.Semester = 1;
var enrollment = new Enrollment();
enrollment.Semester = 1;
using (var con = new SqlConnection("Data Source=db-mssql;Initial Catalog=s17470;Integrated Security=True"))
using (var com = new SqlCommand())
{
com.Connection = con;
con.Open(); //otiweramy polaczenie
var tran = con.BeginTransaction(); //otwieramy nowa transakcje
try
{
//1. Spr czy istnieja studia
com.CommandText = "SELECT IdStudy FROM Studies WHERE name=@name"; //SQL command
com.Parameters.AddWithValue("name", request.Studies); //set value to @name parameter
//response.StudiesName = request.Studies;
com.Transaction = tran; //musi byc przed ExecuteReader
var dr = com.ExecuteReader(); //odczytujemy efekt zapytania
if (!dr.Read()) //jesli zapytanie NIC nie zwrocilo..
{
dr.Close();
tran.Rollback(); //wycofujemy transakcje
// return BadRequest("Studia nie istnieja."); //musimy zwrocic blad
}
int idStudies = (int)dr["IdStudy"]; //bierzemy number id studiow, przyda sie pozniej...
//response.IdStudies = idStudies;
enrollment.IdStudies = idStudies;
dr.Close();
//2. Spr czy nr indexu studenta jest unikalny
com.CommandText = "SELECT INDEXNUMBER FROM STUDENT WHERE INDEXNUMBER = @Index";
com.Parameters.AddWithValue("Index", request.IndexNumber);
dr = com.ExecuteReader(); //odczytujemy efekt zapytania
if (dr.Read()) //jesli zapytanie cos zwrocilo...
{
dr.Close();
tran.Rollback(); //wycofujemy transakcje
//return BadRequest("W bazie istnieje juz ten number indeksu."); //musimy zwrocic blad
}
dr.Close();
int IdEnrollment = 0;
//3. odnajdujemy najnowszy wpis w tabeli Enrollments zgodny ze studiami studenta i wartoscia Semester = 1
com.CommandText = "Select IdEnrollment FROM Enrollment WHERE Semester = 1 AND IdStudy =" + idStudies;
dr = com.ExecuteReader(); //odczytujemy efekt zapytania
if (dr.Read()) //jesli zapytanie cos zwrocilo...
{
IdEnrollment = ((int)dr["IdEnrollment"]);
dr.Close();
}
else if (!dr.Read())
{
dr.Close();
com.CommandText = "Select IdEnrollment FROM Enrollment WHERE IdEnrollment = (Select MAX(IdEnrollment) FROM Enrollment)";
dr = com.ExecuteReader(); //Dodane
dr.Read();
IdEnrollment = ((int)dr["IdEnrollment"]) + 1;
dr.Close();
com.CommandText = "INSERT INTO ENROLLMENT (IDENROLLMENT,SEMESTER,IDSTUDY,STARTDATE) VALUES (" + IdEnrollment + ",1," + idStudies + ", '2021-09-10')";
com.ExecuteNonQuery();
}
else
{
tran.Rollback();
// return BadRequest("ERROR!");
};
//response.IdEnrollment = IdEnrollment;
enrollment.IdEnrollment = IdEnrollment;
com.CommandText = "INSERT INTO Student(IndexNumber, FirstName,LastName,BirthDate,IdEnrollment) VALUES (@index2,@firstName,@lastName,@birthDate,@idEnrollment)"; //nowe zapytanie, a raczej wypchniecie danych do tabeli
com.Parameters.AddWithValue("index2", request.IndexNumber); //przypisanie wartosci do powyzszych paramterow
com.Parameters.AddWithValue("firstName", request.FirstName); //j.w.
com.Parameters.AddWithValue("lastName", request.LastName);
com.Parameters.AddWithValue("birthDate", "1993-09-11");
com.Parameters.AddWithValue("idEnrollment", IdEnrollment);
com.ExecuteNonQuery(); //wypychamy zapytanie
tran.Commit(); //potwierdzamy transakcje
dr.Close();
com.CommandText = "Select * From Enrollment WHERE IdEnrollment = " + IdEnrollment;
dr = com.ExecuteReader();
string message = "";
while (dr.Read())
{
//message = string.Concat(message, '\n', "Enrollment ID:" , dr["IdEnrollment"].ToString(), ", Semester: ", dr["Semester"].ToString());
message = string.Concat(message, '\n', "Enrollment ID:", enrollment.IdEnrollment.ToString(), ", Semester: ", enrollment.Semester.ToString(), ", ID Studies: :", enrollment.IdStudies.ToString());
}
//return Ok(message);
}
catch (SqlException exc) //wylapujemy ewentualny blad...
{
tran.Rollback(); //...i w razie czego robimy rollback
// return BadRequest(exc.ToString());
}
}
return(enrollment);
}
public EnrollStudentResponse EnrollStudent(EnrollStudentRequest request)
{
var st = new Student();
st.FirstName = request.FirstName;
st.LastName = request.LastName;
st.IndexNumber = request.IndexNumber;
st.Birthdate = DateTime.Now.Date;
//Console.WriteLine(DateTime.Now.Date);
int sem = -1, idS = -1; DateTime sDate = DateTime.Now; int idEnrollment = -1;
using (var con = new SqlConnection("Data Source=db-mssql;Initial Catalog=s18445; Integrated Security=True"))
using (var com = new SqlCommand())
{
com.Connection = con;
con.Open();
var tran = con.BeginTransaction();
try
{
com.Transaction = tran;
com.CommandText = "select IdStudy from studies where name = @name";
com.Parameters.AddWithValue("name", request.Studies);
var dr = com.ExecuteReader();
if (!dr.Read())
{
tran.Rollback();
//return BadRequest("Studia nie istnieja");
}
int idStudies = (int)dr["IdStudy"];
com.CommandText = "Select TOP (1) IDENROLLMENT, SEMESTER, ENROLLMENT.IDSTUDY, STARTDATE FROM ENROLLMENT, STUDIES " +
"WHERE ENROLLMENT.IDSTUDY = STUDIES.IDSTUDY " +
"AND SEMESTER = 1 " +
"AND NAME = @nNAME " +
"ORDER BY STARTDATE DESC";
com.Parameters.AddWithValue("nNAME", request.Studies);
dr.Close();
dr = com.ExecuteReader();
if (!dr.Read())
{
com.CommandText = "INSERT INTO ENROLLMENT VALUES( (SELECT MAX(IDENROLLMENT)+1 FROM ENROLLMENT),1,@idStudies,@dateNow)";
com.Parameters.AddWithValue("idStudies", idStudies);
com.Parameters.AddWithValue("dateNow", DateTime.Now.Date);
dr.Close();
com.ExecuteNonQuery();
com.CommandText = "SELECT TOP(1) * FROM ENROLLMENT ORDER BY IDENROLLMENT DESC";
dr = com.ExecuteReader();
dr.Read();
Console.WriteLine(dr.ToString());
idEnrollment = (int)dr["IDENROLLMENT"];
}
else
{
idEnrollment = (int)dr["IDENROLLMENT"];
}
//dodanie studenta
com.CommandText = "SELECT * FROM STUDENT WHERE INDEXNUMBER = @indexnumber";
com.Parameters.AddWithValue("indexnumber", request.IndexNumber);
dr.Close();
dr = com.ExecuteReader();
if (dr.Read())
{
//return BadRequest("Student o tym indeksie juz istnieje");
}
dr.Close();
com.CommandText = "INSERT INTO Student(IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) VALUES(@Index, @Fname, @LName, @Bday, @IdEn)";
com.Parameters.AddWithValue("Index", request.IndexNumber);
com.Parameters.AddWithValue("Fname", request.FirstName);
com.Parameters.AddWithValue("Lname", request.LastName);
string dt = request.Birthdate.Replace(".", "-");
string tmp = dt.Substring(6, 4) + "-" + dt.Substring(3, 2) + "-" + dt.Substring(0, 2);
//10.10.1999 -> 1999-01-01
com.Parameters.AddWithValue("Bday", tmp);
com.Parameters.AddWithValue("IdEn", idEnrollment);
com.ExecuteNonQuery();
com.CommandText = "Select * from Enrollment where idenrollment = @IdEnz";
com.Parameters.AddWithValue("IdEnz", idEnrollment);
dr = com.ExecuteReader();
dr.Read();
sem = (int)dr["Semester"];
idS = (int)dr["IdStudy"];
sDate = ((DateTime)dr["StartDate"]);
dr.Close();
tran.Commit();
}
catch (SqlException exc)
{
tran.Rollback();
// return BadRequest(exc.Message);
}
}
var response = new EnrollStudentResponse();
response.idEnrollment = idEnrollment;
response.Semester = sem;
response.IdStudy = idS;
response.StartDate = sDate;
return(response);
}
public EnrollStudentRequest EnrollStudent(EnrollStudentRequest request)
{
if (request.FirstName == null || request.BirthDate == null || request.StudiesName == null)
{
return(null);
}
var enrollment = new EnrollStudentRequest();
using (var con = new SqlConnection(_connString))
{
con.Open();
var tran = con.BeginTransaction();
try
{
using (var com = new SqlCommand())
{
int idStudy = 0;
int idEnrollment = 0;
com.Connection = con;
com.Transaction = tran;
com.CommandText = "select IdStudy from Studies where Name=@StudiesName";
com.Parameters.AddWithValue("StudiesName", request.StudiesName);
var dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
tran.Rollback();
return(null);
}
else
{
idStudy = (int)dr["IdStudy"];
com.Parameters.AddWithValue("IdStudy", idStudy);
}
dr.Close();
com.CommandText = "Select IdEnrollment from Enrollment where Semester=1 AND IdStudy=@idStudy";
dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
com.CommandText = "Select MAX(IdEnrollment) from Enrollment";
DateTime currentDate = DateTime.Now;
com.Parameters.AddWithValue("CurrentDate", currentDate);
var dr1 = com.ExecuteReader();
while (dr1.Read())
{
idEnrollment = int.Parse(dr1["IdEnrollment"].ToString()) + 1;
com.Parameters.AddWithValue("IdEnrollment", idEnrollment);
}
dr1.Close();
com.CommandText = "Insert into Enrollment(IdEnrollment, Semester, IdStudy, StartDate) values (@IdEnrollment, 1, @idStudy, @CurrentDate)";
com.ExecuteNonQuery();
}
else
{
idEnrollment = (int)dr["IdEnrollment"];
com.Parameters.AddWithValue("IdEnrollment", idEnrollment);
dr.Close();
}
com.CommandText = "Insert into Student(IndexNumber, FirstName, LastName, Birthdate, IdEnrollment) values (@IndexNumber, @FirstName, @LastName, @BirthDate, @IdEnrollment)";
string indexNumber = $"s{new Random().Next(1, 2000)}";
com.Parameters.AddWithValue("IndexNumber", indexNumber);
com.Parameters.AddWithValue("FirstName", request.FirstName);
com.Parameters.AddWithValue("LastName", request.LastName);
com.Parameters.AddWithValue("BirthDate", request.BirthDate);
dr.Close();
com.ExecuteNonQuery();
com.CommandText = "Select IndexNumber, FirstName, LastName, BirthDate, Name, Semester from Student, Studies, Enrollment " +
"where Enrollment.IdEnrollment = Student.IdEnrollment AND Enrollment.IdStudy = Studies.IdStudy " +
"AND IndexNumber = @IndexNumber;";
dr = com.ExecuteReader();
while (dr.Read())
{
enrollment.IndexNumber = dr["IndexNumber"].ToString();
enrollment.FirstName = dr["FirstName"].ToString();
enrollment.LastName = dr["LastName"].ToString();
enrollment.BirthDate = DateTime.Parse(dr["BirthDate"].ToString());
enrollment.StudiesName = dr["Name"].ToString();
enrollment.Semester = Int32.Parse(dr["Semester"].ToString());
}
dr.Close();
}
}
catch (Exception)
{
tran.Rollback();
}
tran.Commit();
}
return(enrollment);
}
public IActionResult EnrollStudent(EnrollStudentRequest request)
{
return(StatusCode(201, _service.EnrollStudent(request)));
}
public EnrollStudentsResponse enroll(EnrollStudentRequest s)
{
EnrollStudentsResponse ret = new EnrollStudentsResponse();
CultureInfo provider = CultureInfo.InvariantCulture;
s.Bdate = DateTime.ParseExact(s.BirthDate.Replace('.', '-'), "dd-MM-yyyy", provider);
using (var con = new SqlConnection(constr))
using (var com = new SqlCommand())
{
com.Connection = con;
con.Open();
var tran = con.BeginTransaction();
com.Transaction = tran;
try
{
com.CommandText = "select IdStudy from studies where name = @name";
com.Parameters.AddWithValue("name", s.Studies);
var dr0 = com.ExecuteReader();
if (!dr0.Read())
{
tran.Rollback();
message = "Studia nie istnieja";
code = 400;
return(null);
}
int IdStudy = (int)dr0["IdStudy"];
com.CommandText = "select * from enrollment where idStudy = @id and semester = 1";
com.Parameters.AddWithValue("id", IdStudy);
dr0.Close();
int idEnr = 0;
Boolean addEnr = false;
var dr1 = com.ExecuteReader();
if (dr1.Read())
{
idEnr = (int)dr1["IdEnrollment"];
ret.IdEnrollment = idEnr;
ret.IdStudy = (int)dr1["IdStudy"];
ret.Semester = (int)dr1["Semester"];
ret.StartDate = (DateTime)dr1["StartDate"];
dr1.Close();
}
else
{
addEnr = true;
dr1.Close();
com.CommandText = "select Max(IdEnrollment) AS MID from enrollment ";
var dr2 = com.ExecuteReader();
dr2.Read();
idEnr = ((int)dr2["MID"]) + 1;
dr2.Close();
}
com.Parameters.Clear();
com.CommandText = "Select IndexNumber from Student where IndexNumber = @idS";
com.Parameters.AddWithValue("idS", s.IndexNumber);
var dr3 = com.ExecuteReader();
if (dr3.Read())
{
message = "Student juz istnieje";
code = 400;
dr3.Close();
tran.Rollback();
return(null);
}
dr3.Close();
if (addEnr)
{
com.Parameters.Clear();
com.CommandText = "Insert into Enrollment (IdEnrollment, Semester, IdStudy, StartDate) VALUES (@IdEnr, 1, @IdS, @sdate)";
com.Parameters.AddWithValue("IdEnr", idEnr);
com.Parameters.AddWithValue("IdS", IdStudy);
com.Parameters.AddWithValue("sdate", DateTime.Today);
com.ExecuteNonQuery();
com.CommandText = "select * from enrollment where IdEnrollmet = (Select Max(IdEnrollment) from enrollment)";
var dr4 = com.ExecuteReader();
dr4.Read();
ret.IdEnrollment = idEnr;
ret.IdStudy = (int)dr4["IdStudy"];
ret.Semester = (int)dr4["Semester"];
ret.StartDate = (DateTime)dr4["StartDate"];
dr4.Close();
}
com.Parameters.Clear();
com.CommandText = "Insert into Student (IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) values (@index, @name, @sname, @bdate, @Idenr);";
com.Parameters.AddWithValue("index", s.IndexNumber);
com.Parameters.AddWithValue("name", s.FirstName);
com.Parameters.AddWithValue("sname", s.LastName);
com.Parameters.AddWithValue("bdate", s.Bdate);
com.Parameters.AddWithValue("Idenr", idEnr);
com.ExecuteNonQuery();
tran.Commit();
return(ret);
}
catch (SqlException e)
{
tran.Rollback();
message = e.Message;
code = 400;
return(null);
}
}
}
public Response EnrollStudent(EnrollStudentRequest request)
{
using (var con = new SqlConnection(ConString))
using (var com = new SqlCommand())
{
com.Connection = con;
con.Open();
var tran = con.BeginTransaction();
com.Transaction = tran;
try
{
//czy studia istniaja
com.CommandText = @"select IdStudy
from studies
where name = @name";
com.Parameters.AddWithValue("name", request.Studies);
var dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
tran.Rollback();
return(new Response("400", "Studia " + request.Studies + " nie istnieją", null));
}
int idStudy = (int)dr["IdStudy"];
dr.Close();
//czy istnieje wpis dla studiow, z semestrem 1
com.CommandText = @"select idEnrollment
from enrollment
where semester = 1 and idStudy = @idStudy";
com.Parameters.AddWithValue("idStudy", idStudy);
int idEnrollment;
com.Transaction = tran;
dr = com.ExecuteReader();
if (dr.Read())
{
idEnrollment = (int)dr["idEnrollment"];
dr.Close();
}
else
{
dr.Close();
com.CommandText = @"insert into Enrollment
values((SELECT MAX(idEnrollment) + 1 FROM Enrollment),
1,
@idStudy,
getDate())";
com.Transaction = tran;
com.ExecuteNonQuery();
dr.Close();
com.CommandText = @"select idEnrollment
from enrollment
where semester = 1 and idStudy = @idStudy";
com.Transaction = tran;
dr = com.ExecuteReader();
dr.Read();
idEnrollment = (int)dr["idEnrollment"];
dr.Close();
}
//dodanie studenta
com.CommandText = @"insert into student
values(@IndexNumber, @FirstName, @LastName, @BirthDate,
(select MAX(idEnrollment) from Enrollment))";
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.ExecuteNonQuery();
tran.Commit();
var enrollment = new EnrollStudentResponse();
enrollment.IndexNumber = request.IndexNumber;
enrollment.FirstName = request.FirstName;
enrollment.LastName = request.LastName;
enrollment.Studies = request.Studies;
enrollment.Semester = 1;
return(new Response("201", "Zapisano studenta", enrollment));
}
catch (SqlException exc)
{
tran.Rollback();
}
}
return(new Response("400", "Bad request", null));
}
public IActionResult EnrollStudent(EnrollStudentRequest request)
{
var st = new Student()
{
firstName = request.firstName,
lastName = request.lastName,
index = request.indexNUmber,
birthDate = request.birthDate,
studies = request.studies
};
using (var con = new SqlConnection("Data Source=db-mssql;Initial Catalog=s19017;Integrated Security=True"))
using (var com = new SqlCommand())
{
com.Connection = con;
con.Open();
var tran = con.BeginTransaction();
com.Transaction = tran;
try
{
com.CommandText = "select IdStudy from studies where name=@name;";
com.Parameters.AddWithValue("name", request.studies);
var dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
tran.Rollback();
return(BadRequest("Studia nie istnieja"));
}
int idstudies = (int)dr["IdStudy"];
dr.Close();
com.CommandText = "select e.IdEnrollment, e.Semester, e.IdStudy, e.StartDate from Enrollment e join Studies s on e.idStudy = s.IdStudy where e.semester=1 and s.name=@name;";
int idEnrollment;
dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
com.CommandText = "insert into Enrollment values (ISNULL(MAX(IdEnrollment) + 1), @Semestr, @IdStudy, GetDate())";
com.Parameters.AddWithValue("Semestr", 1);
com.Parameters.AddWithValue("IdStudy", request.studies);
com.ExecuteNonQuery();
}
dr.Close();
com.CommandText = "select e.IdEnrollment, e.Semester, e.IdStudy, e.StartDate from Enrollment e join Studies s on e.idStudy = s.IdStudy where e.semester=1 and s.name=@name;";
dr = com.ExecuteReader();
dr.Read();
idEnrollment = (int)dr["IdEnrollment"];
dr.Close();
com.CommandText = "select * from student where IndexNumber=@index;";
com.Parameters.AddWithValue("index", request.indexNUmber);
dr = com.ExecuteReader();
if (dr.Read())
{
dr.Close();
tran.Rollback();
return(BadRequest("Student o podanym indexie istnieje w bazie"));
}
dr.Close();
com.CommandText = "INSERT INTO Student(IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) VALUES(@Index, @Fname, @Lname, @BirthDate, @IdEnrollment)";
com.Parameters.AddWithValue("Fname", request.firstName);
com.Parameters.AddWithValue("Lname", request.lastName);
com.Parameters.AddWithValue("BirthDate", request.birthDate);
com.Parameters.AddWithValue("IdEnrollment", idEnrollment);
com.ExecuteNonQuery();
tran.Commit();
}
catch (SqlException e)
{
tran.Rollback();
return(BadRequest(e));
}
var response = new EnrollStudentResponse()
{
lastName = request.lastName,
semester = 1,
startDate = DateTime.Now
};
return(StatusCode(201, response));
}
}
public async Task <IActionResult> EnrollStudent(EnrollStudentRequest request)
{
return(await _service.EnrollStudent(request));
}
public EnrollStudentResponse EnrollStudent(EnrollStudentRequest request)
{
if (db.Student.Any(s => s.IndexNumber == request.IndexNumber))
{
return(null);
}
var study = db.Studies.Where(s => s.Name == request.Studies).First();
if (study == null)
{
return(null);
}
var enr = db.Enrollment.Where(en => en.StartDate
.Equals(db.Enrollment.Where(e => e.Semester == 1 && e.IdStudy == study.IdStudy)
.Max(e => e.StartDate))).First();;
if (enr != null)
{
String salt = passwordService.CreateSalt();
db.Student.Add(new EStudent
{
IndexNumber = request.IndexNumber,
FirstName = request.FirstName,
LastName = request.LastName,
BirthDate = request.Birthdate,
IdEnrollment = enr.IdEnrollment,
Pass = passwordService.HashPassword(request.Password, salt),
Salt = salt
});
db.SaveChanges();
return(new EnrollStudentResponse
{
IdEnrollment = enr.IdEnrollment,
Semester = enr.Semester,
Study = study.Name,
StartDate = enr.StartDate
});
}
else
{
db.Enrollment.Add(new EEnrollment
{
IdEnrollment = db.Enrollment.Max(e => e.IdEnrollment),
Semester = 1,
IdStudy = db.Studies.Max(s => s.IdStudy),
StartDate = DateTime.Today
});
String salt = passwordService.CreateSalt();
db.Student.Add(new EStudent
{
IndexNumber = request.IndexNumber,
FirstName = request.FirstName,
LastName = request.LastName,
BirthDate = request.Birthdate,
IdEnrollment = enr.IdEnrollment,
Pass = passwordService.HashPassword(request.Password, salt),
Salt = salt
});
db.SaveChanges();
return(new EnrollStudentResponse
{
IdEnrollment = enr.IdEnrollment,
Semester = enr.Semester,
Study = study.Name,
StartDate = enr.StartDate
});
}
}
public EnrollStudentResponse EnrollStudent(EnrollStudentRequest request)
{
using (var con = new SqlConnection(connection))
{
using (var com = new SqlCommand())
{
com.Connection = con;
con.Open();
var tran = con.BeginTransaction();
com.Transaction = tran;
try
{
com.CommandText = "select IdStudy from studies where Name=@name";
com.Parameters.AddWithValue("@name", request.Studies);
var dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
tran.Rollback();
throw new NotImplementedException("Studia nie istnieją");
}
int studies = Int32.Parse(dr["IdStudy"].ToString());
dr.Close();
com.CommandText = "SELECT IdEnrollment FROM Enrollment WHERE IdStudy=@idStudy AND Semester=1";
com.Parameters.AddWithValue("idStudy", studies);
dr = com.ExecuteReader();
int IdEnrollment = 0;
//Jesli tak wpis nie istnieje to dodajemy go do bazy danych (StartDate ustawiamyna aktualna date)
if (!dr.Read())
{
dr.Close();
com.CommandText = "SELECT MAX(IdEnrollment) FROM Enrollment";
dr = com.ExecuteReader();
int newID = 0;
if (dr.Read())
{
newID = Int32.Parse(dr[dr.GetName(0)].ToString());
}
newID++;
com.CommandText = "INSERT INTO Enrollment VALUES(@newID, 1, @studies, @date)";
com.Parameters.AddWithValue("newID", newID);
com.Parameters.AddWithValue("studies", studies);
com.Parameters.AddWithValue("date", DateTime.Now);
IdEnrollment = newID;
dr.Close();
com.ExecuteNonQuery();
}
else
{
IdEnrollment = Int32.Parse(dr["IdEnrollment"].ToString());
}
dr.Close();
//Pamietamy o tym, aby sprawdzic czy indeks podany przez studenta jest unikalny. W przeciwnym wypadku zgłaszamy bład.
com.CommandText = "SELECT COUNT(*) FROM STUDENT WHERE IndexNumber = @IndexNumber";
com.Parameters.AddWithValue("IndexNumber", request.IndexNumber);
dr = com.ExecuteReader();
if (dr.Read())
{
if (Int32.Parse(dr[dr.GetName(0)].ToString()) > 0)
{
dr.Close();
tran.Rollback();
throw new NotImplementedException("Indeks nie jest unikalny");
}
}
else
{
dr.Close();
tran.Rollback();
throw new NotImplementedException("Cannot read data");
}
dr.Close();
//Na koncu dodajemy wpis w tabeli Students
com.CommandText = "INSERT INTO Student VALUES (\'" + request.IndexNumber + "\',\'" + request.FirstName + "\',\'" + request.LastName + "\',\'" + request.BirthDate.ToString("yyyy-MM-dd") + "\'," + IdEnrollment + ")";
com.ExecuteNonQuery();
tran.Commit();
}
catch (SqlException ex)
{
tran.Rollback();
throw new NotImplementedException(ex.ToString());
}
}
}
var response = new EnrollStudentResponse();
response.LastName = request.LastName;
response.Semester = 1;
response.StartDate = DateTime.Now;
return(response);
}
public IEnumerable <EnrollStudentResponse> EnrollStudent(EnrollStudentRequest request)
{
//DTOs-Data Transfer Objects
var st = new EnrollStudentRequest();
st.FirstName = request.FirstName;
/*st.LastName = request.LastName;
* st.BirthDate = request.BirthDate;
* st.IndexNumber = request.LastName;
* st.Studies = request.Studies;*/
//.. cos robie
using (SqlConnection con = new SqlConnection(ConString))
using (SqlCommand com = new SqlCommand())
{
com.Connection = con;
con.Open();
var tran = con.BeginTransaction();
try
{
//czy istnieja?
com.CommandText = "select IdStudies from studies where name=@name";
com.Parameters.AddWithValue("name", request.Studies);
var dr = com.ExecuteReader();
if (!dr.Read())
{
tran.Rollback();
//return BadRequestObjectResult(Error.Add"Studia nie istnieja");
return(null);
//...
}
int idstudies = (int)dr["IdStudies"];
//x.Dodanie studenta
com.CommandText = "INSERT INTO Student(IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) VALUES(@Index,@Fname,@Lname,@BDay,@IdEn)";
com.Parameters.AddWithValue("index", request.IndexNumber);
com.Parameters.AddWithValue("Fname", request.FirstName);
com.Parameters.AddWithValue("Lname", request.LastName);
com.Parameters.AddWithValue("BDay", request.BirthDate);
com.Parameters.AddWithValue("IdEn", 0);
//..
tran.Commit();
}
catch (SqlException exc)
{
tran.Rollback();
}
}
var response = new EnrollStudentResponse();
response.LastName = st.LastName;
//...
return(new List <EnrollStudentResponse>()); // do zmiany
}
public void EnrollStudent(EnrollStudentRequest request)
{
throw new NotImplementedException();
}
public EnrollStudentResult EnrollStudent(EnrollStudentRequest request)
{
EnrollStudentResult result = new EnrollStudentResult();
if (string.IsNullOrWhiteSpace(request.FirstName) ||
string.IsNullOrWhiteSpace(request.LastName) ||
string.IsNullOrWhiteSpace(request.IndexNumber) ||
string.IsNullOrWhiteSpace(request.BirthDate) ||
string.IsNullOrWhiteSpace(request.Studies) ||
!DateTime.TryParse(request.BirthDate, out DateTime birthDate))
{
result.ResultCode = ResultCodes.NieWpisanoWszystkichDanychStudenta;
return(result);
}
var studies = context.Studies.FirstOrDefault(e => e.Name == request.Studies);
context.SaveChanges();
if (studies == null)
{
result.ResultCode = ResultCodes.NieIstniejaStudia;
return(result);
}
int idStudy = studies.IdStudy;
var bigSelect = context.Student.Any(x => x.IndexNumber == "" && x.IdEnrollmentNavigation.Semester == 1);
if (bigSelect)
{
result.ResultCode = ResultCodes.StudentJestJuzZapisanyNaSemest;
return(result);
}
var maxSelect = context.Enrollment.Max(e => e.IdEnrollment);
int maxId = maxSelect + 1;
DateTime startDate = DateTime.Now;
context.Enrollment.Add(new Enrollment
{
IdEnrollment = maxId,
Semester = 1,
IdStudy = idStudy,
StartDate = startDate,
});
var nameSelect = context.Student.Any(e => e.IndexNumber == request.IndexNumber);
if (nameSelect)
{
result.ResultCode = ResultCodes.StudentJuzIstnieje;
return(result);
}
DateTime date = DateTime.Parse(request.BirthDate);
context.Student.Add(new Student
{
IndexNumber = request.IndexNumber,
FirstName = request.FirstName,
LastName = request.LastName,
BirthDate = date,
IdEnrollment = maxId
});
var response = new EnrollStudentResponse
{
IdEnrollment = maxId,
IdStudy = idStudy,
Semester = 1,
StartDate = startDate
};
result.ResultCode = ResultCodes.StudentDodany;
result.Response = response;
return(result);
}
public Enrollment EnrollStudent(EnrollStudentRequest student)
{
var st = new Student();
st.IndexNumber = student.IndexNumber;
st.FirstName = student.FirstName;
st.LastName = student.LastName;
st.BirthDate = student.BirthDate;
st.Studies = student.Studies;
using (var con = new SqlConnection("Data Source=db-mssql; Initial Catalog=s18727;Integrated Security=True"))
using (var com = new SqlCommand())
{
com.Connection = con;
con.Open();
var tran = con.BeginTransaction();
var enrollment = new Enrollment();
try
{
com.CommandText = "SELECT 1 FROM Studies WHERE Name = @name";
com.Parameters.AddWithValue("name", st.Studies);
var dr = com.ExecuteReader();
if (!dr.Read())
{
}
var studies = new EnrollStudiesResponse();
studies.IdStudy = Int16.Parse(dr["IdStudy"].ToString());
studies.Name = dr["Name"].ToString();
com.CommandText = "SELECT 1 FROM Enrollments WHERE IdStudy = @idStudy AND Semester = 1 ORDER BY StartDate DESC";
com.Parameters.AddWithValue("idStudy", studies.IdStudy);
dr = com.ExecuteReader();
if (!dr.Read())
{
var enrollmentReq = new EnrollEnrollmentRequest();
enrollmentReq.Semester = 1;
enrollmentReq.IdStudy = studies.IdStudy;
enrollmentReq.StartDate = DateTime.Now;
com.CommandText = "INSERT INTO Enrollment (Semester, IdStudy, StartDate) VALUES (@semester, @idStudy, @startDate)";
com.Parameters.AddWithValue("semester", enrollmentReq.Semester);
com.Parameters.AddWithValue("idStudy", enrollmentReq.IdStudy);
com.Parameters.AddWithValue("startDate", enrollmentReq.StartDate);
com.CommandText = "SELECT 1 FROM Enrollments WHERE IdStudy = @idStudy AND Semester = 1 ORDER BY StartDate DESC";
com.Parameters.AddWithValue("idStudy", studies.IdStudy);
dr = com.ExecuteReader();
enrollment.IdEnrollment = Int16.Parse(dr["IdEnrollment"].ToString());
enrollment.IdStudy = Int16.Parse(dr["IdStudy"].ToString());
enrollment.Semester = Int16.Parse(dr["Semester"].ToString());
enrollment.StartDate = DateTime.Parse(dr["StartDate"].ToString());
}
else if (dr.Read())
{
enrollment.IdEnrollment = Int16.Parse(dr["IdEnrollment"].ToString());
enrollment.IdStudy = Int16.Parse(dr["IdStudy"].ToString());
enrollment.Semester = Int16.Parse(dr["Semester"].ToString());
enrollment.StartDate = DateTime.Parse(dr["StartDate"].ToString());
}
com.CommandText = "Select 1 From Student Where IndexNumber = @index";
com.Parameters.AddWithValue("index", st.IndexNumber);
if (dr.Read())
{
}
com.CommandText = "INSERT INTO Student(IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) VALUES (@index, @fname, @lname, @birthDate, @idEnrollment";
com.Parameters.AddWithValue("index", st.IndexNumber);
com.Parameters.AddWithValue("fname", st.FirstName);
com.Parameters.AddWithValue("lname", st.LastName);
com.Parameters.AddWithValue("birthDate", st.BirthDate);
com.Parameters.AddWithValue("idEnrollment", enrollment.IdEnrollment);
tran.Commit();
}
catch (SqlException exc)
{
tran.Rollback();
}
return(enrollment);
}
}
public IActionResult EnrollStudent(EnrollStudentRequest request)
{
var s = _context.EnrollStudent(request);
return(Ok(s));
}
public IActionResult EntrollStudent(EnrollStudentRequest request)
{
var result = service.EnrollStudent(request);
return(Ok("Student został zapisany"));
}
public void EnrollStudent(EnrollStudentRequest request)
{
using (SqlConnection con = new SqlConnection(ConString))
using (SqlCommand com = new SqlCommand())
{
com.Connection = con;
con.Open();
var tran = con.BeginTransaction();
try
{
//1. Czy studia istnieja
com.CommandText = "select IdStudy from studies where name=@name";
com.Parameters.AddWithValue("name", request.Studies);
com.Transaction = tran;
SqlDataReader dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
request.Studies = null;
tran.Rollback();
}
else
{
int idStudies = (int)dr["IdStudy"];
dr.Close();
com.CommandText = "select IdStudy from enrollment where idstudy=@idstudies AND semester=1";
com.Parameters.AddWithValue("idstudies", idStudies);
dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
com.CommandText = "select TOP 1 * from Enrollment ORDER BY IdEnrollment DESC";
dr = com.ExecuteReader();
if (dr.Read())
{
int nextIdEnrollment = (int)dr["IdEnrollment"];
nextIdEnrollment += 1;
dr.Close();
com.CommandText = "Insert into Enrollment values(@idEnrollment,@semester,@idStudy,@startDate)";
com.Parameters.AddWithValue("idEnrollment", nextIdEnrollment);
com.Parameters.AddWithValue("semester", 1);
com.Parameters.AddWithValue("idStudy", idStudies);
com.Parameters.AddWithValue("startDate", DateTime.Now);
com.ExecuteNonQuery();
}
}
dr.Close();
com.CommandText = "select IndexNumber from Student where IndexNumber=@indexNumber";
com.Parameters.AddWithValue("indexNumber", request.IndexNumber);
dr = com.ExecuteReader();
if (dr.Read())
{
request.IndexNumber = null;
dr.Close();
tran.Rollback();
}
dr.Close();
//INSERT STUDENT
//CHECK IDENROLLMENT
if (request.IndexNumber != null)
{
com.CommandText = "select TOP 1 * from Enrollment where semester = 1 ORDER BY IdEnrollment DESC";
dr = com.ExecuteReader();
int lastIdEnrollment = 1;
if (dr.Read())
{
lastIdEnrollment = (int)dr["IdEnrollment"];
}
Console.WriteLine("req ind num = " + request.IndexNumber);
dr.Close();
//INSERT INTO STUDENT TABLE
DateTime date = Convert.ToDateTime(request.BirthDate);
com.CommandText = "Insert into Student (IndexNumber, FirstName, LastName, BirthDate, IdEnrollment)values(@index,@firstName,@lastName,@birthDate,@idEnrollmentStudent)";
com.Parameters.AddWithValue("index", request.IndexNumber);
com.Parameters.AddWithValue("firstName", request.FirstName);
com.Parameters.AddWithValue("lastName", request.LastName);
com.Parameters.AddWithValue("birthDate", date);
com.Parameters.AddWithValue("idEnrollmentStudent", lastIdEnrollment);
com.ExecuteNonQuery();
tran.Commit();
}
}
}
catch (SqlException exc)
{
Console.WriteLine(exc.Message);
tran.Rollback();
}
}
}
public IActionResult EnrollStudent(EnrollStudentRequest request)
{
return(_service.EnrollStudent(request));
}
public EnrollStudentResponse EnrollStudent(EnrollStudentRequest req)
{
EnrollStudentResponse resp = null;
Enrollment respEnrollment = new Enrollment();
SqlTransaction transaction = null;
using (var con = new SqlConnection(SqlConn))
using (var com = new SqlCommand())
{
com.CommandText = "SELECT * FROM Studies WHERE Name = @StudyName";
com.Parameters.AddWithValue("StudyName", req.Studies);
com.Transaction = transaction;
com.Connection = con;
con.Open();
transaction = con.BeginTransaction();
com.Transaction = transaction;
SqlDataReader dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
transaction.Rollback();
dr.Dispose();
throw new ArgumentException("Studies not found");
}
int idStudy = (int)dr["IdStudy"]; // needed for 3.
dr.Close();
int idEnrollment = 0;
com.CommandText = "SELECT * FROM Enrollment WHERE Semester = 1 AND IdStudy = @IdStudy";
com.Parameters.AddWithValue("IdStudy", idStudy);
com.Transaction = transaction;
dr = com.ExecuteReader();
if (dr.Read())
{
idEnrollment = Int32.Parse(dr["IdEnrollment"].ToString());
}
else
{
dr.Close();
com.CommandText = "SELECT MAX(IdEnrollment) AS EnrolmentMaxId FROM Enrollment WHERE Semester = 1";
com.Transaction = transaction;
dr = com.ExecuteReader();
idEnrollment = Int32.Parse(dr["EnrolmentMaxId"].ToString());
DateTime todayDate = DateTime.Today;
com.CommandText = "INSERT INTO Enrollment (IdEnrollment, IdStudy, Semester, StartDate)values (@EnrolmentId, @IdStudies, 1, @TodayDate);";
com.Parameters.AddWithValue("@TodayDate", todayDate);
com.Parameters.AddWithValue("@EnrolmentId", idEnrollment);
com.ExecuteNonQuery();
}
dr.Close();
com.CommandText = "SELECT * FROM Student WHERE IndexNumber = @IndexNumber";
com.Parameters.AddWithValue("IndexNumber", req.IndexNumber);
com.Transaction = transaction;
dr = com.ExecuteReader();
if (dr.Read())
{
dr.Close();
transaction.Rollback();
dr.Dispose();
throw new InvalidOperationException("Niewlasciwy numer studenta");
}
dr.Close();
com.CommandText = "INSERT INTO Student (IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) VALUES (@IndexNumber, @Firstname, @LastName, convert(datetime, @BirthDate, 104), @IdEnrollment)";
com.Parameters.AddWithValue("FirstName", req.FirstName);
com.Parameters.AddWithValue("LastName", req.LastName);
com.Parameters.AddWithValue("BirthDate", req.BirthDate);
com.Parameters.AddWithValue("IdEnrollment", idEnrollment);
com.Transaction = transaction;
com.ExecuteNonQuery();
dr.Close();
com.CommandText = "SELECT * FROM Enrollment WHERE IdEnrollment = @IdEnrollment";
com.Transaction = transaction;
dr = com.ExecuteReader();
dr.Read();
resp = new EnrollStudentResponse();
resp.LastName = req.LastName;
resp.Semester = 1;
resp.Studies = req.Studies;
resp.StartDate = dr["StartDate"].ToString();
dr.Dispose();
transaction.Commit();
}
return(resp);
}
public IActionResult EnrollStudent(EnrollStudentRequest request)
{
EnrollStudentResponse esr = new EnrollStudentResponse()
{
};
// if (request.FirstName == null || request.LastName == null)
// {
// return BadRequest("Zadanie jest niepoprawne");
// }
using (var con = new SqlConnection("Data Source=db-mssql;Initial Catalog=s18803;Integrated Security=True"))
using (var com = new SqlCommand())
{
con.Open();
var tran = con.BeginTransaction();
com.Connection = con;
com.Transaction = tran;
try
{
//1. Czy studia istnieją?
com.CommandText = "SELECT IdStudy AS idStudies FROM Studies WHERE Name=@name";
com.Parameters.AddWithValue("name", request.Studies);
var dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
tran.Rollback();
return(NotFound("Nie ma takich studiów"));
}
int idStudies = (int)dr["idStudies"];
dr.Close();
//2. Sprawdzenie czy nie występuje konflikt indeksów
com.CommandText = "SELECT IndexNumber FROM Student WHERE IndexNumber = " + request.IndexNumber; // ' '
dr = com.ExecuteReader();
if (dr.Read())
{
dr.Close();
tran.Rollback();
return(BadRequest("Student z takim indeksem już istnieje"));
}
dr.Close();
//3. Nadanie IdEnrollment
int idEnrollment;
com.CommandText = "SELECT IdEnrollment FROM Enrollment JOIN Studies ON " +
"Enrollment.IdStudy = Studies.IdStudy WHERE Semester = 1 and IdStudy = " + idStudies;
dr = com.ExecuteReader();
if (dr.Read())
{
dr.Close();
com.CommandText = "SELECT MAX(IdEnrollment)+1 AS idEnroll from Enrollment";
dr = com.ExecuteReader();
idEnrollment = (int)dr["idEnroll"];
}
else
{
idEnrollment = 1;
dr.Close();
}
//4. Wstawienie Enrollment
com.CommandText = "INSERT INTO Enrollment(IdEnrollment,Semester,IdStudy,StartDate)" +
" VALUES(" + idEnrollment + ", 1, " + idStudies + ",GetDate())";
com.ExecuteNonQuery();
//5. Wstawienie studenta
com.CommandText = "INSERT INTO Student(IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) " +
"VALUES (" + request.IndexNumber + ", " + request.FirstName + ", " + request.LastName + ", " +
request.BirthDate + ", " + idEnrollment + ") ";
com.ExecuteNonQuery();
esr.IdEnrollment = idEnrollment;
esr.IdStudy = idStudies;
esr.Semester = 1;
esr.StartDate = DateTime.Now;
tran.Commit();
tran.Dispose();
return(StatusCode((int)HttpStatusCode.Created, esr));
}
catch (SqlException exc)
{
tran.Rollback();
return(BadRequest(exc.Message));
}
}
}
public void EnrollStudent(EnrollStudentRequest request)
{
string connstring =
"Data Source=10.1.1.36,1433;Initial Catalog=s18588;User ID=apbds18588;Password=admin";
var response = new EnrollStudentResponse();
var cl = new HttpClient();
cl.BaseAddress = new Uri("http://*****:*****@IdEnrollment, 1, @IdStudies, @StartDate)";
com.Parameters.AddWithValue("IdStudies", IdStudies);
com.Parameters.AddWithValue("IdEnrollment", IdEnrollment);
com.Parameters.AddWithValue("StartDate", StartDate);
com.ExecuteNonQuery();
}
dr.Close();
com.CommandText = "Select * From Student Where IndexNumber=@Id";
com.Parameters.AddWithValue("Id", request.IndexNumber);
dr = com.ExecuteReader();
var Enrollment = IdEnrollment;
com.Parameters.Clear();
if (!dr.Read())
{
dr.Close();
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", Enrollment);
com.ExecuteNonQuery();
response.Semester = "1";
response.LastName = request.LastName;
Console.WriteLine("Student inserted.");
}
else
{
dr.Close();
transaction.Rollback();
}
transaction.Commit();
}
}
public EnrollStudentResponse EnrollStudent(EnrollStudentRequest request)
{
var result = new EnrollStudentResponse();
using (var client = new SqlConnection(CONNECTION_STRING))
using (var command = new SqlCommand())
{
client.Open();
var tran = client.BeginTransaction();
command.Connection = client;
command.Transaction = tran;
command.CommandText = "SELECT IdStudy FROM Studies where Name = @Name;";
command.Parameters.AddWithValue("Name", request.Studies);
try
{
var CheckStudyReader = command.ExecuteReader();
//check if studies exist else rollback + 404
if (!CheckStudyReader.Read())
{
//tran.Rollback();
//studies does not exist
return(null);
}
int idStudy = int.Parse(CheckStudyReader["IdStudy"].ToString());
// Note that the assignment asks to search for values where semester = 1; However, our tables have
// years as semesters so for example 2020 , for that reason I just assumed 2020 as the default semester
//instead of 1
CheckStudyReader.Close();
int enrollId = 0;
command.Parameters.Clear();
command.CommandText = "SELECT IdEnrollment FROM Enrollment where Semester = 1 AND IdStudy=@IdStudy";
command.Parameters.AddWithValue("IdStudy", idStudy);
var CheckEnrollmentReader = command.ExecuteReader();
//check if enrollment already exists , else insert one
if (!CheckEnrollmentReader.Read())
{
CheckEnrollmentReader.Close();
command.CommandText = "Select Top 1 IdEnrollment as id from Enrollment Order By IdEnrollment DESC";
var GetEnrollmentIdReader = command.ExecuteReader();
GetEnrollmentIdReader.Read();
enrollId = int.Parse(GetEnrollmentIdReader["id"].ToString());
GetEnrollmentIdReader.Close();
command.Parameters.Clear();
command.CommandText = "insert into Enrollment(IdEnrollment,IdStudy, Semester, StartDate) values (@enroll, @IdStudy, @Semester, @Date)";
command.Parameters.AddWithValue("enroll", ++enrollId);
command.Parameters.AddWithValue("IdStudy", idStudy);
command.Parameters.AddWithValue("Semester", 1);
command.Parameters.AddWithValue("Date", DateTime.Parse(DateTime.Now.ToString("yyyy'-'MM'-'dd")));
}
else
{
enrollId = int.Parse(CheckEnrollmentReader["IdEnrollment"].ToString());
CheckEnrollmentReader.Close();
}
//check if index number was assigned to any other student , if not insert student
command.Parameters.Clear();
command.CommandText = "Select count(1) as studentCount from Student where IndexNumber = @index";
command.Parameters.AddWithValue("index", request.IndexNumber);
var CheckIndexReader = command.ExecuteReader();
if (CheckIndexReader.Read() && int.Parse(CheckIndexReader["studentCount"].ToString()) > 0)
{
tran.Rollback();
return(null);
}
CheckIndexReader.Close();
command.Parameters.Clear();
command.CommandText = "INSERT INTO Student(IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) VALUES (@index,@FirstName,@LastName,@Birthdate,@enrollId)";
command.Parameters.AddWithValue("index", request.IndexNumber);
command.Parameters.AddWithValue("FirstName", request.FirstName);
command.Parameters.AddWithValue("LastName", request.LastName);
command.Parameters.AddWithValue("BirthDate", request.BirthDate);
command.Parameters.AddWithValue("enrollId", enrollId);
command.ExecuteNonQuery();
result.IdEnrollment = enrollId;
result.IndexNumber = request.IndexNumber;
result.Semester = 1;
tran.Commit();
client.Close();
}
catch (Exception e)
{
tran.Rollback();
return(null);
}
return(result);
}
}
public EnrollStudentResult EnrollStudent(EnrollStudentRequest request)
{
EnrollStudentResult result = new EnrollStudentResult();
if (string.IsNullOrWhiteSpace(request.FirstName) ||
string.IsNullOrWhiteSpace(request.LastName) ||
string.IsNullOrWhiteSpace(request.IndexNumber) ||
string.IsNullOrWhiteSpace(request.BirthDate) ||
string.IsNullOrWhiteSpace(request.Studies) ||
!DateTime.TryParse(request.BirthDate, out DateTime birthDate))
{
result.ResultCode = ResultCodes.NieWpisanoWszystkichDanychStudenta;
return(result);
}
using (var con = new SqlConnection("Data Source=db-mssql;Initial Catalog=s16703;Integrated Security=True"))
using (var com = new SqlCommand())
{
com.Connection = con;
con.Open();
var tran = con.BeginTransaction();
com.Transaction = tran;
com.CommandText = "select IdStudy from Studies where Name = @name";
com.Parameters.AddWithValue("name", request.Studies);
var dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
tran.Rollback();
result.ResultCode = ResultCodes.NieIstniejaStudia;
return(result);
}
int idStudy = (int)dr["IdStudy"];
dr.Close();
com.Parameters.Clear();
com.CommandText = "select e.StartDate, e.IdEnrollment from Enrollment e join Student s on e.IdEnrollment = s.IdEnrollment " +
"where e.Semester = 1 and s.IndexNumber = @IndexNumber " +
"order by StartDate desc";
com.Parameters.AddWithValue("IndexNumber", request.IndexNumber);
dr = com.ExecuteReader();
if (dr.Read())
{
dr.Close();
tran.Rollback();
result.ResultCode = ResultCodes.StudentJestJuzZapisanyNaSemest;
return(result);
}
dr.Close();
com.Parameters.Clear();
com.CommandText = "select max(IdEnrollment) from Enrollment";
int maxId = (int)com.ExecuteScalar() + 1;
DateTime startDate = DateTime.Now;
com.CommandText = "Insert into Enrollment (IdEnrollment, Semester, IdStudy, StartDate) values (@IdEnrollment, @Semester, @IdStudy, @StartDate)";
com.Parameters.AddWithValue("IdEnrollment", maxId);
com.Parameters.AddWithValue("Semester", 1);
com.Parameters.AddWithValue("IdStudy", idStudy);
com.Parameters.AddWithValue("StartDate", startDate);
com.ExecuteNonQuery();
com.Parameters.Clear();
//----------------------//
com.CommandText = "select FirstName from Student where IndexNumber = @IndexNumber";
com.Parameters.AddWithValue("IndexNumber", request.IndexNumber);
dr = com.ExecuteReader();
if (dr.Read())
{
dr.Close();
tran.Rollback();
result.ResultCode = ResultCodes.StudentJuzIstnieje;
return(result);
}
dr.Close();
com.Parameters.Clear();
com.CommandText = "Insert into Student (IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) values (@Index, @Fname, @LName, @Date, @IdEnroll)";
com.Parameters.AddWithValue("Index", request.IndexNumber);
com.Parameters.AddWithValue("Fname", request.FirstName);
com.Parameters.AddWithValue("Lname", request.LastName);
com.Parameters.AddWithValue("Date", request.BirthDate);
com.Parameters.AddWithValue("IdEnroll", maxId);
com.ExecuteNonQuery();
tran.Commit();
var response = new EnrollStudentResponse
{
IdEnrollment = maxId,
IdStudy = idStudy,
Semester = 1,
StartDate = startDate
};
result.ResultCode = ResultCodes.StudentDodany;
result.Response = response;
return(result);
}
}
public IActionResult EnrollStudent(EnrollStudentRequest request)
{
var response = _service.EnrollStudent(request);
return(Ok(response));
}
public string EnrollStudent(EnrollStudentRequest req)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
StringBuilder result = new StringBuilder();
connection.Open();
SqlTransaction trans = connection.BeginTransaction("Samp");
using (SqlCommand command = new SqlCommand("SELECT * FROM Studies WHERE name = @name", connection))
{
command.Parameters.AddWithValue("name", req.Studies);
command.Transaction = trans;
using (var reader = command.ExecuteReader())
{
if (!reader.Read())
{
reader.Close();
trans.Rollback();
return("Failed: There is not such " + req.Studies + " in table.");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM Enrollment e INNER JOIN Studies s ON s.idStudy = e.idStudy WHERE s.name = @name;" +
" SELECT * FROM Studies WHERE name = @name;" +
" SELECT MAX(idEnrollment) + 1 FROM Enrollment;", connection))
{
int IdEnrollment = -1;
int IdStudy = -1;
command.Parameters.AddWithValue("name", req.Studies);
command.Transaction = trans;
using (var reader = command.ExecuteReader())
{
if (!reader.Read())
{
result.Append("Needed to create table.\n");
reader.NextResult();
reader.Read();
IdStudy = (int)reader[0];
reader.NextResult();
reader.Read();
IdEnrollment = (int)reader[0];
String date = DateTime.Now.ToString();
command.CommandText = "INSERT INTO Enrollment (IdEnrollment,Semester,IdStudy,StartDate) VALUES (@idenrollment,1,@idstudy,'" + date.Substring(0, date.IndexOf(" ")).ToString() + "')";
command.Parameters.AddWithValue("idenrollment", IdEnrollment);
command.Parameters.AddWithValue("idstudy", IdStudy);
reader.Close();
command.ExecuteNonQuery();
}
DateTime d = Convert.ToDateTime(req.BirthDate);
String sDate;
sDate = d.Date.ToString("MM-dd-yyyy").Substring(0, d.Date.ToString().IndexOf(" "));
reader.Close();
command.CommandText = "INSERT INTO Student (IndexNumber,FirstName,LastName,BirthDate,IdEnrollment) " +
"VALUES (@index,@first_name,@last_name,'" + sDate + "',@id_enrollment)";
command.Parameters.AddWithValue("index", req.IndexNumber);
command.Parameters.AddWithValue("first_name", req.FirstName);
command.Parameters.AddWithValue("last_name", req.LastName);
command.Parameters.AddWithValue("id_enrollment", IdEnrollment);
try
{
command.ExecuteNonQuery();
}catch (SqlException e)
{
trans.Rollback();
return("Failed: Not unique index.");
}
trans.Commit();
result.Append("ADDED");
return(result.ToString());
}
}
}
}
public void EnrollStudent(EnrollStudentRequest request)
{
}
public EnrollStudentResponse EnrollStudents(EnrollStudentRequest request)
{
//Mapowanieroll
var student = new Student();
student.IndexNumber = request.IndexNumber;
student.FirstName = request.FirstName;
student.LastName = request.LastName;
student.BirthDate = DateTime.Parse(request.BirthDate);
var enrolment = new EnrollStudentResponse();
enrolment.Semester = 1;
int idStudies = 0;
int lastEnrollmentId = 0;
using (var connection = new SqlConnection(SqlConn))
using (var command = new SqlCommand())
{
command.Connection = connection;
connection.Open();
var transaction = connection.BeginTransaction();
//Sprawdzamy czy istnieją studia o podanej nazwie
command.CommandText = "Select IdStudy from Studies where name = @studyName";
command.Parameters.AddWithValue("studyName", request.Studies);
command.Transaction = transaction;
var dr = command.ExecuteReader();
if (!dr.Read())
{
dr.Close();
transaction.Rollback();
return(null);
}
idStudies = (int)dr["IdStudy"];
dr.Close();
enrolment.studia = request.Studies;
//Sprawdzamy czy istnieje już wpis dla danych studiów na dany semestr
command.CommandText = "Select top 1 IdEnrollment from Enrollment where IdStudy = @idStudy " +
"and semester = 1 order by StartDate desc";
command.Parameters.AddWithValue("idStudy", idStudies);
dr = command.ExecuteReader();
if (!dr.Read())
{
//Sprawdzenie ostatniego numeru idEnrollment aby dodać z o 1 większym kolejny wpis
dr.Close();
command.CommandText = "Select max(IdEnrollment) as IdEnrollment from Enrollment";
dr = command.ExecuteReader();
if (dr.Read())
{
lastEnrollmentId = (int)dr["IdEnrollment"];
}
dr.Close();
//Dodanie nowego enrollmentu
command.CommandText = "Insert into Enrollment values(@idEnrollment, 1, @idStudies ,@actualDate )";
command.Parameters.AddWithValue("idEnrollment", lastEnrollmentId + 1);
command.Parameters.AddWithValue("idStudies", idStudies);
command.Parameters.AddWithValue("actualDate", DateTime.Now);
int dodaneWiersze = command.ExecuteNonQuery();
student.IdEnrollment = lastEnrollmentId + 1;
}
else
{
lastEnrollmentId = (int)dr["IdEnrollment"];
student.IdEnrollment = lastEnrollmentId;
dr.Close();
}
enrolment.IdEnrollment = student.IdEnrollment;
//Sprawdzamy czy indeks studenta jest unikalny
command.CommandText = "Select IndexNumber from Student where IndexNumber = @iNumber";
command.Parameters.AddWithValue("iNumber", student.IndexNumber);
dr = command.ExecuteReader();
if (!dr.Read())
{
dr.Close();
//Jeżeli indeks jest unikalny to dodajemy studenta do bazy
command.CommandText = "Insert into Student (IndexNumber, firstname, lastname, birthdate, IdEnrollment ) " +
"values (@indexNumber, @firstName, @lastName, @birthDate, @idEnroll)";
command.Parameters.AddWithValue("indexNumber", student.IndexNumber);
command.Parameters.AddWithValue("firstName", student.FirstName);
command.Parameters.AddWithValue("lastName", student.LastName);
command.Parameters.AddWithValue("birthDate", student.BirthDate.ToShortDateString());
command.Parameters.AddWithValue("idEnroll", student.IdEnrollment);
int dodaneWiersze = command.ExecuteNonQuery();
}
else
{
dr.Close();
transaction.Rollback();
return(null);
}
if (!dr.IsClosed)
{
dr.Close();
}
transaction.Commit();
connection.Close();
}
return(enrolment);
}
