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