public PromotionResponse PromoteStudents(PromotionRequest request)
{
PromotionResponse response;
using (var connection = new SqlConnection(SqlServerDb.connectionString))
{
using (var command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = "PromoteStudents";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@studies", request.Studies);
command.Parameters.AddWithValue("@semester", request.Semester);
connection.Open();
command.ExecuteNonQuery();
response = new PromotionResponse
{
Studies = request.Studies,
Semester = request.Semester + 1
};
}
}
return(response);
}
public IActionResult promoteStudents(PromotionRequest req)
{
PromotionResponse rep = new PromotionResponse()
{
};
using (var client = new SqlConnection("Data Source=db-mssql;Initial Catalog=s18533;Integrated Security=True"))
{
client.Open();
using (var com = new SqlCommand())
using (var transaction = client.BeginTransaction())
{
try
{
com.Connection = client;
com.CommandText = "SELECT * FROM Studies WHERE Name = @studyName;";
com.Parameters.AddWithValue("studyName", req.studies);
com.Transaction = transaction;
var dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
transaction.Rollback();
return(new BadRequestObjectResult("No studies found with given Name"));
}
int idStudy = (int)dr["IdStudy"];
dr.Close();
int sem = req.semester;
rep.idStudy = idStudy;
com.CommandText = "SELECT * FROM Enrollment WHERE IdStudy = @currId AND Semester = @sem;";
com.Parameters.AddWithValue("currId", idStudy);
com.Parameters.AddWithValue("sem", sem);
dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
transaction.Rollback();
return(new NotFoundObjectResult("Enrollment with given IdStudy and Semester was not found"));
}
dr.Close();
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "promoteStudents";
com.Parameters.Clear();
com.Parameters.AddWithValue("studyName", req.studies);
rep.semester = req.semester + 1;
com.Parameters.AddWithValue("semester", req.semester);
com.ExecuteNonQuery();
//if (!dr.Read())
//{
// dr.Close();
// transaction.Rollback();
// return BadRequest("Something went wrong with the stored procedure");
//}
dr.Close();
com.CommandType = CommandType.Text;
com.CommandText = "SELECT IdEnrollment AS newId, StartDate FROM Enrollment WHERE IdStudy = @currId AND Semester = @sem";
com.Parameters.AddWithValue("currId", idStudy);
com.Parameters.AddWithValue("sem", rep.semester);
dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
transaction.Rollback();
return(new BadRequestObjectResult("No enrollment with higher semester! Something must have went wrong!"));
}
rep.idEnrollment = (int)dr["newId"];
rep.startDate = (DateTime)dr["StartDate"];
dr.Close();
transaction.Commit();
var res = new ObjectResult(rep);
res.StatusCode = (int)HttpStatusCode.Created;
return(res);//StatusCode((int)HttpStatusCode.Created, rep);
}
catch (SqlException ex)
{
transaction.Rollback();
return(new BadRequestObjectResult(ex.Message));
}
}
}
}
public Response PromoteStudents(PromotionRequest request)
{
using (var con = new SqlConnection(ConString))
using (var com = new SqlCommand())
{
com.Connection = con;
con.Open();
var tran = con.BeginTransaction();
try
{
com.CommandText = "select IdStudy from Studies where Name = @name";
com.Parameters.AddWithValue("name", request.Studies);
com.Transaction = tran;
var dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
tran.Rollback();
return(new Response("NotFound", "Studia nie znalezione!"));
}
dr.Close();
com.CommandText = "select IdEnrollment from Enrollment inner join Studies on Enrollment.IdStudy=Studies.IdStudy" +
" where Studies.Name = @name and Enrollment.Semester = 1";
com.Transaction = tran;
dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
tran.Rollback();
return(new Response("NotFound", "Wpis na studia nie odnaleziony!"));
}
dr.Close();
com.Parameters.Clear();
com.CommandText = "dbo.PromoteStudents";
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add(new SqlParameter("@Studies", request.Studies));
com.Parameters.Add(new SqlParameter("@Semester", request.Semester));
com.ExecuteNonQuery();
}
catch (SqlException exc)
{
tran.Rollback();
Console.WriteLine(exc);
return(new Response("ERR", exc.ToString()));
}
finally
{
con.Close();
}
PromotionResponse promotion = new PromotionResponse();
promotion.Semester = request.Semester + 1;
promotion.Studies = request.Studies;
return(new Response("Ok", "PROMOTIONS: Proceed succesfly", promotion));
}
}
public IActionResult PromoteStudents(PromotionRequest pReq)
{
PromotionResponse promoResp = new PromotionResponse()
{
};
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;
try
{
com.Transaction = tran;
com.CommandText = "SELECT IdStudy FROM Studies WHERE Name = '" + pReq.Studies + "'";
var dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
tran.Rollback();
return(BadRequest("Studia nie istnieją"));
}
int idStudies = (int)dr["IdStudy"];
dr.Close();
com.CommandText = "SELECT * FROM Enrollment WHERE IdStudy = @idStudies AND Semester = @semester";
com.Parameters.AddWithValue("idStudies", idStudies);
com.Parameters.AddWithValue("semester", pReq.Semester);
dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
tran.Rollback();
return(NotFound("Nie znaleziono"));
}
int semester = pReq.Semester + 1;
dr.Close();
com.Parameters.Clear();
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "PromoteStudents";
com.Parameters.AddWithValue("Studies", idStudies);
com.Parameters.AddWithValue("Semester", semester);
com.ExecuteNonQuery();
com.CommandType = CommandType.Text;
com.CommandText = "SELECT IdEnrollment, StartDate FROM Enrollment WHERE IdStudy = @idStudies AND Semester = @semester";
com.Parameters.AddWithValue("idStudies", idStudies);
com.Parameters.AddWithValue("semester", semester);
dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
tran.Rollback();
return(BadRequest("Błąd"));
}
promoResp.IdEnrollment = (int)dr["IdEnrollment"];
promoResp.StartDate = (DateTime)dr["StartDate"];
promoResp.Semester = semester;
promoResp.IdStudy = idStudies;
dr.Close();
tran.Commit();
tran.Dispose();
return(StatusCode((int)HttpStatusCode.Created, promoResp));
}
catch (SqlException exc)
{
tran.Rollback();
return(BadRequest(exc.Message));
}
}
}
