//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;
}
//Retrieve TMDB info with a TMDBNum
public Models.Movie ReturnMovieInfoFromTMDB(int tmdbNum)
{
TMDbClient client = new TMDbClient("1fee8f2397ff73412985de2bb825f020");
Movie movie = client.GetMovie(tmdbNum,
MovieMethods.AlternativeTitles | MovieMethods.Credits | MovieMethods.Images | MovieMethods.Releases |
MovieMethods.Videos);
Models.Movie movieInfo = new Models.Movie();
movieInfo.MovieTitle = movie.Title;
movieInfo.MovieTMDBNum = movie.Id;
if (movie.Releases.Countries.FirstOrDefault(m => m.Iso_3166_1 == "US") != null)
{
movieInfo.MpaaRating = movie.Releases.Countries.FirstOrDefault(m => m.Iso_3166_1 == "US").Certification;
}
else
{
movieInfo.MpaaRating = "NR";
}
if (movie.ReleaseDate != null)
{
movieInfo.ReleaseDate = movie.ReleaseDate.Value.Date;
}
if (movie.Overview != null)
{
movieInfo.Synopsis = movie.Overview;
}
if (movie.Runtime != null)
{
movieInfo.Duration = movie.Runtime.Value;
}
if (movie.PosterPath != null)
{
movieInfo.PosterUrl = "http://image.tmdb.org/t/p/w396" + movie.PosterPath;
}
else
{
movieInfo.PosterUrl =
"http://assets.tmdb.org/assets/7f29bd8b3370c71dd379b0e8b570887c/images/no-poster-w185-v2.png";
}
if (movie.Videos.Results.Where(v => v.Type == "Trailer").FirstOrDefault() != null)
{
movieInfo.YouTubeTrailer = "http://www.youtube.com/embed/" +
movie.Videos.Results.Where(v => v.Type == "Trailer").FirstOrDefault().Key;
}
if (movie.Genres.Count != 0)
{
foreach (var g in movie.Genres)
{
var newGenre = new Models.Genre();
newGenre.GenreName = g.Name;
movieInfo.Genres.Add(newGenre);
}
}
if (movie.Credits.Crew.FirstOrDefault(d => d.Job == "Director") != null)
{
movieInfo.Director.DirectorName = movie.Credits.Crew.FirstOrDefault(d => d.Job == "Director").Name;
movieInfo.Director.DirectorTMDBNum = movie.Credits.Crew.FirstOrDefault(d => d.Job == "Director").Id;
}
if (movie.ProductionCompanies.Count != 0)
{
movieInfo.Studio.StudioName = movie.ProductionCompanies[0].Name;
movieInfo.Studio.StudioTMDBNum = movie.ProductionCompanies[0].Id;
}
if (movie.AlternativeTitles.Titles.Count != 0)
{
foreach (var t in movie.AlternativeTitles.Titles)
{
if (t.Iso_3166_1 == "US")
{
var newMovieAlias = new MovieAlias();
newMovieAlias.MovieAliasTitle = t.Title;
movieInfo.MovieAliases.Add(newMovieAlias);
}
}
}
if (movie.Credits.Cast.Count < 10 && movie.Credits.Cast.Count > 0)
{
foreach (var a in movie.Credits.Cast)
{
Actor newActor = new Actor();
newActor.ActorName = a.Name;
newActor.ActorTMDBNum = a.Id;
newActor.CharacterName = a.Character;
movieInfo.MovieActors.Add(newActor);
}
}
else if (movie.Credits.Cast.Count == 0)
{
return movieInfo;
}
else
{
for (int i = 0; i < 10; i++)
{
Actor newActor = new Actor();
newActor.ActorName = movie.Credits.Cast[i].Name;
newActor.ActorTMDBNum = movie.Credits.Cast[i].Id;
newActor.CharacterName = movie.Credits.Cast[i].Character;
movieInfo.MovieActors.Add(newActor);
}
}
return movieInfo;
}