public async Task <IdentityResult> CreateAsync(ApplicationUser user)
{
var command = "INSERT INTO public.\"Users\" " +
" (" +
$"\"{nameof(ApplicationUser.Id)}\", " +
$"\"{nameof(ApplicationUser.UserName)}\", " +
$"\"{nameof(ApplicationUser.NormalizedUserName)}\", " +
$"\"{nameof(ApplicationUser.Email)}\", " +
$"\"{nameof(ApplicationUser.NormalizedEmail)}\", " +
$"\"{nameof(ApplicationUser.EmailConfirmed)}\", " +
$"\"{nameof(ApplicationUser.PasswordHash)}\", " +
$"\"{nameof(ApplicationUser.SecurityStamp)}\", " +
$"\"{nameof(ApplicationUser.ConcurrencyStamp)}\", " +
$"\"{nameof(ApplicationUser.PhoneNumber)}\", " +
$"\"{nameof(ApplicationUser.PhoneNumberConfirmed)}\", " +
$"\"{nameof(ApplicationUser.TwoFactorEnabled)}\", " +
$"\"{nameof(ApplicationUser.LockoutEnd)}\", " +
$"\"{nameof(ApplicationUser.LockoutEnabled)}\", " +
$"\"{nameof(ApplicationUser.AccessFailedCount)}\") " +
"VALUES (@Id, @UserName, @NormalizedUserName, @Email, @NormalizedEmail, @EmailConfirmed, @PasswordHash, @SecurityStamp, @ConcurrencyStamp, " +
"@PhoneNumber, @PhoneNumberConfirmed, @TwoFactorEnabled, @LockoutEnd, @LockoutEnabled, @AccessFailedCount);";
int rowsInserted;
using (var sqlConnection = await _context.CreateConnectionAsync())
{
rowsInserted = await sqlConnection.ExecuteAsync(command, new
{
user.Id,
user.UserName,
user.NormalizedUserName,
user.Email,
user.NormalizedEmail,
user.EmailConfirmed,
user.PasswordHash,
user.SecurityStamp,
user.ConcurrencyStamp,
user.PhoneNumber,
user.PhoneNumberConfirmed,
user.TwoFactorEnabled,
user.LockoutEnd,
user.LockoutEnabled,
user.AccessFailedCount
});
}
return(rowsInserted == 1
? IdentityResult.Success
: IdentityResult.Failed(new IdentityError
{
Code = nameof(CreateAsync),
Description = $"User with Email {user.Email} could not be inserted."
}));
}
public async Task <IList <UserLoginInfo> > GetLoginsAsync(ApplicationUser user)
{
var command = "SELECT * " +
"FROM public.\"UserLogins\" " +
$"WHERE \"{nameof(UserLogin.UserId)}\" = @UserId;";
using (var sqlConnection = await _context.CreateConnectionAsync())
{
return((
await sqlConnection.QueryAsync <UserLogin>(command, new { UserId = user.Id })
)
.Select(x => new UserLoginInfo(x.LoginProvider, x.ProviderKey, x.ProviderDisplayName))
.ToList());
;
}
}
public async Task <IdentityResult> CreateAsync(ApplicationRole role)
{
var command = "INSERT INTO public.\"Roles\" " +
"(" +
$"\"{nameof(ApplicationRole.Id)}\"," +
$"\"{nameof(ApplicationRole.Name)}\", " +
$"\"{nameof(ApplicationRole.NormalizedName)}\", " +
$"\"{nameof(ApplicationRole.ConcurrencyStamp)}\") " +
"VALUES (@Id, @Name, @NormalizedName, @ConcurrencyStamp);";
int rowsInserted;
using (var sqlConnection = await _context.CreateConnectionAsync())
{
rowsInserted = await sqlConnection.ExecuteAsync(command, new
{
role.Id,
role.Name,
role.NormalizedName,
role.ConcurrencyStamp
});
}
return(rowsInserted == 1
? IdentityResult.Success
: IdentityResult.Failed(new IdentityError
{
Code = string.Empty,
Description = $"The role with name {role.Name} could not be inserted."
}));
}
public async Task <IList <Claim> > GetClaimsAsync(Guid roleId)
{
var command = @"SELECT * " +
"FROM public.\"RoleClaims\" " +
$"WHERE \"{nameof(RoleClaim.RoleId)}\" = @RoleId;";
IEnumerable <RoleClaim> roleClaims = new List <RoleClaim>();
using (var sqlConnection = await _context.CreateConnectionAsync())
{
return((
await sqlConnection.QueryAsync <RoleClaim>(command, new { RoleId = roleId })
)
.Select(x => new Claim(x.ClaimType, x.ClaimValue))
.ToList());
}
}