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(); } }