public LiveBroadcastRepository() { _cache = new Dictionary <string, LiveBroadcast>(); using (SqlConnection connection = new SqlConnection(DatabaseConnectionStrings.ReadOnly)) { using (SqlCommand sqlCommand = new SqlCommand()) { sqlCommand.Connection = connection; sqlCommand.CommandType = CommandType.Text; sqlCommand.CommandText = "SELECT * FROM live_streams ORDER BY stream_start DESC, stream_end DESC;"; sqlCommand.Connection.Open(); SqlDataReader dbDataReader = sqlCommand.ExecuteReader(); if (dbDataReader.HasRows) { while (dbDataReader.Read()) { LiveBroadcast parsedBroadcast = dbDataReaderToLiveBroadcast(dbDataReader); _cache.Add(parsedBroadcast.ID, parsedBroadcast); } } sqlCommand.Connection.Close(); } } }
public void Update(LiveBroadcast broadcast) { using (SqlConnection connection = new SqlConnection(DatabaseConnectionStrings.ReadWrite)) { using (SqlCommand sqlCommand = new SqlCommand()) { sqlCommand.Connection = connection; sqlCommand.CommandType = CommandType.Text; sqlCommand.CommandText = "UPDATE live_streams SET name=@NAME, location=@LOC, description=@DESC, thumbnail_url=@THUMB, width=@WIDTH, height=@HEIGHT," + " stream_start=@STARTS, stream_end=@ENDS, hidden=@ISHIDDEN, private=@ISPRIVATE, force_online=@FORCEONLINE, youtube_id=@YOUTUBEID, cancelled=@CANCELLED, delayed=@DELAYED, embed_instead_of_linking=@EMBED" + " WHERE id=@ID"; sqlCommand.Parameters.AddWithValue("ID", broadcast.ID); sqlCommand.Parameters.AddWithValue("NAME", broadcast.Name); sqlCommand.Parameters.AddWithValue("LOC", broadcast.Location); sqlCommand.Parameters.AddWithValue("DESC", broadcast.Description); //sqlCommand.Parameters.AddWithValue("THUMB", broadcast.ThumbnailURL); sqlCommand.Parameters.AddWithValue("THUMB", ""); sqlCommand.Parameters.AddWithValue("WIDTH", broadcast.Width); sqlCommand.Parameters.AddWithValue("HEIGHT", broadcast.Height); sqlCommand.Parameters.AddWithValue("STARTS", broadcast.StartTime); sqlCommand.Parameters.AddWithValue("ENDS", broadcast.EndTime); sqlCommand.Parameters.AddWithValue("ISHIDDEN", broadcast.IsHidden); sqlCommand.Parameters.AddWithValue("ISPRIVATE", broadcast.IsPrivate); sqlCommand.Parameters.AddWithValue("FORCEONLINE", broadcast.ForcedLive); sqlCommand.Parameters.AddWithValue("YOUTUBEID", broadcast.YouTubeID); sqlCommand.Parameters.AddWithValue("DELAYED", broadcast.IsDelayed); sqlCommand.Parameters.AddWithValue("CANCELLED", broadcast.IsCancelled); sqlCommand.Parameters.AddWithValue("EMBED", broadcast.EmbedInsteadOfLink); sqlCommand.Connection.Open(); sqlCommand.ExecuteNonQuery(); sqlCommand.Connection.Close(); } } }
public void Delete(LiveBroadcast broadcast) { if (!string.IsNullOrEmpty(broadcast.ID)) { using (SqlConnection connection = new SqlConnection(DatabaseConnectionStrings.ReadWrite)) { using (SqlCommand sqlCommand = new SqlCommand()) { sqlCommand.Connection = connection; sqlCommand.CommandType = CommandType.Text; sqlCommand.CommandText = "DELETE FROM live_streams WHERE id=@ID"; sqlCommand.Parameters.AddWithValue("ID", broadcast.ID); sqlCommand.Connection.Open(); sqlCommand.ExecuteNonQuery(); sqlCommand.Connection.Close(); } } } }
public void Insert(LiveBroadcast broadcast) { if (string.IsNullOrEmpty(broadcast.ID)) { broadcast.ID = CreateNewID(); } using (SqlConnection connection = new SqlConnection(DatabaseConnectionStrings.ReadWrite)) { using (SqlCommand sqlCommand = new SqlCommand()) { sqlCommand.Connection = connection; sqlCommand.CommandType = CommandType.Text; sqlCommand.CommandText = "INSERT INTO live_streams(id,name,location,description,thumbnail_url,width,height,stream_start,stream_end,hidden,private,force_online,youtube_id,delayed,cancelled)" + "VALUES(@ID,@NAME,@LOC,@DESC,@THUMB,@WIDTH,@HEIGHT,@STARTS,@ENDS,@ISHIDDEN,@ISPRIVATE,@FORCEONLINE,@YOUTUBEID,@DELAYED,@CANCELLED)"; sqlCommand.Parameters.AddWithValue("ID", broadcast.ID); sqlCommand.Parameters.AddWithValue("NAME", broadcast.Name); sqlCommand.Parameters.AddWithValue("DESC", broadcast.Description); sqlCommand.Parameters.AddWithValue("LOC", broadcast.Location); //sqlCommand.Parameters.AddWithValue("THUMB", broadcast.ThumbnailURL); sqlCommand.Parameters.AddWithValue("THUMB", ""); sqlCommand.Parameters.AddWithValue("WIDTH", broadcast.Width); sqlCommand.Parameters.AddWithValue("HEIGHT", broadcast.Height); sqlCommand.Parameters.AddWithValue("STARTS", broadcast.StartTime); sqlCommand.Parameters.AddWithValue("ENDS", broadcast.EndTime); sqlCommand.Parameters.AddWithValue("ISHIDDEN", broadcast.IsHidden); sqlCommand.Parameters.AddWithValue("ISPRIVATE", broadcast.IsPrivate); sqlCommand.Parameters.AddWithValue("FORCEONLINE", broadcast.ForcedLive); sqlCommand.Parameters.AddWithValue("YOUTUBEID", broadcast.YouTubeID); sqlCommand.Parameters.AddWithValue("DELAYED", broadcast.IsDelayed); sqlCommand.Parameters.AddWithValue("CANCELLED", broadcast.IsCancelled); sqlCommand.Connection.Open(); sqlCommand.ExecuteNonQuery(); sqlCommand.Connection.Close(); } } }