public IActionResult GetStudent(string indexNumber) //action method
{
using (SqlConnection con = new SqlConnection(conString))
using (SqlCommand com = new SqlCommand())
{
com.Connection = con;
com.CommandText = "select s.FirstName, s.LastName, s.BirthDate, st.Name, e.Semester from Student s join Enrollment e on e.IdEnrollment = s.IdEnrollment join Studies st on st.IdStudy = e.IdStudy where indexnumber = @index";
SqlParameter par = new SqlParameter();
par.Value = indexNumber;
par.ParameterName = "index";
com.Parameters.Add(par);
con.Open();
var dr = com.ExecuteReader();
if (dr.Read())
{
var st = new StudentInfoDTO();
st.FirstName = dr["FirstName"].ToString();
st.LastName = dr["LastName"].ToString();
st.BirthDate = dr["BirthDate"].ToString();
st.Name = dr["Name"].ToString();
st.Semester = dr["Semester"].ToString();
return(Ok(st));
}
return(NotFound());
}
}
public IActionResult GetStudent(string indexNumber)
{
using (SqlConnection sqlConnection = new SqlConnection(ConnString))
using (SqlCommand command = new SqlCommand())
{
command.Connection = sqlConnection;
//command.CommandText = "SELECT * FROM Student WHERE indexnumber=@indexNumber";
//command.Parameters.AddWithValue("index", indexNumber);
command.CommandText = "SELECT * FROM Student WHERE indexnumber=@id";
command.Parameters.AddWithValue("id", id);
sqlConnection.Open();
var dr = command.ExecuteReader();
if (dr.Read())
{
var st = new StudentInfoDTO()
{
IndexNumber = dr["IndexNumber"].ToString(),
FirstName = dr["FirstName"].ToString(),
LastName = dr["LastName"].ToString()
};
return(Ok(st));
}
}
return(NotFound("Nie znaleziono studenta."));
}
public IActionResult GetStudent(string id)
{
var student = new StudentInfoDTO();
using (SqlConnection connection = new SqlConnection(conString))
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = "select s.FirstName, s.LastName, s.BirthDate, s.IndexNumber, st.Name, e.Semester " +
"from Student s " +
"join Enrollment e on e.IdEnrollment = s.IdEnrollment " +
"join Studies st on st.IdStudy = e.IdStudy " +
"where s.IndexNumber = @id";
command.Parameters.AddWithValue("id", id);
connection.Open();
SqlDataReader dataReader = command.ExecuteReader();
dataReader.Read();
student.FirstName = dataReader["FirstName"].ToString();
student.LastName = dataReader["LastName"].ToString();
student.Name = dataReader["Name"].ToString();
student.BirthDate = dataReader["BirthDate"].ToString();
student.Semester = dataReader["Semester"].ToString();
}
return(Ok(student));
}
public IActionResult GetStudent(string id)
{
var student = new StudentInfoDTO();
using (SqlConnection connection = new SqlConnection(ConString))
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = "select s.FirstName, s.LastName, s.BirthDate, s.IndexNumber, st.Name, e.Semester from Student s " +
"join Enrollment e on e.IdEnrollment = s.IdEnrollment join Studies st on st.IdStudy = e.IdStudy " +
$"where s.IndexNumber = '{id}'";
connection.Open();
SqlDataReader dataReader = command.ExecuteReader();
dataReader.Read();
student.FirstName = dataReader["FirstName"].ToString();
student.LastName = dataReader["LastName"].ToString();
student.Name = dataReader["Name"].ToString();
student.BirthData = dataReader["BirthDate"].ToString();
student.Semester = dataReader["Semester"].ToString();
}
ObjectResult objectResult = new ObjectResult(student);
objectResult.StatusCode = 201;
return(objectResult);
}
public IActionResult GetStudent([FromRoute] string id) //action method
{
var list = new List <StudentInfoDTO>();
using (SqlConnection connection = new SqlConnection(myDatabase))
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
string sqlQuery = "select s.FirstName, s.LastName, s.BirthDate, st.Name, e.Semester from Student s " +
"join Enrollment e on e.IdEnrollment = s.IdEnrollment join Studies st on st.IdStudy = e.IdStudy " +
"where s.IndexNumber=@id";
command.CommandText = sqlQuery;
command.Parameters.AddWithValue("id", id);
connection.Open();
SqlDataReader sqlDataReader = command.ExecuteReader();
while (sqlDataReader.Read())
{
var studentInfoDTO = new StudentInfoDTO
{
BirthDate = sqlDataReader["BirthDate"].ToString(),
LastName = sqlDataReader["LastName"].ToString(),
FirstName = sqlDataReader["FirstName"].ToString(),
Name = sqlDataReader["Name"].ToString(),
Semester = sqlDataReader["Semester"].ToString()
};
list.Add(studentInfoDTO);
}
}
return(Ok(list));
}
public IActionResult GetStudents([FromServices] IDbService service)
{
var list = new List <StudentInfoDTO>();
using (SqlConnection connection = new SqlConnection(myDatabase))
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = "select s.FirstName, s.LastName, s.BirthDate, st.Name, e.Semester from Student s " +
"join Enrollment e on e.IdEnrollment = s.IdEnrollment join Studies st on st.IdStudy = e.IdStudy";
connection.Open();
SqlDataReader sqlDataReader = command.ExecuteReader();
while (sqlDataReader.Read())
{
var studentInfoDTO = new StudentInfoDTO
{
BirthDate = sqlDataReader["BirthDate"].ToString(),
LastName = sqlDataReader["LastName"].ToString(),
FirstName = sqlDataReader["FirstName"].ToString(),
Name = sqlDataReader["Name"].ToString(),
Semester = sqlDataReader["Semester"].ToString()
};
list.Add(studentInfoDTO);
}
}
return(Ok(list));
}
//zad 4.1 i 4.2
// [HttpGet]
public IActionResult GetStudent()
{
using (SqlConnection con = new SqlConnection(conString))
using (SqlCommand request = new SqlCommand()){
request.Connection = con;
request.CommandText = "select FirstName, LastName, BirthDate, Name, Semester from Studies" +
" left join Enrollment on Studies.IdStudy = Enrollment.IdStudy" +
" left join Student on Enrollment.IdEnrollment = Student.IdEnrollment";
con.Open();
var reader = request.ExecuteReader();
while (reader.Read())
{
StudentInfoDTO student = new StudentInfoDTO();
student.firstName = reader["FirstName"].ToString();
student.lastName = reader["LastName"].ToString();
student.birthDate = reader.GetDateTime(reader.GetOrdinal("BirthDate"));
student.name = reader["Name"].ToString();
student.semester = reader.GetInt32(reader.GetOrdinal("Semester"));
list.Add(student);
}
}
return(Ok(list));
}
public async Task <IActionResult> GetStudent(string id)
{
using SqlConnection con = new SqlConnection(conString);
using SqlCommand request = new SqlCommand();
request.Connection = con;
request.CommandText = "select FirstName, LastName, BirthDate, Name, Semester from Studies" +
" left join Enrollment on Studies.IdStudy = Enrollment.IdStudy" +
" left join Student on Enrollment.IdEnrollment = Student.IdEnrollment Where Student.IndexNumber=@id";
request.Parameters.AddWithValue("id", id);
con.Open();
var reader = request.ExecuteReader();
reader.Read();
if (!reader.HasRows)
{
return(NotFound("Nie znaleziono studenta o takim indeksie"));
}
StudentInfoDTO student = new StudentInfoDTO();
student.firstName = reader["FirstName"].ToString();
student.lastName = reader["LastName"].ToString();
student.birthDate = reader.GetDateTime(reader.GetOrdinal("BirthDate"));
student.name = reader["Name"].ToString();
student.semester = reader.GetInt32(reader.GetOrdinal("Semester"));
return(Ok(student));
}
public IActionResult GetStudents()
{
var list = new List <StudentInfoDTO>();
using (SqlConnection con = new SqlConnection(conString))
using (SqlCommand com = new SqlCommand())
{
com.Connection = con;
com.CommandText = "SELECT s.FirstName, s.LastName, s.BirthDate, st.Name, e.Semester " +
"FROM Student s " +
"JOIN Enrollment e ON e.IdEnrollment = s.IdEnrollment " +
"JOIN Studies st ON st.IdStudy = e.IdStudy";
con.Open();
SqlDataReader dr = com.ExecuteReader();
while (dr.Read())
{
var st = new StudentInfoDTO
{
LastName = dr["LastName"].ToString(),
FirstName = dr["FirstName"].ToString(),
BirthDate = dr["BirthDate"].ToString(),
Name = dr["Name"].ToString(),
Semester = dr["Semester"].ToString()
};
list.Add(st);
}
}
return(Ok(list));
}
//public Task<ClaimsIdentity> Authenticate(StudentInfoDTO studentInfoDto)
//{
// throw new NotImplementedException();
//}
public async Task <OperationDetails> ChangeInformation(StudentInfoDTO studentInfoDTO)
{
string id = Convert.ToString(studentInfoDTO.studentInfoID);
StudentInfo studentInfo = Database.RStudentsInfo.Get(id);
if (studentInfo == null)
{
throw new ValidationException("Student is not found", id);
}
StudentInfo student = new StudentInfo();
studentInfo.stundtInfoID = student.stundtInfoID;
studentInfo.studentName = student.studentName;
studentInfo.studentMiddleName = student.studentMiddleName;
studentInfo.studentLastName = student.studentLastName;
studentInfo.bday = student.bday;
studentInfo.studentSex = student.studentSex;
studentInfo.email = student.email;
studentInfo.homeAddress = student.homeAddress;
studentInfo.phone = student.phone;
studentInfo.StudentFirstParentName = student.StudentFirstParentName;
studentInfo.StudentFirstParentPhone = student.StudentFirstParentPhone;
studentInfo.StudentSecondParentName = student.StudentSecondParentName;
studentInfo.StudentSecondParentPhone = student.StudentSecondParentPhone;
Database.RStudentsInfo.Update(student);
await Database.Save();
return(new OperationDetails(true, "User successfully changed", "UserProfile"));
}
public IActionResult GetStudents(string orderBy)
{
var list = new List <StudentInfoDTO>();
using (SqlConnection con = new SqlConnection(conString))
using (SqlCommand com = new SqlCommand())
{
com.Connection = con;
com.CommandText = "select s.FirstName, s.LastName, s.BirthDate, st.Name, e.Semester from Student s join Enrollment e on e.IdEnrollment = s.IdEnrollment join Studies st on st.IdStudy = e.IdStudy";
con.Open();
SqlDataReader dr = com.ExecuteReader();
while (dr.Read())
{
var st = new StudentInfoDTO
{
FirstName = dr["FirstName"].ToString(),
LastName = dr["LastName"].ToString(),
BirthDate = dr["BirthDate"].ToString(),
Name = dr["Name"].ToString(),
Semester = dr["Semester"].ToString()
};
list.Add(st);
}
}
return(Ok(list));
}
public IHttpActionResult PutStudent(string id, StudentInfoDTO student)
{
if (studentsService.ExistsID(id) == false)
{
return(NotFound());
}
return(Ok(studentsService.PutStudent(id, student)));
}
public StudentInfoDTO FindStudentInfo(string Name, string LastName, string bDay, string phoneNum)
{
StudentInfo studentInfo = Database.RStudentsInfo.Get(Name);
if (studentInfo != null)
{
StudentInfoDTO studentInfoDTO = new StudentInfoDTO();
studentInfoDTO.studentName = studentInfo.studentName;
if (studentInfo.studentName == Name)
{
return(studentInfoDTO);
}
}
return(null);
}
public StudentInfoDTO PutStudent(string id, StudentInfoDTO student)
{
StudentModel studentFromDB = db.StudentsRepository.GetByID(id);
studentFromDB.FirstName = student.FirstName;
studentFromDB.LastName = student.LastName;
studentFromDB.UserName = student.UserName;
studentFromDB.Year = student.Year;
studentFromDB.DateOfBirth = student.DateOfBirth;
db.StudentsRepository.Update(studentFromDB);
db.Save();
logger.Info($"Information about student with and Id of {id} were changed");
return(MakeStudentInfoDTO(studentFromDB));
}
public async Task <IActionResult> AddStudentInfo(StudentInfoViewModel model)
{
if (model.StudentSecondParentName == "")
{
model.StudentSecondParentName = null;
}
if (model.StudentSecondParentPhone == "")
{
model.StudentSecondParentPhone = null;
}
if (ModelState.IsValid)
{
StudentInfoDTO studentInfoDTO = new StudentInfoDTO()
{
studentName = model.studentName,
studentMiddleName = model.studentMiddleName,
studentLastName = model.studentLastName,
studentSex = model.studentSex,
bday = model.bday,
phone = model.phone,
homeAddress = model.homeAddress,
email = model.email,
StudentFirstParentName = model.StudentFirstParentName,
StudentFirstParentPhone = model.StudentFirstParentPhone,
StudentSecondParentName = model.StudentSecondParentName,
StudentSecondParentPhone = model.StudentSecondParentPhone
};
if (studentInfo.FindStudentInfo(model.studentName, model.studentLastName, model.bday, model.phone) == null)
{
await studentInfo.CreateStudentInfo(studentInfoDTO);
return(RedirectToAction("StudentDepartment", "Home"));
}
else
{
ModelState.AddModelError("Name", "Даний студент вже існує");
}
}
return(View(model));
}
public IActionResult GetStudent([FromRoute] string indeks)
{
var st = new StudentInfoDTO();
using (SqlConnection con = new SqlConnection(conString))
using (SqlCommand com = new SqlCommand())
{
com.Connection = con;
com.CommandText = "select s.FirstName, s.LastName, s.BirthDate, st.Name, e.Semester from Student s join Enrollment e on e.IdEnrollment = s.IdEnrollment join Studies st on st.IdStudy = e.IdStudy " + $"where s.IndexNumber = '{indeks}'";
con.Open();
SqlDataReader dr = com.ExecuteReader();
dr.Read();
st.FirstName = dr["FirstName"].ToString();
st.LastName = dr["LastName"].ToString();
st.BirthDate = dr["BirthDate"].ToString();
st.Name = dr["Name"].ToString();
st.Semester = dr["Semester"].ToString();
}
return(Ok(st));
}
public async Task <OperationDetails> CreateStudentInfo(StudentInfoDTO studentInfoDTO)
{
StudentInfo studentInfo = new StudentInfo()
{
studentName = studentInfoDTO.studentName,
studentMiddleName = studentInfoDTO.studentMiddleName,
studentLastName = studentInfoDTO.studentLastName,
bday = studentInfoDTO.bday,
email = studentInfoDTO.email,
studentSex = studentInfoDTO.studentSex,
homeAddress = studentInfoDTO.homeAddress,
phone = studentInfoDTO.phone,
StudentFirstParentName = studentInfoDTO.StudentFirstParentName,
StudentFirstParentPhone = studentInfoDTO.StudentFirstParentPhone,
StudentSecondParentName = studentInfoDTO.StudentSecondParentName,
StudentSecondParentPhone = studentInfoDTO.StudentSecondParentPhone
};
Database.RStudentsInfo.Create(studentInfo);
await Database.Save();
return(new OperationDetails(true, "StudentInfo added", ""));
}
public StudentInfoDTO MakeStudentInfoDTO(StudentModel studentFromDB)
{
StudentInfoDTO student = new StudentInfoDTO(studentFromDB.Id, studentFromDB.FirstName, studentFromDB.LastName, studentFromDB.UserName, studentFromDB.Year, studentFromDB.DateOfBirth);
foreach (ParentModel parent in studentFromDB.Parents)
{
ParentsInfoForStudentDTO parentsInfo = new ParentsInfoForStudentDTO(parent.Id, parent.FirstName, parent.LastName);
student.ParentsInfo.Add(parentsInfo);
}
foreach (StudentToSubject sts in studentFromDB.StudentToSubjects)
{
SubjectWithGradesDTO subject = new SubjectWithGradesDTO(sts.Subject.Id, sts.Subject.SubjectName);
foreach (Grade grade in sts.Grades)
{
subject.Grades.Add(grade);
}
student.SubjectsWithGrades.Add(subject);
}
return(student);
}
