public async Task <OdissUser> FindByEmailAsync(string email)
{
using (var db = new MainDatabase().Get)
{
return(await db.QueryFirstOrDefaultAsync <OdissUser>("SELECT * FROM [dbo].[Users] WHERE Email = @email", new { email }));
}
}
public async Task ClearPasswordResetTokensAsync(Guid userId)
{
using (var db = new MainDatabase().Get)
{
await db.ExecuteAsync("DELETE FROM [dbo].[UserPasswordReset] WHERE UserId = @userId", new { userId });
}
}
public async Task <UserPasswordReset> GetUserPasswordResetAsync(string passwordResetKey)
{
using (var db = new MainDatabase().Get)
{
return(await db.QueryFirstAsync <UserPasswordReset>("SELECT TOP 1 CONVERT(NVARCHAR(50), Id) AS PasswordResetKey, UserId, ExpiryDate, ResetPasswordToken AS PasswordResetToken FROM [dbo].[UserPasswordReset] WHERE Id = @passwordResetKey ORDER BY ExpiryDate DESC", new { passwordResetKey }));
}
}
public async Task DeleteAsync(OdissUser user)
{
using (var db = new MainDatabase().Get)
{
var sql = "DELETE FROM [dbo].[Users] WHERE Id = @Id";
await db.ExecuteAsync(sql, user);
}
}
public async Task UpdateAsync(OdissUser user)
{
using (var db = new MainDatabase().Get)
{
var sql = "UPDATE [dbo].[Users] SET UserName = @UserName, Password = @Password, Type = @Type, FirstName = @FirstName, LastName = @LastName, PhoneOffice = @PhoneOffice, PhoneMobile = @PhoneMobile, Email = @Email, Company = @Company, Expire = @Expire, Permissions = @Permissions, Active = @Active, LockAccessUntil = @LockAccessUntil, WrongAccessAttempts = @WrongAccessAttempts WHERE ID = @Id";
await db.ExecuteAsync(sql, user);
}
}
public async Task CreateAsync(OdissUser user)
{
using (var db = new MainDatabase().Get)
{
var sql = "INSERT INTO [dbo].[Users] (UserName, Password, Type, FirstName, LastName, PhoneOffice, PhoneMobile, Email, Company, Expire, Permissions, Active, LockAccessUntil, WrongAccessAttempts) VALUES (@UserName, @Password, @Type, @FirstName, @LastName, @PhoneOffice, @PhoneMobile, @Email, @Company, @Expire, @Permissions, @Active, @LockAccessUntil, @WrongAccessAttempts)";
await db.ExecuteAsync(sql, user);
}
}
public async Task <IEnumerable <UserSession <Guid, Guid> > > ClearSessionsAsync()
{
using (var db = new MainDatabase().Get)
{
return(await db.QueryAsync <UserSession <Guid, Guid> >(@"
DELETE FROM [dbo].[Sessions]
OUTPUT DELETED.*
WHERE Expire < GETDATE()"));
}
}
public async Task <UserSession <Guid, Guid> > GetByUserIdAsync(Guid userId)
{
using (var db = new MainDatabase().Get)
{
return(await db.QueryFirstOrDefaultAsync <UserSession <Guid, Guid> >(@"
SELECT TOP 1 ID AS SessionId, IDUser AS UserId, Expire AS ExpiryDate, Data, Created AS CreatedAt, LastAction AS LastActionAt
FROM [dbo].[Sessions]
WHERE IDUser = @userId
ORDER BY LastAction DESC", new { userId }));
}
}
public async Task <Guid> CreateAsync(Guid userId, DateTime expiryDate)
{
using (var db = new MainDatabase().Get)
{
return(await db.QuerySingleAsync <Guid>(@"
INSERT INTO [dbo].[Sessions]
(ID, IDUser, Expire, Data)
OUTPUT INSERTED.ID
VALUES (NEWID(), @userId, @expiryDate, @data)", new { userId, expiryDate, data = new byte[0] }));
}
}
public async Task <bool> RemoveByUserIdAsync(Guid userId)
{
using (var db = new MainDatabase().Get)
{
var deletedId = await db.QueryFirstOrDefaultAsync <Guid>(@"
DELETE FROM [dbo].[Sessions]
OUTPUT DELETED.IDUser
WHERE IDUser = @userId", new { userId });
return(deletedId == userId);
}
}
public async Task <bool> UpdateAsync(UserSession <Guid, Guid> session)
{
using (var db = new MainDatabase().Get)
{
var updatedId = await db.QueryFirstOrDefaultAsync <Guid>(@"
UPDATE [dbo].[Sessions]
SET Expire = @ExpiryDate,
Data = @Data,
LastAction = @LastActionAt
OUTPUT INSERTED.ID
WHERE ID = @SessionId", session);
return(updatedId == session.SessionId);
}
}
public async Task <string> RegisterPasswordResetTokenAsync(Guid userId, string passwordResetToken)
{
using (var db = new MainDatabase().Get)
using (var transaction = db.BeginTransaction())
{
try
{
var expiryDate = DateTime.Now.Add(expiryLength);
await db.ExecuteAsync("DELETE FROM [dbo].[UserPasswordReset] WHERE UserId = @userId", new { userId }, transaction : transaction);
var generatedId = await db.QuerySingleAsync <Guid>("INSERT INTO [dbo].[UserPasswordReset] (UserId, ExpiryDate, ResetPasswordToken) OUTPUT INSERTED.Id VALUES (@userId, @expiryDate, @passwordResetToken)", new { userId, expiryDate, passwordResetToken }, transaction : transaction);
transaction.Commit();
return(generatedId.ToString());
}
catch (SqlException ex)
{
transaction.Rollback();
throw ex;
}
}
}