public async Task <PagedApiResourcePropertyDto> GetPagedAsync(int apiResourceId, int page, int pageSize)
{
var result = new PagedApiResourcePropertyDto();
IDbSession session = DbSession;
var apiResourceQuery = new Query(AttributeExtension.GetTableAttributeName <Core.Entities.ApiResource.ApiResource>()).Select("Id", "Name").Where("Id", "=", apiResourceId);
var apiResourceSqlResult = GetSqlResult(apiResourceQuery);
var apiResource = await session.Connection.QueryFirstOrDefaultAsync <Core.Entities.ApiResource.ApiResource>(apiResourceSqlResult.Sql, apiResourceSqlResult.NamedBindings);
try
{
if (apiResource != null)
{
result.ApiResourceId = apiResource.Id;
result.ApiResourceName = apiResource.Name;
var totalCountQuery = new Query(TableName).AsCount();
var resultQuery = new Query(TableName).Select("*");
if (apiResourceId > 0)
{
totalCountQuery = totalCountQuery.Where("ApiResourceId", "=", apiResourceId);
resultQuery = resultQuery.Where("ApiResourceId", "=", apiResourceId);
}
var totalCountSqlResult = GetSqlResult(totalCountQuery);
resultQuery = resultQuery.OrderByDesc("Id").Offset((page - 1) * pageSize).Limit(pageSize);
var clientSecretSqlResult = GetSqlResult(resultQuery);
result.TotalCount = await session.Connection.QueryFirstOrDefaultAsync <int>(totalCountSqlResult.Sql,
totalCountSqlResult.NamedBindings);
result.DataPagedList =
(await session.Connection.QueryAsync <ApiResourcePropertyForPage>(clientSecretSqlResult.Sql,
clientSecretSqlResult.NamedBindings)).ToList();
result.PageSize = pageSize;
}
return(result);
}
finally
{
session.Dispose();
}
}
public async Task <bool> IsUserClaimExistsAsync(int userId, string claimType, string claimValue)
{
IDbSession session = DbSession;
var query = new Query(AttributeExtension.GetTableAttributeName <UserClaim>()).Select("*")
.Where("UserId", "=", userId)
.Where("ClaimType", "=", claimType)
.Where("ClaimValue", "=", claimValue);
var sqlResult = GetSqlResult(query);
var result = (await session.Connection.QueryFirstOrDefaultAsync <UserClaim>(sqlResult.Sql, sqlResult.NamedBindings)) != null;
session.Dispose();
return(result);
}
public override async Task <int> InsertAsync(Core.Entities.IdentityResource.IdentityResource entity, bool useTransaction = false, int?commandTimeout = null)
{
IDbSession session = DbSession;
IDbTransaction transaction = session.BeginTrans();
try
{
var insertResult = await session.Connection.InsertAsync(entity, transaction);
if (insertResult > 0 && entity.UserClaims.Any())
{
var claimsColumns = new[]
{
"Type", "IdentityResourceId"
};
var insertClaimsQuery = new Query(AttributeExtension.GetTableAttributeName <IdentityResourceClaim>())
.AsInsert(
claimsColumns, entity.UserClaims.Select(x => new object[]
{
x.Type,
insertResult
}));
var insertClaimsSqlResult = GetSqlResult(insertClaimsQuery);
await session.Connection.ExecuteAsync(insertClaimsSqlResult.Sql,
insertClaimsSqlResult.NamedBindings, transaction);
}
transaction.Commit();
return(insertResult);
}
catch (Exception ex)
{
transaction.Rollback();
Log.Error($"IdentityResourceRepository >> InsertAsync Error: {ex.Message}");
return(0);
}
finally
{
session.Dispose();
}
}
public async Task <PagedUserRoleDto> GetPagedUserRoleAsync(int userId, int page, int pageSize)
{
var result = new PagedUserRoleDto();
IDbSession session = DbSession;
var userQuery = new Query(TableName).Select("Id", "Name").Where("Id", "=", userId);
var userSqlResult = GetSqlResult(userQuery);
var user = await session.Connection.QueryFirstOrDefaultAsync <Core.Entities.Users.User>(userSqlResult.Sql, userSqlResult.NamedBindings);
try
{
if (user != null)
{
result.UserId = user.Id;
result.UserName = user.Name;
var totalCountQuery = new Query($"{TableName} as u")
.Join($"{AttributeExtension.GetTableAttributeName<UserRoleMapping>()} as urm", "u.Id", "urm.UserId")
.Join($"{AttributeExtension.GetTableAttributeName<Core.Entities.Users.Role>()} as r", "urm.RoleId", "r.Id")
.Where("u.Id", "=", userId).AsCount();
var resultQuery = new Query($"{TableName} as u")
.Join($"{AttributeExtension.GetTableAttributeName<UserRoleMapping>()} as urm", "u.Id", "urm.UserId")
.Join($"{AttributeExtension.GetTableAttributeName<Core.Entities.Users.Role>()} as r", "urm.RoleId", "r.Id")
.Select("u.Id as UserId", "u.Name as UserName", "r.Id as RoleId", "r.Name as RoleName").Where("u.Id", "=", userId);
var totalCountSqlResult = GetSqlResult(totalCountQuery);
resultQuery = resultQuery.OrderByDesc("u.Id").Offset((page - 1) * pageSize).Limit(pageSize);
var clientSecretSqlResult = GetSqlResult(resultQuery);
result.TotalCount = await session.Connection.QueryFirstOrDefaultAsync <int>(totalCountSqlResult.Sql, totalCountSqlResult.NamedBindings);
result.DataPagedList = (await session.Connection.QueryAsync <UserRoleForPage>(clientSecretSqlResult.Sql, clientSecretSqlResult.NamedBindings)).ToList();
result.PageSize = pageSize;
}
return(result);
}
finally
{
session.Dispose();
}
}
public async Task <UserRoleDto> GetUserRoleByAsync(int userId, int roleId)
{
IDbSession session = DbSession;
var query = new Query($"{TableName} as u")
.Join($"{AttributeExtension.GetTableAttributeName<UserRoleMapping>()} as urm", "u.Id", "urm.UserId")
.Join($"{AttributeExtension.GetTableAttributeName<Core.Entities.Users.Role>()} as r", "urm.RoleId", "r.Id")
.Select("u.Id as UserId", "u.Name as UserName", "r.Id as RoleId", "r.Name as RoleName")
.Where("u.Id", "=", userId).Where("r.Id", "=", roleId);
var sqlResult = GetSqlResult(query);
var result = await session.Connection.QueryFirstOrDefaultAsync <UserRoleDto>(sqlResult.Sql, sqlResult.NamedBindings);
session.Dispose();
return(result);
}
public override async Task <Core.Entities.IdentityResource.IdentityResource> GetAsync(int id, bool useTransaction = false, int?commandTimeout = null)
{
IDbSession session = DbSession;
var query = new Query(TableName).Select("*").Where("Id", "=", id);
var sqlResult = GetSqlResult(query);
var identityResource = await session.Connection.QueryFirstOrDefaultAsync <Core.Entities.IdentityResource.IdentityResource>(sqlResult.Sql, sqlResult.NamedBindings);
if (identityResource.Id > 0)
{
var claimsQuery = new Query(AttributeExtension.GetTableAttributeName <IdentityResourceClaim>()).Select("Type").Where("IdentityResourceId", "=", identityResource.Id);
var claimsSqlResult = GetSqlResult(claimsQuery);
identityResource.UserClaims = (await session.Connection.QueryAsync <IdentityResourceClaim>(claimsSqlResult.Sql, claimsSqlResult.NamedBindings)).ToList();
}
session.Dispose();
return(identityResource);
}
public async Task <List <Core.Entities.IdentityResource.IdentityResource> > GetIdentityResourcesByScopeNameAsync(string[] scopeNames)
{
IDbSession session = DbSession;
var query = new Query(TableName).Select("*");
if (scopeNames != null && scopeNames.Any())
{
query = query.WhereIn("Name", scopeNames);
}
var sqlResult = GetSqlResult(query);
var result = (await session.Connection.QueryAsync <Core.Entities.IdentityResource.IdentityResource>(sqlResult.Sql, sqlResult.NamedBindings)).ToList();
if (result.Any())
{
var identityResourceIds = result.Select(x => x.Id).ToArray();
var claimQuery = new Query(AttributeExtension.GetTableAttributeName <IdentityResourceClaim>()).Select("*").WhereIn("IdentityResourceId", identityResourceIds);
var claimSqlResult = GetSqlResult(claimQuery);
var propertyQuery = new Query(AttributeExtension.GetTableAttributeName <IdentityResourceProperty>()).Select("*").WhereIn("IdentityResourceId", identityResourceIds);
var propertySqlResult = GetSqlResult(propertyQuery);
var multi = await session.Connection.QueryMultipleAsync($"{claimSqlResult.Sql};{propertySqlResult.Sql}", claimSqlResult.NamedBindings);
var claims = (await multi.ReadAsync <IdentityResourceClaim>()).ToList();
var properties = (await multi.ReadAsync <IdentityResourceProperty>()).ToList();
result.ForEach(x =>
{
x.UserClaims = claims.Where(y => y.IdentityResourceId == x.Id).ToList();
x.Properties = properties.Where(y => y.IdentityResourceId == x.Id).ToList();
});
}
return(result);
}
public async Task <int> InsertUserRoleAsync(int userId, int roleId)
{
var query = new Query(AttributeExtension.GetTableAttributeName <UserRoleMapping>()).AsInsert(new
{
UserId = userId,
RoleId = roleId
});
var sqlResult = GetSqlResult(query);
try
{
var insertedResult = await ExecuteAsync(sqlResult.Sql, sqlResult.NamedBindings);
return(insertedResult);
}
catch (Exception ex)
{
Log.Error($"UserRepository >> InsertUserRoleAsync Error: {ex.Message}");
return(0);
}
}
public async Task <PagedPersistedGrantByUserDto> GetPagedByUserAsync(string subjectId, int page, int pageSize)
{
var result = new PagedPersistedGrantByUserDto();
IDbSession session = DbSession;
try
{
var totalCountQuery = new Query($"{TableName} as p")
.Join($"{AttributeExtension.GetTableAttributeName<Core.Entities.Users.User>()} as u", "p.SubjectId", "u.SubjectId").AsCount();
var resultQuery = new Query($"{TableName} as p")
.Join($"{AttributeExtension.GetTableAttributeName<Core.Entities.Users.User>()} as u", "p.SubjectId", "u.SubjectId")
.Select("p.*", "u.Name as SubjectName");
if (!string.IsNullOrEmpty(subjectId))
{
totalCountQuery = totalCountQuery.Where("p.SubjectId", "=", subjectId);
resultQuery = resultQuery.Where("p.SubjectId", "=", subjectId);
}
var totalCountSqlResult = GetSqlResult(totalCountQuery);
resultQuery = resultQuery.OrderByDesc("p.Expiration").Offset((page - 1) * pageSize).Limit(pageSize);
var clientSecretSqlResult = GetSqlResult(resultQuery);
result.TotalCount = await session.Connection.QueryFirstOrDefaultAsync <int>(totalCountSqlResult.Sql, totalCountSqlResult.NamedBindings);
result.DataPagedList = (await session.Connection.QueryAsync <PersistedGrantByUserForPage>(clientSecretSqlResult.Sql, clientSecretSqlResult.NamedBindings)).ToList();
result.PageSize = pageSize;
return(result);
}
finally
{
session.Dispose();
}
}
public async Task <PagedPersistedGrantDto> GetPagedAsync(string search, int page, int pageSize)
{
var result = new PagedPersistedGrantDto();
IDbSession session = DbSession;
try
{
//var subjectIdQuery = new Query($"{TableName} as p")
// .LeftJoin($"{AttributeExtension.GetTableAttributeName<User>()} as u", "p.SubjectId", "u.SubjectId")
// .Select("p.SubjectId")
// .GroupBy("p.SubjectId");
//var subjectIdSqlResult = GetSqlResult(subjectIdQuery);
//var subjectIds = (await session.Connection.QueryAsync<string>(subjectIdSqlResult.Sql, subjectIdSqlResult.NamedBindings)).ToList();
//if (!subjectIds.Any())
//{
// return new PagedPersistedGrantDto();
//}
// string subjectIdString = string.Join(",", subjectIds);
//var totalCountQuery = new Query().FromRaw($"STRING_SPLIT('{subjectIdString}',',')")
// .Join($"{TableName} as p", "value", "p.SubjectId")
// .LeftJoin($"{AttributeExtension.GetTableAttributeName<User>()} as u", "p.SubjectId", "u.SubjectId")
// .AsCount();
//var resultQuery = new Query().FromRaw($"STRING_SPLIT('{subjectIdString}',',')")
// .Join($"{TableName} as p", "value", "p.SubjectId")
// .LeftJoin($"{AttributeExtension.GetTableAttributeName<User>()} as u", "p.SubjectId", "u.SubjectId")
// .Select("p.*", "u.Name as SubjectName");
var withQuery = new Query($"{TableName} as p")
.LeftJoin($"{AttributeExtension.GetTableAttributeName<Core.Entities.Users.User>()} as u", "p.SubjectId", "u.SubjectId")
.Select("p.*", "u.Name as SubjectName")
.SelectRaw("ROW_NUMBER() OVER (PARTITION BY p.ClientId ORDER BY p.Expiration DESC) AS ROW_NUMBER");
var totalCountQuery = new Query("Grants").With("Grants", withQuery).Where("Grants.ROW_NUMBER", "=", 1).AsCount();
var resultQuery = new Query("Grants").With("Grants", withQuery).Where("Grants.ROW_NUMBER", "=", 1).Select("*");
if (!string.IsNullOrEmpty(search))
{
totalCountQuery = totalCountQuery.WhereContains("p.SubjectId", search).OrWhereContains("u.Name", search);
resultQuery = resultQuery.WhereContains("p.SubjectId", search).OrWhereContains("u.Name", search);
}
var totalCountSqlResult = GetSqlResult(totalCountQuery);
resultQuery = resultQuery.OrderByDesc("Expiration").Offset((page - 1) * pageSize).Limit(pageSize);
var clientSecretSqlResult = GetSqlResult(resultQuery);
result.TotalCount = await session.Connection.QueryFirstOrDefaultAsync <int>(totalCountSqlResult.Sql, totalCountSqlResult.NamedBindings);
result.DataPagedList = (await session.Connection.QueryAsync <PersistedGrantForPage>(clientSecretSqlResult.Sql, clientSecretSqlResult.NamedBindings)).ToList();
result.PageSize = pageSize;
return(result);
}
finally
{
session.Dispose();
}
}
public override async Task <bool> UpdateAsync(Core.Entities.Clients.Client entity, bool useTransaction = false, int?commandTimeout = null)
{
IDbSession session = DbSession;
session.BeginTrans();
IDbTransaction transaction = session.Transaction;
try
{
var updatedClientResult = await session.Connection.UpdateAsync(entity, transaction);
if (updatedClientResult)
{
if (entity.AllowedGrantTypes.Any())
{
string grantTypeTableName = AttributeExtension.GetTableAttributeName <ClientGrantType>();
var grantTypeColumns = new[]
{
"GrantType", "ClientId"
};
await UpdateItems(session, transaction, entity.Id, grantTypeTableName, grantTypeColumns,
entity.AllowedGrantTypes.Select(x => x.GrantType));
}
if (entity.AllowedScopes.Any())
{
string scopeTableName = AttributeExtension.GetTableAttributeName <ClientScope>();
var scopeColumns = new[]
{
"Scope", "ClientId"
};
await UpdateItems(session, transaction, entity.Id, scopeTableName, scopeColumns,
entity.AllowedScopes.Select(x => x.Scope));
}
if (entity.RedirectUris.Any())
{
string redirectUriTableName = AttributeExtension.GetTableAttributeName <ClientRedirectUri>();
var redirectUriColumns = new[]
{
"RedirectUri", "ClientId"
};
await UpdateItems(session, transaction, entity.Id, redirectUriTableName, redirectUriColumns,
entity.RedirectUris.Select(x => x.RedirectUri));
}
if (entity.PostLogoutRedirectUris.Any())
{
string postLogoutRedirectUriTableName = AttributeExtension.GetTableAttributeName <ClientPostLogoutRedirectUri>();
var postLogoutRedirectUriColumns = new[]
{
"PostLogoutRedirectUri", "ClientId"
};
await UpdateItems(session, transaction, entity.Id, postLogoutRedirectUriTableName,
postLogoutRedirectUriColumns,
entity.PostLogoutRedirectUris.Select(x => x.PostLogoutRedirectUri));
}
if (entity.IdentityProviderRestrictions.Any())
{
string identityProviderRestrictionsTableName = AttributeExtension.GetTableAttributeName <ClientIdPRestriction>();
var identityProviderRestrictionsColumns = new[]
{
"Provider", "ClientId"
};
await UpdateItems(session, transaction, entity.Id, identityProviderRestrictionsTableName,
identityProviderRestrictionsColumns,
entity.IdentityProviderRestrictions.Select(x => x.Provider));
}
if (entity.AllowedCorsOrigins.Any())
{
string allowedCorsOriginsTableName = AttributeExtension.GetTableAttributeName <ClientCorsOrigin>();
var allowedCorsOriginsColumns = new[]
{
"Origin", "ClientId"
};
await UpdateItems(session, transaction, entity.Id, allowedCorsOriginsTableName,
allowedCorsOriginsColumns, entity.AllowedCorsOrigins.Select(x => x.Origin));
}
}
transaction.Commit();
return(true);
}
catch (Exception ex)
{
transaction.Rollback();
Log.Error($"ClientRepository >> UpdateAsync Error: {ex.Message}");
return(false);
}
finally
{
session.Dispose();
}
}
public async Task <List <Core.Entities.ApiResource.ApiResource> > GetApiResourcesByScopeNameAsync(string[] scopeNames)
{
IDbSession session = DbSession;
var resultQuery = new Query($"{TableName} as t")
.Join($"{AttributeExtension.GetTableAttributeName<ApiResourceScope>()} as t1", "t.Id", "t1.ApiResourceId")
.Select("t.*");
if (scopeNames != null && scopeNames.Any())
{
resultQuery = resultQuery.WhereIn("t1.Scope", scopeNames);
}
var resultSqlResult = GetSqlResult(resultQuery);
try
{
List <Core.Entities.ApiResource.ApiResource> result = (await session.Connection.QueryAsync <Core.Entities.ApiResource.ApiResource>(resultSqlResult.Sql, resultSqlResult.NamedBindings)).ToList();
if (result.Any())
{
var apiResourceIds = result.Select(x => x.Id).Distinct().ToArray();
var secretQuery = new Query(AttributeExtension.GetTableAttributeName <ApiResourceSecret>()).Select("*").WhereIn("ApiResourceId", apiResourceIds);
var secretSqlResult = GetSqlResult(secretQuery);
var secretResult = await session.Connection.QueryAsync <ApiResourceSecret>(secretSqlResult.Sql,
secretSqlResult.NamedBindings);
var scopeQuery = new Query(AttributeExtension.GetTableAttributeName <ApiResourceScope>()).Select("*").WhereIn("ApiResourceId", apiResourceIds);
var scopeSqlResult = GetSqlResult(scopeQuery);
var scopeResult =
await session.Connection.QueryAsync <ApiResourceScope>(scopeSqlResult.Sql,
scopeSqlResult.NamedBindings);
var userClaimQuery = new Query(AttributeExtension.GetTableAttributeName <ApiResourceClaim>()).Select("*").WhereIn("ApiResourceId", apiResourceIds);
var userClaimSqlResult = GetSqlResult(userClaimQuery);
var userClaimResult =
await session.Connection.QueryAsync <ApiResourceClaim>(userClaimSqlResult.Sql,
userClaimSqlResult.NamedBindings);
var propertyQuery = new Query(AttributeExtension.GetTableAttributeName <ApiResourceProperty>()).Select("*").WhereIn("ApiResourceId", apiResourceIds);
var propertySqlResult = GetSqlResult(propertyQuery);
var propertyvResult =
await session.Connection.QueryAsync <ApiResourceProperty>(propertySqlResult.Sql,
propertySqlResult.NamedBindings);
result.ForEach(x =>
{
x.Secrets = secretResult.Where(y => y.ApiResourceId == x.Id).ToList();
x.Scopes = scopeResult.Where(y => y.ApiResourceId == x.Id).ToList();
x.UserClaims = userClaimResult.Where(y => y.ApiResourceId == x.Id).ToList();
x.Properties = propertyvResult.Where(y => y.ApiResourceId == x.Id).ToList();
});
}
return(result);
}
finally
{
session.Dispose();
}
}
