public ActionResult getSpecificUserDetails(string reviewer, string review, int rating, string email, Professional_Details_ViewModel vm) { string insertQuery = "INSERT INTO dbo.UserReviews(reviewerName,reviewText,rating,professionalEmail) VALUES(@reviewer,@review,@rating,@email)"; using (SqlConnection connection = new SqlConnection("Server=tcp:se1-ratemyprofessional.database.windows.net,1433;Initial Catalog=Identity;Persist Security Info=False;User ID=rmpadmin;Password=TeamOne1;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;")) { SqlParameter param1 = new SqlParameter(); SqlParameter param2 = new SqlParameter(); SqlParameter param3 = new SqlParameter(); SqlParameter param4 = new SqlParameter(); param1.ParameterName = "@reviewer"; param2.ParameterName = "@review"; param3.ParameterName = "@rating"; param4.ParameterName = "@email"; param1.Value = reviewer; param2.Value = review; param3.Value = rating; param4.Value = email; connection.Open(); SqlCommand command = new SqlCommand(insertQuery, connection); command.Parameters.Add(param1); command.Parameters.Add(param2); command.Parameters.Add(param3); command.Parameters.Add(param4); command.ExecuteNonQuery(); connection.Close(); } //vm.Reviews.Add(new Review { reviewerName = reviewer, reviewText = review, rating = rating }); //return View(vm); return(RedirectToAction("Professional_Roles")); }
public ActionResult getSpecificUserDetails(string id, string uId, Professional_Details_ViewModel vmOld) { List <Review> reviews = new List <Review>(); string queryString = "SELECT reviewId, reviewText, reviewerName, rating, professionalEmail FROM dbo.UserReviews WHERE professionalEmail=@id"; using (SqlConnection connection = new SqlConnection("Server=tcp:se1-ratemyprofessional.database.windows.net,1433;Initial Catalog=Identity;Persist Security Info=False;User ID=rmpadmin;Password=TeamOne1;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;")) { SqlParameter param = new SqlParameter(); param.ParameterName = "@id"; param.Value = uId; SqlCommand command = new SqlCommand(queryString, connection); command.Parameters.Add(param); connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { reviews.Add(new Review() { ID = (int)reader[0], reviewText = reader[1].ToString(), reviewerName = reader[2].ToString(), rating = (int)reader[3], professionalId = reader[4].ToString() }); } } connection.Close(); } string oneStarQuery = "SELECT COUNT(*) FROM [dbo].[UserReviews] WHERE rating = 1 AND professionalEmail=@id"; int oneStarQueries; using (SqlConnection connection = new SqlConnection("Server=tcp:se1-ratemyprofessional.database.windows.net,1433;Initial Catalog=Identity;Persist Security Info=False;User ID=rmpadmin;Password=TeamOne1;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;")) { SqlParameter param = new SqlParameter(); param.ParameterName = "@id"; param.Value = uId; using (SqlCommand command = new SqlCommand(oneStarQuery, connection)) { command.Parameters.Add(param); connection.Open(); oneStarQueries = (int)command.ExecuteScalar(); } connection.Close(); } string twoStarQuery = "SELECT COUNT(*) FROM [dbo].[UserReviews] WHERE rating = 2 AND professionalEmail=@id"; int twoStarQueries; using (SqlConnection connection = new SqlConnection("Server=tcp:se1-ratemyprofessional.database.windows.net,1433;Initial Catalog=Identity;Persist Security Info=False;User ID=rmpadmin;Password=TeamOne1;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;")) { SqlParameter param = new SqlParameter(); param.ParameterName = "@id"; param.Value = uId; using (SqlCommand command = new SqlCommand(twoStarQuery, connection)) { command.Parameters.Add(param); connection.Open(); twoStarQueries = (int)command.ExecuteScalar(); } connection.Close(); } string threeStarQuery = "SELECT COUNT(*) FROM [dbo].[UserReviews] WHERE rating = 3 AND professionalEmail=@id"; int threeStarQueries; using (SqlConnection connection = new SqlConnection("Server=tcp:se1-ratemyprofessional.database.windows.net,1433;Initial Catalog=Identity;Persist Security Info=False;User ID=rmpadmin;Password=TeamOne1;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;")) { SqlParameter param = new SqlParameter(); param.ParameterName = "@id"; param.Value = uId; using (SqlCommand command = new SqlCommand(threeStarQuery, connection)) { command.Parameters.Add(param); connection.Open(); threeStarQueries = (int)command.ExecuteScalar(); } connection.Close(); } string fourStarQuery = "SELECT COUNT(*) FROM [dbo].[UserReviews] WHERE rating = 4 AND professionalEmail=@id"; int fourStarQueries; using (SqlConnection connection = new SqlConnection("Server=tcp:se1-ratemyprofessional.database.windows.net,1433;Initial Catalog=Identity;Persist Security Info=False;User ID=rmpadmin;Password=TeamOne1;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;")) { SqlParameter param = new SqlParameter(); param.ParameterName = "@id"; param.Value = uId; using (SqlCommand command = new SqlCommand(fourStarQuery, connection)) { command.Parameters.Add(param); connection.Open(); fourStarQueries = (int)command.ExecuteScalar(); } connection.Close(); } string fiveStarQuery = "SELECT COUNT(*) FROM [dbo].[UserReviews] WHERE rating = 5 AND professionalEmail=@id"; int fiveStarQueries; using (SqlConnection connection = new SqlConnection("Server=tcp:se1-ratemyprofessional.database.windows.net,1433;Initial Catalog=Identity;Persist Security Info=False;User ID=rmpadmin;Password=TeamOne1;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;")) { SqlParameter param = new SqlParameter(); param.ParameterName = "@id"; param.Value = uId; using (SqlCommand command = new SqlCommand(fiveStarQuery, connection)) { command.Parameters.Add(param); connection.Open(); fiveStarQueries = (int)command.ExecuteScalar(); } connection.Close(); } string totalRating = "SELECT COUNT(*) FROM [dbo].[UserReviews] WHERE professionalEmail=@id"; int totalQueries; using (SqlConnection connection = new SqlConnection("Server=tcp:se1-ratemyprofessional.database.windows.net,1433;Initial Catalog=Identity;Persist Security Info=False;User ID=rmpadmin;Password=TeamOne1;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;")) { SqlParameter param = new SqlParameter(); param.ParameterName = "@id"; param.Value = uId; using (SqlCommand command = new SqlCommand(totalRating, connection)) { command.Parameters.Add(param); connection.Open(); totalQueries = (int)command.ExecuteScalar(); } connection.Close(); } decimal averageRating = 0; if (totalQueries > 0) { averageRating = (decimal)(5 * fiveStarQueries + 4 * fourStarQueries + 3 * threeStarQueries + 2 * twoStarQueries + 1 * oneStarQueries) / totalQueries; } var pro = _dbcontext.Users.Where(p => p.Email == id).FirstOrDefault(); if (pro == null) { return(new NotFoundResult()); } Professional_Details_ViewModel vm = new Professional_Details_ViewModel() { professionalId = pro.Id, Email = pro.Email, FirstName = pro.FirstName, LastName = pro.LastName, Address = pro.Address, City = pro.City, State = pro.State, Profession = pro.Profession, Rate = pro.Rate, Company = pro.Company, Reviews = reviews, Rating = averageRating }; ViewBag.vm = vm; return(View(vm)); }