//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});
}
