基于角色的身份验证
本文关键字:身份验证 角色 于角色 | 更新日期: 2023-09-27 18:16:16
我正在尝试使用角色创建身份验证。我已经在SQL Server中创建了一个表Users和一个存储过程,如果user是有效的,则返回UserId和UserRole,如下所示。
CREATE TABLE [dbo].[Users]
([UserID] [int] IDENTITY(1,1) NOT NULL,
[Username] [nvarchar](20) NOT NULL,
[Password] [nvarchar](20) NOT NULL,
[Email] [nvarchar](30) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[LastLoginDate] [datetime] NULL,
[UserRole] [nvarchar](20) NULL
)
CREATE PROCEDURE [dbo].[Validate_User]
@Username NVARCHAR(20),
@Password NVARCHAR(20),
@UserRole NVARCHAR(20) OUTPUT,
@UserId INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @LastLoginDate DATETIME
SELECT @UserId = UserId, @LastLoginDate = LastLoginDate, @UserRole=UserRole
FROM Users WHERE Username = @Username AND [Password] = @Password
IF @UserId IS NOT NULL
BEGIN
UPDATE Users
SET LastLoginDate = GETDATE()
WHERE UserId = @UserId
SELECT @UserId [UserId], @UserRole [UserRole]
END
ELSE
BEGIN
SELECT @UserId=-1
END
END
这是asp.net中验证用户的代码:
protected void ValidateUser(object sender, EventArgs e)
{
int userId = 0;
string constr = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("Validate_User"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Username", Login1.UserName);
cmd.Parameters.AddWithValue("@Password", Login1.Password);
cmd.Parameters.Add("@UserRole", SqlDbType.NVarChar);
cmd.Parameters["@UserRole"].Direction = ParameterDirection.Output;
cmd.Parameters.AddWithValue("@UserId", SqlDbType.Int);
cmd.Parameters["@UserId"].Direction = ParameterDirection.Output;
cmd.Connection = con;
con.Open();
userId = Convert.ToInt32(cmd.Parameters["@UserId"].Value);
Session["userRole"] = Convert.ToString(cmd.Parameters["@UserRole"].Value);
Session["userId"]=Convert.ToInt32(cmd.Parameters["@UserId"].Value);
con.Close();
}
switch (userId)
{
case -1:
Login1.FailureText = "Username and/or password is incorrect.";
break;
default:
FormsAuthentication.RedirectFromLoginPage(Login1.UserName, Login1.RememberMeSet);
break;
}
}
}
问题是UserRole总是返回为null, UserId返回一个无效的身份(例如UserId= 1在db上返回为UserId=8)注意,当我执行存储过程时,我得到了正确的结果。
提前谢谢你。
在读取输出参数之前使用以下命令执行存储过程:
cmd.ExecuteNonQuery();