safal.786 Ответов: 2

Как получить несколько значений столбцов из БД в переменных сеанса при входе в систему


Я хочу получить UID, имя пользователя, город, адрес электронной почты из регистрационной таблицы в БД sql server при успешном входе пользователя.
Я хочу сохранить эти значения в переменных сеанса.



plz помогите мне с кодом и решением
ниже приведен мой код

Что я уже пробовал:

protected void ValidateUser(object sender, EventArgs e)
        {
            int userId = 0;
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("Validate_User"))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Username", txtUname.Text.Trim());
                    cmd.Parameters.AddWithValue("@Password", txtPassword.Text.Trim());
                    cmd.Connection = con;
                    con.Open();
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    userId = Convert.ToInt32(cmd.ExecuteScalar());
                    con.Close();
                    if (dt.Rows.Count > 0)
                    {
                        Session["uid"] = dt.Columns[0].ToString();
                        Session["Username"] = dt.Columns[1].ToString();
                        Session["city"] = dt.Columns[2].ToString();
                        Response.Redirect("Redirectform.aspx");
                        Session.RemoveAll();
                    }
                    else
                    {
                        lblmsg.Text = "You're username and word is incorrect";
                        lblmsg.ForeColor = System.Drawing.Color.Red;

                    }   
                }
                switch (userId)
                {
                    case -1:
                        lblmsg.Text = "Username and/or password is incorrect.";
                        break;
                    case -2:
                        lblmsg.Text = "Account has not been activated.";
                        break;
                    default:
                        //FormsAuthentication.RedirectFromLoginPage(Login1.UserName, Login1.RememberMeSet);
                        break;
                }
            }
        }



Это моя хранимая процедура .. проверенный пользователь

ALTER  PROCEDURE [dbo].[Validate_User]
    @Username NVARCHAR(20),
    @Password NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @UserId INT, @LastLoginDate DATETIME
    
    SELECT @UserId = UserId, @LastLoginDate = LastLoginDate
    FROM Users WHERE Username = @Username AND [Password] = @Password
    
    IF @UserId IS NOT NULL
    BEGIN
        IF NOT EXISTS(SELECT UserId FROM UserActivation WHERE UserId = @UserId)
        BEGIN
            UPDATE Users
            SET LastLoginDate =  GETDATE()
            WHERE UserId = @UserId
            SELECT @UserId [UserId] -- User Valid
        END
        ELSE
        BEGIN
            SELECT -2 -- User not activated.
        END
    END
    ELSE
    BEGIN
        SELECT -1 -- User invalid.
    END
END

2 Ответов

Рейтинг:
11

Ali Majed HA

Привет
План Отделенного Кода:

protected void Page_Load(object sender, EventArgs e)
        {
            ProductList();

            MasterDCDataContext db = new MasterDCDataContext();
            var q = db.MOptions;
            lvAboutus.DataSource = q;
            lvAboutus.DataBind();

            lvAddress.DataSource = q;
            lvAddress.DataBind();

            var p = db.Posts;
            lvFooterNews.DataSource = p.OrderByDescending(c => c.PoID).Take(8);
            lvFooterNews.DataBind();

            var t = Session["LoginID"];
            mvLogin.ActiveViewIndex = Convert.ToInt32(t);
            int role = Convert.ToInt32(t);

            var m = Session["UserID"];
            int Us = Convert.ToInt32(m);

            if (Us >= 1)
            {
                string strConnection = "Data Source =Ali-HP;Database=EzBuy;Integrated Security=yes";
                using (SqlConnection Connection = new SqlConnection(strConnection))
                {
                    String strQuery = "SELECT * FROM [User] WHERE UsID = " + Us + " ";
                    SqlCommand Command = new SqlCommand(strQuery,Connection);
                    Command.Connection.Open();
                    SqlDataReader rdr = Command.ExecuteReader();
                    rdr.Read();
                    switch(role)
                    {
                        case 1: lblUserName.Text = rdr[0].ToString() + " " + rdr[1].ToString();
                            break;
                        case 2: lblAdminName.Text = rdr[0].ToString() + " " + rdr[1].ToString();
                            break;
                    }
                    Command.Connection.Close();
                }
            }

        }

        protected void ValidateUser(object sender, EventArgs e)
        {
            int userId = 0;
            string roleId = "";
            string roles = string.Empty;
            string strConnection = "Data Source =Ali-HP;Database=EzBuy;Integrated Security=yes";
            using (SqlConnection Connection = new SqlConnection(strConnection))
            {
                using (SqlCommand Command = new SqlCommand("ValidateLogin"))
                {
                    Command.CommandType = CommandType.StoredProcedure;
                    Command.Parameters.AddWithValue("@Username", loginBox.UserName);
                    Command.Parameters.AddWithValue("@Password", loginBox.Password);
                    Command.Connection = Connection;
                    Command.Connection.Open();
                    SqlDataReader rdr = Command.ExecuteReader();
                    rdr.Read();
                    userId = Convert.ToInt32(rdr["UserId"].ToString());
                    roleId = rdr["Roles"].ToString();
                    Command.Connection.Close();
                }
                switch (userId)
                {
                    case -1:
                        loginBox.FailureText = "Invalid UserName/Password";
                        break;
                    case -2:
                        loginBox.FailureText = "InActive Account";
                        break;
                    default:
                        switch(roleId)
                        {
                            case "User":
                                Session["LoginID"] = 1;
                                Session["UserID"] = userId;
                                Response.Redirect("UHome.aspx");
                                break;
                            case "Admin":
                                Session["LoginID"] = 2;
                                Session["UserID"] = userId;
                                Response.Redirect("UHome.aspx");
                                break;
                        }
                        break;
                }
            }
        }


Процедура:

USE [EzBuy]
GO
/****** Object:  StoredProcedure [dbo].[ValidateLogin]    Script Date: 2016-10-06 13:17:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ValidateLogin]
      @Username NVARCHAR(50),
      @Password NVARCHAR(50)
AS
BEGIN
      SET NOCOUNT ON;
      DECLARE @UserID INT, @LastLoginDate DATETIME, @RoleId INT
      
      SELECT @UserID = UserID, @LastLoginDate = UsLastLogin,  @RoleId = RoleId
      FROM Login WHERE UserName = @Username AND UPassword = @Password
      
      IF @UserId IS NOT NULL
      BEGIN
            IF  EXISTS(SELECT UserID FROM Login WHERE UserID = @UserID)
            BEGIN
                  UPDATE Login
                  SET UsLastLogin = GETDATE()
                  WHERE UserID = @UserID
                  
                  SELECT @UserID as UserID,
                              (SELECT RoleName FROM RoleChart
                               WHERE RoleId = @RoleId) [Roles]
            END
            ELSE
            BEGIN
                  SELECT -2 as UserID, '' [Roles]-- User not activated.
            END
      END
      ELSE
      BEGIN
            SELECT -1 as UserID, '' [Roles] -- User invalid.
      END
END


надеюсь, это сработает
с уважением


Рейтинг:
0

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Вы все смешиваете. Я бы предложил вернуть все данные в одном операторе select и использовать DataReader для чтения значений и присвоения Session.