public List <Video> GetFromCategory(VideoCategory category, bool includePrivateVideos)
{
List <Video> returnMe = new List <Video>();
using (SqlConnection connection = new SqlConnection(DatabaseConnectionStrings.ReadOnly))
{
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = connection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = "SELECT * FROM videos WHERE category_id='" + category.ID + "' ORDER BY date_added DESC;";
sqlCommand.Connection.Open();
SqlDataReader dbDataReader = sqlCommand.ExecuteReader();
if (dbDataReader.HasRows)
{
while (dbDataReader.Read())
{
returnMe.Add(dbDataReaderToVideo(dbDataReader));
}
}
sqlCommand.Connection.Close();
}
}
return(returnMe);
}
public void Insert(VideoCategory category)
{
if (string.IsNullOrEmpty(category.ID))
{
category.ID = CreateNewVideoCategoryID();
}
using (SqlConnection connection = new SqlConnection(DatabaseConnectionStrings.ReadWrite))
{
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = connection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = "INSERT INTO video_categories(name, hidden, private, id, parent)"
+ "VALUES(@NAME, @HIDDEN, @PRIVATE, @ID, @PARENT)";
sqlCommand.Parameters.AddWithValue("ID", category.ID);
sqlCommand.Parameters.AddWithValue("NAME", category.Name);
sqlCommand.Parameters.AddWithValue("HIDDEN", category.IsHidden);
sqlCommand.Parameters.AddWithValue("PRIVATE", category.IsPrivate);
sqlCommand.Parameters.AddWithValue("PARENT", category.ParentCategoryID);
sqlCommand.Connection.Open();
sqlCommand.ExecuteNonQuery();
sqlCommand.Connection.Close();
}
}
}
public void Delete(VideoCategory category)
{
using (SqlConnection connection = new SqlConnection(DatabaseConnectionStrings.ReadWrite))
{
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = connection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = "DELETE FROM video_categories WHERE id=@ID";
sqlCommand.Parameters.AddWithValue("ID", category.ID);
sqlCommand.Connection.Open();
sqlCommand.ExecuteNonQuery();
sqlCommand.Connection.Close();
}
}
}
public VideoCategoryRepository()
{
_cache = new Dictionary <string, VideoCategory>();
using (SqlConnection connection = new SqlConnection(DatabaseConnectionStrings.ReadOnly))
{
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = connection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = "SELECT * FROM vCategoriesWithCount;";
sqlCommand.Connection.Open();
SqlDataReader dbDataReader = sqlCommand.ExecuteReader();
if (dbDataReader.HasRows)
{
while (dbDataReader.Read())
{
VideoCategory parsedCategory = dbDataReaderToVideoCategory(dbDataReader);
_cache.Add(parsedCategory.ID, parsedCategory);
}
}
sqlCommand.Connection.Close();
}
}
// Link categories to their children
foreach (VideoCategory cat in _cache.Values)
{
if (!string.IsNullOrEmpty(cat.ParentCategoryID))
{
if (_cache.ContainsKey(cat.ParentCategoryID))
{
cat.ParentCategory = _cache[cat.ParentCategoryID];
_cache[cat.ParentCategoryID].Children.Add(cat);
}
}
}
}
public void Update(VideoCategory category)
{
using (SqlConnection connection = new SqlConnection(DatabaseConnectionStrings.ReadWrite))
{
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = connection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = "UPDATE video_categories SET name=@NAME, hidden=@HIDDEN, private=@PRIVATE, parent=@PARENT WHERE id=@ID";
sqlCommand.Parameters.AddWithValue("ID", category.ID);
sqlCommand.Parameters.AddWithValue("NAME", category.Name);
sqlCommand.Parameters.AddWithValue("HIDDEN", category.IsHidden);
sqlCommand.Parameters.AddWithValue("PRIVATE", category.IsPrivate);
sqlCommand.Parameters.AddWithValue("PARENT", category.ParentCategoryID);
sqlCommand.Connection.Open();
sqlCommand.ExecuteNonQuery();
sqlCommand.Connection.Close();
}
}
}
