public PromoteStudents Promote(PromoteStudents study)
{
var studies = study.Studies;
var semester = study.Semester;
var isNumeric = int.TryParse(semester, out _);
if (string.IsNullOrEmpty(studies) || !Regex.Match(studies,
"^([A-Z]{1})([A-Za-z]{1,})$").Success)
{
return(new PromoteStudents("Invalid Data In The Field Studies"));
}
if (string.IsNullOrEmpty(semester) || !isNumeric)
{
return(new PromoteStudents("Invalid Data In The Field IdStudent"));
}
if (!PromoteStudents.IsThereStudy(studies))
{
return(new PromoteStudents("There Is No Such Study"));
}
using (var sqlConnection = new SqlConnection(@"Server=db-mssql.pjwstk.edu.pl;Database=s18881;User Id=apbds18881;Password=admin;"))
{
sqlConnection.Open();
SqlTransaction transaction = sqlConnection.BeginTransaction("Transaction");
using (var mainCommand = new SqlCommand())
{
mainCommand.Connection = sqlConnection;
mainCommand.CommandText = "EXEC PromoteProcedure @Name, @Semester";
mainCommand.Parameters.AddWithValue("Name", study.Studies);
mainCommand.Parameters.AddWithValue("Semester", study.Semester);
mainCommand.Transaction = transaction;
mainCommand.ExecuteNonQuery();
try
{
mainCommand.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Commit Exception Type: {0}", e.GetType());
Console.WriteLine(" Message: {0}", e.Message);
try
{
transaction.Rollback();
return(new PromoteStudents(e.Message));
}
catch (Exception e2)
{
Console.WriteLine("Rollback Exception Type: {0}", e2.GetType());
Console.WriteLine(" Message: {0}", e2.Message);
return(new PromoteStudents(e.Message));
}
}
transaction.Commit();
}
}
var result = PromoteStudents.GetEnrollment(study.Studies);
return(result);
}
public void PromoteStudents(PromoteStudents promote)
{
using (var client = new SqlConnection(SqlConnection))
using (var command = new SqlCommand())
{
command.Connection = client;
client.Open();
var transaction = client.BeginTransaction();
command.Transaction = transaction;
//try{
//@studies varchar(250), @semester int
//Need id
int id = -1;
command.CommandText = "select EnrollmentN.IdEnrollment from EnrollmentN inner join StudiesN on EnrollmentN.IdStudy = StudiesN.IdStudy and StudiesN.Name like '" + promote.studies + "' where EnrollmentN.Semester =" + (promote.semester + 1);
var dr = command.ExecuteReader();
if (dr.Read())
{
lastUseE = (int)dr[0];
dr.Close();
}
if (!dr.Read())
{
dr.Close();
command.CommandText = "select * from findMinEnrollmentN";
var reader = command.ExecuteReader();
if (reader.Read())
{
lastUseE = (int)reader[0];
reader.Close();
}
else
{
reader.Close();
}
}
command.CommandText = "promoteStudent";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("studies", promote.studies);
command.Parameters.AddWithValue("semester", promote.semester);
//string s += client.InfoMessage;
command.ExecuteNonQuery();
transaction.Commit();
msg = id;
/*
* }
* catch (SqlException exc)
* {
* transaction.Rollback();
* msg = -4;
* return;
* }*/
}
}
public IActionResult getPromote(PromoteStudents promote)
{
//EF
context.Database.BeginTransaction();
var studies = context.studies.FirstOrDefault(s => s.Name == promote.studies);
if (studies == null)
{
context.Database.RollbackTransaction();
return(NotFound("Nie ma takiego Kierunku"));
}
//procedura dajaca promocje
context.Database.ExecuteSqlRaw("exec promoteStudent " + promote.studies + " " + promote.semester);
context.Database.CommitTransaction();
context.SaveChanges();
//stare
//exception i try catch :C
dbService.PromoteStudents(promote);
if (dbService.getMsg() == -4)
{
return(BadRequest("Something gone wrong :C"));
}
return(Created(" ", dbService.GetEnrollment()));
}
public IActionResult Promote(PromoteStudents study)
{
/*var studies = study.Studies;
* var semester = study.Semester;
* var isNumeric = int.TryParse(semester, out _);
*
* if (string.IsNullOrEmpty(studies) || !Regex.Match(studies,
* "^([A-Z]{1})([A-Za-z]{1,})$").Success)
* return BadRequest("Invalid Data In The Field Studies");
* if (string.IsNullOrEmpty(semester) || !isNumeric)
* return BadRequest("Invalid Data In The Field IdStudent");
*
* if (!PromoteStudents.IsThereStudy(studies))
* return BadRequest("There Is No Such Study");
*
* using (var sqlConnection = new SqlConnection(@"Server=db-mssql.pjwstk.edu.pl;Database=s18881;User Id=apbds18881;Password=admin;"))
* {
* sqlConnection.Open();
* var transaction = sqlConnection.BeginTransaction("Transaction");
* using var mainCommand = new SqlCommand();
* mainCommand.Connection = sqlConnection;
* mainCommand.CommandText = "EXEC PromoteProcedure @Name, @Semester";
* mainCommand.Parameters.AddWithValue("Name", study.Studies);
* mainCommand.Parameters.AddWithValue("Semester", study.Semester);
* mainCommand.Transaction = transaction;
* mainCommand.ExecuteNonQuery();
* try
* {
* mainCommand.ExecuteNonQuery();
* }
* catch (Exception e)
* {
* Console.WriteLine("Commit Exception Type: {0}", e.GetType());
* Console.WriteLine(" Message: {0}", e.Message);
* try
* {
* transaction.Rollback();
* return Problem(e.Message);
* }
* catch (Exception e2)
* {
* Console.WriteLine("Rollback Exception Type: {0}", e2.GetType());
* Console.WriteLine(" Message: {0}", e2.Message);
* return Problem(e.Message);
* }
* }
* transaction.Commit();
* }
* var result = PromoteStudents.GetEnrollment(study.Studies);
* return CreatedAtAction(nameof(Promote), result);*/
var result = _service.Promote(study);
if (result.Semester != null)
{
return(CreatedAtAction(nameof(Promote), result));
}
return(BadRequest(result.Studies));
}
public bool Promote(PromoteStudents promote)
{
try
{
var enrollments = _context.Student.ToList();
if (enrollments.Count == 0)
{
return(false);
}
_context.Database.BeginTransaction();
_context.Database.ExecuteSqlCommand("PromoteStudents", promote.sname, promote.semester);
_context.Database.CommitTransaction();
_context.SaveChanges();
return(true);
}catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(false);
}
//using (SqlConnection con = new SqlConnection(dbName))
//{
// using (SqlCommand com = new SqlCommand())
// {
// com.Connection = con;
// con.Open();
// SqlTransaction transaction = con.BeginTransaction();
// com.CommandText = "SELECT * FROM Enrollment e INNER JOIN Studies s ON s.idstudy = e.idstudy WHERE e.semester = reqSemester AND s.name = sname";
// com.Parameters.AddWithValue("sname", promote.sname);
// com.Parameters.AddWithValue("reqSemester", promote.semester);
// SqlDataReader reader = com.ExecuteReader();
// if (!reader.Read())
// {
// transaction.Rollback();
// reader.Close();
// return false;
// }
// reader.Close();
// com.Parameters.Clear();
// com.CommandText = "PromoteStudents";
// com.CommandType = CommandType.StoredProcedure;
// com.Parameters.AddWithValue("sname", promote.sname);
// com.Parameters.AddWithValue("reqSemester", promote.semester);
// com.ExecuteNonQuery();
// transaction.Commit();
// transaction.Dispose();
// return true;
// }
//}
}
public IActionResult getPromote(PromoteStudents promote)
{
dbService.PromoteStudents(promote);
if (dbService.getMsg() == -4)
{
return(BadRequest("Something gone wrong :C"));
}
return(Created(" ", dbService.GetEnrollment()));
}
public IActionResult Promote(PromoteStudents promote)
{
if (_service.Promote(promote) == false)
{
return(BadRequest(404));
}
return(Ok(promote));
}
public IActionResult Promote(PromoteStudents study)
{
var result = _service.Promote(study);
if (result.Semester != null)
{
return(CreatedAtAction(nameof(Promote), result));
}
return(BadRequest(result.Studies));
}
public async Task <IActionResult> Promote(PromoteStudents command)
{
if (!ModelState.IsValid)
{
return(BadRequest());
}
var enrollment = await _studiesService.PromoteStudents(command.Studies, command.Semester);
return(Created("", enrollment.ToViewModel()));
}
public IActionResult PromoteStudent(PromoteStudents request)
{
using (var con = new SqlConnection("Data Source=db-mssql; Initial Catalog=s7973; Integrated Security=True; MultipleActiveResultSets=True"))
using (var com = new SqlCommand())
using (var proc = new SqlCommand("PromoteStudents"))
{
com.Connection = con;
com.CommandText = "select * from enrollment e, studies s where e.idstudy = s.idstudy and name = @name and semester = @semester";
proc.Connection = con;
con.Open();
com.Parameters.AddWithValue("name", request.Studies);
com.Parameters.AddWithValue("semester", request.Semester);
var dr = com.ExecuteReader();
while (!dr.Read())
{
return(BadRequest("404 Not Found"));
}
proc.Connection = con;
proc.CommandType = System.Data.CommandType.StoredProcedure;
proc.Parameters.Add(new SqlParameter("@STUDIES", request.Studies));
proc.Parameters.Add(new SqlParameter("@SEMESTER", request.Semester));
proc.ExecuteReader();
var output = new Enrollment();
con.Close();
con.Open();
com.CommandText = "select * from enrollment where StartDate = (select max(StartDate) from enrollment)";
dr = com.ExecuteReader();
while (dr.Read())
{
output = new Enrollment
{
IdEnrollment = int.Parse(dr["IdEnrollment"].ToString()),
Semester = int.Parse(dr["Semester"].ToString()),
IdStudy = int.Parse(dr["IdStudy"].ToString()),
StartDate = DateTime.Parse(dr["StartDate"].ToString())
};;
}
return(StatusCode(201, output));
}
}
public IActionResult PromotionStudents(PromoteStudents request)
{
return(Ok(_service.PromoteStudents(request.Semester, request.Studies)));
}
