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 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 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 ConfirmationExists(Guid confirmation)
{
using (SqlCmd cmd = new SqlCmd("SELECT Id FROM [User] WHERE EmailConfirmation = @Confirmation", false))
{
cmd.AddIGuid("@Confirmation", confirmation);
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 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 (bool reset, DateTime requestDate, int userId)? GetResetRequest(Guid resetRequest)
{
using (SqlCmd cmd = new SqlCmd("SELECT * FROM ResetRequests WHERE Id = @Id", false))
{
cmd.AddIGuid("@Id", resetRequest);
var r = cmd.ExecuteSingleRowOrNull();
if (r == null)
{
return(null);
}
return((bool)r["PasswordReset"], (DateTime)r["RequestTime"], (int)r["UserId"]);
}
}
public static string GetUser(Guid confirmation)
{
using (SqlCmd cmd = new SqlCmd("SELECT UserName FROM [User] WHERE EmailConfirmation = @Confirmation", false))
{
cmd.AddIGuid("@Confirmation", confirmation);
var r = cmd.ExecuteSingleRowOrNull();
if (r == null)
{
return(null);
}
return((string)r["UserName"]);
}
}
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);
}
}
public static string GetUrl(int id)
{
DataRow r;
using (SqlCmd cmd = new SqlCmd("SELECT UrlLabel FROM Episode WHERE Id = @Id", false))
{
cmd.AddIInt("@Id", id);
r = cmd.ExecuteSingleRowOrNull();
}
if (r == null)
{
return("");
}
return(r["UrlLabel"].ToString());
}
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 Get(int id)
{
DataRow r;
using (SqlCmd cmd = new SqlCmd("SELECT * FROM ContentPage WHERE Id = @Id", false))
{
cmd.AddIInt("@Id", id);
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 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());
}
