将存储过程中if条件中的SELECT语句返回到c#.net代码

本文关键字：返回 语句 代码 net SELECT 存储过程 存储 过程中 if 条件 | 更新日期: 2023-09-27 18:21:20

我是个新手。我正在尝试开发一个时间表项目，我有一个员工登录页面经理和开发人员都是一样的，当经理登录时，他/她必须查看员工时间表。我正在使用存储过程来区分经理和开发人员。。这是Sp:

ALTER Procedure sp_xyz
    @UserName nvarchar(100),
    @Password nvarchar(100)
as
Begin
    Declare @Count int
    Declare @ReturnCode int
    Select @Count = COUNT(EmployeeName) from tblEmployee
    where [EmployeeName] = @UserName and [Password] = @Password
    if(@Count = 1)
    Begin
        --Select 1 as ReturnCode
        exec @ReturnCode = sp_Abc @UserName, @Password
        return 100
    End
    Else
    Begin
        Select -111 as ReturnCode
    End
End 
ALTER PROCEDURE sp_Abc
    @UserName as nvarchar(200),
    @Password as nvarchar(200)
AS
BEGIN
    Declare @Role as nvarchar(200)
    SELECT @Role = Emp.Role From tblEmployee Emp
    Where Emp.EmployeeName = @UserName and Emp.Password = @Password
    if (@Role = 'Manager')
    Begin
        select
            EmployeeName,
            ProjectName,
            Sunday,
            Monday,
            Tuesday,
            Wednesday,
            Thursday,
            Friday,
            Saturday,
            Week
        from
            tblEmployee
            inner join
                tblProject
            on
                tblProject.ProjectId = tblTimeSheet.ProjectId
            inner join tblTimeSheet
            on
                tblEmployee.EmployeeId = tblTimeSheet.EmployeeId
end
else
    return 10
end

我的代码：

private bool AuthenticateUser(string username, string password)
    {
        string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
        using (SqlConnection con = new SqlConnection(CS))
        {
            SqlCommand cmd = new SqlCommand("spAuthenticateUsersWhileLogin", con);
            cmd.CommandType = CommandType.StoredProcedure;
            // FormsAuthentication is in System.Web.Security
            string EncryptedPassword = FormsAuthentication.HashPasswordForStoringInConfigFile(password, "SHA1");
            cmd.Parameters.AddWithValue("@UserName", username);
            cmd.Parameters.AddWithValue("@Password", EncryptedPassword);
            var returnParam = new SqlParameter
            {
                ParameterName = "@return",
                Direction = ParameterDirection.ReturnValue
            };
            cmd.Parameters.Add(returnParam);
            con.Open();
            cmd.ExecuteNonQuery();
            var ReturnCode = (int)returnParam.Value;
            if (ReturnCode == 100)
            {
                return true; 
            }
            else
            {
                return false;
            }
        }
    }

我弄错了。。当我以开发人员的身份登录时，我可以查看我不想查看的其他时间表。我不知道我哪里出了问题。。。提前谢谢。

将存储过程中if条件中的SELECT语句返回到c#.net代码

您需要将登录逻辑与角色逻辑分离因为现在（至少从你向我们展示的情况来看）您在C#代码中检查的唯一一件事是用户存在于基本中

 var ReturnCode = (int)returnParam.Value;
        if (ReturnCode == 100)
        {
            return true; 
        }
        else
        {
            return false;
        }

所以在这之后你需要做的是调用第二个存储过程

  //.....call of sp_Abc
 if (ReturnCodeAbc == 10) //10 means is not manager
        {
            // do some logic 
        }
        else
        {
           //do the manager stuff
        }
相关文章：