public int TitlesInsert(Title title) { int titleId = 0; if (RecordExists(title)) { throw new Exception("The record with similar details already exists."); } using (SqlConnection conn = GetNewConnection()) { List <SqlParameter> paramColl = new List <SqlParameter>(); paramColl.Add(new SqlParameter() { ParameterName = "@Id", Direction = ParameterDirection.Output, Size = int.MaxValue }); paramColl.Add(new SqlParameter() { ParameterName = "@Title", Value = NullSafeGetter.IsDefault <string>(title.TitleName) }); paramColl.Add(new SqlParameter() { ParameterName = "@Season", Value = NullSafeGetter.IsDefault <string>(title.Season) }); paramColl.Add(new SqlParameter() { ParameterName = "@Episode", Value = NullSafeGetter.IsDefault <string>(title.Episode) }); paramColl.Add(new SqlParameter() { ParameterName = "@Year", Value = NullSafeGetter.IsDefault <string>(title.Year) }); paramColl.Add(new SqlParameter() { ParameterName = "@IndexStatus", Value = NullSafeGetter.IsDefault <string>(title.IndexStatus) }); paramColl.Add(new SqlParameter() { ParameterName = "@RowStatus", Value = NullSafeGetter.IsDefault <string>(title.RowStatus) }); paramColl.Add(new SqlParameter() { ParameterName = "@Tags", Value = NullSafeGetter.IsDefault <string>(title.Tags) }); using (SqlCommand command = BuildCommand(usp_Title_Insert, CommandType.StoredProcedure, paramColl)) { command.Connection = conn; command.Connection.Open(); command.ExecuteNonQuery(); titleId = Convert.ToInt32(command.Parameters["@Id"].Value); } } return(titleId); }
public void TitlesUpdate(Title title) { using (SqlConnection conn = GetNewConnection()) { List <SqlParameter> paramColl = new List <SqlParameter>(); paramColl.Add(new SqlParameter() { ParameterName = "@Id", Value = NullSafeGetter.IsDefault <long>(title.Id) }); paramColl.Add(new SqlParameter() { ParameterName = "@Title", Value = NullSafeGetter.IsDefault <string>(title.TitleName) }); paramColl.Add(new SqlParameter() { ParameterName = "@Season", Value = NullSafeGetter.IsDefault <string>(title.Season) }); paramColl.Add(new SqlParameter() { ParameterName = "@Episode", Value = NullSafeGetter.IsDefault <string>(title.Episode) }); paramColl.Add(new SqlParameter() { ParameterName = "@Year", Value = NullSafeGetter.IsDefault <string>(title.Year) }); paramColl.Add(new SqlParameter() { ParameterName = "@IndexStatus", Value = NullSafeGetter.IsDefault <string>(title.IndexStatus) }); paramColl.Add(new SqlParameter() { ParameterName = "@RowStatus", Value = NullSafeGetter.IsDefault <string>(title.RowStatus) }); paramColl.Add(new SqlParameter() { ParameterName = "@Tags", Value = NullSafeGetter.IsDefault <string>(title.Tags) }); using (SqlCommand command = BuildCommand(usp_Titles_Update, CommandType.StoredProcedure, paramColl)) { command.Connection = conn; command.Connection.Open(); command.ExecuteNonQuery(); } } }
public long UserFavoritesInsert(UserFavorite userFavorite) { long insertedUserFavoriteId; using (SqlConnection conn = GetNewConnection()) { List <SqlParameter> paramColl = new List <SqlParameter>(); paramColl.Add(new SqlParameter() { ParameterName = "@UserFavoriteId", Direction = ParameterDirection.Output, Size = int.MaxValue }); paramColl.Add(new SqlParameter() { ParameterName = "@UserIdentification", Value = NullSafeGetter.IsDefault <string>(userFavorite.UserIdentification) }); paramColl.Add(new SqlParameter() { ParameterName = "@IdentificationSource", Value = NullSafeGetter.IsDefault <string>(userFavorite.IdentificationSource) }); paramColl.Add(new SqlParameter() { ParameterName = "@SubtitleTextId", Value = NullSafeGetter.IsDefault <long>(userFavorite.SubtitleTextId) }); paramColl.Add(new SqlParameter() { ParameterName = "@Rating", Value = NullSafeGetter.IsDefault <int>(userFavorite.Rating) }); paramColl.Add(new SqlParameter() { ParameterName = "@RecordStatus", Value = NullSafeGetter.IsDefault <string>("A") }); using (SqlCommand command = BuildCommand(usp_UserFavorites_Insert, CommandType.StoredProcedure, paramColl)) { command.Connection = conn; command.Connection.Open(); command.ExecuteNonQuery(); insertedUserFavoriteId = Convert.ToInt64(command.Parameters["@UserFavoriteId"].Value); } } return(insertedUserFavoriteId); }
public IndexableEntity RandomSubtitleGet(string userIdentification) { IndexableEntity subtitlesText = null; using (SqlConnection conn = GetNewConnection()) { List <SqlParameter> paramColl = new List <SqlParameter>(); paramColl.Add(new SqlParameter() { ParameterName = "@userId", Value = NullSafeGetter.IsDefault <string>(userIdentification) }); using (SqlCommand command = GetCommand(usp_RandomSubtitleId_Get, CommandType.StoredProcedure)) { command.Connection = conn; command.Connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); if (reader.Read()) { subtitlesText = new IndexableEntity() { Id = reader.GetValueOrDefault <long>("Id"), SubtitleTextId = reader.GetValueOrDefault <long>("SubtitleTextId"), StartTime = reader.GetValueOrDefault <TimeSpan>("StartTime"), EndTime = reader.GetValueOrDefault <TimeSpan>("EndTime"), SubtitleText = reader.GetValueOrDefault <string>("SubtitleText"), TitleName = reader.GetValueOrDefault <string>("Title"), Rating = reader.GetValueOrDefault <byte>("Rating"), RatingId = reader.GetValueOrDefault <int>("UserFavoriteId") }; subtitlesText.UrlFriendlyQuoteText = subtitlesText.SubtitleText.UrlFriendlyString(); } reader.Close(); } } return(subtitlesText); }
public int SubtitlesTextInsert(SubtitlesText subtitlesText) { int subtitlesTextId = 0; using (SqlConnection conn = GetNewConnection()) { List <SqlParameter> paramColl = new List <SqlParameter>(); paramColl.Add(new SqlParameter() { ParameterName = "@Id", Direction = ParameterDirection.Output, Size = int.MaxValue }); paramColl.Add(new SqlParameter() { ParameterName = "@SubtitleTextId", Value = NullSafeGetter.IsDefault <long>(subtitlesText.SubtitleTextId) }); paramColl.Add(new SqlParameter() { ParameterName = "@StartTime", Value = NullSafeGetter.IsDefault <TimeSpan>(subtitlesText.StartTime) }); paramColl.Add(new SqlParameter() { ParameterName = "@EndTime", Value = NullSafeGetter.IsDefault <TimeSpan>(subtitlesText.EndTime) }); paramColl.Add(new SqlParameter() { ParameterName = "@SubtitleText", Value = NullSafeGetter.IsDefault <string>(subtitlesText.SubtitleText) }); using (SqlCommand command = BuildCommand(usp_SubtitlesText_Insert, CommandType.StoredProcedure, paramColl)) { command.Connection = conn; command.Connection.Open(); command.ExecuteNonQuery(); subtitlesTextId = Convert.ToInt32(command.Parameters["@Id"].Value); } } return(subtitlesTextId); }
public bool RecordExists(Title title) { bool isExists = false; using (SqlConnection conn = GetNewConnection()) { List <SqlParameter> paramColl = new List <SqlParameter>(); paramColl.Add(new SqlParameter() { ParameterName = "@Title", Value = title.TitleName }); paramColl.Add(new SqlParameter() { ParameterName = "@Season", Value = NullSafeGetter.IsDefault <string>(title.Season) }); paramColl.Add(new SqlParameter() { ParameterName = "@Episode", Value = NullSafeGetter.IsDefault <string>(title.Episode) }); paramColl.Add(new SqlParameter() { ParameterName = "@Year", Value = NullSafeGetter.IsDefault <string>(title.Year) }); using (SqlCommand command = BuildCommand(usp_Titles_CheckDuplicity, CommandType.StoredProcedure, paramColl)) { command.Connection = conn; command.Connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); isExists = reader.HasRows; reader.Close(); } } return(isExists); }
public int TitleSubtitleInsert(TitleSubtitle titleSubtitle) { int titleSubtitleId = 0; using (SqlConnection conn = GetNewConnection()) { List <SqlParameter> paramColl = new List <SqlParameter>(); paramColl.Add(new SqlParameter() { ParameterName = "@Id", Direction = ParameterDirection.Output, Size = int.MaxValue }); paramColl.Add(new SqlParameter() { ParameterName = "@TitleId", Value = NullSafeGetter.IsDefault <long>(titleSubtitle.TitleId) }); paramColl.Add(new SqlParameter() { ParameterName = "@SubtitleText", Value = NullSafeGetter.IsDefault <string>(titleSubtitle.SubtitleText) }); paramColl.Add(new SqlParameter() { ParameterName = "@SubtitleLanguageCode", Value = NullSafeGetter.IsDefault <string>(titleSubtitle.SubtitleLanguageCode) }); using (SqlCommand command = BuildCommand(usp_TitleSubtitle_Insert, CommandType.StoredProcedure, paramColl)) { command.Connection = conn; command.Connection.Open(); command.ExecuteNonQuery(); titleSubtitleId = Convert.ToInt32(command.Parameters["@Id"].Value); } } return(titleSubtitleId); }
public void UserFavoritesUpdate(UserFavorite userFavorite) { using (SqlConnection conn = GetNewConnection()) { List <SqlParameter> paramColl = new List <SqlParameter>(); paramColl.Add(new SqlParameter() { ParameterName = "@UserFavoriteId", Value = NullSafeGetter.IsDefault <long>(userFavorite.UserFavoriteId) }); paramColl.Add(new SqlParameter() { ParameterName = "@Rating", Value = NullSafeGetter.IsDefault <int>(userFavorite.Rating) }); using (SqlCommand command = BuildCommand(usp_UserFavorites_Update, CommandType.StoredProcedure, paramColl)) { command.Connection = conn; command.Connection.Open(); command.ExecuteNonQuery(); } } }
public void TitlesIndexStatusUpdate(long titleId, string indexStatus) { using (SqlConnection conn = GetNewConnection()) { List <SqlParameter> paramColl = new List <SqlParameter>(); paramColl.Add(new SqlParameter() { ParameterName = "@Id", Value = NullSafeGetter.IsDefault <long>(titleId) }); paramColl.Add(new SqlParameter() { ParameterName = "@IndexStatus", Value = NullSafeGetter.IsDefault <string>(indexStatus) }); using (SqlCommand command = BuildCommand(usp_Titles_IndexStatus_Update, CommandType.StoredProcedure, paramColl)) { command.Connection = conn; command.Connection.Open(); command.ExecuteNonQuery(); } } }