public static List <Episode> List(int?season = null)
{
List <Episode> list = new List <Episode>();
DataTable t;
using (SqlCmd cmd = new SqlCmd("SELECT Id, Number, Title, AirDate, ProductionNumber, UrlLabel, CASE WHEN LEN(Transcript) > 10 THEN CAST(1 as bit) ELSE CAST(0 as bit) END as HasTranscript FROM Episode WHERE @Season IS NULL OR ProductionNumber LIKE @Season + '-%'", false))
{
cmd.AddIString("@Season", 10, season?.ToString());
t = cmd.ExecuteTable();
}
foreach (DataRow r in t.Rows)
{
list.Add(new Episode()
{
Id = (int)r["Id"],
Number = (int)r["Number"],
Title = (string)r["Title"],
AirDate = (DateTime)r["AirDate"],
ProductionNumber = (string)r["ProductionNumber"],
UrlLabel = (string)r["UrlLabel"],
HasTranscript = (bool)r["HasTranscript"]
});
}
return(list);
}
public static DataTable ListNotes(int id)
{
using (SqlCmd cmd = new SqlCmd("SELECT Note FROM Notes WHERE EpisodeId = @Id", false))
{
cmd.AddIInt("@Id", id);
return(cmd.ExecuteTable());
}
}
public static DataTable ListCultural(int id)
{
using (SqlCmd cmd = new SqlCmd("SELECT CulturalText FROM Cultural WHERE EpisodeId = @Id", false))
{
cmd.AddIInt("@Id", id);
return(cmd.ExecuteTable());
}
}
public static List <ContentPage> List()
{
List <ContentPage> l = new List <ContentPage>();
DataTable t;
using (SqlCmd cmd = new SqlCmd("SELECT * FROM ContentPage ORDER BY [Name]", false))
{
t = cmd.ExecuteTable();
}
foreach (DataRow r in t.Rows)
{
l.Add(new ContentPage
{
Id = (int)r["Id"],
Name = (string)r["Name"],
Path = (string)r["Path"],
Content = (string)r["Content"]
});
}
return(l);
}
public static List <Timeline> ListToday()
{
List <Timeline> l = new List <Timeline>();
DataTable t;
using (SqlCmd cmd = new SqlCmd("SELECT * FROM Timeline WHERE MONTH(GETUTCDATE()) = MONTH([Date]) AND DAY(GETUTCDATE()) = DAY([Date])", false))
{
t = cmd.ExecuteTable();
}
foreach (DataRow r in t.Rows)
{
l.Add(new Timeline
{
Id = (int)r["Id"],
Date = (DateTime)r["Date"],
Message = (string)r["Message"],
UserId = (int)r["UserId"]
});
}
return(l);
}
