public static async Task <HashSet <Alias> > ReadAllByChar(CharacterModel character)
{
const string query = "SELECT * FROM aliases WHERE charID = @charID";
using (var dbConn = DbConnectionProvider.CreateDbConnection())
{
try
{ // cumhere
var result = await dbConn.QueryAsync(query, new { charID = character.ID });
var aliases = new HashSet <Alias>();
foreach (var i in result)
{
aliases.Add(new Alias(i.CharID, i.AliasedID, i.AliasName, i.AliasDesc));
}
return(aliases);
}
catch (DbException ex)
{
DbConnectionProvider.HandleDbException(ex);
}
return(null);
}
}
public static async Task <bool> CreateAsync(AccountModel account, string emailAddress)
{
const string query = "INSERT INTO emailtokens(accountID, token, expirydate, emailaddress) VALUES (@accountid, @token, @expirydate, @emailaddress)";
if (await ExistsAsync(account))
{
await RemoveAsync(account);
}
var emailToken = new EmailToken(account, emailAddress);
using (var dbConn = DbConnectionProvider.CreateDbConnection())
{
try
{
await dbConn.ExecuteAsync(query, new
{
accountid = emailToken.Account.ID,
token = emailToken.Token,
emailaddress = emailToken.EmailAddress,
expirydate = emailToken.ExpiryDate
});
return(true);
}
catch (DbException ex)
{
DbConnectionProvider.HandleDbException(ex);
}
return(false);
}
}
public static async Task <IEnumerable <T> > ReadByKeyAsync <TC>(Expression <Func <TC> > expression, object searchKey)
{
var tAttribute = (TableAttribute)typeof(T).GetCustomAttributes(typeof(TableAttribute), true)[0];
var tableName = tAttribute.Name;
if (tableName == null)
{
return(null);
}
var expressionBody = expression.Body as MemberExpression;
var expressionMember = expressionBody?.Member;
var columnName = expressionMember?.Name;
if (columnName == null)
{
return(null);
}
var query = $"SELECT * FROM {tableName} WHERE {columnName} = @value;";
using (var dbConn = DbConnectionProvider.CreateDbConnection())
{
try
{
return(await dbConn.QueryAsync <T>(query, new { value = searchKey }));
}
catch (DbException ex)
{
DbConnectionProvider.HandleDbException(ex);
}
}
return(null);
}
public static async Task <bool> CreateAsync(int charid, int alisedid, string aliasName, string aliasDesc = "")
{
const string query = "INSERT INTO aliases(CharID, AliasedID, AliasName, AliasDesc) VALUES (@CharID, @AliasedID, @AliasName, @AliasDesc)";
var newAlias = new Alias(charid, alisedid, aliasName, aliasDesc);
using (var dbConn = DbConnectionProvider.CreateDbConnection())
{
try
{
await dbConn.ExecuteAsync(query, new
{
CharID = newAlias.CharID,
AliasedID = newAlias.AliasedID,
AliasName = newAlias.AliasName,
AliasDesc = newAlias.AliasDesc == "" ? null : newAlias.AliasDesc
});
return(true);
}
catch (DbException ex)
{
DbConnectionProvider.HandleDbException(ex);
}
}
return(false);
}
public static async Task <EmailToken> FetchAsync(AccountModel account)
{
const string query = "SELECT accountID, token, expirydate, emailaddress FROM emailtokens WHERE accountID = @accountid";
if (!await ExistsAsync(account))
{
return(null);
}
using (var dbConn = DbConnectionProvider.CreateDbConnection())
{
try
{
var result = await dbConn.QueryAsync(query, new { accountid = account.ID });
var unwrapped = result.SingleOrDefault();
if (unwrapped == null)
{
return(null);
}
return(new EmailToken(account, unwrapped.token, unwrapped.expirydate, unwrapped.emailaddress));
}
catch (DbException ex)
{
DbConnectionProvider.HandleDbException(ex);
}
}
throw new Exception("Error in [EmailToken.FetchAsync]");
}
public static async Task RemoveExpiredCodesAsync()
{
const string query = "DELETE FROM emailtokens WHERE expirydate < @current";
using (var dbConn = DbConnectionProvider.CreateDbConnection())
{
try
{
await dbConn.ExecuteAsync(query, new { current = DateTime.Now });
}
catch (DbException ex)
{
DbConnectionProvider.HandleDbException(ex);
}
}
}
public static async Task <bool> ExistsAsync(int sqlID)
{
using (var dbConn = DbConnectionProvider.CreateDbConnection())
{
try
{
return((await dbConn.GetAsync <T>(sqlID)) != null);
}
catch (DbException ex)
{
DbConnectionProvider.HandleDbException(ex);
}
return(false);
}
}
public static async Task <T> ReadAsync(int sqlID)
{
using (var dbConn = DbConnectionProvider.CreateDbConnection())
{
try
{
return(await dbConn.GetAsync <T>(sqlID));
}
catch (DbException ex)
{
DbConnectionProvider.HandleDbException(ex);
}
return(null);
}
}
public static async Task <bool> DeleteAsync(T entry)
{
using (var dbConn = DbConnectionProvider.CreateDbConnection())
{
try
{
return(await dbConn.DeleteAsync(entry));
}
catch (DbException ex)
{
DbConnectionProvider.HandleDbException(ex);
}
return(false);
}
}
public static async Task <int> CreateAsync(T newEntry)
{
using (var dbConn = DbConnectionProvider.CreateDbConnection())
{
try
{
return(await dbConn.InsertAsync(newEntry));
}
catch (DbException ex)
{
DbConnectionProvider.HandleDbException(ex);
}
return(-1);
}
}
public static async Task <bool> ExistsAsync(AccountModel account)
{
const string query = "SELECT accountID FROM emailtokens WHERE accountID = @accountid";
using (var dbConn = DbConnectionProvider.CreateDbConnection())
{
try
{
var result = await dbConn.QueryAsync <int?>(query, new { accountid = account.ID });
return(result.Any());
}
catch (DbException ex)
{
DbConnectionProvider.HandleDbException(ex);
}
}
throw new Exception("There was an error in [EmailToken.ExistsAsync]");
}
public static async Task <bool> IsEmailTakenAsync(string emailAddress)
{
const string query = "SELECT emailaddress FROM emailtokens WHERE emailaddress = @emailaddress";
using (var dbConn = DbConnectionProvider.CreateDbConnection())
{
try
{
var result = await dbConn.QueryAsync(query, new { emailaddress = emailAddress });
return(result.Any());
}
catch (DbException ex)
{
DbConnectionProvider.HandleDbException(ex);
}
}
throw new Exception("There was an error in [EmailToken.IsEmailTakenAsync]");
}
private static async Task RemoveAsync(AccountModel account)
{
if (!await ExistsAsync(account))
{
return;
}
const string query = "DELETE FROM emailtokens WHERE accountID = @accountid";
using (var dbConn = DbConnectionProvider.CreateDbConnection())
{
try
{
await dbConn.ExecuteAsync(query, new { accountid = account.ID });
}
catch (DbException ex)
{
DbConnectionProvider.HandleDbException(ex);
}
}
}
public static async Task <bool> Exist(int charid, int alisedID)
{
const string query = "SELECT * FROM aliases WHERE charID = @charID AND AliasedID = @aliasedID";
using (var dbConn = DbConnectionProvider.CreateDbConnection())
{
try
{
var result =
await dbConn.QueryAsync <int>(query, new { charID = charid, aliasedID = alisedID });
return(result.Any());
}
catch (DbException ex)
{
DbConnectionProvider.HandleDbException(ex);
}
return(false);
}
}
public static async Task <bool> DeleteAlias(Alias alias)
{
var query = "DELETE FROM aliases WHERE CharID = @charID AND AliasedID = @aliasedID";
using (var dbConn = DbConnectionProvider.CreateDbConnection())
{
try
{
await dbConn.ExecuteAsync(query, new
{
charID = alias.CharID,
aliasedID = alias.AliasedID
});
return(true);
}
catch (DbException ex)
{
DbConnectionProvider.HandleDbException(ex);
}
}
return(false);
}
public async Task SaveAsync()
{
const string query = "UPDATE emailtokens " +
"SET token = @token, emailAddress = @emailaddress, expirydate = @expirydate " +
"WHERE accountID = @sqlId";
using (var dbConn = DbConnectionProvider.CreateDbConnection())
{
try
{
await dbConn.ExecuteAsync(query, new
{
sqlId = Account.ID,
token = Token,
emailaddress = EmailAddress,
expirydate = ExpiryDate
});
}
catch (DbException ex)
{
DbConnectionProvider.HandleDbException(ex);
}
}
}
public static async Task <bool> UpdateAlias(Alias alias)
{
var query = "UPDATE aliases SET aliasName = @aliasname, aliasDesc = @aliasdesc WHERE CharID = @charID AND AliasedID = @aliasedID";
using (var dbConn = DbConnectionProvider.CreateDbConnection())
{
try
{
await dbConn.ExecuteAsync(query, new
{
aliasname = alias.AliasName,
aliasdesc = alias.AliasDesc,
charID = alias.CharID,
aliasedID = alias.AliasedID
});
}
catch (DbException ex)
{
DbConnectionProvider.HandleDbException(ex);
}
return(false);
}
}
