如何使用dapper ORM调用aspnet_usersinroles_isuserinrole存储过程 [英] How to call aspnet_usersinroles_isuserinrole stored procedure with dapper ORM

问题描述

您好。我需要从Aspnet Membership调用aspnet_UsersInRoles_IsUserInRole。我这样做的短小精致：



最好的问候。



我的尝试：

  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（）; 
} 
} 

在控制器中测试它我添加：

  public   int  IsUserInRole（IsUserInRole isUserInRole ）
 {
  var  model = _userRepository.CheckIfUserIsInRole（ new  IsUserInRole（）
 {
 UserName =   testuser，
 RoleName =   user，
 ApplicationName =   USERMANAGEMENT 
}）; 
 
 返回模型; 
} 

用户存在且角色正确但每次都返回0.

我有numerus用户的测试人员，我尝试传递与Execute和ExecutScalar相同的bool值，但所有用户的呼叫返回0都是一样的。

以下是AspNet成员的存储过程：

  USE  [User2] 
  GO  
 
  SET   ANSI_NULLS  < span class =code-keyword> 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 低ER（ @ ApplicationName ）= LoweredApplicationName 
  IF （ @ApplicationId   IS   NULL ）
  RETURN （ 2 ）
  DECLARE   @ UserId   uniqueidentifier  
  SELECT   @ UserId  =  NULL  
  DECLARE   @ RoleId   uniqueidentifier  
  SELECT   @ RoleId  =  NULL  
 
  SELECT  < span class =code-sdkkeywor d> @ 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（< span class =code-sdkkeyword> @ RoleName ） AND  ApplicationId =  @ ApplicationId  
 
  IF （ @ RoleId   IS   NULL ）
  RETURN （ 3 ）
 
  IF （ EXISTS （ SELECT  *  FROM  dbo.aspnet_UsersInRoles  WHERE  UserId =  @ UserId   AND  RoleId =  @ RoleId ））
 < span class =code-keyword> RETURN （ 1 ）
  ELSE  
  RETURN （ 0 ）
 结束 

我在哪里错了？有什么建议如何修复它？



我需要这个存储过程来检查用户是否在那个角色所以我可以将它用于[AuthorizeRoles（RoleTest） ]

解决方案

该存储过程不返回任何记录;它使用返回值代替。这需要作为参数处理：

  public   int  CheckIfUserIsInRole（IsUserInRole userInRole）
 {
 使用（ 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>（  @返回值）; 
} 
} 

GitHub - StackExchange / dapper-dot-net：Dapper - .Net的简单对象映射器 [ ^ ]



（也发布到您在StackOverflow上复制此问题 [ ^ ] 。）

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")]

解决方案

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[^].)

这篇关于如何使用dapper ORM调用aspnet_usersinroles_isuserinrole存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！