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