//Retrieve Full Movie Info and DVDs based on MovieID from the SQL DB public List<DVD> RetrieveFullDVDInfoFromDB(int movieId) { List<DVD> listOfDVDInfo = new List<DVD>(); using (var cn = new SqlConnection(Settings.ConnectionString)) { var cmd = new SqlCommand(); Models.Movie movieInfo = new Models.Movie(); //Get Movie Info including Director & Studio By MovieId cmd.CommandText = "GetMovieInfoByMovieId"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = cn; cmd.Parameters.AddWithValue("@MovieID", movieId); cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { movieInfo.MovieId = int.Parse(dr["MovieID"].ToString()); movieInfo.MovieTitle = dr["MovieTitle"].ToString(); movieInfo.MovieTMDBNum = int.Parse(dr["MovieTMDBNum"].ToString()); movieInfo.ReleaseDate = DateTime.Parse(dr["ReleaseDate"].ToString()); movieInfo.MpaaRating = dr["Rating"].ToString(); movieInfo.Duration = int.Parse(dr["DurationInMin"].ToString()); if (dr["Synopsis"] != DBNull.Value) { movieInfo.Synopsis = dr["Synopsis"].ToString(); } if (dr["PosterUrl"] != DBNull.Value) { movieInfo.PosterUrl = dr["PosterUrl"].ToString(); } if (dr["YouTubeTrailer"] != DBNull.Value) { movieInfo.YouTubeTrailer = dr["YouTubeTrailer"].ToString(); } movieInfo.Director.DirectorId = int.Parse(dr["DirectorID"].ToString()); movieInfo.Director.DirectorName = dr["DirectorName"].ToString(); if (dr["DirectorTMDBNum"] != DBNull.Value) { movieInfo.Director.DirectorTMDBNum = int.Parse(dr["DirectorTMDBNum"].ToString()); } movieInfo.Studio.StudioId = int.Parse(dr["StudioID"].ToString()); movieInfo.Studio.StudioName = dr["StudioName"].ToString(); if (dr["StudioTMDBNum"] != DBNull.Value) { movieInfo.Studio.StudioTMDBNum = int.Parse(dr["StudioTMDBNum"].ToString()); } } } cn.Close(); //Get Actors List for Movie by MovieID cmd.CommandText = "GetActorsListByMovieID"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = cn; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@MovieID", movieId); cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { if (dr["ActorID"] != DBNull.Value) { Actor newActor = new Actor(); newActor.ActorId = int.Parse(dr["ActorID"].ToString()); if (dr["ActorName"] != DBNull.Value) { newActor.ActorName = dr["ActorName"].ToString(); } if (dr["ActorTMDBNum"] != DBNull.Value) { newActor.ActorTMDBNum = int.Parse(dr["ActorTMDBNum"].ToString()); } if (dr["CharacterName"] != DBNull.Value) { newActor.CharacterName = dr["CharacterName"].ToString(); } movieInfo.MovieActors.Add(newActor); } } } cn.Close(); //Get Genres List for Movie by MovieID cmd.CommandText = "GetGenresListByMovieID"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = cn; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@MovieID", movieId); cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { if (dr["GenreID"] != DBNull.Value) { Models.Genre newGenre = new Models.Genre(); newGenre.GenreId = int.Parse(dr["GenreID"].ToString()); if (dr["GenreName"] != DBNull.Value) { newGenre.GenreName = dr["GenreName"].ToString(); } movieInfo.Genres.Add(newGenre); } } } cn.Close(); //Get Movie Aliases List for Movie by MovieID cmd.CommandText = "GetMovieAliasesByMovieID"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = cn; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@MovieID", movieId); cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { if (dr["MovieAliasID"] != DBNull.Value) { MovieAlias newMovieAlias = new MovieAlias(); newMovieAlias.MovieAliasId = int.Parse(dr["MovieAliasID"].ToString()); if (dr["MovieAlias"] != DBNull.Value) { newMovieAlias.MovieAliasTitle = dr["MovieAlias"].ToString(); } movieInfo.MovieAliases.Add(newMovieAlias); } } } cn.Close(); //Get User Ratings for Movie by MovieID cmd.CommandText = "GetUserRatingsByMovieID"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = cn; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@MovieID", movieId); cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { if (dr["UserRatingID"] != DBNull.Value) { UserRating newUserRating = new UserRating(); newUserRating.UserRatingId = int.Parse(dr["UserRatingID"].ToString()); if (dr["MovieID"] != DBNull.Value) { newUserRating.MovieId = int.Parse(dr["MovieID"].ToString()); } if (dr["BorrowerID"] != DBNull.Value) { newUserRating.BorrowerId = int.Parse(dr["BorrowerID"].ToString()); } if (dr["Rating"] != DBNull.Value) { newUserRating.Rating = int.Parse(dr["Rating"].ToString()); } if (dr["OwnerRating"] != DBNull.Value) { newUserRating.Owner = bool.Parse(dr["OwnerRating"].ToString()); } if (dr["FirstName"] != DBNull.Value) { newUserRating.BorrowerName = (dr["FirstName"].ToString() + " " + dr["LastName"].ToString()); } movieInfo.UserRatings.Add(newUserRating); } } } cn.Close(); //Get User Notes for Movie by MovieID cmd.CommandText = "GetUserNotesByMovieID"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = cn; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@MovieID", movieId); cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { if (dr["UserNoteID"] != DBNull.Value) { UserNote newUserNote = new UserNote(); newUserNote.UserNoteId = int.Parse(dr["UserNoteID"].ToString()); if (dr["MovieID"] != DBNull.Value) { newUserNote.MovieId = int.Parse(dr["MovieID"].ToString()); } if (dr["MovieTitle"] != DBNull.Value) { newUserNote.MovieTitle = dr["MovieTitle"].ToString(); } if (dr["BorrowerID"] != DBNull.Value) { newUserNote.BorrowerId = int.Parse(dr["BorrowerID"].ToString()); } if (dr["Note"] != DBNull.Value) { newUserNote.Note = dr["Note"].ToString(); } if (dr["OwnerNote"] != DBNull.Value) { newUserNote.Owner = bool.Parse(dr["OwnerNote"].ToString()); } if (dr["FirstName"] != DBNull.Value) { newUserNote.BorrowerName = (dr["FirstName"].ToString() + " " + dr["LastName"].ToString()); } movieInfo.UserNotes.Add(newUserNote); } } } cn.Close(); //Get DVDID and DVDType Info by MovieID cmd.CommandText = "GetDVDInfoByMovieID"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = cn; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@MovieID", movieId); cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { if (dr["DVDID"] != DBNull.Value) { DVD newDVD = new DVD(); newDVD.DVDId = int.Parse(dr["DVDID"].ToString()); if (dr["DVDType"] != DBNull.Value) { newDVD.DVDType = dr["DVDType"].ToString(); } newDVD.Movie = movieInfo; listOfDVDInfo.Add(newDVD); } } } cn.Close(); //Get Borrower Statuses for a DVD by DVDID foreach (var d in listOfDVDInfo) { cmd.CommandText = "GetBorrowerStatusesByDVDID"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = cn; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@DVDID", d.DVDId); cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { if (dr["BorrowerStatusID"] != DBNull.Value) { Status newStatus = new Status(); newStatus.StatusId = int.Parse(dr["BorrowerStatusID"].ToString()); if (dr["BorrowerID"] != DBNull.Value) { newStatus.Borrower.BorrowerId = int.Parse(dr["BorrowerID"].ToString()); } if (dr["DVDID"] != DBNull.Value) { newStatus.DVDId = int.Parse(dr["DVDID"].ToString()); } if (dr["DateBorrowed"] != DBNull.Value) { newStatus.DateBorrowed = DateTime.Parse(dr["DateBorrowed"].ToString()); } if (dr["DateReturned"] != DBNull.Value) { newStatus.DateReturned = DateTime.Parse(dr["DateReturned"].ToString()); } if (dr["IsOwner"] != DBNull.Value) { newStatus.Borrower.IsOwner = bool.Parse(dr["IsOwner"].ToString()); } if (dr["FirstName"] != DBNull.Value) { newStatus.Borrower.FirstName = dr["FirstName"].ToString(); } if (dr["LastName"] != DBNull.Value) { newStatus.Borrower.LastName = dr["LastName"].ToString(); } if (dr["Email"] != DBNull.Value) { newStatus.Borrower.Email = dr["Email"].ToString(); } if (dr["StreetAddress"] != DBNull.Value) { newStatus.Borrower.Address = dr["StreetAddress"].ToString(); } if (dr["City"] != DBNull.Value) { newStatus.Borrower.City = dr["City"].ToString(); } if (dr["State"] != DBNull.Value) { newStatus.Borrower.State = dr["State"].ToString(); } if (dr["Zipcode"] != DBNull.Value) { newStatus.Borrower.Zipcode = dr["Zipcode"].ToString(); } if (dr["Phone"] != DBNull.Value) { newStatus.Borrower.Phone = dr["Phone"].ToString(); } d.Statuses.Add(newStatus); } } } cn.Close(); } } return listOfDVDInfo; }
//Add a new User Rating to the DB public bool AddUserRatingToDb(UserRating newRating) { using (SqlConnection cn = new SqlConnection(Settings.ConnectionString)) { SqlCommand cmd = new SqlCommand(); cmd.CommandText = "select count(UserRatings.UserRatingID) from UserRatings" + " WHERE BorrowerID = @BorrowerID AND MovieID = @MovieID"; cmd.Parameters.AddWithValue("@BorrowerID", newRating.BorrowerId); cmd.Parameters.AddWithValue("@MovieID", newRating.MovieId); cmd.Connection = cn; cn.Open(); int userRatingCount = int.Parse(cmd.ExecuteScalar().ToString()); cn.Close(); if (userRatingCount == 0) { //Grab rest of Borrower Info to update var p1 = new DynamicParameters(); p1.Add("@BorrowerID", newRating.BorrowerId); newRating.Owner = cn.Query<bool>("select IsOwner from Borrowers where BorrowerID = @BorrowerID", p1).FirstOrDefault(); //Run insert new UserRating var p2 = new DynamicParameters(); p2.Add("BorrowerID", newRating.BorrowerId); p2.Add("MovieID", newRating.MovieId); p2.Add("Rating", newRating.Rating); p2.Add("OwnerRating", newRating.Owner); p2.Add("UserRatingID", DbType.Int32, direction: ParameterDirection.Output); cn.Execute("AddNewUserRatingToUserRatings", p2, commandType: CommandType.StoredProcedure); newRating.UserRatingId = p2.Get<int>("UserRatingID"); return true; } else { //Run update UserRating var p1 = new DynamicParameters(); p1.Add("@BorrowerID", newRating.BorrowerId); p1.Add("@MovieID", newRating.MovieId); p1.Add("Rating", newRating.Rating); cn.Execute("UpdateUserRating", p1, commandType: CommandType.StoredProcedure); return false; } } }
public void TestAddUserRatingToDb() { UserRating newRating = new UserRating() { BorrowerId = 9, MovieId = 1, Rating = 5, Owner = false, }; repo.AddUserRatingToDb(newRating); int ratingsCount = 0; using (SqlConnection cn = new SqlConnection(TestConnectionString)) { SqlCommand cmd = new SqlCommand(); cmd.CommandText = "select count(UserRatings.MovieID) from UserRatings where MovieID=1"; cmd.Connection = cn; cn.Open(); ratingsCount = int.Parse(cmd.ExecuteScalar().ToString()); } Assert.AreEqual(ratingsCount, 2); }
//public Borrower Borrower { get; set; } public Response() { UserRating = new UserRating(); }
public ActionResult SubmitRating() { if (Request["hiddenRating"] == "") { MessageBox.Show("You must select a rating between 1-5 stars to submit it!!"); return RedirectToAction("SelectMovie", "Home", new { id = int.Parse(Request["MovieId"]) }); } var newUserRating = new UserRating(); newUserRating.BorrowerId = int.Parse(Request["BorrowerId"]); newUserRating.MovieId = int.Parse(Request["MovieId"]); newUserRating.Rating = int.Parse(Request["hiddenRating"]); //Send to DB var response = _oops.AddUserRating(newUserRating); MessageBox.Show(response.Message); return RedirectToAction("SelectMovie", "Home", new {id = newUserRating.MovieId}); }