public static SqlManipul GetInstance() { if (instance == null) { instance = new SqlManipul(); } return(instance); }
/// <summary> /// Получение списка ID фильмов удовлетворяющих условию фильтрации /// </summary> /// <returns></returns> public string GetFilterIdFilms() { SqlCommand command = new SqlCommand(); //SELECT ID, Poster, Название, [Год выхода], Язык, [Длительность(мин)], Жанр, Страна, Режиссёр, [Рейтинг / 10] string commandText = "select ID FROM dbo.vMovies "; //если хоть один фильтр выбран if (GenreFilterId != -1 || CountryFilterId != -1 || YearFilterVal != -1 || DirectorFilterId != -1 || LanguageFilterId != -1 || RatedFilterId != -1) { string commandFilter = "where dbo.vMovies.ID in ( select dbo.Movie.MovieId from dbo.Movie "; //фильтрация по жанру if (GenreFilterId != -1) { SqlParameter pGenre = new SqlParameter("@genre", System.Data.SqlDbType.Int) { Value = GenreFilterId }; command.Parameters.Add(pGenre); commandFilter += @"inner join dbo.MovieOnGenre on dbo.Movie.MovieId=dbo.MovieOnGenre.MovieId and dbo.MovieOnGenre.GenreId=@genre "; } //фильтрация по стране if (CountryFilterId != -1) { SqlParameter pCountry = new SqlParameter("@country", System.Data.SqlDbType.Int) { Value = CountryFilterId }; command.Parameters.Add(pCountry); commandFilter += @"inner join dbo.MovieOnCountry on dbo.Movie.MovieId=dbo.MovieOnCountry.MovieId and dbo.MovieOnCountry.CountryId=@country "; } //фильтрация по языку if (LanguageFilterId != -1) { SqlParameter pLanguage = new SqlParameter("@language", System.Data.SqlDbType.Int) { Value = LanguageFilterId }; command.Parameters.Add(pLanguage); commandFilter += @"inner join dbo.LanguageInMovie on dbo.Movie.MovieId=dbo.LanguageInMovie.MovieId and dbo.LanguageInMovie.LanguageId=@language "; } //фильтрация по режессёру if (DirectorFilterId != -1) { SqlParameter pDirector = new SqlParameter("@director", System.Data.SqlDbType.Int) { Value = DirectorFilterId }; command.Parameters.Add(pDirector); commandFilter += @"inner join dbo.vDirectorsInMovieTable on dbo.vDirectorsInMovieTable.MovieId=dbo.Movie.MovieId and dbo.vDirectorsInMovieTable.PersonId=@director "; } //фильтры для where для рейтинга и года выхода if (YearFilterVal != -1 || RatedFilterId != -1) { commandFilter += "where "; bool flag = false; if (RatedFilterId != -1) { SqlParameter pRated = new SqlParameter("@rated", System.Data.SqlDbType.Int) { Value = RatedFilterId }; command.Parameters.Add(pRated); commandFilter += @"dbo.Movie.RatedId=@rated"; flag = true; } if (YearFilterVal != -1) { if (flag) { commandFilter += " and "; } SqlParameter pYear = new SqlParameter("@year", System.Data.SqlDbType.Int) { Value = YearFilterVal }; command.Parameters.Add(pYear); commandFilter += @"year(dbo.Movie.Released)=@year"; } } commandFilter += " )"; commandText += commandFilter; } command.CommandText = commandText; string result = ""; using (SqlConnection connection = new SqlConnection(SqlManipul.GetInstance().ConnectionString)) { connection.Open(); command.Connection = connection; SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { result += reader.GetInt32(0).ToString() + ","; } } } return(result.Trim(',')); }