Click here to Skip to main content
14,696,571 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
Hi. I need to call aspnet_UsersInRoles_IsUserInRole from Aspnet Membership.Im making dapper call like this:

Best Regards.

What I have tried:

public int CheckIfUserIsInRole(IsUserInRole userInRole)
    {
        using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString()))
        {
            DynamicParameters param = new DynamicParameters();
            param.Add("@UserName", userInRole.UserName);
            param.Add("@ApplicationName", userInRole.ApplicationName);
            param.Add("@RoleName", userInRole.RoleName);

         return    connection.Query("aspnet_UsersInRoles_IsUserInRole", param, commandType: CommandType.StoredProcedure).FirstOrDefault();               
        }
    }


And in controller to test it I add:

public int IsUserInRole(IsUserInRole isUserInRole)
    {            
        var model = _userRepository.CheckIfUserIsInRole(new IsUserInRole()
        {
            UserName = "testuser",
            RoleName = "user",
            ApplicationName = "USERMANAGEMENT"
        });

        return model;
    }


The user exist and have the correct role but every time returns 0.
I have tester with numerus users and i have try to pass bool value same as Execute and ExecutScalar but all the same the call return 0 all time for all users.
Here is the Stored Procedure from AspNet Membership:

USE [User2]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[aspnet_UsersInRoles_IsUserInRole]
    @ApplicationName  nvarchar(256),
    @UserName         nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(2)
    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL
    DECLARE @RoleId uniqueidentifier
    SELECT  @RoleId = NULL

    SELECT  @UserId = UserId
    FROM    dbo.aspnet_Users
    WHERE   LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId

    IF (@UserId IS NULL)
        RETURN(2)

    SELECT  @RoleId = RoleId
    FROM    dbo.aspnet_Roles
    WHERE   LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId

    IF (@RoleId IS NULL)
        RETURN(3)

    IF (EXISTS( SELECT * FROM dbo.aspnet_UsersInRoles WHERE  UserId = @UserId AND RoleId = @RoleId))
        RETURN(1)
    ELSE
        RETURN(0)
END


Where I'm mistaking? Any advice how to fix it ?

I need this Stored Procedure to check if the user is in that role so i can use it for [AuthorizeRoles("RoleTest")]
Posted
Updated 22-Jul-16 4:18am
Comments
F-ES Sitecore 22-Jul-16 9:28am
   
My advice is to not reinvent the wheel, use the membership\identity API to discover if an account is in a role.

1 solution

That stored procedure doesn't return any records; it uses the return value instead. This needs to be handled as a parameter:
public int CheckIfUserIsInRole(IsUserInRole userInRole)
{
    using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString()))
    {
        DynamicParameters param = new DynamicParameters();
        param.Add("@UserName", userInRole.UserName);
        param.Add("@ApplicationName", userInRole.ApplicationName);
        param.Add("@RoleName", userInRole.RoleName);
        param.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
        
        connection.Execute("aspnet_UsersInRoles_IsUserInRole", param, commandType: CommandType.StoredProcedure);
        
        return param.Get<int>("@ReturnValue");
    }
}

GitHub - StackExchange/dapper-dot-net: Dapper - a simple object mapper for .Net[^]

(Also posted to your copy of this question on StackOverflow[^].)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900