public void Set()
{
using (SqlCmd cmd = new SqlCmd("UPDATE ContentPage SET [Name] = @Name, [Path] = @Path, Content = @Content WHERE Id = @Id", false))
{
cmd.AddIInt("@Id", Id);
cmd.AddIString("@Name", 100, Name);
cmd.AddIString("@Path", 100, Path);
cmd.AddIString("@Content", -1, Content);
cmd.Execute();
}
}
public void Add()
{
using (SqlCmd cmd = new SqlCmd("INSERT INTO ContentPage VALUES (@Name, @Path, @Content) SET @Id = SCOPE_IDENTITY()", false))
{
cmd.AddIString("@Name", 100, Name);
cmd.AddIString("@Path", 100, Path);
cmd.AddIString("@Content", -1, Content);
cmd.AddOInt("@Id");
cmd.Execute();
Id = cmd.GetInt("@Id");
}
}
public void Add()
{
using (SqlCmd cmd = new SqlCmd("INSERT INTO LogHistory (UserId, [Type], ActionType, PreviousChange, NewChange) VALUES (@UserId, @Type, @Action, @Previous, @New)", false))
{
cmd.AddIInt("@UserId", UserId);
cmd.AddIInt("@Type", (int)Type);
cmd.AddIInt("@Action", (int)Action);
cmd.AddIString("@Previous", -1, PreviousChange);
cmd.AddIString("@New", -1, NewChange);
cmd.Execute();
}
}
public void Set()
{
using (SqlCmd cmd = new SqlCmd("UPDATE Article SET Title = @Title, SubHeading = @SubHeading, ShortDescription = @ShortDescription, Content = @Content, PublishDate = @PublishDate, UrlLabel = @UrlLabel WHERE Id = @Id", false))
{
cmd.AddIInt("@Id", Id);
cmd.AddIString("@Title", 300, Title);
cmd.AddIString("@SubHeading", 300, SubHeading);
cmd.AddIString("@ShortDescription", 1000, ShortDescription);
cmd.AddIString("@Content", -1, Content);
cmd.AddIDateTime("@PublishDate", PublishDate);
cmd.AddIString("@UrlLabel", 300, CreateUrl());
cmd.Execute();
}
}
public void Add(string password, string ip, Guid emailConfirmation)
{
using (SqlCmd cmd = new SqlCmd("INSERT INTO [User] (UserName, UserEmail, UserPassword, TimeZone, IPAddress, IsActive, EmailConfirmation) VALUES (@UserName, @UserEmail, @Password, @TimeZone, @IPAddress, 0, @EmailConfirmation); SET @Id = SCOPE_IDENTITY();", false))
{
cmd.AddIString("@UserName", 40, UserName);
cmd.AddIString("@UserEmail", 50, Email);
cmd.AddIString("@Password", 250, password);
cmd.AddIString("@TimeZone", 50, TimeZone.Id);
cmd.AddIString("@IPAddress", 100, ip);
cmd.AddIGuid("@EmailConfirmation", emailConfirmation);
cmd.AddOInt("@Id");
cmd.Execute();
Id = cmd.GetInt("@Id");
}
}
public void Add()
{
using (SqlCmd cmd = new SqlCmd("INSERT INTO Article VALUES (@UserId, @Title, @SubHeading, @ShortDescription, @Content, @PublishDate, @UrlLabel); SET @Id = SCOPE_IDENTITY();", false))
{
cmd.AddIInt("@UserId", Author.Id);
cmd.AddIString("@Title", 300, Title);
cmd.AddIString("@SubHeading", 300, SubHeading);
cmd.AddIString("@ShortDescription", 1000, ShortDescription);
cmd.AddIString("@Content", -1, Content);
cmd.AddIDateTime("@PublishDate", PublishDate);
cmd.AddIString("@UrlLabel", 300, CreateUrl());
cmd.AddOInt("@Id");
cmd.Execute();
Id = cmd.GetInt("@Id");
}
}
public static Episode GetByUrl(string url)
{
DataRow r;
using (SqlCmd cmd = new SqlCmd("SELECT * FROM Episode WHERE UrlLabel = @Url", false))
{
cmd.AddIString("@Url", 100, url);
r = cmd.ExecuteSingleRowOrNull();
}
if (r == null)
{
return(null);
}
return(new Episode()
{
Id = (int)r["Id"],
Number = (int)r["Number"],
Title = (string)r["Title"],
AirDate = (DateTime)r["AirDate"],
ProductionNumber = (string)r["ProductionNumber"],
Stars = ((string)r["Starring"]).Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Trim().ToArray(),
GuestStars = ((string)r["GuestStarring"]).Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Trim().ToArray(),
Producer = ((string)r["Producer"]).Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Trim().ToArray(),
ExecutiveProducer = ((string)r["ExecProducer"]).Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Trim().ToArray(),
Description = (string)r["Description"],
Writer = ((string)r["Writer"]).Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Trim().ToArray(),
Studio = (string)r["Studio"],
UrlLabel = (string)r["UrlLabel"],
Director = ((string)r["Director"]).Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Trim().ToArray(),
Recap = r["Recap"].ToString(),
Transcript = r["Transcript"].ToString()
});
}
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 User GetByLogin(string login)
{
using (SqlCmd cmd = new SqlCmd("SELECT Id, UserName, UserEmail, TimeZone, JoinDate, ShowEmail, DisplayName, IsActive, TwoFactor FROM [User] WHERE UserName = @Login OR UserEmail = @Login", false))
{
cmd.AddIString("@Login", 50, login);
var r = cmd.ExecuteSingleRowOrNull();
if (r == null)
{
return(null);
}
return(new User
{
Id = (int)r["Id"],
UserName = (string)r["UserName"],
Email = (string)r["UserEmail"],
TimeZone = TimeZoneInfo.FindSystemTimeZoneById((string)r["TimeZone"]),
JoinDate = (DateTime)r["JoinDate"],
ShowEmail = (bool)r["ShowEmail"],
DisplayName = r["DisplayName"] as string ?? "",
IsActive = (bool)r["IsActive"],
TwoFactorAuth = r["TwoFactor"].ToString()
});
}
}
public static bool LoginExists(string login)
{
using (SqlCmd cmd = new SqlCmd("SELECT Id FROM [User] WHERE UserName = @Login", false))
{
cmd.AddIString("@Login", 50, login);
return(cmd.ExecuteSingleRowOrNull() != null);
}
}
public static bool EmailExists(string email)
{
using (SqlCmd cmd = new SqlCmd("SELECT Id FROM [User] WHERE UserEmail = @Email", false))
{
cmd.AddIString("@Email", 50, email);
return(cmd.ExecuteSingleRowOrNull() != null);
}
}
public static void UpdatePassword(int userId, string hash)
{
using (SqlCmd cmd = new SqlCmd("UPDATE [User] SET UserPassword = @Hash WHERE Id = @UserId; UPDATE ResetRequests SET PasswordReset = 1 WHERE UserId = @UserId;", false))
{
cmd.AddIInt("@UserId", userId);
cmd.AddIString("@Hash", 250, hash);
cmd.Execute();
}
}
public static bool NeedsPasswordChanged(string login)
{
using (SqlCmd cmd = new SqlCmd("SELECT EmailConfirmation FROM [User] WHERE UserName = @Login", false))
{
cmd.AddIString("@Login", 50, login);
var r = cmd.ExecuteSingleRowOrNull();
if (r == null)
{
return(false);
}
return(r["EmailConfirmation"] as string != null);
}
}
public static (string password, string userName, string displayName, int userId, bool isActive, string TwoFactorAuth)? GetPassword(string login)
{
using (SqlCmd cmd = new SqlCmd("SELECT Id, UserPassword, UserName, DisplayName, IsActive, TwoFactor FROM [User] WHERE (UserName = @Login OR UserEmail = @Login) AND IsActive = 1", false))
{
cmd.AddIString("@Login", 50, login);
var r = cmd.ExecuteSingleRowOrNull();
if (r == null)
{
return(null);
}
string twoFactor = r["TwoFactor"].ToString();
return((string)r["UserPassword"], (string)r["UserName"], r["DisplayName"] as string ?? "", (int)r["Id"], (bool)r["IsActive"], twoFactor);
}
}
private bool UrlExists()
{
DataRow r;
using (SqlCmd cmd = new SqlCmd("SELECT Id FROM Article WHERE UrlLabel = @UrlLabel AND MONTH(PublishDate) = MONTH(@Date) AND YEAR(PublishDate) = YEAR(@Date)", false))
{
cmd.AddIString("@UrlLabel", 300, UrlLabel);
cmd.AddIDateTime("@Date", PublishDate);
r = cmd.ExecuteSingleRowOrNull();
}
if (r == null)
{
return(false);
}
return((int)r["Id"] != Id);
}
public static ContentPage GetByUrl(string path)
{
DataRow r;
using (SqlCmd cmd = new SqlCmd("SELECT * FROM ContentPage WHERE [Path] = @Path", false))
{
cmd.AddIString("@Path", 100, path);
r = cmd.ExecuteSingleRowOrNull();
}
if (r == null)
{
return(null);
}
return(new ContentPage
{
Id = (int)r["Id"],
Name = (string)r["Name"],
Path = (string)r["Path"],
Content = (string)r["Content"]
});
}
public static string RenderImdbLinks(this string[] vals)
{
string val = "";
foreach (string v in vals)
{
DataRow r;
using (SqlCmd cmd = new SqlCmd("SELECT ImdbNameID FROM CrewLink WHERE CrewName = @CrewMember", false))
{
cmd.AddIString("@CrewMember", 50, v);
r = cmd.ExecuteSingleRowOrNull();
}
if (r == null)
{
val += $" {v}";
}
else
{
val += @" <a href=""https:" + $@"//www.imdb.com/name/nm{r["ImdbNameID"]}/"" target=""_blank"" rel=""nofollow"">{v}</a>";
}
}
return(val.Trim());
}
