public ActionResult GetMovieInformation(string title) { List <FullMovie> moviesForTitle = new List <FullMovie>(); NpgsqlConnection conn = OpenConnection(); string sql_movie = string.Format("SELECT idmovies as Id, title, year FROM movies where title like \'% {0} %\' ", title); var movieInfo = conn.Query <MovieInfo>(sql_movie).ToList(); foreach (MovieInfo movie in movieInfo) { string sql_genres = string.Format("SELECT g.idgenres as Id, g.genre as Genre from public.genres g where g.idgenres in (select mg.idgenres from public.movies_genres mg where mg.idmovies = {0})", movie.Id); var genreInfo = conn.Query <GenreInfo>(sql_genres).ToList(); string sql_keywords = string.Format(@"select k.idkeywords as Id, k.keyword from public.keywords k where k.idkeywords in (select mk.idkeywords from public.movies_keywords mk where mk.idmovies = {0})", movie.Id); var keywordsInfo = conn.Query <KeywordsInfo>(sql_keywords).ToList(); string sql_actors = string.Format(@"select a.idactors as Id, a.fname as FirstName, a.lname as LastName, a.gender, aka.name as CharacterName from public.actors a, public.aka_names aka where a.idactors in (select ai.idactors from public.acted_in ai where ai.idmovies = {0}) and a.idactors = aka.idactors", movie.Id); var actorInfo = conn.Query <ActorForMovie>(sql_actors).ToList(); FullMovie fullMovieInformation = new FullMovie() { MovieInformation = movie, Genres = genreInfo, Keywords = keywordsInfo, Actors = actorInfo }; moviesForTitle.Add(fullMovieInformation); } JsonResult result = new JsonResult(moviesForTitle); conn.Close(); return(result); }
public ActionResult GetMovieInformation(long id) { NpgsqlConnection conn = OpenConnection(); string sql_movie = string.Format("SELECT idmovies as Id, title, year FROM movies where idmovies = {0}", id); var movieInfo = conn.Query <MovieInfo>(sql_movie).ToList().FirstOrDefault(); string sql_genres = string.Format("SELECT g.idgenres as Id, g.genre as Genre from public.genres g where g.idgenres in (select mg.idgenres from public.movies_genres mg where mg.idmovies = {0})", id); var genreInfo = conn.Query <GenreInfo>(sql_genres).ToList(); string sql_keywords = string.Format(@"select k.idkeywords as Id, k.keyword from public.keywords k where k.idkeywords in (select mk.idkeywords from public.movies_keywords mk where mk.idmovies = {0})", id); var keywordsInfo = conn.Query <KeywordsInfo>(sql_keywords).ToList(); string sql_actors = string.Format(@"select a.idactors as Id, a.fname as FirstName, a.lname as LastName, a.gender, aka.name as CharacterName from public.actors a, public.aka_names aka where a.idactors in (select ai.idactors from public.acted_in ai where ai.idmovies = {0}) and a.idactors = aka.idactors", id); var actorInfo = conn.Query <ActorForMovie>(sql_actors); FullMovie fullMovieInformation = new FullMovie() { MovieInformation = movieInfo, Genres = genreInfo, Keywords = keywordsInfo, Actors = actorInfo }; JsonResult result = new JsonResult(fullMovieInformation); conn.Close(); return(result); }
public ActionResult GetMovieInformation(long id) { // Connect to the demo keyspace on our cluster running at 127.0.0.1 Cluster cluster = Cluster.Builder().AddContactPoint("127.0.0.1").Build(); ISession session = cluster.Connect("imdb"); string query = string.Format("SELECT id,title,year,location,language,actors_name FROM tbl_movies_by_id WHERE id={0}", id); Row result = session.Execute(query).First(); IEnumerable <ActorForMovie> actorsForMovie = null; IEnumerable <string> r = result["actors_name"] as IEnumerable <string>; if (actorsForMovie != null && actorsForMovie.Count() > 0) { actorsForMovie = new List <ActorForMovie>(); actorsForMovie = r.Select(a => new ActorForMovie { FirstName = a }); } FullMovie movie = new FullMovie { MovieInformation = new MovieInfo { Id = result["id"] == null ? 0 : Int32.Parse(result["id"].ToString()), Title = result["title"] == null ? null : result["title"].ToString(), Year = result["year"] == null ? 0 : Int32.Parse(result["year"].ToString()) }, Actors = actorsForMovie }; JsonResult json_result = new JsonResult(movie); return(json_result); }
public ActionResult GetMovieInformation(string[] title) { // Connect to the demo keyspace on our cluster running at 127.0.0.1 Cluster cluster = Cluster.Builder().AddContactPoint("127.0.0.1").Build(); ISession session = cluster.Connect("imdb"); string longestWord = title.OrderByDescending(s => s.Length).First(); string query = string.Format("SELECT id,title,year,location,language,actors_name FROM tbl_movies_by_word WHERE word='{0}'", longestWord); List <Row> result = session.Execute(query).ToList(); List <FullMovie> allMovies = new List <FullMovie>(); foreach (Row row in result) { int count = 0; for (int i = 0; i < title.Length; i++) { if (row["title"] != null && row["title"].ToString().ToLower().Contains(title[i].ToLower())) { count++; } } if (count == title.Length) { IEnumerable <ActorForMovie> actorsForMovie = null; IEnumerable <string> r = row["actors_name"] as IEnumerable <string>; if (actorsForMovie != null && actorsForMovie.Count() > 0) { actorsForMovie = new List <ActorForMovie>(); actorsForMovie = r.Select(a => new ActorForMovie { FirstName = a }); } FullMovie movie = new FullMovie { MovieInformation = new MovieInfo { Id = row["id"] == null ? 0 : Int32.Parse(row["id"].ToString()), Title = row["title"] == null ? null : row["title"].ToString(), Year = row["year"] == null ? 0 : Int32.Parse(row["year"].ToString()) }, Actors = actorsForMovie }; allMovies.Add(movie); } } JsonResult json_result = new JsonResult(allMovies); return(json_result); }
public async Task <FullMovie> AddToDBFullMovieDeails(FullMovie fullMovie) { using (var trans = _context.Database.BeginTransaction()) { try { //AddMovie await _context.Movies.AddAsync(fullMovie.movie); // AddMovieGenres var movieGenres = new List <MovieGenres>(); foreach (var item in fullMovie.genres_collection) { movieGenres.Add(new MovieGenres { movie_id = fullMovie.movie.id, movie_genre_id = _context.Genres.FirstOrDefault(g => g.genre_id == item.id && g.material_type_id == 1).id, }); } await _context.MovieGenres.AddRangeAsync(movieGenres); //AddKeyWords var KeyWords = new List <KeyWords>(); foreach (var item in fullMovie.keyWords_collection) { KeyWords.Add(new KeyWords { keyword_id = item.id, material_type_id = 1, name = item.name }); } await _context.KeyWords.AddRangeAsync(KeyWords); await _context.SaveChangesAsync(); //AddMovieKeyWords var movieKeyWords = new List <MovieKeyWords>(); foreach (var item in fullMovie.keyWords_collection) { var movieKeyWord = await _context.KeyWords.FirstOrDefaultAsync(k => k.keyword_id == item.id && k.material_type_id == 1); movieKeyWords.Add(new MovieKeyWords { movie_id = fullMovie.movie.id, movie_ = fullMovie.movie, movie_keyword_ = movieKeyWord, movie_keyword_id = movieKeyWord.id }); } await _context.MovieKeyWords.AddRangeAsync(movieKeyWords); //AddMovieCredits var movieCredits = new List <MovieCredits>(); foreach (var item in fullMovie.casts_collection) { movieCredits.Add(new MovieCredits { movie_id = fullMovie.movie.id, credit_id = item.credit_id, is_cast = true, movie_ = fullMovie.movie }); } foreach (var item in fullMovie.crews_collection) { movieCredits.Add(new MovieCredits { movie_id = fullMovie.movie.id, credit_id = item.credit_id, is_cast = false, movie_ = fullMovie.movie }); } await _context.MovieCredits.AddRangeAsync(movieCredits); await _context.MovieCasts.AddRangeAsync(fullMovie.casts_collection); await _context.MovieCrews.AddRangeAsync(fullMovie.crews_collection); await _context.SaveChangesAsync(); trans.Commit(); return(fullMovie); } catch (Exception ex) { trans.Rollback(); throw new Exception(ex.Message); } finally { trans.Dispose(); } } }