private async Task <List <ReviewTask_RA> > GetData(SqlDataReader reader)
{
List <ReviewTask_RA> lst = new List <ReviewTask_RA>();
//1. try catch verhindert applicatie crash
try
{
while (await reader.ReadAsync())
{
ReviewTask_RA s = new ReviewTask_RA();
s.Id = (Guid)reader["Id"];
s.Name = !Convert.IsDBNull(reader["Name"]) ? (string)reader["Name"] : "";
s.Comment = !Convert.IsDBNull(reader["Comment"]) ? (string)reader["Comment"] : "";
s.Score = Convert.ToInt32(reader["Score"]);
s.MovieId = !Convert.IsDBNull(reader["MovieId"]) ? (string)reader["MovieId"] : "";
s.UserId = !Convert.IsDBNull(reader["UserId"]) ? (string)reader["UserId"] : "";
lst.Add(s);
}
}
catch (Exception exc)
{
Console.Write(exc.Message); //later loggen
}
finally
{
reader.Close(); //Niet vergeten. Beperkt aantal verbindingen (of kosten)
}
return(lst);
}
public async Task <ReviewTask_RA> Add(ReviewTask_RA review)
{
using (SqlConnection con = new SqlConnection(connectionString))
{ //TO DO: Gender, DateOfBirth, Password
string SQL = "Insert into Review(Id, Name, Comment, Score, MovieId, UserId)"; SQL += " Values(@Id, @Name, @Comment, @Score, @MovieId, @UserId)";
SqlCommand cmd = new SqlCommand(SQL, con);
cmd.Parameters.AddWithValue("@Id", Guid.NewGuid());
cmd.Parameters.AddWithValue("@Name", review.Name);
cmd.Parameters.AddWithValue("@Comment", review.Comment ?? ""); //EducationId is NULLABLE! int?
cmd.Parameters.AddWithValue("@Score", review.Score);
cmd.Parameters.AddWithValue("@MovieId", review.MovieId);
cmd.Parameters.AddWithValue("@UserId", review.UserId);
con.Open();
await cmd.ExecuteNonQueryAsync(); //enkel uitvoeren, geen reader
con.Close();
return(review);
}
}
public async Task UpdateReview(ReviewTask_RA review)
{
using (SqlConnection con = new SqlConnection(connectionString))
{ //TO DO: Gender, DateOfBirth, Password
try
{
string SQL = "UPDATE Review SET Comment = @Comment, Score = @Score WHERE UserId = @UserId AND Id = @Id";
SqlCommand cmd = new SqlCommand(SQL, con);
cmd.Parameters.AddWithValue("@Comment", review.Comment);
cmd.Parameters.AddWithValue("@Score", review.Score);
cmd.Parameters.AddWithValue("@UserId", review.UserId);
cmd.Parameters.AddWithValue("@Id", review.Id);
con.Open();
await cmd.ExecuteNonQueryAsync(); //enkel uitvoeren, geen reader
con.Close();
}
catch (Exception ex)
{
throw ex;
}
}
}
public async Task Update([FromBody] ReviewTask_RA review)
{
await this.dataProvider.UpdateReview(review);
}
public async Task Add([FromBody] ReviewTask_RA review)
{
await this.dataProvider.Add(review);
}
