public StudentInfoDto 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();
if (!dataReader.Read())
{
return(null);
}
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(student);
}
public IActionResult GetStudents([FromServices] IStudentsDal dbService)
{
var list = new List <Student>();
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(),
Name = dr["Name"].ToString(),
BirthDate = dr["BirthDate"].ToString(),
Semester = dr["Semester"].ToString()
};
list.Add(st);
}
}
return(Ok(list));
}
public IEnumerable <StudentInfoDto> GetStudents()
{
var list = new List <StudentInfoDto>();
using (SqlConnection connection = new SqlConnection(ConString))
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 dataReader = command.ExecuteReader();
while (dataReader.Read())
{
var student = new StudentInfoDto
{
FirstName = dataReader["FirstName"].ToString(),
LastName = dataReader["LastName"].ToString(),
Name = dataReader["Name"].ToString(),
BirthDate = dataReader["BirthDate"].ToString(),
Semester = dataReader["Semester"].ToString()
};
list.Add(student);
}
}
return(list);
}
/// <summary>
/// Get one student object by Id
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public StudentInfoDto GetStudent(int Id)
{
StudentInfo studentInfo = _studentRepository.GetById(Id);
StudentInfoDto studentInfoDto = Mapper.Map <StudentInfo, StudentInfoDto>(studentInfo);
return(studentInfoDto);
}
public async Task <ActionResult> Update(int id, [FromBody] StudentInfoDto dto)
{
if (id != dto.StudentId)
{
return(BadRequest());
}
var student = await _studentRepository.LoadEntitiesAsIQueryable(x => x.StudentId == id)
.AsNoTracking()
.FirstOrDefaultAsync();
if (student == null)
{
return(NotFound());
}
_mapper.Map(dto, student);
await _studentRepository.EditEntityAsync(student);
return(NoContent());
}
public ActionResult Create(FormCollection collection)
{
try
{
StudentInfoDto stu = new StudentInfoDto()
{
Code = collection["Code"],
FirstName = collection["FirstName"],
LastName = collection["LastName"],
Age = Convert.ToInt32(collection["Age"])
};
_studentService.CreateStudent(stu);
return(RedirectToAction("Index"));
}
catch
{
return(View());
}
}
// GET: Student/Edit/5
public ActionResult Edit(int id)
{
StudentInfoDto student = _studentService.GetStudent(id);
return(View(student));
}
/// <summary>
/// Update student service
/// </summary>
/// <param name="student"></param>
/// <returns></returns>
public async Task UpdateStudent(int Id, StudentInfoDto student)
{
StudentInfo stu = Mapper.Map <StudentInfoDto, StudentInfo>(student);
await _studentRepository.Update(Id, stu);
}
/// <summary>
/// Create student service
/// </summary>
/// <param name="student"></param>
/// <returns></returns>
public async Task CreateStudent(StudentInfoDto student)
{
StudentInfo stu = Mapper.Map <StudentInfoDto, StudentInfo>(student);
await _studentRepository.Create(stu);
}
